Any assistance, examples and recommendations would be greatly appreciated. The section of code I need help with is listed below. My code works as is (when I unremark the section which is remarked). How do I create a multi column listbox and have it lauch from the section of my code where the form "frmCmpList" is luanching. See statement below the "Set Rst" line for what is required.
Private Sub Cmplookup_Click()
Dim strcboStocks As String
Dim strcboSpecs As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
If IsNull(CboStocks) Or IsNull(CboSpecs) Then
MsgBox "You must choose both a Stock Type and a Specification."
End If
Set db = CurrentDb
Set qdf = db.QueryDefs("qryCmpFilter")
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
qdf.SQL = strSQL
'DoCmd.OpenQuery "qryCmpFilter"
DoCmd.OpenForm "frmCmpList", acNormal
Set rst = db.OpenRecordset(strSQL)
'The section of code below is to be excuted after a "CompoundCost" (noted as "MillBatch")is selected from the strSQL results. There are 5 columns in each Row of the strSQL results which are "CompoundCost" ("MillBatch1, 2, 3, 4, 5) options. The user only need to select one columns - field (item). A requery of restart of the code should occur and the code listed just below would excute using the "CompoundCost" (MillBatch) selected by the user. Or is the a way to allow the "CompoundCost to be automatically filled with the "MillBatch" selection made by the user. The "frmCmpList" has a listbox with which is filled with the results of strSQL. I created this list box with the listbox tools, but I am lost as too how to create
with DAO. The form "frmCmpList" loads with the correct records, but I would like to how to create just the list with DAO and code the columns so that an individual field can be selected and used in the process below.
'If Not rst.BOF Then
'Me.CompoundID = rst("CompoundID")
'Me.CompoundCode = rst("CompoundCode")
'Me.NWRECode = rst("NWRECode")
'Me.CompoundStock = rst("CompoundStock")
'Me.CompoundSPGV = rst("CompoundSPGV")
'Me.CompoundCost = rst("MillBatch")
'Me.FreightCost = rst("FreightCost")
'Me.MillCost = rst("MillCost")
'Me.CatalystCost = rst("CatalystCost")
'Me.ColorCost = rst("ColorCost")
End If
rs.Close
Set qdf = Nothing
Set rs = Nothing
db.Close
Set db = Nothing
End If
End Sub
Private Sub Cmplookup_Click()
Dim strcboStocks As String
Dim strcboSpecs As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
If IsNull(CboStocks) Or IsNull(CboSpecs) Then
MsgBox "You must choose both a Stock Type and a Specification."
End If
Set db = CurrentDb
Set qdf = db.QueryDefs("qryCmpFilter")
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
qdf.SQL = strSQL
'DoCmd.OpenQuery "qryCmpFilter"
DoCmd.OpenForm "frmCmpList", acNormal
Set rst = db.OpenRecordset(strSQL)
'The section of code below is to be excuted after a "CompoundCost" (noted as "MillBatch")is selected from the strSQL results. There are 5 columns in each Row of the strSQL results which are "CompoundCost" ("MillBatch1, 2, 3, 4, 5) options. The user only need to select one columns - field (item). A requery of restart of the code should occur and the code listed just below would excute using the "CompoundCost" (MillBatch) selected by the user. Or is the a way to allow the "CompoundCost to be automatically filled with the "MillBatch" selection made by the user. The "frmCmpList" has a listbox with which is filled with the results of strSQL. I created this list box with the listbox tools, but I am lost as too how to create
with DAO. The form "frmCmpList" loads with the correct records, but I would like to how to create just the list with DAO and code the columns so that an individual field can be selected and used in the process below.
'If Not rst.BOF Then
'Me.CompoundID = rst("CompoundID")
'Me.CompoundCode = rst("CompoundCode")
'Me.NWRECode = rst("NWRECode")
'Me.CompoundStock = rst("CompoundStock")
'Me.CompoundSPGV = rst("CompoundSPGV")
'Me.CompoundCost = rst("MillBatch")
'Me.FreightCost = rst("FreightCost")
'Me.MillCost = rst("MillCost")
'Me.CatalystCost = rst("CatalystCost")
'Me.ColorCost = rst("ColorCost")
End If
rs.Close
Set qdf = Nothing
Set rs = Nothing
db.Close
Set db = Nothing
End If
End Sub
