Adding multiple criteria

[whojstall11]

How would I add multiple criteria to this statement this doesnt work

Private Sub cmdUpdate_Click() 
    DoCmd.RunSQL "UPDATE [Hardware Asset]" & _ 
    " SET [Hardware Asset].[OS] = """ & Me.[OS] """ [Hardware Asset].[State] = """ & Me.[State] & _ 
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """" 

End Sub

 

[jdraw]

First work with a select query to make sure you are selecting the appropriate records.
Test, test, test before working with Update queries that are not easily reversed.

http://www.w3schools.com/sql/sql_update.asp

 

[Red17]

How would I add multiple criteria to this statement this doesnt work

Private Sub cmdUpdate_Click() 
    DoCmd.RunSQL "UPDATE [Hardware Asset]" & _ 
    " SET [Hardware Asset].[OS] = """ & Me.[OS] """ [Hardware Asset].[State] = """ & Me.[State] & _ 
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """" 

End Sub

I think that this will solve your problem:

Dim strQry as String

strQry = "UPDATE [Hardware Asset] SET [Hardware Asset].[OS] = """ & Me.OS & """, [Hardware Asset].[State] = """ & Me.State & """ WHERE [Hardware Asset].[Asset Number]= """ & Forms![Hardware Asset Update Form]![AssetNumber] & """;"

DoCmd.RunSQL strQry

 

[Severin]

DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
" SET [Hardware Asset].[OS] = '" & Me.[OS] & "', [Hardware Asset].[State] = '" & Me.[State] & _
"' WHERE [Hardware Asset].[AssetNumber] = '" & Forms![Hardware Asset Update Form]![AssetNumber] & "'"

 

[vbaInet]

You use the OR, AND or IN keywords or even a subquery to add multiple criteria. What exactly are you trying to do?

Also, please make note of jdraw's good advice. I would also add that if you're unsure how to write it in code, build in the Query Designer and copy the SQL statement once you're satisified with the results.

 

[whojstall11]

I actually fix this a while ago thank you all

Private Sub CmdUpdate_Click()
DoCmd.SetWarnings False
 
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[OS] = """ & Me.[OS] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Memory] = """ & Me.[Memory] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Speed] = """ & Me.[Speed] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Processor Type] = """ & Me.[Processor Type] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Total Disk Space] = """ & Me.[Total Disk Space] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Serial Number] = """ & Me.[Serial Number] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Warranty Known] = """ & Me.[Warranty Known] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Warranty Expiration] = """ & Me.[Warranty Expiration] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Site] = """ & Me.[Site] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Support Site] = """ & Me.[Support Site] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[SystemName] = """ & Me.[SystemName] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[MAC Address] = """ & Me.[MAC Address] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Status] = """ & Me.[Status] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Log] = """ & Me.[Log] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Notes/Attachments] = """ & Me.[Notes/Attachments] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Part Number] = """ & Me.[Part Number] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
DoCmd.RunSQL "UPDATE [Hardware Asset]" & _
    " SET [Hardware Asset].[Processor] = """ & Me.[Processor] & _
    """ WHERE [Hardware Asset].[AssetNumber] = """ & Forms![Hardware Asset Update Form]![AssetNumber] & """"
End Sub

 

[vbaInet]

Right, it appears you only have ONE criterion but multiple fields to update.

Here's how you do it for two update fields,

    DoCmd.RunSQL "UPDATE [Hardware Asset] " & _
                 "SET [OS] = " & Chr(34) & Me.[OS] & Chr(34) & ", " & _
                     "[Memory] = " & Chr(34) & Me.[Memory] & Chr(34) & " " & _
                 "WHERE [AssetNumber] = " & Chr(34) & Forms![Hardware Asset Update Form]![AssetNumber] & Chr(34)

I took your first two update statements. Use the same idea for the others.

Note: Chr(34) means double-quote, (i.e. ")

 

[whojstall11]

thank you i will change it but could you like at this post
http://www.access-programmers.co.uk/forums/showthread.php?t=228801

 

[vbaInet]

thank you i will change it but could you like at this post
http://www.access-programmers.co.uk/forums/showthread.php?t=228801

pbaldy and boblarson are already helping you on that thread. Either of them will give you feedback when they log-in.