1

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

Community
  • 1
  • 1
Dino52
  • 11
  • 2
  • 1
    Expression in textbox ControlSource property does not populate field. Either set DefaultValue property with the expression or use code in form BeforeUpdate event to set value of CustomerNum field: `Me!CustomerNum = Me.CustomerNum`. Be aware there is possibility of multiple simultaneous users generating the same CustomerNum and causing conflict. If you generate value at beginning of record creation (as occurs with DefaultValue), need to immediately commit record to table. Could use DMax() on data table instead of DLookup to an aggregate query. – June7 Feb 13 '23 at 19:47
  • Neither setting the DefaultValue property nor using that code in the Before Update Event of the form populates the field in the Table. – Dino52 Feb 14 '23 at 22:16
  • I tested DefaultValue and it sort of works. Need to requery/refresh form when moving to another row so the DefaultValue will recalculate but that changes focus to first record which means navigating with intrinsic navigation bar is not practical. I've never used this method. – June7 Feb 14 '23 at 22:32
  • I also tested BeforeUpdate event code and works for me. If you have code, then edit question to show that attempt. I've never needed form BeforeUpdate. I do have one db that generates a custom unique identifier but manage this differently. Record is created from button click code then form opened to that new record for user to complete data entry. – June7 Feb 14 '23 at 22:33
  • I've shown the Default Value code I'm using in my original question. I tried requery/refreshing the form but it made no difference. I have the code you recommended in the form's before update event. The only other code I have is the "DoCmd.RunCommand acCmdRecordsGoToNew" in the form's Open event. Still nothing works, except entering a new CustomerNum manually. Thanks. – Dino52 Feb 15 '23 at 21:43
  • 1
    Suggest you post in a forum that allows attaching files and provide your db for analysis. Generating custom unique identifier is an old and common topic. – June7 Feb 15 '23 at 21:51

0 Answers0