Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    25

    Unhappy Unanswered: ARRGH! Need help with DMax

    Hello,

    I have a database that tracks all our participants. In the New Participant Form, there is a text field that is used to enter a BookNumber. The name of the field is txt2BookNumber, the control from the underlying Participant table is called BookNumber.

    I would like the BookNumber field to automatically update with an increment of 1, but just can't seem to get it to work. Here's what I have tried so far:

    1. I have tried putting the code = Nz(DMax("[BookNumber]", "Participant")) + 1 in the Default property of the txt2BookNumber field on the form - that just comes up with #Error.

    2. I have tried putting the same code in the Control Source property of the txt2BookNumber field on the form - same deal.

    3. I have tried putting the code:

    If IsNull(Me.txt2BookNumber) = True Then
    Me.txt2BookNumber = Nz(DMax("[BookNumber]", "Participant")) + 1
    End If

    in the Before Update property of the txt2BookNumber field - that doesn't seem to do anything; the field just remains blank when I create a new record.

    Please, someone, tell me what I am doing wrong before I go barmy!

    Thanks,
    Bronwyn

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    If Booknumber is a unique value, you should use an Autonumber field, in which Access will automatically assign the value and ensure you don't get duplicates.

    = Nz(DMax("[BookNumber]", "Participant")) + 1
    You code sample should work, providing a few things:
    The field name is BookNumber, the Table name is Participant. Curious why you used the Nz function, but you should add the default value of zero so it knows you want numbers if the value is null.
    Code:
    = Nz(DMax("[BookNumber]", "Participant"),0) + 1
    The #Error indicates one of the conditions is not met. Also, if you are in Access 97, there is no Nz function.

    The default property only gets applied when the form is opened, so that won't work beyond the first record.

    If you use the control source property, then a) your vaue won't get stored in the table, and b) you will not be able to enter it in anyway.

    You mentioned using the Before Update function. Here's a "programmer's question" for you: if you want to have your program assign the value, why are you having the user enter it? If the value is a suggestion, the use a seperate field and place the corrected formula in the controlsource property. If the value is mandatory, place the code in the OnCurrent event and make the control disabled so the user is not able to change it. (Combine enabled = false with locked = true to preserve formatting)

    Side note: in your code:
    If IsNull(Me.txt2BookNumber) = True Then
    Me.txt2BookNumber = Nz(DMax("[BookNumber]", "Participant")) + 1
    End If
    Following the IF statement, you know it is null, so you wouldn't need the DMax or Nz functions and could simply set the value = 1.

    My money would be on the table, either the field name or the table name is spelled wrong (unless you have an old version of Accesss).

    good luck,
    tc

  3. #3
    Join Date
    May 2006
    Posts
    25

    Here's what I know...

    You'll have to bear with me here, I don't know much about Access, and I have inherited this database. Here's what I know:

    Table name = Participant

    Field in the Table = BookNumber (this is a text field)

    Form name = frmDukesDatabase (this is separated into pages or sections with six tabs at the top for the different sections - the section (page?) we are dealing with is called Participant.

    Name of the field in the Form = txt2BookNumber.

    Version of Access = 2003

    I want the BookNumber field to automatically populate with the next number each time a new Participant record is created. Yes, this needs to be mandatory, and the users will not be able to change it.

    I am reluctant to use the AutoNumber field because until now the numbers have been entered by the user, and some of them do not fit a sequence.

    Do I need to lock the txt2BookNumber field?

    Also, where do I find the OnCurrent property?

    Lastly, if the database already has a heap of records in it where the BookNumber has just been entered manually, will changing the properties of the field upset the existing data?

    Hope this makes sense...

    Regards,
    Bronwyn

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You'll have to bear with me here, I don't know much about Access, and I have inherited this database. Here's what I know:

    Table name = Participant

    Field in the Table = BookNumber (this is a text field)
    Potential problem: Text field with data posing as Numbers, you're asking Access to find the MAX and add 1 to it - you're relying on the software to automatically convert back and forth between text and number data types. Will probably work, but should fix later.

    Form name = frmDukesDatabase (this is separated into pages or sections with six tabs at the top for the different sections - the section (page?) we are dealing with is called Participant.
    Here's a spot for things to get mixed up: the source table and a control on the form have the same name. Suggest renaming the page of the tab control.

    Name of the field in the Form = txt2BookNumber.

    Version of Access = 2003

    I want the BookNumber field to automatically populate with the next number each time a new Participant record is created. Yes, this needs to be mandatory, and the users will not be able to change it.
    The method you are attempting is a good one for this case, we need to resolve the #Error. Add a couple extra text boxes and try setting the control source to just the DMax function and see if you can get that working. Since there's a bunch of data there already, you won't get a null, so you don't need the Nz.

    I am reluctant to use the AutoNumber field because until now the numbers have been entered by the user, and some of them do not fit a sequence.
    Won't work anyway, since the field is a text value. You can add an autonumber field and line up the data. What you do is create a blank table and do a few paste appends form Excel to seed it with enough autonumbers to cover your data. You then use a query to match the autonumber with the value in your field (convert it to a number in the query). Or, you dump the table to Excel and get it setup by hand. Excel is less complicated, but a lot of busy work - using queries is much faster for large data sets.

    Do I need to lock the txt2BookNumber field?
    Yes, if you don't want the user changin it - either lock it, disable it or hide it.

    Also, where do I find the OnCurrent property?
    It is an event of the Form itelf. Click the upper left corner of the form to select just the form.

    Lastly, if the database already has a heap of records in it where the BookNumber has just been entered manually, will changing the properties of the field upset the existing data?
    Don't change the properties in the table an you are ok. The form is just a reflection of the data in the table - you can set the field properties on the form to anything you want.

    Hope this makes sense...

    Regards,
    Bronwyn

    Good luck and have fun - I have to go to bed now (it's 1 AM here)
    tc

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    My two cents worth if I may:

    Your BookNumber field in Table is of Text data type. If the Book Numbers in table do not contain any Alpha characters (A,B,C,D, etc.) then change the data type to Number. If the Data Type must remain Text and the Book Number are indeed AlphaNumeric (AC0214R) then you're in a whole world of crap. It's not the end of the world but I should think you will need to decide the type of AlphaNumeric format you want to maintain and then display it here.

    If you must keep the Text Data Type in table and the Book Numbers are all numeric (1435336) then place this into the Form's OnCurrent event code window (via the Visual Basic Editor - VBE):
    Code:
    If IsNull(Me.txt2BookNumber) = True Then
       Me.txt2BookNumber = Val(Nz(DMax("[BookNumber]", "Participant"), "0")) + 1
       ' or   Me.txt2BookNumber = CLng(Nz(DMax("[BookNumber]", "Participant"), "0")) + 1
    End If
    Set the TabStop property for the Form's txt2BookNumber field to No (found in the Other tab of the properties window).

    Under the Data tab in the properties window, set the Locked property for the txt2BookNumber field to Yes.

    and if you want to go even further...place this into the GotFocus event (VBE code window) for the txt2BookNumber field:

    Me.theNextControlOnForm.SetFocus

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  6. #6
    Join Date
    May 2006
    Posts
    25

    Thanks

    Quote Originally Posted by tcace
    Good luck and have fun - I have to go to bed now (it's 1 AM here)
    tc
    Thanks for sacrificing you sleep for me. I will give your suggestions a go and let you know how it all pans out.

    Regards,
    Bronwyn

  7. #7
    Join Date
    May 2006
    Posts
    25
    Yay!! I finally got time to work on this problem. It all came down to the type of field. I changed the BookNumber field in the table to a Number field instead of a text field. Then the code: = Nz(DMax("[BookNumber]", "Participant"),0) + 1 worked when placed in the default value of the txt2BookNumber field in the form.

    Thanks for your help guys!

    Bronwyn

Posting Permissions

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