find and update a record using multipe critea

[pds8475]

Hi
I Have a forms which brings up a record in textboxes when you hit a search Button using a value that you type into a Textbox called BarTxt. My problem is that this value may not be unique. Therefore I'm trying to find and update the table using 2 values from the table (Barcode and PurchaseOrder)
I have entered the code

 Private Sub SearchBtn_Click()
  
 PNTxt = DLookup("PartNumber", "BookInTable", "BarCode ='" & [BarTxt] & "'" And "PurchaseOrder ='" & [POTxt] & "'")
  
 End Sub

This however is just bringing up a Type mismatch error. Even though both are text fields.

Also even if I just use Barcode to search which works. My update Code

 DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "'  WHERE BarCode ='" & [BarTxt] & "'" AND PurchaseOrder ='" & [POTxt] & "'"

I get an Compile error saying "expected expression".

Any help would be appreciated

 

[JHB]

You have a " to much:

 Private Sub SearchBtn_Click()
  
 PNTxt = DLookup("PartNumber", "BookInTable", "BarCode ='" & [BarTxt] & "'[B][COLOR=Red]"[/COLOR][/B] And "PurchaseOrder ='" & [POTxt] & "'")
  
 End Sub

This however is just bringing up a Type mismatch error. Even though both are text fields.

Also even if I just use Barcode to search which works. My update Code

 DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "'  WHERE BarCode ='" & [BarTxt] & "'[B][COLOR=Red]"[/COLOR][/B] AND PurchaseOrder ='" & [POTxt] & "'"

I get an Compile error saying "expected expression".

Any help would be appreciated

I think you also need 2 # here:

 DoCmd.RunSQL "Update BookInTable SET DateBookedOut = [B][COLOR=Red]#[/COLOR][/B]" & Me!DateTxt & "[B][COLOR=Red]#[/COLOR][/B]  WHERE BarCode ='" & [BarTxt] & "'[B][COLOR=Red]"[/COLOR][/B] AND PurchaseOrder ='" & [POTxt] & "'"

 

[pds8475]

Thanks JHB

I have tried to change the code line to

 PNTxt = DLookup("PartNumber", "BookInTable", "BarCode ='" & [BarTxt] & "' And PurchaseOrder ='" & [POTxt] & "'")

This does not throw an error but I'm not getting any output from the DLookup now?

 

[pds8475]

Nevermind the Code

 PNTxt = DLookup("PartNumber", "BookInTable", "BarCode ='" & Me![BarTxt] & "' AND PurchaseOrder ='" & [Text22] & "'")

works.
Which as far as I can see is exactly the same other than I am pointing it at another textbox. So I'm guessing it's a glitch with the original textbox.

Thanks for your help