Results 1 to 7 of 7
  1. #1
    astewart is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    6

    Field in form that shows last number entered to aid in entering next number

    Ok, so on a form I have I'd like to have a field labeled Last: {} with the {} containing the last number entered in a field in a table. Then the user could verify they were entering the correct next number. I can't autonumber these because based on a variety of different parameters, not every record gets a number. I have 7 fields I'd like to do this for.



    For example, the user needs to enter a number in a field called IA_Number. I'd like them to be able to see what the last number entered was before they enter the next number.

    Any ideas?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is this a multi-user database? Trying to show the 'last' or 'max' value from a field with multiple users is risky if they are entering data sumultaneously.

    However, you can use DMax() domain aggregate function in the ControlSource of a textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    astewart is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    6
    Quote Originally Posted by June7 View Post
    Is this a multi-user database? Trying to show the 'last' or 'max' value from a field with multiple users is risky if they are entering data sumultaneously.

    However, you can use DMax() domain aggregate function in the ControlSource of a textbox.
    Only one user enters at a time. The DMax() function works great for most of my fields. I have one field that's a little more complicated. In my table, I have one field where the user selects one of three districts 1, 2, or 4. In the next field is a sequential number for each of those districts as needed. Not every record has a district or a district number. In the form, I'd like to use DMax() with criteria, so if the user selects 1, 2, or 4 in the combo box, the last district # used for the selected district shows in my text box. If that's too complicated, then I can have three DMax() controlled text boxes, one for each district. That's what I tried, so the expression looks like

    =DMax("District_Number", "2013Register", "District=1")

    But my field just says #error.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't understand 'last district # used for the selected district'. Do you mean the 'last sequence # for the selected district'?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    astewart is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    6
    Hmm, I think that's what I mean. Each time a district is selected, the user enters the next sequential number corresponding to the selected district, the numbers run from 1 - whatever for a given year.

    Does that clarify?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So the expression would maybe be:

    =DMax("SequenceNumber", "2013Register", "District=" & [Distrct])
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    cactuspete13 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2012
    Posts
    53
    If the number is automatic, remove the human error possibility and make it completely automated. I found an SQL code that does something like that:
    Private Sub Form_BeforeInsert(Cancel As Integer)

    FieldName = Nz(DMax("FieldName", "TableName")) + 1

    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-15-2012, 02:21 AM
  2. Replies: 14
    Last Post: 02-23-2012, 06:32 PM
  3. Replies: 6
    Last Post: 07-25-2011, 01:54 PM
  4. Replies: 5
    Last Post: 12-26-2010, 10:56 PM
  5. Entering 16 digit numbers into number field
    By chrismid259 in forum Access
    Replies: 7
    Last Post: 12-14-2010, 10:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums