Use a ComboBox Value as part of an SQL Query? (MS Access 2010-2013)

Question

I have a Database that we use to create Bills of Materials from Tags in AutoCAD. Because of the nature of this, I need to create 3 separate queries. One for our "Steel", one for our "Non-Steel", and one for our "Uncut Tubes".

The SQL for the Queries is as follows:

Steel:

SELECT DISTINCTROW Sum([CUT-LENGTH-WEIGHT]) AS [SumOfCUT-LENGTH-WEIGHT], Sum([CUT-SHEET-WEIGHT]) AS [SumOfCUT-SHEET-WEIGHT], Sum([TOTAL-SHEETING-WEIGHT]) AS [SumOfTOTAL-SHEETING-WEIGHT], Sum([TOTAL-ITEM-WEIGHT]) AS [SumOfTOTAL-ITEM-WEIGHT]
FROM [13-1302 Cut-Lengths]; 

Non-Steel:

SELECT tbl2013BOM.fJobID, Sum(tbl2013BOM.fWeight) AS SumOffWeight
FROM tbl2013BOM
GROUP BY tbl2013BOM.fJobID
HAVING (((tbl2013BOM.fJobID)=23));

Uncut Tubes:

SELECT DISTINCT [13-1302 Cut-Lengths].[TOTAL-LENGTH-WEIGHT], [13-1302 Cut-Lengths].MATERIAL, [13-1302 Cut-Lengths].ORDER
FROM [13-1302 Cut-Lengths]
ORDER BY [13-1302 Cut-Lengths].ORDER;

I have a ComboBox that chooses the Job Number (For Main and Uncut Tubes, e.g. 13-1302) and a Textbox that displays the JobID (For Non-Steel).

Is there a way that I can set up the SQL shown above to look at the ComboBox and TextBox Values, instead of me having to change them by hand?

EDIT

---

I figured it all out now. (Thank you Elias)

Basically, I cannot use a Field on a table as a RecordSource in SQL, in other words, Combo26 cannot be the Table in an SQL Query. HOWEVER, what CAN be done is to use VBA to inject that value into an SQL Definition, then use that definition as a Recordsource.

I will place the code for my Button below so anyone can use it and reference it:

Private Sub Command27_Click()

    Dim dbs As Database
    Dim rstSQL As DAO.Recordset
    Dim strSQL As String
    Dim strSQL2 As String
    Dim strSQL3 As String

    Dim Field As String
    Set dbs = CurrentDb

    Field = [Forms]![frmBOM_Combined]![Text26].[Value]

    strSQL = "SELECT DISTINCTROW Sum([CUT-LENGTH-WEIGHT]) AS [SumOfCUT-LENGTH-WEIGHT], Sum([TOTAL-SHEETING-WEIGHT]) AS [SumOfTOTAL-SHEETING-WEIGHT], Sum([TOTAL-ITEM-WEIGHT]) AS [SumOfTOTAL-ITEM-WEIGHT] FROM " & "[" & [Forms]![frmBOM_Combined]![Text26].[Value] & "]" & ";"
    strSQL2 = "SELECT tbl2013BOM.fJobID, Sum(tbl2013BOM.fWeight) AS SumOffWeight FROM tbl2013BOM GROUP BY tbl2013BOM.fJobID HAVING (((tbl2013BOM.fJobID)= " & [Forms]![frmBOM_Combined]![Combo25].[Value] & "));"
    strSQL3 = "SELECT DISTINCT [TOTAL-LENGTH-WEIGHT], [MATERIAL], [ORDER] FROM " & "[" & [Forms]![frmBOM_Combined]![Text26].[Value] & "]" & " ORDER BY [ORDER];"

    Debug.Print strSQL
    Debug.Print strSQL2
    Debug.Print strSQL3

    DoCmd.OpenForm ("frmEstWeight")

    Forms!frmEstWeight.RecordSource = strSQL
    Forms!frmEstWeight.frmTestBomWeight.Form.RecordSource = strSQL2
    Forms!frmEstWeight.frmTotalLengthWeight.Form.RecordSource = strSQL3

End Sub

This is working exactly as it should with no errors or anything.

