Need to add 2nd criteria to strSQL

[robsworld78]

I have a form that updates a field for a table for a specific order when the form opens, now I want to apply that same update except with a 2nd criteria. Here's what I have that works on the OnLoad event.



Dim strBarsReturned As String
Dim OrderID As Long
Dim strSQL As String

strBarsReturned = 0
On Error Resume Next
OrderID = Me!NightCountOrdersSubform.Form!OrderID

strSQL = "UPDATE Inventory set Inventory.[BarsReturned] = " & strBarsReturned & " where Inventory.[OrderID] = " & OrderID
CurrentDb.Execute strSQL

Now I want to add the following code but can't get it.

"where Inventory.[BarsSold] = " & IsNull

This would go in the OnClose event. The idea is if someone blanks out the field on the form when it closes it will add a 0 to the blank records on that order for that field.

[pbaldy]

Try

strSQL = "UPDATE Inventory set Inventory.[BarsReturned] = " & strBarsReturned & " where Inventory.[OrderID] = " & OrderID & " AND Inventory.[BarsSold] Is Null"

[robsworld78]

thanks, that does work however I need it to be different, instead of updating with a 0 I need it to update from a control on the form. Here's what I have.

Dim strBarsSold As String
Dim OrderID As Long
Dim strSQL As String

strBarsSold = Me!NightCountInventorySubform.Form!BarsLeft
OrderID = Me!NightCountOrdersSubform.Form!OrderID

strSQL = "UPDATE Inventory set Inventory.[BarsSold] = " & strBarsSold & " where Inventory.[OrderID] = " & OrderID & " AND Inventory.[BarsSold] Is Null"

CurrentDb.Execute strSQL

What this does is populate all the records for that order with the same number, not the number in BarsLeft for that specific record. Its picking one record from the BarsLeft subform and using it for all the records in the order. I need it to go record by record. These records use InventoryID for the ID. The 2 subforms have a record set so the records follow each other on each form.

[pbaldy]

Am I missing something? It sounds like you just need to use InventoryID instead of OrderID.

[robsworld78]

That's what I thought however it doesn't work, no errors come with the code, if I change the location for InventoryID it throws an error cause it can't find so it is finding it with the following code. The InventoryID is on 2 subforms I tried referring to both but same results.

What I originally wanted was the form not to close if a field is left blank or is a negative number, couldn't get that working so I tried this. I think doing the originally idea is easier and works just as effective if I can get it for all records.

When I hit the post button on the form this is the code that's run.

If Forms!NightCount!NightCountEnterReturnsSubform.For m!BarsReturned >= 0 And Forms!NightCount!NightCountEnterReturnsSubform.For m!BarsSold >= 0 Then
Me.Status = "Review"
DoCmd.Close
Else
MsgBox "You must have missed an item, please try again! ", , "Please Try Again"
End If

The msgbox will show up if your on the record that's empty or a negative. So If I blank "BarsReturned" on one record the error will show as long as I stay on that record, when I go to another record it will post the order because the current record it sits on isn't empty or negative. If forgets to look at all the child records on the order.

[pbaldy]

I would probably use the subform's before update event to prevent the 0 or Null records from getting saved in the first place. Or setting a validation rule at the table level so Access will enforce it for you.

[robsworld78]

Thanks, I feel like an idiot, I forgot about the validation rule, that should work fine.

[pbaldy]

Happy to help. Sometimes there are so many ways to do something it's hard to remember them all.

[robsworld78]

Yeah how true but that's whats nice about access, anything can be accomplished if you have the know how and a good day, hopefully soon I'll have the know how.