run-time error 3075

[rwilliams616]

Hi I have the following code:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.FileNo = Nz(DMax("[FileNo]", "tblFiles", "[LogNo] = " & Me.LogNo)) + 1
End Sub

I get the run-time error for the '[LogNo] = ' portion of the code. LogNo is
a primary key in a table called tblFRACAS. LogNo is also used in tblFiles.
LogNo is linked in a one-to-many relationship between tblFRACAS and tblFiles.
It is an integer in each of the tables.

This code is being used in a subform called frmFiles. The main form is
called frmFRACAS.

Can anyone offer any help?

 

[Allen Browne]

The code assumes that your form has controls named FileNo and LogNo. Do you
have both controls on your form?

Although VBA highlights the "[LogNo] = " part, the error could be anywhere
in the line.

I notice that you are running this code in Form_BeforeInsert. Will the LogNo
text box actually have a value at this stage? It might (if there's a default
value), but if not, the code will fail, and you may need to move it into the
AfterUpdate event procedure of the LogNo text box and test it for null:

If Not IsNull(Me.LogNo) Then
Me.FileNo = Nz(DMax("[FileNo]", "tblFiles", "[LogNo] = " &
Me.LogNo), 0) + 1
End If

I've also suggested supplying the 2nd argument for Nz().