Replies

Or you could put a two-page tab control on one form and put a table on each tab. then it is as simple as clicking the tab to change tables.

You can copy an entire row and paste it into a new record by selecting the desired row using the record selector (the gray area to the far left if it shows on your form), copy, then select the record selector of the new record and paste.

You can select the records in two ways:
1) mouse click on the record selector of the records you are dealing with.

2) use “Shift-Spacebar” to select the current record. NOTE to use shift-spacebar in the current record, if that record has data in it, you need to have the entire field selected (that the cursor is in) for it to select the record. If the cursor is in the field between two characters then pressing shift-spacebar will insert a space and show a dirty-record symbol.

Also, once a record is seleced and copied, you can use the TAB key to move to the new (blank) record. Then just paste the copied record in. (ctrl-v).

If you are starting totally from scratch, I recommend one of the “access in 24 hours” type books.

Sure, using the column property you can set the second field value equal to any column of a chosen combobox row

FieldValue = ComboboxName.Column(Column#)

However, be aware that the FIRST column number is “0” , the second is “1”, the third is “2”, etc.

There is more to this property, so check it out in Help.

Thanks again. So what you seem to be saying is that all my queries would have to be re-written to take full advantage of SQL Server (sorry, I am just beginning to research this so I have read up on it yet). My result sets aren’t that big (single returns , tens of records, at most hundreds of records) but from the sounds of it I wouldn’t want to go through the trouble without optimizing it for the server software. Our returns aren’t all that slow right now on our present servers, but it could grow since we are getting into a lot of graphics storage (photos).

Thanks for the info. I’m not modifiying design of existing tables – everything works fine. Rather I am adding features (new table and forms and reports) and streamlining processes as I learn new ways of doing things in Access. This is a part -time job in addition to my normal duties and we add things as we find we want/need them. For our purposes this is a very nice advantage of using Access – instead of having to bring in a special programmer we can do it all ourselves at a huge time and cost savings. I am just wondering how moving to SQL server will affect that process. Sounds like it may negatively affect the ease of use.

Thanks for the clues!

Well, it has been fixed (fingers crossed). I didn’t think it was the code references since this version has been used successfully for a couple months. BUT, apparently there is something about the windows 2000 server software that highlighted some missing references. A couple months ago I had been playing with some ActiveX controls. I didn’t think anything of it and actually distributied the version with test forms with several of these controls on them. There were no problems and I had forgotten about that until this changeover to win200 server. The problem was that some machines had the dll’s needed for the activeX controls and some did not. Once I deleted the test form the problems cleared up.

If the info is unique to the customer, keep it in the same table as the customer ID along with customer name, etc. If it is info that could be common to other customers as well, put that in a separate table and use a relationship to link your customers to it.

As to form navigation, why not use the Tab control? This allows you to have several levels of info on one screen – each tab can have either native fields (to the underlying table or query) or subforms, or both. I use the tab control anytime I have a place where someone has to enter a lot of info related to a given record and I don’t want them to get lost going between different screens. The users love it.

If you don’t use a tab control you have to include some linking code to open the next screen to the same key data as the first screen. That isn’t to tricky to do (in fact the Access button control wiard will write the code for you), but it is tricky to figure out when someone tries to dissect your work at some future date.

Perfect, Thanks!

You’re right. I have made a custom contact DB for a person who often calls me out of the blue just to tell me how much they like having their own custom app. And it hardly requires even VB – I use Access or Filmaker Pro to do these things and use just a few lines of code where needed.

1. While the full names of all students appear in the look-up field, they appear in order of entry. Is there a way to make them appear alphabetically?

If this is a combobox or list box you can put ORDER BY with ASC or DESC after the SQL in the ROW SOURCE property of the control:
SELECT DISTINCTROW [tblTechCode].[TechCode], [tblTechCode].[TechName] FROM tblTechCode, ORDER BY [tblTechCode].[TechName] ASC;

2. Although the full names appear in the drop down list, only the first names actually appear in the parents table. How can I get both first and last name to appear?

Again assuming it’s a combo or list box, As you have it , it only takes the info from the first data column to put in your assigned field. You will have to set the other fields to take their info from the appropriate column of the combo/list box.

3. When I use mail merge in Word with my school database as the data source, the look-up fields come in as numbers, no names at all. How do I get names into my Word merge document?

You need to set the bound column to the column with the info you want entered in the table. By default the combo or list box wizard takes the first field on the left as the bound column, and that field is simply a sequential number of the number of items on the list. You can change that in the control’s property list to bind the column with the data field you want. You can make more than one field key off the combo or list box by setting the other fields data source equal to the appropriate column number from the combo/list box. Another method is that you can have your mailmerge run off a different query that combines related info from the appropriate tables.

Randy. this is normally used by two or three people at a time. The last line saves the record, which prevents anyone else from getting the same number. I originally had some required fields in the form but if the form is not saved a duplicate number can get assigned during the time someone is filling in the form, so I quit using required fields. So far it has worked fine.

Tim Morken
Atlanta

Here is some code I use for incrementing case numbers in a pathology lab database. These have a year designation before the incremented number, and the code accounts for the new year on Jan 1. It also inserts leading zeros. Hopefully I have commented it enough that you can figure it out. It is on a button on the form.

What this does is find the current max number and then increments it and pastes the new number in the case # field. All users must use this button to get a new number.

‘*********************code*************************
Private Sub NextIDP_Click()
‘Increments the IDP number. Accounts for change-over to a new year and starts with 0001.

Dim i As Integer ‘used to increment IDPNUM
Dim MaxNum As Variant ‘Maximum IDPNUM in tblSpecimenLog
Dim NewNum As Variant ‘New year number
Dim Zeros As Variant ‘Used to add leading zeros to final number (ie. 0001)

‘//Open new record
DoCmd.GoToRecord acDataForm, “SpecLog”, acNewRec

‘Get maximum IDPNUM number from tblSpecimenLog
MaxNum = DMax(“[IDPNUM]”, “tblSpecimenLog”)
‘MsgBox “MaxNum =” & MaxNum ‘debugging aid.

‘//TEST for year. If year is less than current year (as on Jan 1) then set the year to_
‘the current year and increment number. If year is equal to current year, then increment_
‘number. Left$(MaxNum,4) grabs the year part of the IDPNUM.

‘Check for year less than current year.
If Left$(MaxNum, 4) Year(Now()) Then
MsgBox “You cannot enter a future year. Please delete this number”

End If

‘//Set up leading zeros for incrementing part of number.
Zeros = IIf(i < 10, "000", IIf(i < 100, "00", IIf(i < 1000, "0", "")))

'//Set IDPNUM equal to new numbers
Forms!SpecLog!IDPNUM = NewNum & "-" & Zeros & Format(i)
'Set the cursor focus to the DASH field.
Forms!SpecLog!DASHNUM.SetFocus
'Save the record
DoCmd.RunCommand acCmdSaveRecord

End Sub
'************************end code************************************************

Here’s a test. Open Access. Open New Database. See what a customer can do with what is there without any development work on their part.

Obviously they can’t do anything, because there is nothing there.

Saying a developed Access application is not custom because the database engine is commercially available is like saying a cabinetmakers cabinets are not custom because the wood, screws, nails, glue, and tools to make them are all commercially available.

In other words, that tax person is stupid.

Thanks, I’ll try that (sorry to reply so late, I was out last week).