Results 1 to 7 of 7
  1. #1
    dniezby is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2013
    Posts
    183

    Macro to find last record number?


    Okay so I figured something out.

    I created a data macro that will automatically fill in a field with the result of a formula for me.

    For example, I created an customer number using the year they became a customer and a formatted autonumber field. The field is automatically generated and locked on forms so it can't be changed.

    This works great and if I can't figure out my different idea then I will just leave it.

    What I'm wondering is, is there a command that can look at a field (let's call it player count) find the last number and then increment it then use the setfield command?

    So for example, player count is currently 60 then the next record is added. The data macro looks at the last player count, adds one then goes into my setfield statement.

    The player count is for example purposes only.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,623
    Locked or not, cannot edit autonumber fields by simple data entry/edit.

    Not a command but a domain aggregate function can query the table and return a value.

    Review discussion of same topic in https://www.accessforums.net/databas...ice-34483.html
    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
    dniezby is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2013
    Posts
    183
    I don't want to change the autonumber.

    I have a separate field that I just want to count and add the next value to the current record using SetField.

    If I can't do it with a macro then I'm not going to worry about it. I have it doing what I want now. I was just looking for another way for future learning.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,623
    I know you don't want to change the autonumber, just pointing out that even if the textbox is not locked, users can't.

    I expect domain aggregate functions can work in macros. I don't use macros so never tried.
    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
    dniezby is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2013
    Posts
    183
    June7 We have a problem.

    This code
    Code:
     member_ID = DMax("member_ID", "members_bak") + 1
    Works wonderfully...So wonderful, it updates the the field EVERY time I view a record. Even Records that already have a an ID.

    So, learning as I am (ever so slowly) I assume I will have to create an If statement? I don't know how to write it though. Here is my attempt, please let me know.

    Code:
    IIF member_ID = 0 
        member_ID = DMax("member_ID", "members_bak") + 1
    else
    endif

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,623
    Are you using macro or VBA?

    Why would member_ID = 0? Do you have DefaultValue property set?

    Try:

    If Me!member_ID = 0 Then Me!member_ID = DMax("member_ID", "members_bak") + 1

    or try:

    If IsNull(Me!member_ID) Then Me!member_ID = DMax("member_ID", "members_bak") + 1

    or try:

    If Me!member_ID & "" = "" Then Me!member_ID = DMax("member_ID", "members_bak") + 1
    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
    dniezby is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2013
    Posts
    183
    I'm using VBA here:
    Code:
    Private Sub Form_Current()
    If Me!member_ID = 0 Then Me!member_ID = DMax("member_ID", "members_bak") + 1
    End Sub
    FINALLY!!! Thank you for your help. This is what I've been asking for all along. It works even BETTER than I planned.

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

Similar Threads

  1. Access AutoExec Macro Cannot Find Function
    By JuanTooTree in forum Access
    Replies: 10
    Last Post: 06-08-2023, 10:10 AM
  2. Find First number in sequence of numbers
    By losstww in forum Queries
    Replies: 4
    Last Post: 11-16-2012, 02:44 PM
  3. Replies: 8
    Last Post: 01-17-2012, 02:43 PM
  4. Find and Replace Within a Macro
    By bmschaeffer in forum Access
    Replies: 1
    Last Post: 12-20-2011, 11:27 AM
  5. Find Missing Number in Sequence per Record
    By escuro19 in forum Programming
    Replies: 1
    Last Post: 11-10-2009, 03:15 PM

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