How do I display record generated by combo selections

[obdonone]

The code below is for the onclick event property of a button on my form. When the combo box selections are made and the button is clicked my form is automatically completed. My problem is that the combo selections always generated multiple records. How do I modify this code to display a list or popup form of the records generated to allow the selection of the desired record? Any help would be greatly appreciated.

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 = " & Me.CboStocks.Value & " AND SpecNumber = " & Me.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

 

[PHV]

You may consider a ListBox with RowSource dynamically set to "SELECT * FROM Compounds WHERE StockID = " & Me.CboStocks.Value & " AND SpecNumber = " & Me.CboSpecs.Value

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