#Error?

[gmatriix]

Hello All,

I have a field called [Product ID]. I am getting this #Error in the field until the event is triggered. The event is this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.[Product ID] = Nz(DMax("[product ID]", "product"), 1000) + 1
End Sub

Is there anyway I can hide or get ride of this? I tried "If IsError" and others and it is still showing up.

Any Ideas?

 

[John Big Booty]

What is the control source for the field [Product ID].

Do I understand correctly that the #Error persists until the form's BeforeInsert event is triggered?

Also avoid using spaces and other special characters in your control and object names. Limit yourself to alpha and numeric characters and the Underscore (_)

 

[gmatriix]

Hello,

The control source is "Product ID" from the product table. That is correct....when click a new record it shows #error until I populate another field then it runs the code and is fine.

I just wanted the field to either be blank or say "(New)" until the code runs. I was researching how replace function could work but was unsuccessful.

Any Ideas?

 

[gmatriix]

Any one know anything about this?

 

[John Big Booty]

What you could do is move your code to the On Current event of the Form, and test if you have a new record or not, something along the lines of;

If Me.NewRecord
     Me.[Product ID] = Nz(DMax("[product ID]", "product"), 1000) + 1
End If

 

[gmatriix]

Hey Thanks,

This is what I had originally, however you made me look into this again and I came up with this.

If Me.NewRecord Then
Me.productid.Visible = False
If MsgBox("Add a New Quote?", vbYesNo, "New Quote") = vbYes Then
Me.[Product ID] = Nz(DMax("[product ID]", "product"), 1000) + 1
Me.productid.Visible = True
Else
End If
End If
End Sub

I did not want the user to just click a new record with really wanting to because this would cause alot of empty records when they did not mean create one. This also hides the #error in the field.

Thanks for all your help!