Can DLookup be used to set a value to a specific field?

[Vita]

Hey I know this is probably a dumb question but I was wondering if DLookup can be used to set a value to a specific field?
I have a huge list of records which has 3 primary keys and a bunch of other fields attached to them. I want to find the specific record that matches all 3 of the entered keys so that I can change one of the fields attached to that record only.

I was trying to do something like the following:

'StatusCriteria is defined elsewhere based on input but is always something like (Key1 = 5, Key2 = 7, Key3 = 8). I know the criteria isn't the problem because it works perfectly elsewhere in the code.

Dim Status_Err As String
Nz(DLookup("Status", "WorkTickets", StatusCriteria), Status_Err) = "COM"

If Not Status_Err = "" Then
    Debug.Print "Error setting status in workticket. Check 'StatusCode' DLookup"
End If

But I keep getting "Runtime error: '424' Object Required" on the DLookup line. I assume this is happening because the correct syntax is supposed to be

Object = "COM"

and DLookup is a function not an object.

Am I right in thinking that? If so does that mean I should use recordset and how do I get recordset to filter to that one specific record?

Thank you in advance.

[Gicu]

I would simply use a Update SQL statement:

CurrentDb.Execute "UPDATE WorkTickets SET Status ='Com' Where Key1 =" & Me.Key1 & " AND Key2 = '" & Me.Key2 & "' AND Key3 = #" & Me.Key3 & "#",dbFailonError
'I showed how to properly wrap the variables with key1 being a number, key2 a string and key3 a date

To use your already built StatusCriteria you can build a query to include the Status field to be updated and a calculated field to replicate the StatusCriteria; then you just use that in the Update statement above:

CurrentDb.Execute "UPDATE qryWorkTickets SET Status ='Com' Where StatusCriteria = '" & StatusCriteria & "'",dbFailOnError

Cheers,

[Welshgasman]

Not the correct syntax for dlookup()

[Vita]

I would simply use a Update SQL statement:

CurrentDb.Execute "UPDATE WorkTickets SET Status ='Com' Where Key1 =" & Me.Key1 & " AND Key2 = '" & Me.Key2 & "' AND Key3 = #" & Me.Key3 & "#",dbFailonError
'I showed how to properly wrap the variables with key1 being a number, key2 a string and key3 a date

To use your already built StatusCriteria you can build a query to include the Status field to be updated and a calculated field to replicate the StatusCriteria; then you just use that in the Update statement above:

CurrentDb.Execute "UPDATE qryWorkTickets SET Status ='Com' Where StatusCriteria = '" & StatusCriteria & "'",dbFailOnError

Cheers,

This worked perfectly! Thank you very much! Will have to keep this on my toolbelt.
I am gonna look into dbFailonError too because I just had it wrapped in a NZ

[Gicu]

You're very welcome, good luck with your project!
Cheers,