Changing SQL to VB code for use in MSA

[glitke]

I have no clue how to write in code but my Access program needs demand that I use code to validate a field. Somewhere I read you can copy an Access query's SQL code into VB code and only need to modify the controls that have user defined parameters.

I already have a query built that generates this SQL:

SELECT [T - Main Frame].[MSO #], [T - Main Frame].[Part #], Sum([T - Main Frame].Quantity) AS SumOfQuantity
FROM [T - Main Frame]
WHERE ((([T - Main Frame].Status)="Received" Or ([T - Main Frame].Status)="Shipped" Or ([T - Main Frame].Status)="Scrapped"))
GROUP BY [T - Main Frame].[MSO #], [T - Main Frame].[Part #]
HAVING ((([T - Main Frame].[MSO #])=136556) AND (([T - Main Frame].[Part #])="10R2538"));

I think I have to change the "136556" to "Forms!Shipping!Combo3", and "10R2538" to "Forms!Shipping!Combo8" which are the values in a set of combo boxes on my form.

Here is the code in VB that I have to do this "inline". The "strSQL" in the IF statement is the above SQL.

Private Sub Quantity_AfterUpdate()
If Quantity > strSQL Then
MsgBox "Insufficient quantity available for current MSO #", vbOKOnly
Me!Control = Null
Me!Control.SetFocus
End If
End Sub

Basically I don't know how to structure the SQL in code so that it performs the same action as the query. Any input would be helpful.

If anyone knows where I can get a quick reference to the format for building these types of statements in code I could pick it up myself.

Obviously I should take a class for this, but hopefully someone has the time to help while I schedule that class for next trimester. Today marks day 3 of my attempt at getting this to work.

If I should post in a different forum please feel free to tell me so.

[debasisdas]

i dont understand what is the use of the following line in your code

If Quantity > strSQL Then

[glitke]

i dont understand what is the use of the following line in your code

If Quantity > strSQL Then

This line checks to see if the value that a user inputed is > the available quantity to be shipped.

So the "Quantity" is what the user inputted.

The "strSQL" is actually a query that finds the max quantity available.

The way the "available quantity to be shipped" is calculated is by
1. - selecting all records on the table, T - Main Frame,
- where the records status is either "Shipped", "Scrapped", or "Received",
and
- where the MSO# of that record matches what the user selected from a
ComboBox on the same form, Combo3, and
- where the Part # of that record matches what ther user selected from a
ComboBox on the same form, Combo8.

2. - Then the sum of the field "Quantity" of the records selected is taken and
produces a single number, the "max quantity available".

I hope that explains it. If it still doesn't make sense don't worry about it. I'll hammer my way through it in time.

[debasisdas]

you can't compare two strings like that using > operator.

[glitke]

you can't compare two strings like that using > operator.

Ok. Thanks for the info.

[glitke]

you can't compare two strings like that using > operator.

So how do you compare two strings in code then?

[glitke]

So how do you compare two strings in code then?

Here's what I've come up with. And this code goes under "Event - Lost Focus" for the control "Quantity" on Form "Shipping".

Private Sub Quantity_LostFocus()

If (StrComp("[Quantity]", "[Q - Limit Quantities]![Sum Of Quantity]", vbBinaryCompare)) = 1 Then
MsgBox "Insufficient Quantity", vbOKOnly
End If

End Sub

Another note, the value of the "Sum Of Quantity" from SQL "[Q - Limit Quantities]![Sum Of Quantity]", is from a Query that uses the values in two ComboBoxes on the form that I'm using this code.