Set a default value for a form field using an output from SQL

[Andydr]

I have a table MAIL_MERGE which has a numeric field for batch_number. I have created a form for data input, which also has the field batch_number, what I want to do, is when a new record is created in the form, is to default the value for batch_number as the last batch_number (ie Maximum batch_number)in the table,incremented by 1. I have tried several ways unsuccessfully. Any suggestions on the easiet way to do this?. I know SQL well but Access VB just learning the hard way.
Many Thanks
Andy

 

[shannonp]

Why not just link table MAIL_MERGE to your form and make batch_number an Autonumber field?

OR

If you don't want to go to the trouble of changing your table &or form then try this VB formula (might pay to assign it to a custom made button which doubles as your "Add New Record" event:


[myform_batch_number] = DMax("[batch_number]","MAIL_MERGE&quot + 1 'increment value by one.

The only problem with the statement above is that if 'MAIL_MERGE' is not bound your form then it won't populate it hence, the next time you add a new record you'll end up with exactly the same number as before.

For this to be avoided add the following line of code to the same record after my previous statement:

DoCmd.RunSQL "INSERT INTO MAIL_MERGE ( batch_number ) " & _
"SELECT " & [myform_batch_number] & _
" AS myform_batch_number;"

PS. Remember you can achieve the EXACT same result if you just (1) bound your form to 'MAIL_MERGE' and (2) make MAIL_Merge.batch_number an autonumber.

 

[Andydr]

Thanks, got it to work with DMAx.
Already have my primary key field defined as Autonumber, and unable to add a second. Form was already bound to the form. Added Dmax in a before Insert Event Procedure on the Form.

Many Thanks
Andy