Solved Run-time error 2465

[Kayleigh]

Hi, Working on simple piece of code which compiles, but when run I get an error. Checked the field names and all seem correct...
Any ideas?

Private Sub Form_Current()
If Not (DLookup("fldPositionName", "qryStaffRolesConcat", [fldStaffPositionID] = 24)) Then
Me.txtRoles = ConcatRelated("fldPositionName", "qryStaffRolesConcat", "fldStaffID = " & [fldStaffID])
End If
End Sub

 

[plog]

The last argument of the DLookup is a string. You have not used any quote marks in yours.

 

[Kayleigh]

Tried that. Now getting a different error...

 

[Gasman]

Tried that. Now getting a different error...

Might want to show what you actually changed it to?

probably needs to be

"[fldStaffPositionID] = 24"

or 13 as you appear to have changed the value?

http://allenbrowne.com/casu-07.html

 

[Kayleigh]

No should definitely be 24.

Private Sub Form_Current()
If Not (DLookup("fldPositionName", "qryStaffRolesConcat", "[fldStaffPositionID] = 24")) Then
Me.txtRoles = ConcatRelated("fldPositionName", "qryStaffRolesConcat", "fldStaffID = " & [fldStaffID])
End If
End Sub

 

[plog]

Is fldStaffPositionID a text or numeric datatype?

 

[Kayleigh]

Numeric

 

[Gasman]

No should definitely be 24.

Private Sub Form_Current()
If Not (DLookup("fldPositionName", "qryStaffRolesConcat", "[fldStaffPositionID] = 24")) Then
Me.txtRoles = ConcatRelated("fldPositionName", "qryStaffRolesConcat", "fldStaffID = " & [fldStaffID])
End If
End Sub

My mistake, I was reading the '13' as a value you had used.

However Dlookup is going to return a string/numeric of fldPositionName I would have thought, not a boolean True or False?

 

[Kayleigh]

Hmmm so how would you code it?

 

[plog]

If you don't need a value from a table, but just need to know it exists I would use a DCount:

MS Access: DCount Function

This MSAccess tutorial explains how to use the Access DCount function with syntax and examples. The Microsoft Access DCount function returns the number of records from an Access table (or domain).

www.techonthenet.com

DLookup can return NULL and those can be a pain to work around. DCount is guaranteed to return a number.

 

[Kayleigh]

Thanks for that suggestion. Works excellent now!
Also found this post useful....