Macro to find last record number?

[dniezby]

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.

[June7]

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

[dniezby]

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.

[June7]

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.

[dniezby]

June7 We have a problem.

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

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

[June7]

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

[dniezby]

I'm using VBA here:

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.