SQL Lookup Syntax Question

[DKasler]

Im trying to get data from 2 tables to be part of this lookup.

When you choose an insurance from a dbcombo box I want it to run an SQL statement that looks at table1 and finds ALL of the matches in that table then takes the DrID field in the same table and matches it with the DrID field in a 2nd table.

But Im not sure how to do that...

Insurance Combo -> Matching insurances in table1 -> DrID field in table1 == DrID field in table 2 -> Display DrName, DrAddress (from table2) in MSHFlexgrid

I can display the data fine... im just not sure how create that type of SQL lookup.

USUALLY When I do something to fill a mshflexgrid I do it like this... So something that would work with the below code would be great! Thanks.

Dim sConnect As String
    Dim sSQL As String
    Dim dfwConn As ADODB.Connection
    Dim datPrimaryRs As New ADODB.Recordset
        
    'set strings
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & frmlocation.Text1.Text & "';Persist Security Info=False"
    sSQL = "select InsRecID, SpecID, Insurance, InsuranceID from DrsInsuranceList where SpecID like '" & Text7.Text & "'"
    
    ' open connection
    Set dfwConn = New ADODB.Connection
    dfwConn.Open sConnect
    
    'create a recordset using the provided collection
    datPrimaryRs.CursorLocation = adUseClient
    datPrimaryRs.Open sSQL, dfwConn, adOpenForwardOnly, adLockReadOnly
    
    Set MSHFlexGrid1.DataSource = datPrimaryRs

[Hack]

Alias your table names. I don't know what your table or field names are, so I just made stuff up, but try something along the lines of

sSQL = "select drsins.InsRecID, drsins.SpecID, drsins.Insurance, drsins.InsuranceID, "
sSQL = sSQL & "doctable.drid, doctable.drname, doctable.address from DrsInsuranceList drsins, "
sSQL = sSQL & "DoctorsTable doctable where drsins.SpecID like '" & Text7.Text & "'"
sSQL = sSQL & "and drsins.drid = doctable.drid"

Bear in mind this is off the top of my head, but you should get the general idea.

[GaryMazzone]

If you are going to use a LIKE for the seach you might as well include the wild card value in the creteria along with text7.text

[DKasler]

Thanks, but I think im even more confused now than I was before...

Lets try this with the actual table & field names.

DrsInsuranceList - TABLE
-SpecID
-Insurance

When someone choose an insurance from dbcombo2 I want it to look for all matches in the "Insurance" field. Then Match the field SpecID with the SpecID field from the "Specialists" table

Specialists - Table
-SpecID
-SpecialistType
-SpecialistName

Then once we have matched the "SpecID" fields from the 2 tables I want to display the "SpecialistType" and "SpecialistName" fields in MSHFlexgrid1

PS. Thanks SO much!

[GaryMazzone]

strsql = "Select SpecialistType,SpecialistName From Specialists Where "
strsql = strsql & "SpecID = (Select SpecID From DrsInsuranceList Where "
strsql = strsql & "Insurance = '" & dbcombo2.text & "'"

[DKasler]

strsql = "Select SpecialistType,SpecialistName From Specialists Where "
strsql = strsql & "SpecID = (Select SpecID From DrsInsuranceList Where "
strsql = strsql & "Insurance = '" & dbcombo2.text & "'"

Huge step in the right direction... It works where only ONE dr has the insurance chosen from the list... but when more than 1 has it I get this error

Run-time error '-2147467259(80004005)
At most one record can be returned by this subquery.

[GaryMazzone]

strsql = "Select SpecialistType,SpecialistName From Specialists Where "
strsql = strsql & "SpecID In (Select SpecID From DrsInsuranceList Where "
strsql = strsql & "Insurance = '" & dbcombo2.text & "'"

[DKasler]

strsql = "Select SpecialistType,SpecialistName From Specialists Where "
strsql = strsql & "SpecID In (Select SpecID From DrsInsuranceList Where "
strsql = strsql & "Insurance = '" & dbcombo2.text & "'"

I changed the "=" to "In" and now I get an error saying "type mismatch in expression"

[GaryMazzone]

Opps add ad close peran at the end of the sql.

strsql = "Select SpecialistType,SpecialistName From Specialists Where "
strsql = strsql & "SpecID In (Select SpecID From DrsInsuranceList Where "
strsql = strsql & "Insurance = '" & dbcombo2.text & "')"

That might help

[DKasler]

Opps add ad close peran at the end of the sql.

strsql = "Select SpecialistType,SpecialistName From Specialists Where "
strsql = strsql & "SpecID In (Select SpecID From DrsInsuranceList Where "
strsql = strsql & "Insurance = '" & dbcombo2.text & "')"

That might help

Yea, I added that already. It worked ok with they way you first posted it, but only for a single match. It seems to be when more than one match is found it throws a fit.

I think the "Type Mismatch in expression" error is being cause by the "In" on the 2nd line.

[GaryMazzone]

Can you post the SQL that is actually being built and sent to the database? Use a debug pring statement and it should go the development immediate window then you could post it here.

[GaryMazzone]

What I think is happening is that the text is all just strung together in one type name. It will need to be seperated out and posible another in statement for the second (sub) query.

[DKasler]

Here is my full code:

Dim sConnect As String
    Dim sSQL As String
    Dim dfwConn As ADODB.Connection
    Dim datPrimaryRs As New ADODB.Recordset
        
    'set strings
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & frmlocation.Text1.Text & "';Persist Security Info=False"
    sSQL = "Select SpecialistType,SpecialistName From Specialists Where "
    sSQL = sSQL & "SpecID In (Select SpecID From DrsInsuranceList Where "
    sSQL = sSQL & "Insurance = '" & DBCombo2.Text & "')"
    
    ' open connection
    Set dfwConn = New ADODB.Connection
    dfwConn.Open sConnect
    
    'create a recordset using the provided collection
    datPrimaryRs.CursorLocation = adUseClient
    datPrimaryRs.Open sSQL, dfwConn, adOpenForwardOnly, adLockReadOnly
    
    Set MSHFlexGrid1.DataSource = datPrimaryRs