Solved Run-Time Error '3075' (1 Viewer)

5hadow

Member
Local time
Today, 12:42
Joined
Apr 26, 2021
Messages
89
I'm getting following error:

1647521045455.png


Line of code:

strLastAudit = Nz(DMax("fldDateClosed", "tblIQA", "fldDocID = " & fldDocID))

I've also tried the following:

strLastAudit = Nz(DMax("fldDateClosed", "tblIQA", "fldDocID = '" & fldDocID & "'"))

fldDocID is a PK in tblIQA, an auto number.

Any ideas?
 

Minty

AWF VIP
Local time
Today, 16:42
Joined
Jul 26, 2013
Messages
10,354
I would assume that fldDocID is null based on the error message.

Can you show in your code how it is declared and then set?
 

Ranman256

Well-known member
Local time
Today, 12:42
Joined
Apr 9, 2015
Messages
4,339
is [fldDocID] the correct field name?
is it numeric?
is me.fldDocID the correct spelling of the form control. (text box?)
does the form control: me.fldDocID have a value? (put a stop on this line and check the value)

strLastAudit = Nz(DMax("fldDateClosed", "tblIQA", "fldDocID = " & me.fldDocID)
 

5hadow

Member
Local time
Today, 12:42
Joined
Apr 26, 2021
Messages
89
I would assume that fldDocID is null based on the error message.

Can you show in your code how it is declared and then set?

Here are all declarations:

Code:
Private Sub Form_Current()
Dim strLastAudit As String
Dim strAudit
Dim strDR As String
Dim strLastDR As String


    strAudit = Me.LastAudit
    strLastAudit = Nz(DMax("fldDateClosed", "tblIQA", "fldDocID = " & fldDocID))
    strDR = Nz(Me.LastReview)
    strLastDR = Nz(DMax("fldDateClosed", "tblDocReview", "fldDocID = " & fldDocID))

is [fldDocID] the correct field name?
is it numeric?
is me.fldDocID the correct spelling of the form control. (text box?)
does the form control: me.fldDocID have a value? (put a stop on this line and check the value)

strLastAudit = Nz(DMax("fldDateClosed", "tblIQA", "fldDocID = " & me.fldDocID)
Yes, [fldDocID] is correct. On mouse-over it shows "Null". On my form I added that field onto the form just to see the value, and there is a value of 16.
When I add the quotes like "fldDocID = '" & fldDocID & "'" then on mouse-over it shows 2 and then I get type missmatch error.
 

moke123

AWF VIP
Local time
Today, 12:42
Joined
Jan 11, 2013
Messages
3,852
Looks like you need to sort out your Dec's.
You dim them as strings but is fldDateClosed a date or a string?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:42
Joined
Feb 28, 2001
Messages
26,999
On mouse-over it shows "Null".

That right there is totally enough to explain the syntax error. From your DMax, ...

Code:
 "fldDocID = " & fldDocID

Now substitute a null and you have

Code:
 "fldDocID = "

I.e. an expression with a left-hand side but no right-hand side on that equals-sign. In that context, "=" is a comparison operator which requires two comparands but only has one.
 

Minty

AWF VIP
Local time
Today, 16:42
Joined
Jul 26, 2013
Messages
10,354
Here are all declarations:

Code:
Private Sub Form_Current()
Dim strLastAudit As String
Dim strAudit
Dim strDR As String
Dim strLastDR As String


    strAudit = Me.LastAudit
    strLastAudit = Nz(DMax("fldDateClosed", "tblIQA", "fldDocID = " & fldDocID))
    strDR = Nz(Me.LastReview)
    strLastDR = Nz(DMax("fldDateClosed", "tblDocReview", "fldDocID = " & fldDocID))


Yes, [fldDocID] is correct. On mouse-over it shows "Null". On my form I added that field onto the form just to see the value, and there is a value of 16.
When I add the quotes like "fldDocID = '" & fldDocID & "'" then on mouse-over it shows 2 and then I get type missmatch error.
Okay a few things to fix: You need to tell access to get the value from the form's recordset, so you need to add Me. in front;

strLastAudit = Nz(DMax("fldDateClosed", "tblIQA", "fldDocID = " & Me.fldlDocID ))

strAudit is being declared as a Variant as you aren't declaring a type.

The NZ() function without a replacement value seems a strange way to use it.
 

5hadow

Member
Local time
Today, 12:42
Joined
Apr 26, 2021
Messages
89
Ok, if I load the form by it self then it works ok. Problems stems when opening form from another related form.

In my related form I have a field fldWISerial and it has on-click event: DoCmd.OpenForm "frmWorkInstruction", , , "fldWISerial= '" & Me.fldWISerial & "'".

So then, following debugging, the form that opens has on current event:

Code:
Private Sub Form_Current()
Dim strLastAudit As Date
Dim strAudit As Date
Dim strDR As Date
Dim strLastDR As Date

Debug.Print Me.fldDocID
    strAudit = Nz(Me.LastAudit)
    strLastAudit = Nz(DMax("fldDateClosed", "tblIQA", "fldDocID = " & Me.fldDocID))
    strDR = Nz(Me.LastReview)
    strLastDR = Nz(DMax("fldDateClosed", "tblDocReview", "fldDocID = " & fldDocID))
   
    .....

When I put a stop on Debug.Print me.fldDocID it is null instead of what it's supposed to be. I think this is where the problem is. Again, it works if I open the form by itself.

Edit: Just to be clear, when clicking on fldWISerial on related form, it opens this form in question on right record, then it goes trough on current event then I get the error.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:42
Joined
Feb 28, 2001
Messages
26,999
When I put a stop on Debug.Print me.fldDocID it is null instead of what it's supposed to be. I think this is where the problem is. Again, it works if I open the form by itself.

When you open the form directly it has no filter so the bound field will have a value.
If you open the form indirectly from another form with a filter
Code:
    DoCmd.OpenForm "frmWorkInstruction", , , "fldWISerial= '" & Me.fldWISerial & "'"
check whether there is a record matching the filter criteria. If not, perhaps the filter didn't find anything so the indirectly opened form didn't find anything either - and thus returned null in ALL of its bound fields. We can't tell if that is the case, but IF the imposed filter found nothing, the rest of the events would fall into place.
 

5hadow

Member
Local time
Today, 12:42
Joined
Apr 26, 2021
Messages
89
Ok, SOLVED!

Here's what I did:

On related form instead of using: DoCmd.OpenForm "frmWorkInstruction", , , "fldWISerial= '" & Me.fldWISerial & "'"
Instead I used fldDocID to locate record, like:
DoCmd.OpenForm "frmWorkInstruction", , , "fldDocID= " & Me.fldDocID

Thanks everyone for help.
 

Minty

AWF VIP
Local time
Today, 16:42
Joined
Jul 26, 2013
Messages
10,354
Where is fldDocID supposed to be coming from the current form or the calling form?

It won't be available if it's not set on the current form
 

Users who are viewing this thread

Top Bottom