Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50

    Unanswered: DMax to Calculate next check number.

    I have a checkbook database that is basically a check register.

    I have a form "fCNewCheck" which is used to enter new checks. I have a "CheckNumber" field in this form with the default value set to
    =DMax("[CheckNumber]","tCTransactions")+1. This automatically fills in the next check number. This works fine as long as you only have one checking account in the database. If you have 2 or more checking account is selects the highest checknumber plus 1.

    My question what do I put in the default field of "CheckNumber" to make it look at the combo box on the previous form that selects which account to use. I already have one field in the "fCNewCheck" form which is based on the accountname combobox of the first form used to select the account.

    Here is an example of how I am using the previous form to select the account name.

    I have a hidden field in the "fCNewCheck" for the "AccountName" with its default value set to =[Forms]![fCSelectAccountName]![Combo4] which automatically adds the check to the proper account based on which account the user selected on the previous form.

    I'm wanting something similar in the CheckNumber field to add 1 to the check number of the selected account.

    Hope this makes sense and I think that I'm am close to the answer. I have just been thinking about it way to hard.

    By the way, this is my first serious attempt at a database and I am just about done. Just finding my little screw ups. Thanks for all of the help you all have given me in the past. I am a search this forum junkie.

    Thanks again. Hooks

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi Hooks

    There's an optional "criteria" argument you can pass to the DMax function.

    If AccountName is text, try
    =DMax("[CheckNumber]","tCTransactions","AccountName=""" & Replace([Forms]!]![fCSelectAccountName]![Combo4],"""","""""") & """") + 1

    or if AccountName is numeric, try
    =DMax("[CheckNumber]","tCTransactions","AccountName=" & CStr([Forms]!]![fCSelectAccountName]![Combo4])) + 1

  3. #3
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Thanks JTRockville.

    I'm getting a syntax error. I am trying to add this to the default value field. Is this correct? If not please help.

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Oooooooooooooooooops!!

    I put an extra

    !]

    between [Forms]

    and ![fCSelectAccountName]

    It should be

    [Forms]![fCSelectAccountName]

    NOT

    [Forms]!]![fCSelectAccountName]

    Sorry

  5. #5
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Thanks I will try when I get home from work. I really appreciate your help and the rest of the forum.

  6. #6
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    I'm still getting and error in the checknumber box.

    Thanks for your help.

  7. #7
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Is AccountName a field in tCTransactions?
    What data type is it?

  8. #8
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Yes and it is a text field.

  9. #9
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    I have an account table that is linked to the transactions table.

  10. #10
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    hmmmm... Lets try process of elimination...

    This works:

    =DMax("[CheckNumber]","tCTransactions") + 1

    This does not:

    =DMax("[CheckNumber]","tCTransactions","AccountName=""" & Replace([Forms]![fCSelectAccountName]![Combo4],"""","""""") & """") + 1

    Can you "hard-code" an account name, just for testing?

    See if this produces an error (change MyAccountName to the actual account name)

    =DMax("[CheckNumber]","tCTransactions","AccountName=""MyAccountName""") + 1

  11. #11
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    I get the same result with the code you gave me as I do with what I originally started out with. My checking account names are "test" and "Main Checking". Main Checking is on check number 648 and test is on 002.

  12. #12
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    accually what you just told me to do works fine. What should i try next

  13. #13
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    =DMax("[CheckNumber]","tCTransactions","AccountName=""" & [Forms]![fCSelectAccountName]![Combo4] & """") + 1

  14. #14
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    50
    Thank You JTRockVille. It works perfectly. Thanks for having the patience to work with a newbie.

  15. #15
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    WAIT!!!

    If you have a double quote in your account name, evil will happen!!!

    How likely do you think that is?

Posting Permissions

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