ID number and true false relationship

[rmanaus]

I'm going to do my best to explain this.

I am using access off line and no user will ever access it except myself.

I am using it as a database for customers in a loyalty program.

I need to make my table more efficient.

Every time a member goes from standard to VIP they are assigned a number, which I have to manually type in.

They are flagged as a VIP by clicking a true false tick box.

How am I able to generate a new maximum number after clicking that the customer is now true ( they become a vip)and automatically assign them an ID number.

I'm at work all day today so any suggestions would help, also this is MS Office 2007 and im in Australia

 

[jedraw]

I think there may be some questions to ask before you get to the number issue.

You are dealing with people; and
some sort of Status (standard/VIP)

If the id identifies the person, then the id exists independent of Status.

So person 22 is John Doe who has Standard status today, but moves to VIP next week. The status changed not the person id.

Can a person who has stats VIP get "demoted" to Standard? If so what happens to your id number?

Tell us more.

 

[rmanaus]

The members have no number to begin with, they only have a number when they are flagged as a vip.


they are flagged as a vip when they give up more details.

i want them assigned an id number automatically when their status changes from standard to vip.

 

[dhookom]

On your form where you update the VIP status. Your code in the after update of the VIP check box might be something like:

   If Nz(Me.chkVIP,0) <> 0 AND Me.txtVIPMemNum is Null Then
      Me.txtVIPMemNum  = DMax("VIPMemNum","tblMembers") + 1
   End If

If you can't figure this out, come back with details like the control names, field names, table names, ...

Duane MS Access MVP
Now help me support United Cerebral Palsy