Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically Increasing Number (Access 97)

    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)

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Increasing Number (Access 97)

    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.

  3. #3
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Increasing Number (Access 97)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically Increasing Number (Access 97)

    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.
    Charlotte

  5. #5
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Increasing Number (Access 97)

    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?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically Increasing Number (Access 97)

    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.
    Charlotte

  7. #7
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Increasing Number (Access 97)

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatically Increasing Number (Access 97)

    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.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Increasing Number (Access 97)

    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.

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Increasing Number (Access 97)

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

  11. #11
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Increasing Number (Access 97)

    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.

  12. #12
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Increasing Number (Access 97)

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

  13. #13
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Increasing Number (Access 97)

    i don't see the attachment

  14. #14
    Lounger
    Join Date
    Jul 2002
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Increasing Number (Access 97)

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

  15. #15
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Increasing Number (Access 97)

    did you try charlottes decompile trick before zipping the database

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •