*NOT SOLVED* Default Values = Number of Records + 1

[iProRyan]

Hi Guys,

I have a bit of a problem with a Form in Access, what I want is a fields Default Value to be the Total Number of Records for that form + 1. I don't want to use an AutoNumber because that messed with Relationships. If anyone can help, it would be much appreciated.



Ryan

[John_G]

The problem here is that a form does not have set number of records - its underlying table does. The form will display all or only some of them, depending on filtering that is set.

I gather from what you said that when you insert a new record you want to have a field be assigned the value one higher than the current maximum, i.e. the next number is a sequence.

You can do this using VBA in the Before Insert event of your form, with the DMax function:

me![ID_Field] = nz(dmax("ID_Field","TableName"),0) +1

This finds the current maximum value of ID_Field, adds 1 to it, and puts the resulting value in the form field.

I used nz to cover the case where there are no existing records in the table.

One caveat with this is that if you create this new record, and then delete it, the next new record you create will have the same ID number. There are also potential problems if you have multiple users accessing the same table at the same time.

HTH

John

[iProRyan]

The problem here is that a form does not have set number of records - its underlying table does. The form will display all or only some of them, depending on filtering that is set.

I gather from what you said that when you insert a new record you want to have a field be assigned the value one higher than the current maximum, i.e. the next number is a sequence.

You can do this using VBA in the Before Insert event of your form, with the DMax function:

me![ID_Field] = nz(dmax("ID_Field","TableName"),0) +1

This finds the current maximum value of ID_Field, adds 1 to it, and puts the resulting value in the form field.

I used nz to cover the case where there are no existing records in the table.

One caveat with this is that if you create this new record, and then delete it, the next new record you create will have the same ID number. There are also potential problems if you have multiple users accessing the same table at the same time.

HTH

John

Hi John,

I have entered this into the Before Insert event of the form

Me![DVD ID] = Nz(DMax("DVD ID", "DVD's"), 0) + 1

and get this error:

Syntax Error (missing operator) in query expression.

Any ideas?

Ryan

[John_G]

The problem is the Tablename "DVD's". The single quote (apostrophe) in it is confusing MS Access. Rename the table to remove the single quote and it should work. It is never a good idea to use special characters in MS Access object names (other than the underscore "_"), and this is one reason why.

John

[iProRyan]

Thanks managed to get it to work, I think it was also the space in "DVD ID" causing an issue because I wouldn't work until I removed the space. Thank you so much for your help John!