runtime error 3075 dlookup Resulting In Null Value (1 Viewer)

[ivonsurf123]

Hello,


Hope you can help me to figure this out, had tried everything I know and nothing works, keep giving Nulls, It was working before, but then start giving nulls.

Private Sub Form_Current()
   
    If Me.NewRecord Then
        Me.lblRecordCounter.Caption = _
         "Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount + 1
    Else
        Me.lblRecordCounter.Caption = _
         "Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount
    End If
    
    Me.CD1_Width = Nz(DLookup("[Width]", "tbl_ClientDataFormat", "Field = '" & Me.CD1_Width.Tag & "' and CustomerSpecific_ID=" & Me.ID), "")
    Me.CD1_Format = Nz(DLookup("[Format]", "tbl_ClientDataFormat", "Field = '" & Me.CD1_Format.Tag & "' and CustomerSpecific_ID=" & Me.ID), "")

End Sub

Private Sub CD1_Width_AfterUpdate()

    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("Select * from tbl_ClientDataFormat where Field= '" & Me.CD1_Width.Tag & "' and CustomerSpecific_ID=" & Me.ID, dbOpenDynaset, dbSeeChanges)
 If rst.RecordCount > 0 Then
    CurrentDb.Execute "INSERT INTO tbl_ClientDataFormat ([Width], [CustomerSpecific_ID], [Field]) " & _
                    "SELECT '" & Me.CD1_Width & "', " & Me.ID & ", '" & Me.CD1_Width.Tag & "';"
 Else
    rst.Edit
    rst!Width = Me.CD1_Width
    rst.Update

 End If
 rst.Close
 Set rst = Nothing
 
End Sub

Private Sub CD1_Format_AfterUpdate()

Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("Select * from tbl_ClientDataFormat where Field= '" & Me.CD1_Format.Tag & "' and CustomerSpecific_ID=" & Me.ID, dbOpenDynaset, dbSeeChanges)
 If rst.RecordCount > 0 Then
    CurrentDb.Execute "INSERT INTO tbl_ClientDataFormat ([Format], [CustomerSpecific_ID], [Field]) " & _
                    "SELECT '" & Me.CD1_Format & "', " & Me.ID & ", '" & Me.CD1_Format.Tag & "';"
 Else
     rst.Edit
    rst!Format = Me.CD1_Format
    rst.Update


 End If
  rst.Close
 Set rst = Nothing
End Sub

 

[pbaldy]

What is the data type of "Field"? What values are in the Tag property of the two controls?

Edit: and what line throws the error?

 

[ivonsurf123]

Hello Paul,


What is the data type of "Field"? = String

What values are in the Tag property of the two controls? = String

Me.ID= Numeric
Edit: and what line throws the error?

This line for both Width and Format, it throws Null on Me.ID and "" Me.CD1_Width


Private Sub Form_Current()


Me.CD1_Width = Nz(DLookup("[Width]", "tbl_ClientDataFormat", "Field = '" & Me.CD1_Width.Tag & "' and CustomerSpecific_ID=" & Me.ID), "")

 

[pbaldy]

Nothing is jumping out. Can you attach the db here?

 

[ivonsurf123]

Sure, I can.

 

[pbaldy]

What steps do I have to take to recreate the error? I'm cycling through records on the customer form and not getting an error. I note there are no values in the table to be returned.

 

[ivonsurf123]

Within the forms, you find two subforms when open frm_PartnerNetwork_AgencyMasterlist, then if you stay on tab "Agency Masterlist" start passing records, will recreate the error, see before when I was able to add data for ID 3 and ID 4 on Customer Specific, now I can even pass the records on Agency Masterlist tab, as soon as I get to ID 5 it throws that error 3075

 

[pbaldy]

I think you're on a new record at that point. Presuming so, Me.ID returns Null so the DLookup criteria is invalid. You may want to put those lines in the Else of the NewRecord test.

 

[ivonsurf123]

No, did not work same error, I'll keep trying. Thanks.

 

[pbaldy]

Really? I switched to this in the current event and I don't get an error anymore:

    If Me.NewRecord Then
        Me.lblRecordCounter.Caption = _
        "Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount + 1
    Else
        Me.lblRecordCounter.Caption = _
        "Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount

        Me.CD1_Width = Nz(DLookup("[Width]", "tbl_ClientDataFormat", "Field = '" & Me.CD1_Width.Tag & "' and CustomerSpecific_ID=" & Me.ID), "")
        Me.CD1_Format = Nz(DLookup("[Format]", "tbl_ClientDataFormat", "Field = '" & Me.CD1_Format.Tag & "' and CustomerSpecific_ID=" & Me.ID), "")
    End If

 

[ivonsurf123]

Yes, that's exactly what I did but still not working....I'll do some more tests, I'll let you know.