Using recordsource to change a form's query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form which I would like to load with different record sources,
depending upon which button is pressed on another form.

I can get this to work with a simple application, but a more complex one
with an IF statement, seems not to work.

If I embed the record source in the form ‘frmCensusResidents’ properties and
remove line 7, the procedure below works fine, but when I remove the data
source from the form and run the procedure with line X inserted, the form
loads without a record source. What is happening please?

Also, I have tried to insert the txtCensusDate value in the 'stLinkCriteria'
parameter of line 6, but that doesn't work either. Suggestions welcome!

1 Dim stDocName As String
2 Dim stLinkCriteria As String
3 Dim msg, response

3 stDocName = "frmCensusResidents"

4 If (FortyOne) = -1 Then
5 txtCensusDate = #6/6/1841#
6 DoCmd.OpenForm stDocName, , , stLinkCriteria
7 Forms! frmCensusResidents.RecordSource = qryCensusWomanResidents
8 DoCmd.MoveSize 0, 0, 14700, 8050
9 Else
10 msg = "No census data available"
11 response = msgbox(msg)
12 End If
 
From the code you posted, it looks like you are setting the record source
for your form to a query. How is that query being modified?

Hint: if you aren't doing so already, you can "point" a query's criterion to
a control in a form, saying "where this field is equal to/greater than/...
what's in that form's control".
 
Thanks Rob. I'll try it.

RobFMS said:
There is an OpenArgs parameter available.
Pass the recordsource value via the OpenArgs.

If <this condition> Then
strRecordSource = qry1
Else
strRecordSource = qry2
End IF

Docmd.OpenForm FormName:="xxxx", OpenArgs:=strRecordSource

HTH

Rob

--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

FMS Advanced Systems Group
http://www.fmsasg.com/
Click to expand...
 
Hi Jeff,

If I can explain a little more:

I have 2 identical forms but each one is based on a different 'embedded'
query (ie in the record source property of the forms' Properties box).

What I want to do is just have one copy of the form with no embedded query
and then select 'query1' or 'query2' by which button on the switchboard I
press, so the form displays the info from query1 or query2 as appropriate.
 
I can't tell if you and I have the same definition of "switchboard". If you
are using the built-in switchboard manager in Access, you may be able to use
Rob's approach.

If you have created your own "main menu" form, you could add code behind the
button-click that sets the record source for your form.
 
Back
Top