How would I write this IF statement

[robsworld78]

Hi, I have a subform set to continuous which has several records on it. One of the columns has a dropdown which shows a list of products to place on the order.

How would I write an IF statement to say the following?

IF me.recordset contains me.productID then
do something
Else
do something else
End if

Thanks

[June7]

Where do you want this code - in VBA event, perhaps AfterUpdate of the combobox?

By that pseudocode, I can only guess you want to check if a value is already selected and base action on the result. Tell use what you are trying to accomplish and maybe we can advise how. Or give us real code you have attempted and errors.

[robsworld78]

Yeah I was thinking in the AfterUpdate of the combobox.

That's right I want to check if a value is already selected in the current recordset of the subform.

I tried variants of the following code, it never worked, no errors but always same message box.

If Me.Recordset = Forms!CreateOrders!VendorInventoryLevelSubform.ProductID Then
'MsgBox "Product Found!"
'Else
'MsgBox "Product Not Found!"
'End If

I've tried this.

If Not IsNull(Me.BarsGivenExtra) Then
  If DCount("Product", "Inventory") = Me.Recordset.ProductID Then
    MsgBox "Product Found!"
  Else
    MsgBox "Product Not Found!"
  End If
 End If

And I've tried the following.

If Not IsNull(Me.BarsGivenExtra) Then
  If DCount("Product", "Inventory", "[Product] = '" & Me.Productdropdown & "'") > 0 Then
    MsgBox "Product Found!"
  Else
    MsgBox "Product Not Found!"
  End If
 End If

[robsworld78]

Here's what I'm trying to accomplish.

The subform is for order details and the dropdown column holds the actual product description.

I do have code working that removes an item from the combobox once a user selects it.

Example: The combobox has these values.

Item1
Item2
Item3
Item4

Now if i select item3 from the combobox it will disappear from the combobox so a user can't have duplicate items on an order.

But now I've done some automating on the form so the order details subform gets autofilled with items, now because I didn't manually select them with the combobox they still exist in the combobox which makes its possible for me to have the same item twice. If the item was on the order and I didn't see it and select it again from the combo box I get 2 the same and I can't have that.

So now I want some code in the AfterUpdate of the combobox which checks all the records in the subform (the productID column) and IF the item selected from the combobox has a matching "productID" THEN do something ELSE do nothing.

I can do the rest of the code I need help with the actual IF statement I can fill in what happens.

Hope that makes sense.

[June7]

If you want to know if this product in table for this user name, need to include user name in the search criteria.

"[Product] = '" & Me.Productdropdown & "' AND UserID=" Me.UserID

[robsworld78]

Thanks, when I try that and change userID to orderID (this is for the order not user) it gives me a runtime error 3075, syntax error in string in query expression '[Product] = '41 AND OrderID=916'.

I've played with quotes etc but always the same or worse, it does have the ID's correct and they always are when this error comes up.

If DCount("Product", "Inventory", "[Product] = '" & Me.Productdropdown & " AND OrderID= " & Me.OrderID) > 0 Then

[June7]

Quotes and apostrophes must be in pairs. Missing second apostrophe for Me.Productdropdown.

Is [Product] a text or number field? The criteria looks look a number. If the combobox is bound to a numeric unique ID field for product, then the field in the DCount should also be number (the fk field). If these are numeric fields and data, remove the apostrophes which delimit string value (# for dates, nothing for numbers).

[robsworld78]

Thanks, I got it working but now it always meets the criteria, even if the product isn't in the order yet. I think it finds the product is because technically its there once selected.

Some how I need the IF statement to look insde a query or recordset. I can make a query with all the records in the subform but how would I tell it to search inside the query for the matching productID using an IF statement?

Is there something like IF me.productID = From "Query" or "Recordset" Then

[June7]

Oh, try the BeforeUpdate event instead.

Search on table or form recordset (would be RecordsetClone), just have to get the correct event.

[robsworld78]

You sir are brilliant, works like it should, here's the final code in the BeforeUpdate of the combobox.

  If DCount("Product", "Inventory", "[Product] = " & Me.Productdropdown & " AND OrderID= " & Me.OrderID) > 0 Then
    MsgBox "Product Found!"
  Else
    MsgBox "Product Not Found!"
  End If

Thank-you for your help!

[robsworld78]

Of course, things are never that easy, it works but guess what it won't do what I want because its before the table got updated, lol.

What I want to happen is after the message box that says "Product found" it deletes the product/record I added. So I added the following.

DoCmd.SetWarnings False
On Error Resume Next
DoCmd.RunSQL "Delete From Inventory" & " WHERE InventoryID = " & Me.InventoryID & ""
Me.Requery
DoCmd.SetWarnings True

But because its BeforeUpdate that code does nothing. When I put that piece of code under AfterUpdate it works like it should.

So now I need some kind of IF statement on the AfterUpdate that knows IF the criteria for BeforeUpdate was a match, it needs to work with the IF statement on the BeforeUpdate except reverse.

Make sense?

[June7]

Try using DoCmd.RunCommand acCmdUndo in the BeforeUpdate or simply Me.Undo

Also review this about BeforeUpdate http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[robsworld78]

Thanks! I did try DoCmd.RunCommand acCmdUndo before I posted but it says action "undo" isn't available and put me in a loop, had to press ESC to get out then it removed it.

I just tried me.undo and it works.