Changing record source in Frame

B

Bob Richardson

On my main form I want to change the Detail data that appears, based upon a
radio button (frame). There is a dropdown box (YearBox) where you can select
a school year (e.g. "2004-05"). With 3 of the buttons I want the data to be
taken from the Contacts table. This works fine. With the 4th button I want
to take the data from a query which potentially includes one record for each
year - then I want to limit the data so that it only shows records that
match the year in the drop down box. In the query, the "year" is held in the
SchYear field. Here's my feeble attempt. What am I missing?

Me.RecordSource = "Contacts"
Me.FilterOn = True
Select Case Frame31.Value
Case 0 ' All Contacts
Me.FilterOn = False
Case 1
Qry = "[Instructor] = Yes"
Case 2
Qry = "[TA] = Yes"
Case 3 ' Student
Me.RecordSource = "Q_StudentEachYear"
Qry = "[SchYear] = Me.YearBox"
End Select
Me.Filter = Qry
 
D

Dirk Goldgar

Bob Richardson said:
On my main form I want to change the Detail data that appears, based
upon a radio button (frame). There is a dropdown box (YearBox) where
you can select a school year (e.g. "2004-05"). With 3 of the buttons
I want the data to be taken from the Contacts table. This works fine.
With the 4th button I want to take the data from a query which
potentially includes one record for each year - then I want to limit
the data so that it only shows records that match the year in the
drop down box. In the query, the "year" is held in the SchYear field.
Here's my feeble attempt. What am I missing?

Me.RecordSource = "Contacts"
Me.FilterOn = True
Select Case Frame31.Value
Case 0 ' All Contacts
Me.FilterOn = False
Case 1
Qry = "[Instructor] = Yes"
Case 2
Qry = "[TA] = Yes"
Case 3 ' Student
Me.RecordSource = "Q_StudentEachYear"
Qry = "[SchYear] = Me.YearBox"
End Select
Me.Filter = Qry
Click to expand...

I'd probably do it by modifying the recordsource each time, so as to
cause less requerying. Try this modification:

Select Case Frame31.Value

Case 0 ' All Contacts
Me.RecordSource = "Contacts"

Case 1
Me.RecordSource = _
"SELECT * FROM Contacts"& _
" WHERE [Instructor] = Yes"

Case 2
Me.RecordSource = _
"SELECT * FROM Contacts"& _
" WHERE [TA] = Yes"

Case 3 ' Student
Me.RecordSource = _
"SELECT * FROM Q_StudentEachYear"& _
" WHERE [SchYear] = " & Me.YearBox

End Select

Your main problem was that you had "Me.YearBox" inside the quotes.
 
B

Bob Richardson

Dirk Goldgar said:
Bob Richardson said:
On my main form I want to change the Detail data that appears, based
upon a radio button (frame). There is a dropdown box (YearBox) where
you can select a school year (e.g. "2004-05"). With 3 of the buttons
I want the data to be taken from the Contacts table. This works fine.
With the 4th button I want to take the data from a query which
potentially includes one record for each year - then I want to limit
the data so that it only shows records that match the year in the
drop down box. In the query, the "year" is held in the SchYear field.
Here's my feeble attempt. What am I missing?

Me.RecordSource = "Contacts"
Me.FilterOn = True
Select Case Frame31.Value
Case 0 ' All Contacts
Me.FilterOn = False
Case 1
Qry = "[Instructor] = Yes"
Case 2
Qry = "[TA] = Yes"
Case 3 ' Student
Me.RecordSource = "Q_StudentEachYear"
Qry = "[SchYear] = Me.YearBox"
End Select
Me.Filter = Qry
Click to expand...

I'd probably do it by modifying the recordsource each time, so as to
cause less requerying. Try this modification:

Select Case Frame31.Value

Case 0 ' All Contacts
Me.RecordSource = "Contacts"

Case 1
Me.RecordSource = _
"SELECT * FROM Contacts"& _
" WHERE [Instructor] = Yes"

Case 2
Me.RecordSource = _
"SELECT * FROM Contacts"& _
" WHERE [TA] = Yes"

Case 3 ' Student
Me.RecordSource = _
"SELECT * FROM Q_StudentEachYear"& _
" WHERE [SchYear] = " & Me.YearBox

End Select

Your main problem was that you had "Me.YearBox" inside the quotes.
Click to expand...

Thanks Dirk. It's close, but not quite. When the Me.YearBox shows "2004-05"
and it executes case 3, a dialog box asks me to "Enter Parameter Value" for
"2004-05". If I then type in "2004-05" (or whatever the box shows) then I
get the correct data. What am I missing?
 
D

Dirk Goldgar

Bob Richardson said:
Dirk Goldgar said:
Bob Richardson said:
On my main form I want to change the Detail data that appears, based
upon a radio button (frame). There is a dropdown box (YearBox) where
you can select a school year (e.g. "2004-05"). With 3 of the buttons
I want the data to be taken from the Contacts table. This works
fine. With the 4th button I want to take the data from a query which
potentially includes one record for each year - then I want to limit
the data so that it only shows records that match the year in the
drop down box. In the query, the "year" is held in the SchYear
field. Here's my feeble attempt. What am I missing?

