Multiple combo box selection within a form select record from results

[obdonone]

First my apology for the length of ths request.

Access 2003; 4 tables (tblcustomers, tblCmps, tblSpecs, and tblStocks). I have a form called "Quotes" which has a subform called "subfrmqtes". The "tblcmps" holds information on "Compounds", the "tblSpecs" holds "specificatons", the "tblStocks" holds "Stocks" (material types) information, and the "tblcustomers" hold customer information.

"Compounds" are created with "Stocks" and "Specifications" in a one-to-many relationship with the uniques being the "Compound Code", "SPGV", "Batch Price" and other cost.

On the "Quotes" form I have two combo box (cboStocks, cboSpecs) with an activate button. The following code is applied to the onClick property on the button:

Private Sub Cmplookup_Click()
Dim strcboStocks As String
Dim strcboSpecs As String

If IsNull(CboStocks) Or IsNull(CboSpecs) Then
MsgBox "You must choose both a Stock Type and a Specification."
End If

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

CboStocks.SetFocus
CboSpecs.SetFocus
If CboStocks.Value > 0 And CboSpecs.Value > 0 Then
strSQL = "SELECT * FROM Compounds WHERE StockID = " & CboStocks.Value
strSQL = "SELECT * FROM Compounds WHERE SpecNumber = " & CboSpecs.Value

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then

Me.CompoundID = rs("CompoundID")
Me.CompoundCode = rs("CompoundCode")
Me.NWRECode = rs("NWRECode")
Me.CompoundStock = rs("CompoundStock")
Me.CompoundSPGV = rs("CompoundSPGV")
Me.CompoundCost = rs("MillBatch")
Me.FreightCost = rs("FreightCost")
Me.MillCost = rs("MillCost")
Me.CatalystCost = rs("CatalystCost")
Me.ColorCost = rs("ColorCost")


End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End If


End Sub

This fills the select controls on the form and all caculations operate collectly. My Problem is this: My code selects the first combo box and fills the form with its information (in this the "Stock" only). Certain selections from the combo boxes should retrieve 3 to 10 records. When I execute a query from the design grid with certain stocks and specs as the criteria it displays all compounds made of the particular stock and specs.

How do I bind this query to the button on my form and select with a mouse click the record I want to use to complete the quote.

Thank you for whatever way you can help.

DJL

 

[jbpez]

The query string should be,

strSQL = "SELECT * FROM Compounds WHERE StockID = " & Me.CboStocks.Value & " AND SpecNumber = " & Me.CboSpecs.Value

I hope this helps

John Borges

 

[obdonone]

The response given by "jbpez" worked to a certain extent. When the code excuted it did indeed retrieve the selection of the "cboStocks" and "cboSpecs" combo boxes; and the rest of the form was filled in accordingly.

But the particalur combination has 3 records in the "tblCmps" table. Is there a way to have a datasheet popup (based on a query from the combo box selections) when the button is clicked, and select the record desired to complete the form entries. Again a test query from the design grid displays all the records for the "stock" and "specs" set in the criteria fields.

Any thoughts would be greatly appreciated. Thank you.


DJL

 

[PHV]

You may consider a ListBox with RowSource set dynamically to strSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jbpez]

I agree with PHV, a listbox would be ideal for your situation.

John Borges