changing recordset fo a form

[Ryan]

I need to set a form that I have made's (Form1) Recordset
to one of five different queries based on certain
criteria. I do not know how to go about setting a form's
Recordset property in code though. My code so far looks
a little like this:

Dim stDocName As String

If action number 1 Then
stDocName = Query1
ElseIf action number2 Then
stDocName = Query2
ElseIf etc...

End If

Me.Recordset = stDocName

This does not work.
Through reading a little I can see that this is not the
proper way but I can't figure out to set a form's
recordset through code.

Can someone please help me out?

Ryan

 

[Allen Browne]

What is "action number"? If it's the name of a control on your form, you
would need a square brackets around it (because of the space in the name),
and the "=" sign after it:
If [action number] = 1 Then

It's a good idea to force a save before trying to reassign the form's
RecordSource:
If Me.Dirty Then
Me.Dirty = False
End If

Also, be careful that all your queries supply all the fields for the bound
controls. You can crash Access if a field disappears or changes data type
after the form is open.

More generally, if the queries are similar except for the WHERE clause,
could you achieve the same result by changing the form's Filter? If the
queries draw records from similar tables, would it be a better design to
combine the tables into one with an extra field to distinguish the records?
Food for thought.

 

[Ryan]

Allen,
Sorry for the unclarity. Action number one and number
two were just meant to be general conditions. Meaining:

If this happens Then
stDocName = "query1"
ElseIf that happends Then
stDocName = "query2"
etc..

Also, all of the queries that I am using are the same
except for the WHERE clause so according to you it would
be easier to use the Filter property of the form. Do you
think you could five me a general idea of how to use it.
I am pretty new to Access and have not worked with Filter
at all.

Thanks a lot,
Ryan

-----Original Message-----
What is "action number"? If it's the name of a control on your form, you
would need a square brackets around it (because of the space in the name),
and the "=" sign after it:
If [action number] = 1 Then

It's a good idea to force a save before trying to reassign the form's
RecordSource:
If Me.Dirty Then
Me.Dirty = False
End If

Also, be careful that all your queries supply all the fields for the bound
controls. You can crash Access if a field disappears or changes data type
after the form is open.

More generally, if the queries are similar except for the WHERE clause,
could you achieve the same result by changing the form's Filter? If the
queries draw records from similar tables, would it be a better design to
combine the tables into one with an extra field to distinguish the records?
Food for thought.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

I need to set a form that I have made's (Form1) Recordset
to one of five different queries based on certain
criteria. I do not know how to go about setting a form's
Recordset property in code though. My code so far looks
a little like this:

Dim stDocName As String

If action number 1 Then
stDocName = Query1
ElseIf action number2 Then
stDocName = Query2
ElseIf etc...

End If

Me.Recordset = stDocName

This does not work.
Through reading a little I can see that this is not the
proper way but I can't figure out to set a form's
recordset through code.

Can someone please help me out?

Ryan

.

 

[Allen Browne]

In the Northwind sample database that installs with Access is a Customers
form. It is based directly on the Customers table. You can filter the form
so it shows only customers from Spain like this:
Me.Filter = "Country = 'Spain'"
Me.FilterOn = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,
Sorry for the unclarity. Action number one and number
two were just meant to be general conditions. Meaining:

If this happens Then
stDocName = "query1"
ElseIf that happends Then
stDocName = "query2"
etc..

Also, all of the queries that I am using are the same
except for the WHERE clause so according to you it would
be easier to use the Filter property of the form. Do you
think you could five me a general idea of how to use it.
I am pretty new to Access and have not worked with Filter
at all.

Thanks a lot,
Ryan

-----Original Message-----
What is "action number"? If it's the name of a control on your form, you
would need a square brackets around it (because of the space in the name),
and the "=" sign after it:
If [action number] = 1 Then

It's a good idea to force a save before trying to reassign the form's
RecordSource:
If Me.Dirty Then
Me.Dirty = False
End If

Also, be careful that all your queries supply all the fields for the bound
controls. You can crash Access if a field disappears or changes data type
after the form is open.

More generally, if the queries are similar except for the WHERE clause,
could you achieve the same result by changing the form's Filter? If the
queries draw records from similar tables, would it be a better design to
combine the tables into one with an extra field to distinguish the records?
Food for thought.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

I need to set a form that I have made's (Form1) Recordset
to one of five different queries based on certain
criteria. I do not know how to go about setting a form's
Recordset property in code though. My code so far looks
a little like this:

Dim stDocName As String

If action number 1 Then
stDocName = Query1
ElseIf action number2 Then
stDocName = Query2
ElseIf etc...

End If

Me.Recordset = stDocName

This does not work.
Through reading a little I can see that this is not the
proper way but I can't figure out to set a form's
recordset through code.

Can someone please help me out?

Ryan

.