Me.RecordSource = "Contacts"
Me.FilterOn = True
Select Case Frame31.Value
Case 0 ' All Contacts
Me.FilterOn = False
Case 1
Qry = "[Instructor] = Yes"
Case 2
Qry = "[TA] = Yes"
Case 3 ' Student
Me.RecordSource = "Q_StudentEachYear"
Qry = "[SchYear] = Me.YearBox"
End Select
Me.Filter = Qry
Click to expand...

I'd probably do it by modifying the recordsource each time, so as to
cause less requerying. Try this modification:

Select Case Frame31.Value

Case 0 ' All Contacts
Me.RecordSource = "Contacts"

Case 1
Me.RecordSource = _
"SELECT * FROM Contacts"& _
" WHERE [Instructor] = Yes"

Case 2
Me.RecordSource = _
"SELECT * FROM Contacts"& _
" WHERE [TA] = Yes"

Case 3 ' Student
Me.RecordSource = _
"SELECT * FROM Q_StudentEachYear"& _
" WHERE [SchYear] = " & Me.YearBox

End Select

Your main problem was that you had "Me.YearBox" inside the quotes.
Click to expand...

Thanks Dirk. It's close, but not quite. When the Me.YearBox shows
"2004-05" and it executes case 3, a dialog box asks me to "Enter
Parameter Value" for "2004-05". If I then type in "2004-05" (or
whatever the box shows) then I get the correct data. What am I
missing?
Click to expand...

I had assumed that [SchYear] was a numeric field, containing some such
value as 2005, or 2006. Now I see it's a text field, so you have to
amend the WHERE clause of the SELECT statement to include quotes around
the value from YearBox:

Case 3 ' Student
Me.RecordSource = _
"SELECT * FROM Q_StudentEachYear"& _
" WHERE [SchYear] = '" & Me.YearBox & "'"

That may be a little hard to read, so I'll space it out for readability:

" WHERE [SchYear] = ' " & Me.YearBox & " ' "

However, you must use the original, "non-spaced" version in your code.
 
B

Bob Richardson

Dirk Goldgar said:
Bob Richardson said:
Dirk Goldgar said:
"Bob Richardson" <bobr at whidbey dot com> wrote in message
On my main form I want to change the Detail data that appears, based
upon a radio button (frame). There is a dropdown box (YearBox) where
you can select a school year (e.g. "2004-05"). With 3 of the buttons
I want the data to be taken from the Contacts table. This works
fine. With the 4th button I want to take the data from a query which
potentially includes one record for each year - then I want to limit
the data so that it only shows records that match the year in the
drop down box. In the query, the "year" is held in the SchYear
field. Here's my feeble attempt. What am I missing?

Me.RecordSource = "Contacts"
Me.FilterOn = True
Select Case Frame31.Value
Case 0 ' All Contacts
Me.FilterOn = False
Case 1
Qry = "[Instructor] = Yes"
Case 2
Qry = "[TA] = Yes"
Case 3 ' Student
Me.RecordSource = "Q_StudentEachYear"
Qry = "[SchYear] = Me.YearBox"
End Select
Me.Filter = Qry

I'd probably do it by modifying the recordsource each time, so as to
cause less requerying. Try this modification:

Select Case Frame31.Value

Case 0 ' All Contacts
Me.RecordSource = "Contacts"

Case 1
Me.RecordSource = _
"SELECT * FROM Contacts"& _
" WHERE [Instructor] = Yes"

Case 2
Me.RecordSource = _
"SELECT * FROM Contacts"& _
" WHERE [TA] = Yes"

Case 3 ' Student
Me.RecordSource = _
"SELECT * FROM Q_StudentEachYear"& _
" WHERE [SchYear] = " & Me.YearBox

End Select

Your main problem was that you had "Me.YearBox" inside the quotes.
Click to expand...

Thanks Dirk. It's close, but not quite. When the Me.YearBox shows
"2004-05" and it executes case 3, a dialog box asks me to "Enter
Parameter Value" for "2004-05". If I then type in "2004-05" (or
whatever the box shows) then I get the correct data. What am I
missing?
Click to expand...

I had assumed that [SchYear] was a numeric field, containing some such
value as 2005, or 2006. Now I see it's a text field, so you have to
amend the WHERE clause of the SELECT statement to include quotes around
the value from YearBox:

Case 3 ' Student
Me.RecordSource = _
"SELECT * FROM Q_StudentEachYear"& _
" WHERE [SchYear] = '" & Me.YearBox & "'"

That may be a little hard to read, so I'll space it out for readability:

" WHERE [SchYear] = ' " & Me.YearBox & " ' "

However, you must use the original, "non-spaced" version in your code.
Click to expand...

BEAUTIFUL. Thank you very much. Works great :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with "blank" query 2
Adding New Record to a linked table from Query 1
Assign Alpha Code to Child Record 2
VBA filter for form - newest record in table 7
Get the sum of Filtered form 0
VBA help, please "No current Record" 4
Assigning a Query as Report Record Source 1
Go to specific record in a form PLEASE HELP ME :) 1

Top