VBA, SQL and JOINS

[MichaelSmith]

Hi,

Wondering if anyone can tell me where I'm going wrong with this piece of VBA code in my form:

Dim strRcset As String
strRcset = "SELECT jctVoyagesSources.*, tV.[VoyageID], tS.[SourceID] " & _
           "FROM ([swpdatabase.jctVoyagesSources] " & _
           "INNER JOIN [swpdatabase.tblVoyages] AS tV ON jctVoyagesSources.VoyageID = tV.VoyageID) " & _
           "INNER JOIN [swpdatabase.tblSources] AS tS ON jctVoyagesSources.SourceID = tS.SourceID"
Set Me.Recordset = BEdb.OpenRecordset(strRcset, dbOpenDynaset, dbSeeChanges)

I'm getting a Run-time error 3135 in the code from this - I've also tried it as this:

Dim strRcset As String
strRcset = "SELECT jctVoyagesSources.*, tV.[VoyageID], tS.[SourceID] " & _
           "FROM ([swpdatabase.jctVoyagesSources] " & _
           "INNER JOIN [swpdatabase.tblVoyages] AS tV ON jctVoyagesSources.[VoyageID] = tV.[VoyageID]) " & _
           "INNER JOIN [swpdatabase.tblSources] AS tS ON jctVoyagesSources.[SourceID] = tS.[SourceID]"
Set Me.Recordset = BEdb.OpenRecordset(strRcset, dbOpenDynaset, dbSeeChanges)

Same problem in both. Driving me nuts!

Thanks for the help.

 

[pr2-eugin]

Try Debugging using the Immediate Window. http://www.baldyweb.com/ImmediateWindow.htm

Once you get the generated SQL, copy and Paste it in a QBE, you be able to correct the error almost immediately. If not paste the generated code, we will see what is happening.

PS: Not everyone will know the Error's description by just the Number. So it would be helpful if you provide the Error and its Description, so we can deliver the best help !

 

[CJ_London]

have you tried

strRcset = "SELECT [COLOR=red]swpdatabase.[/COLOR]jctVoyagesSources.*, tV.[VoyageID], tS.[SourceID] " & _
           "FROM ([swpdatabase.jctVoyagesSources] " & _
           "INNER JOIN [swpdatabase.tblVoyages] AS tV ON [COLOR=red]swpdatabase.[/COLOR]jctVoyagesSources.VoyageID = tV.VoyageID) " & _
           "INNER JOIN [swpdatabase.tblSources] AS tS ON [COLOR=red]swpdatabase.[/COLOR]jctVoyagesSources.SourceID = tS.SourceID"

 

[MichaelSmith]

Right - seem to have got it partially working by doing this:

Dim strRcset As String
strRcset = " SELECT jVS.*, tV.*, tS.* " & _
           " FROM ([swpdatabase.jctVoyagesSources] AS jVS " & _
           " INNER JOIN [swpdatabase.tblVoyages] AS tV ON jVS.[VoyageID] = tV.[VoyageID]) " & _
           " INNER JOIN [swpdatabase.tblSources] AS tS ON jVS.[SourceID] = tS.[SourceID] "
Set Me.Recordset = BEdb.OpenRecordset(strRcset, dbOpenDynaset, dbSeeChanges)

However - now my problem is the subform this code is attached to is still loading all the records from the junction table rather than the records which correspond to the parent form - I'm wondering if this is because it's in a tab control - as similar code including only one INNER JOIN seems to be working perfectly on a different set of forms.

I've attached some screen shots for reference.

Basic Form Structure:

Showing jctVoyagesSources subform showing all of the data using the SQL code above:

What the subform should actually be showing:

I'm assuming I need a WHERE statement in my SQL code somewhere - I've tried linking it to "WHERE jVS.[VoyageID] = " & Forms!tblVoyages![VoyageID] but I get an error message telling me it can't find the specified form.

Any thoughts, insights, advice would be appreciated!

 

[CJ_London]

subforms load before the main form so if you have this code in your subform load event then the linkchild/linkmaster properties of the subform control won't be 'activated'.

Try putting the code in the main form current event or the event for when the appropriate tab is selected.

 

[CJ_London]

Try putting the code in the main form current event or the event for when the appropriate tab is selected.

You'll need to change the assignment to

Set subformcontrolname.form.Recordset = BEdb....

replace subformcontrolname with the name of your subform

If you want to add the filter into the code rather than using the linkchild/master properties (probably better) then assuming your code is now in the main form add the criteria

" WHERE jVS.[VoyageID] = " & [VoyageID]

 

[MichaelSmith]

You'll need to change the assignment to

Set subformcontrolname.form.Recordset = BEdb....

replace subformcontrolname with the name of your subform

If you want to add the filter into the code rather than using the linkchild/master properties (probably better) then assuming your code is now in the main form add the criteria

" WHERE jVS.[VoyageID] = " & [VoyageID]

Thanks for this, I really appreciate it!

Having trouble with the subformcontrolname part - the sub forms name is jctVoyagesSources subform and I've tried every iteration I can think of to set it.

[jctVoyagesSources subform].Form.RecordSet
jctVoyagesSources_subform.Form.RecordSet
[Form_jctVoyagesSources subform].Form.RecordSet

None of them seem to work to update the forms recordset - any thoughts on where I can find the actual subform control name?

Thanks!

 

[CJ_London]

any thoughts on where I can find the actual subform control name?

Open the main form in design view, right click on the subform control and open the properties if not already open, click the others tab and you'll see the name property- or you'll see it a the top of the properties box in the drop down list.

Alternatively in the vba window, type me. and then browse all the objects until you find the name there

Spaces in names are not a good idea but people will use them.:banghead: You'll notice vba will replace a space with an underscore where you have a control name with a space which can make it harder to find.

 

[vbaInet]

Open the main form in design view, right click on the subform control ...

Was just going to stress that it should be clicked just once.