Hi
I have a data entry form bound to a table with 3 cascading combo boxes which pull data from a pass through query. I would like to try using SQL to perform this instead. My combo boxes are cboPO, cboLine, and cboRel. With SQL I don't know if a module is needed or an on open event to allow data to show in the combo boxes. Currently each combo box has an after event to allow specific data to show in the next, and after all three selections are made, a number of text boxes are populated.
Here is a tidbit of code I found, and have been trying to modify.
If someone could enlighten me on how to achieve this, it would be greatly appreciated.
Dave
I have a data entry form bound to a table with 3 cascading combo boxes which pull data from a pass through query. I would like to try using SQL to perform this instead. My combo boxes are cboPO, cboLine, and cboRel. With SQL I don't know if a module is needed or an on open event to allow data to show in the combo boxes. Currently each combo box has an after event to allow specific data to show in the next, and after all three selections are made, a number of text boxes are populated.
Here is a tidbit of code I found, and have been trying to modify.
Code:
Dim conStr As String
Dim SQLStr As String
Dim wrkJet As DAO.Workspace
Dim dbVantage As DAO.Database
Dim rst As DAO.Recordset
'ODBC Connect string
conStr = "ODBC;" & _
"DSN=Vantage;" & _
"Description=Vantage;" & _
"Database=mfgsys;" & _
"UID=*****;" & _
"PWD=*****;" & _
"PORT=6150"
' Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
' Open read-only Database object based on information in
' the connect string.
Set dbVantage = wrkJet.OpenDatabase(Name:="Vantage", Options:=dbDriverNoPrompt, ReadOnly:=True, Connect:=conStr)
'Query database and update fields
SQLStr = "SELECT PODetail_0.Company, PODetail_0.OpenLine, PODetail_0.PONUM, PORel_0.POLine, PORel_0.PORelNum, PORel_0.JobNum, Part_0.PartNum, Part_0.PartDescription, PartClass_0.Description, PODetail_0.VendorNum " & _
"FROM PUB.Part Part_0, PUB.PartClass PartClass_0, PUB.PODetail PODetail_0, PUB.PORel PORel_0 " & _
"WHERE Part_0.PartNum = PODetail_0.PartNum AND Part_0.Class = PartClass_0.Class AND Part_0.Company = PODetail_0.Company AND Part_0.Company = PartClass_0.Company AND Part_0.Company = PORel_0.Company AND PartClass_0.Company = PODetail_0.Company AND PartClass_0.Company = PORel_0.Company AND PartClass_0.Class = PODetail_0.Class AND PODetail_0.Company = PORel_0.Company AND PODetail_0.PONUM = PORel_0.PONum AND PODetail_0.POLine = PORel_0.POLine AND ((PODetail_0.Company='ABC') AND (PODetail_0.OpenLine=1))" & _
"ORDER BY PODetail_0.PONUM, PORel_0.POLine, PORel_0.PORelNum"
'Clear objects
' Set rst = Nothing
Set dbVantage = Nothing
Set wrkJet = Nothing
Dave