I am having a problem querying two tables in my database. I have search the net and getting confused now. My code is below.

Private Sub Form_Load()
Public myconnection As ADODB.Connection

Set myconnection = New ADODB.Connection

myconnection.Provider = "microsoft.jet.oledb.4.0"
myconnection.ConnectionString = " data source = k:\curtains.mdb"

myconnection.Open

Set rsmemberbals = New ADODB.Recordset
Set rsmembermain = New ADODB.Recordset

Dim sqlstr As String
sqlstr = "SELECT Membermain.LTDAno, Membermain.Forename, Memberbals.S1bal, Memberbals.S2bal, Memberbals.S3bal, Memberbals.L1bal, Memberbals.L2bal, Memberbals.L3bal" & _
            "FROM Membermain RIGHT JOIN Memberbals ON Membermain.Memberno = Memberbals.Memberno" & _
            "WHERE (((Membermain.LTDAno) Is Not Null) AND ((Membermain.Memberstatus)='A'));"

End Sub


Please help.

and the problem is..???

you miss half of your post...

at first.. you are never opening the recorsets...



HTH

Gonzalo

Sorry things got muddled up.

Hows this.

Dim myconnection As ADODB.Connection
Dim rsmembermain As ADODB.Recordset
Dim rsmemberbals As ADODB.Recordset

Private Sub Form_Load()

Set myconnection = New ADODB.Connection

Set rsmemberbals = New ADODB.Recordset
Set rsmembermain = New ADODB.Recordset

myconnection.Provider = "microsoft.jet.oledb.4.0"
myconnection.ConnectionString = " data source = k:\curtains.mdb"
myconnection.Open

rsmembermain.Open "membermain", myconnection
rsmemberbals.Open "memberbals", myconnection

Dim sqlstr As String
sqlstr = "SELECT Membermain.LTDAno, Membermain.Forename, Memberbals.S1bal, Memberbals.S2bal, Memberbals.S3bal, Memberbals.L1bal, Memberbals.L2bal, Memberbals.L3bal" & _
            "FROM Membermain RIGHT JOIN Memberbals ON Membermain.Memberno = Memberbals.Memberno" & _
            "WHERE (((Membermain.LTDAno) Is Not Null) AND ((Membermain.Memberstatus)='A'));"

End Sub


I would like to see the result of the query but dont know what to use. It would tell me the query is correct.

hi again...

you are still not opening any recordset (bah!, you are opening the same table twice) I need to ask you about your level of expertise to know how to help you, because you are not doing anything right now...

try this...

and try to pass that recordset to a datagrid to see the results...


HTH

Gonzalo