Inner Join question!
- From: Ironr4ge <scherrer_riccardo@xxxxxxxxx>
- Date: Fri, 31 Aug 2007 04:09:33 -0700
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
.
- Prev by Date: Inner Join question!
- Next by Date: Timestamps
- Previous by thread: Inner Join question!
- Next by thread: Timestamps
- Index(es):
Relevant Pages
|
