By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Inner Join question!

P: n/a
Hi everyone,

I was wondering whether is was possible to select only the filtered
records of one form with Recordsource = "C" and open a diffrent form
with recordsource "L" . The way I was thinking accompleshing this was
by using the following innerjoin statement.. and code to open form...
-----------------------------------------------------------------------
Private Sub cmdFilter_Click()

Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.

Dim msg2 As String
Dim strSQL As String

strSQL = "SELECT DISTINCT [visiting_instructor_language].
[id_individual] " & _
" FROM [visiting_instructor_language] INNER JOIN
visiting_instructor_country ON [visiting_instructor_language] " & _
".[id_individual] = visiting_instructor_country.[id_individual] " & _
"WHERE "

'Another text field example. Use Like to find anywhere in the
field.
If Not IsNull(Me.Countryexp) Then
strWhere = strWhere & "([country] like ""%" & Me.Countryexp &
"%"") AND "
End If
If Not IsNull(Me.from) Then
strWhere = strWhere & "([fromdate] >= " & "#" &
Format(Me.from, "short date") & "#" & ") AND "
End If

'Another date field example. Use "less than the next day" since
this field has times as well as ' dates.
If Not IsNull(Me.To) Then 'Less than the next day.
strWhere = strWhere & "([todate] < " & "#" & Format(Me.To + 1,
"short date") & "#" & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the " AND " at the end.
strWhere = Left$(strWhere, lngLen)

Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If

' Code to open form

If MsgBox(msg2, vbQuestion + vbYesNo) = vbYes Then
msg2 = "Filter by Languages?"
Dim language As String
language = strSQL
If language <"" Then

language = "id_individual in (" & language & ")"

DoCmd.OpenForm "language_search", acNormal, , language
End If

End If

-------------------------------------------------------------------------------------
the following code gives me the common "Runtime error 30025" which I
assume is pointing at the fact that we have filtered these records
using a diffrent record source.. However... the intention was only to
select the id_individual (which is link between both tables) of all
filtered record to open on the second form with a diffrent
recordsource (L).
I know one of the solution would be to use the sub_form structure but
my desire is to always have a continous form when look at each
individual record details.

If anyone can spot an error in this code or a possible to solution to
make it work I would be very greateful....

Ric

Aug 31 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.