Automatically Increasing Number (Access 97)

Topic

New Reply

I have two Tables. One table holds a patient’s demographic data. The second holds the contact information. One of the fields in the Contact information is the [contact number]. In the data entry form, the record source is the Main Information table with the demographic details, then I have a subform for the contact information (because there can be more that 1 contact for a patient).

In the Contact Information subform, how can I automatically increase the Contact Number when a new contact is made? Currently I have a control button (Add a New Contact) and in the On Click event I have the following command “IIf([Main Information]![ID]=[Contact Information]![Contact ID],Sum([Contact Information]![Contact Number]+1),””)

([Main Information]![ID] is an autonumber and is linked to [Contact Information]![Contact ID] in a query).

The error message I’m getting is that Access can’t find the macro (then my iif statement)

Reply | Quote

Replies

I use the following code on the Addbutton OnClick event, after I add a new record:

[Forms]![yourformname]![yourtextboxname] = DMax(“[yourfieldname]”, “yourtablename”) + 1

I was a bit confused where your textbox is (Main or Subform) and why. Are you adding a new ContactID for the same ID? What is the difference between ContactID and ContactNumber in your IIf statement? None the less, if you reference the form and textbox correctly, the DMax statement will automatically give you the next highest number.

Reply | Quote

This textbox is in a subform.

Contact ID is the same number as the ID. I’ve linked 2 tables (Demographic Information and Contact Information) in a query. Each patient has a unique ID (Autonumber) and the contact information is linked to each client. The Contact ID is basically a placeholder for the Client ID number. I hope that makes sense.

I’ve tried the statement and nothing happens.

=DMax(“[Contact Information]![Contact Number]”,” Contact Information “)+1

What did I do wrong?

Reply | Quote

You need to apply the DMax to the table in which the records are saved. Look up the DMax there to create the next number.

Reply | Quote

Could you explain how to “Look up the DMax there to create the next number.” I don’t think I follow you.

Are you saying that in the Contact Information table where the Contact Number is stored that I should put the DMax statement in that table? If so, how do I do that?

Reply | Quote

No, but Your use of the bang (!) made me think you were trying to do it on a form. Try something like this in the code in your form:

NextNum = DMax(“[Contact Number]”,”Contact Information”)+1

Don’t put spaces around the table name the way you had in your version, because that will give you a runtime error.

Reply | Quote

When I enter NextNum = DMax(“[Contact Number]”,”Contact Information”)+1, I get an error saying that it can’t find the maco.

Where should I be entering this statement? Currently I’m entering it on the Contact Information subform, in the On Click event of the command button.

Reply | Quote

In that case, check the name of the table. If it doesn’t exactly match the table name in the database window, you’ll get that error. Also try running a Debug–>Compile from the Module code window and see if there’s something else that Access doesn’t like.

As to where, it depends on what you’re doing with it. Is the command button on the subform itself or on the parent form? What are you actually doing with the value, setting a textbox = NextNum, or what? Show us that code and someone will be able to help you with it.

Reply | Quote

do you have any other code in this onclick event
other than the dmax statement

Reply | Quote

No nothing. I’m trying to sent a sample of the database, however, I’m having trouble getting it small enough. I keep getting an error when I try to send it.

Still working on it, will send when done.

Reply | Quote

Remove those spaces after and before the double quotes in the DMax statement (you have ” Contact Information “). This may be causing the table reference difficulty Charlotte was referring to. After that, you need to post the code, as Charlotte suggested.

Reply | Quote

I’ve attached a copy of the what I’m trying to do. The form I’m working with is the Contact Information Form.

Reply | Quote

i don’t see the attachment

Reply | Quote

I can’t get it any smaller that 154K and I can’t send it. Any suggestions?

Reply | Quote

did you try charlottes decompile trick before zipping the database

Reply | Quote

Here it is again.

Reply | Quote

on the on click event click on this line you will see a box to the right with three dots in it click this box then a popup window will appear click on code builder then place the code
for the dmax here at least that is the way in acc2000 i’m at work right now and only have acc2000 here hope this helps

Reply | Quote

Access 97 works the same way.

Reply | Quote

You had the Dmax statement in the OnClick event line, instead of in the VB code that is called by the OnClick event.
See the modification in the attachment.

Reply | Quote

Thank you, I copied it into the file and it doing what the statement says it’s supposed to do. However, what it’s doing is finding the highest number from ALL the records and adding one. What I wanted was to find the highest record for the Client ID then add one. For example, a contact was made to John Doe on May 15, Sally Smith on May 15, May 20, and May 30. The next contact for John Doe would be #2 and the next for Sally Smith would be #4.

That was why my original statement was an IIF statement. If the Client ID and Contact ID are equal then do the DMAX statement. Is this type of thing possible? If so how?

Reply | Quote

It is possible if you include the criteria parameter in the Dmax statement to accomplish this, so that the syntax reads:
DMax(expr, domain, criteria). I don’t understand your statement about the ClientID and ContactID being equal; I would have thought you just wanted the next highest number for that specific ClientID, so I’m not sure how to write the statement. Take a look at Dmax in Access Help – you can find examples of the criteria parameter.

Reply | Quote