Checking multiple criteria search code is correct!

[adh123]

Please if someone could let me know if the below is correct that would be appreciated!

Staff are monitored to make sure they are keeping up to date with our customers. A customer can have multiple projects going through the factory at any one time. Each customer has a record per project and a 'general' record.
Ideally we would like our staff to be able to move the 'general' record when they update a project record as opposed to either having to find and then update the general record after, or forgetting and calling the customer again 2 days later!

Including a msgbox for the EnqNum seems to show the general record correctly, however being new to access I am unsure if I have the update part correct.

If Me.chkMoveGen.Value = "-1" Then
    Dim EnqNum As Integer
    EnqNum = DLookup("[e_id]", "tblEnquiries", "[c_id]=" & Me.txtc_id & " and [e_status] = " & "13")
    DoCmd.RunSQL "UPDATE tblEnquiries " & _
        " SET e_date_due=#" & Format(Me.txte_date_due, "MM/DD/YYYY") & "#" & _
        " WHERE e_id= EnqNum"

 

[CJ_London]

the syntax looks almost OK - this bit
" WHERE e_id= EnqNum"

should be
" WHERE e_id= " & EnqNum

If these are ID's they are usually long so Engnum should be declared as long rather than integer

and you don't have any code to protect against the dlookup returning null

 if not isnull(EngNum) then  
    DoCmd.RunSQL "UPDATE ...
else
    msgbox "nothing found"
end if

finally, consider using currentdb.execute rather than docmd.runsql - the latter will prompt you everytime it runs, whereas the former will only prompt if there is an error if used like this

currentdb.execute "UPDATE ....", dbfailonerror

 

[adh123]

Thanks for the tip!

The database is set so that a general record always exists for each customer (created on account set up automatically) so it should always be there!

The e_id field is 6 characters long, does this need amending to long?

Below seems to be working for now at least!

If Me.chkMoveGen.Value = "-1" Then
    Dim EnqNum As Integer
    EnqNum = DLookup("[e_id]", "tblEnquiries", "[c_id]=" & Me.txtc_id & " and [e_status] = " & "13")
    If Not IsNull(EngNum) Then
    
    CurrentDb.Execute "UPDATE tblEnquiries " & _
        " SET e_date_due=#" & Format(Me.txte_date_due, "MM/DD/YYYY") & "#" & _
        " WHERE e_id=" & EnqNum, dbFailOnError
    Else
        MsgBox "No General enquiry found"
    End If
End If

 

[CJ_London]

The e_id field is 6 characters long, does this need amending to long?

In Access, the maximum number for an integer is 32767 which is only 5 characters and anything greater than 32767 will fail.

But I was referring to EnqNum - this should be the same type as e_id - if e_id is an autonumber, then EnqNum should be long.

However just had a look on line and there is some dispute about how access/vba now treats integers.

My view, play safe, make EnqNum the same as e_id.

Note: if you are using SQL Server/MySQL as a backend, they have a type INT, which is not the same as Integer

Easy enough for you to test, temporarily put in your code

EnqNum=123456

and see what happens