Help with different approach, SQL instead of Pass through?

dkotula

Board Regular
Joined
Apr 12, 2006
Messages
160
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.
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
If someone could enlighten me on how to achieve this, it would be greatly appreciated.
Dave
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top