Refer to feilds in subform within an If statement (1 Viewer)

[sashapixie]

Hi All

I am trying to run a message box using an If statement which is activated in a subform however the If statement is looking at a field in a separate subform, both subforms are within a main form.

The vba I have at the moment is below:

Private Sub Pick_Ship_Quantity_GotFocus()
If Me.Dirty = True Then Me.Dirty = False
If Forms![Order Entry Form]![Backlog Orders subform].Form.[Quantity] > Forms![Order Entry Form]![Backlog Orders subform].Form.[Current Stock] Then
MsgBox "You only have a low stock level of Items in your inventory", vbCritical, "Inventory Information"
End If
End Sub

Unfortunately I have been on this a while now and still cannot get it to work.

Any help is greatly appreciated.

 

[pbaldy]

How is it not working? The syntax to refer to the subforms appears correct. Here's a reference:

http://www.mvps.org/access/forms/frm0031.htm

 

[sashapixie]

Nothing happens, the message box doesn't appear but there appears to be no errors in my code.

 

[pbaldy]

Are you sure the code is running? Is any code running in the db? It needs to be in a trusted location or have code explicitly enabled. Do you know how to set a breakpoint or use Debug.Print to see what the values are when the code runs? If not:

http://www.baldyweb.com/ImmediateWindow.htm

http://www.baldyweb.com/Debugging.htm

 

[sashapixie]

Thanks for the tip Paul, I added this in and the result was FALSE, does this mean it is working?

I'm not if it makes a difference but the field Pick_Ship_Quantity is on a sub form in the main form and the fields I am looking at our on a separate subform in the same main form, they are all linked by Order ID.

 

[pbaldy]

Not sure exactly what you added and where, but if it was the test then it would seem like the data doesn't meet the criteria to give you the message box. Can you attach the db here?

 

[sashapixie]

Sorry Paul I amended it to this:

Private Sub Pick_Ship_Quantity_GotFocus()
Dim strSQL As String
strSQL = Forms![Order Entry Form]![Backlog Orders subform].Form![Quantity] > Forms![Order Entry Form]![Backlog Orders subform].Form![Current Stock]
Debug.Print strSQL
If Me.Dirty = True Then Me.Dirty = False
If Forms![Order Entry Form]![Backlog Orders subform].Form![Quantity] > Forms![Order Entry Form]![Backlog Orders subform].Form![Current Stock] Then
'If Me![Backlog Orders subform].Form![Quantity] > Me![Backlog Orders subform].Form![Current Stock] Then
MsgBox "You only have a stock level of Items in your inventory", vbCritical, "Inventory Information"
End If
End Sub

My db is to big it exceeds the maximum allowance for uploading on here

 

[pbaldy]

Then yes, the debug is telling you that the first value is not greater than the second. You can try things like

Debug.Print Forms![Order Entry Form]![Backlog Orders subform].Form![Quantity]

Also make sure your data types are numeric, or you'll get an alphabetic comparison where 2 is greater than 10. If you're still having trouble, see if this helps attach:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[sashapixie]

Here is the DB

 

[pbaldy]

The code is firing but the data doesn't qualify. If you reverse the test you get the message box. You don't appear to be comparing specific products. When you refer to the subform, you get either the first record's value or the record with focus. Add this and you'll see the values the code is using:

MsgBox "Is " & Forms![Order Entry Form]![Backlog Orders subform].Form![Quantity] & " > " & Forms![Order Entry Form]![Backlog Orders subform].Form![Current Stock]

 

[sashapixie]

Thank you, the message box is showing me the first record only as you mention in your post, I want the result to be taken from the line in focus so line two should be 10>50 not 1>10 as per the first line. Do you know how I can achieve this?

 

[pbaldy]

I can't say I understand your structure, but I would disregard the other subform and open a recordset on its underlying query, using the item from the subform the code is in for the criteria. You could also use DLookup(), but it would be less efficient since you want 2 values from that record.

By the way, the spaces and symbols in your names are going to prove to be more trouble than they're worth in the long run.

 

[sashapixie]

Thanks for all your help and advice Paul, I will try this tomorrow as it is late here now. I really appreciate your quick response to my post and replies on my first post.


I'll let you know if it works

Top Man!

 

[pbaldy]

No problem; have a nice evening!

 

[sashapixie]

Hi Paul

I have re-looked at my issue and by turning the > to a < does work however this is incorrect as the values stored in the fields should only run the If statement when the picked amount is greater than the stock amount as there is not enough stock to fulfil the order.

I also found another way of bringing the current stock into the subform Inventory Transactions Orders, by including this in the lookup for the Transaction Item and using the code Me.Transaction_Item.Column(2).

I have used this method before and it works a treat I just need to understand why access is not reading my calculation correctly.

Regards

Sashapixie

 

[pbaldy]

I don't think it's a matter of not reading your calculation, it's a matter of not referring to the record on the backlog subform that you intend. When you refer to a subform with multiple records on it, you will get either the first record or the record with focus. In my tests, it was always the first record, since I had not visited that subform to put focus anywhere else.

 

[sashapixie]

Hi Paul

I have fixed my issue with the following code:

Private Sub Pick_Ship_Quantity_AfterUpdate()
Dim CurrentStock As Long
Dim SelectedStock As Long
CurrentStock = Me.Transaction_Item.Column(2)
SelectedStock = Me.Pick_Ship_Quantity
If Me.Dirty = True Then Me.Dirty = False
If CurrentStock < SelectedStock Then
MsgBox "You only have a stock level of " & Me.Transaction_Item.Column(2) & " Items in your inventory", vbQuestion, "Inventory Information"
End If
End Sub

 

[pbaldy]

Glad you got it sorted out.