I'm not sure if my explanation will be clear enough and so I have attached a sample. Hope that it provides better illustration of my problem. The steps below lead to the problem:
1. Open up FrmOrderInfoMain.
2. Select OutletA from the Brand combobox. The PONumber txtbox is filled up with the value A1.
3. Next, select ItemCode001 from the ItemCode combobox in the subform. The PONum is A1, same as that of the parent form.
4. Choose OutletA from the Brand combobox again. This time, A2 is shown on the PONumber txtbox.
After all these selections, check up the records in tblOrderInfoMain. The table only shows A2 and not A1. I would want A1 to be the PONum for the form instead of A2. How can I achieve this? I do not want the dmax() function to be activated when i click on the same option in the existing record. This will cause the users to be entering two records at the same time.
In the AfterUpdate event code you have or the Brand ComboBox, add code to check to see if the record being worked on is in fact a new record. You can accomplish this with the NewRecord statement. For example, change the code in your AfterUpdate event to view as follows:
Private Sub cmbOutletChoice_AfterUpdate()
If Me.NewRecord = True Then
Me.txtOutletInitial = Me.cmbOutletChoice.Column(1)
Me.txtOutletNum = Nz(DMax("[OutletNum]", "tblOrderInfoMain", _
"[OutletInitial] ='" & [cmbOutletChoice].[Column](1) & "'"), 0) + 1
Me.txtPONumDB = Me.txtOutletInitial + CStr(Me.txtOutletNum)
Thanks! That did work great for me but I realised another problem as well. When I click on the combobox for a second time, the PONumber does not change accordingly. I would like the PONumber to change to B1 if i choose OutletB and A1 if i choose OutletA.