Change the Record Source of the form based on the combo box select

[Guest]

Hello,

I created a start form and there is a button ( Edit Form) to open an "Edit
Form" .

On the start form, I have a combo box which I can select the year (2000,
2001, 2002...). Based on the selected year, I would like to open the Edit
Form with the changing record source value. For example, If 2000 is selected
from the combo box, when I click the Edit Form button, the form "Edit Form"
will be opened and record source of the form will be qYear2000; if year 2001
is selected, the same "Edit form" is opened and with the recode source
qYear2001.

Is it possible? Thanks!

 

[Marshall Barton]

Hello,

I created a start form and there is a button ( Edit Form) to open an "Edit
Form" .

On the start form, I have a combo box which I can select the year (2000,
2001, 2002...). Based on the selected year, I would like to open the Edit
Form with the changing record source value. For example, If 2000 is selected
from the combo box, when I click the Edit Form button, the form "Edit Form"
will be opened and record source of the form will be qYear2000; if year 2001
is selected, the same "Edit form" is opened and with the recode source
qYear2001.

You can use the OpenForm method's OpenArgs to pass the name
of the query to the form. Then the form's Open event can
set its RecordSource property.

However, having a separate query just to filter data a
little differently is not an ideal approach. If possible,
you should base the form on a query without the varying
criteria. Then use the OpenForm method's WhereCondition
argument to specify the filter.

 

[Guest]

Thanks Marshall.

The code I had as below,

If cboYear = 2000 Then
stDocName = "EditForm"
stLinkCriteria = qYear2000
DoCmd.OpenForm stDocName, , stLinkCriteria

ElseIf cboYear = 2001 Then
stDocName = "EditForm"
stLinkCriteria = qYear2001
DoCmd.OpenForm stDocName, , stLinkCriteria

ElseIf cboYear = 2002 Then
stDocName ="EditForm"
stLinkCriteria = qYear2002
DoCmd.OpenForm stDocName, , stLinkCriteria

Else
stDocName ="EditForm"
stLinkCriteria = qYear2003
DoCmd.OpenForm stDocName, , stLinkCriteria
End If

When I chick on the button, it only opens the form with the query qYear2000
even though the 2001 is selected from the combo box. What is wrong?

Ac

 

[J_Goddard via AccessMonster.com]

Hi -

The stLinkCriteria expression has to look like a "where" clause but without
the where.
What you need is have something like

stDocName ="EditForm"
stLinkCriteria = "[Myformfield] = " & [MyComboBoxValue]
DoCmd.OpenForm stDocName, , stLinkCriteria

Where the combobox value is 2000, 2001, etc.

HTH

John

Thanks Marshall.

The code I had as below,

If cboYear = 2000 Then
stDocName = "EditForm"
stLinkCriteria = qYear2000
DoCmd.OpenForm stDocName, , stLinkCriteria

ElseIf cboYear = 2001 Then
stDocName = "EditForm"
stLinkCriteria = qYear2001
DoCmd.OpenForm stDocName, , stLinkCriteria

ElseIf cboYear = 2002 Then
stDocName ="EditForm"
stLinkCriteria = qYear2002
DoCmd.OpenForm stDocName, , stLinkCriteria

Else
stDocName ="EditForm"
stLinkCriteria = qYear2003
DoCmd.OpenForm stDocName, , stLinkCriteria
End If

When I chick on the button, it only opens the form with the query qYear2000
even though the 2001 is selected from the combo box. What is wrong?

Ac

Hello,

[quoted text clipped - 10 lines]

Is it possible? Thanks!

 

[Marshall Barton]

The code I had as below,

If cboYear = 2000 Then
stDocName = "EditForm"
stLinkCriteria = qYear2000
DoCmd.OpenForm stDocName, , stLinkCriteria

ElseIf cboYear = 2001 Then
stDocName = "EditForm"
stLinkCriteria = qYear2001
DoCmd.OpenForm stDocName, , stLinkCriteria

ElseIf cboYear = 2002 Then
stDocName ="EditForm"
stLinkCriteria = qYear2002
DoCmd.OpenForm stDocName, , stLinkCriteria

Else
stDocName ="EditForm"
stLinkCriteria = qYear2003
DoCmd.OpenForm stDocName, , stLinkCriteria
End If

When I chick on the button, it only opens the form with the query qYear2000
even though the 2001 is selected from the combo box. What is wrong?

I was trying to say that you should create a new query that
does not have a criteria for the year. OTTOH, I have just
assumed that was the difference between the separate
queries. Maybe we need to back up a step and fill me in on
these queries so we can get that straight instead of me
assuming I know what they are doing.