CODEWITHSUNDEEP
vbaformsms-accessautonumber
Dino52
Dino52

Reputation: 11

Save Customer Number From Form Text Field to Table

In an Access database I have a Customer table with a “CustomerNum” text field with the format ‘00001’ for example. This database had no autonumber field in the Customer table. I added “CustomerID”. However, all the coding in existing forms, queries, etc. is based on the CustomerNum field.

When adding a new Customer on a form, I tried to add the next sequential CustomerNum via VBA code to a text box, but it is not saved to the Customer table. The only way I’ve been able to get the record to save is to enter the CustomerNum manually. (I set up a message on the form using the DMax function to inform users of the CustomerNum to enter.)

I tried setting the CustomerNum text field using the DLookup function (=(DLookUp("[MaxOfCustomerNum]","[tblCustomer]"))+1) which does enter the correct CustomerNum in the text field on the form. But when I try to save the record, I get an error message that the CustomerNum needs to be entered into the table.

I tried a recordset:

rec(“CustomerNum”) = me.CustomerNum  
rec.Update

It appears to work, but it restarts the autonumbering of the CustomerID field and wipes out existing records (sets the newest added record to CustomerID = 1, and wipes out the previous record with CustomerID =1).

Upvotes: 1

Views: 74

Answers (0)

Related Questions