Solution

This is within a form correct?

If so, replace the manual values you put in with

REST OF THE QUERY HERE " & Me!Controlname.value & " REST OF THE QUERY HERE

and if you are using something with a control source then just reset the control source value.

me!ControlWithResult.control source = "SELECT tbl2013BOM.fJobID, Sum    (tbl2013BOM.fWeight) AS SumOffWeight
FROM tbl2013BOM
GROUP BY tbl2013BOM.fJobID
HAVING (((tbl2013BOM.fJobID)=" & me!controlname.value & "));"

For Non-Steel try:

On the VBA for the popup form

me!Combo25.rowsource = "SELECT tbl2013BOM.fJobID, Sum(tbl2013BOM.fWeight) AS SumOffWeight
FROM tbl2013BOM
GROUP BY tbl2013BOM.fJobID
HAVING (((tbl2013BOM.fJobID)=" & forms!MAINFORMNAME! &"));

Comments

[ZLHysong]

Am I supposed to use the Quotation Marks as well?

[Elias]

Where are you queries executing? it depends on that

[ZLHysong]

The Queries are separate Queries that run when a Pop-Up Form is loaded. EDIT: Sorry, I didn't realize that Enter commits the comment. Here is a list of mt Tables/Forms/Queries/Etc. mediafire.com/?vb0p2tt9l7adceh Here is the menu showing the (now 2) TextBoxes I will need to use. mediafire.com/?s7edrurhyl8iu8o And here is a link to the Pop-Up Form itself: mediafire.com/?qa72ad3xx26jcup

[ZLHysong]

Did you see my Edit to the comment? When the Popup form is loaded, as you can see in the Pictures above, the values from the Queries are shown in the various TextBoxes there.

[Elias]

I can't view that at the moment. Although if you list the form names, and the control type that the query will result into, I can help now.

[ZLHysong]

The form name is "frmBOM_Combined" and the control name that I need to use the value from for the Main and Uncut-Tubes is simply Text26, and for the Non-Steel is Combo25. Is that what you need?

[ZLHysong]

No it is a form that contains a little under a dozen TextBoxes. All of them currently work, if I modify the code by hand. So what I am looking for is the line I need to replace FROM [13-1302 Cut-Lengths] with FROM (My ComboBox). Also, I got the one for the Non-Steel working thanks to you. Now to the other two.

[ZLHysong]

I'm sorry, but I got the Non-Steel one working properly. What I need is the Main(Steel) one and the Uncut-Steel one.

[Elias]

Okay, whare are the values that need changed in there? The '13-1302'?

[ZLHysong]

In this code: SELECT DISTINCT [TOTAL-LENGTH-WEIGHT], MATERIAL, ORDER FROM [13-1302 Cut-Lengths] ORDER BY ORDER; I need to replace [13-1302 Cut-Lengths] with the value found at Forms!frmBOM_Combined!text26.Value

[Elias]

"SELECT DISTINCT [TOTAL-LENGTH-WEIGHT], MATERIAL, ORDER FROM " & Forms!frmBOM_Combined!text26.Value & "ORDER BY ORDER;". On a side note, change your column name of order to something else, because SQL uses that word and it could cause you issues in the future

[ZLHysong]

When I use that, I get " '" & Forms!frmBOM_Combined!text26.Value & "' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long." And on that side note, I will make a note of that and see what I can do.

[Elias]

Where are yo putting it also, use this instead, I made a minor error "SELECT DISTINCT [TOTAL-LENGTH-WEIGHT], MATERIAL, ORDER FROM " & Forms!frmBOM_Combined!text26.Value & " ORDER BY ORDER;"

[ZLHysong]

I still get the same exact error. I am putting it into the SQL View of the Query itself.

[Elias]

I don't think you can reference a control value through the query window?

[ZLHysong]

This code works though: SELECT tbl2013BOM.fJobID, Sum(tbl2013BOM.fWeight) AS SumOffWeight FROM tbl2013BOM GROUP BY tbl2013BOM.fJobID HAVING (((tbl2013BOM.fJobID)= Forms!frmBOM_Combined!fJobID.Value));