Trouble assigning a record source to a subform

I'm trying to assign a recordsource to a subform.
I have the following variable defined:

Dim QueryName as string

Based on IF THEN ELSE logic, I assign the applicable query name to the QueryName variable.

A snippet of it looks something like this:

If txtYear = 2005 then
  QueryName = "qry2005"
elseif txtYear = 2006 then
  QueryName = "qry2006"
etc
etc

I then try to assign QueryName to the RecordSource of a subform on a form. It looks like this:

DoCmd.OpenForm "frmForm2"
Forms("frmForm2").subform1.Form.RecordSource = "QueryName"

When I try to run the code, which is executed by clicking a command button on a separate form (named Form1), I get the following message:

'The record source 'QueryName' specified on this form or report does not exist

So, what I'm trying to do is use a form with a command button and a textbox on it. The user enters a year and clicks the command button.  The command button should open up Form2 with subform1 on it. subform1 should contain the output of the applicable query.

What am I doing wrong?



dbfromnewjerseyAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
Remove the " " around QueryName.  The quotes make a literal string- you want a variable, so no quotes.
0
 
danishaniConnect With a Mentor Commented:
Try something like this;

If txtYear = 2005 then
  QueryName = "qry2005"
elseif txtYear = 2006 then
  QueryName = "qry2006"
etc

Me!Subform1.Form.RecordSource = QueryName

DoCmd.OpenForm "frmForm2"

HTH,
Daniel

0
 
dbfromnewjerseyAuthor Commented:
I already tried that previously and quotes or no quotes, I get the same message.

If this matters at all, when I assign a query to QueryName, it looks like this:

QueryName = "qry2005"

where the actual query name has quotes around it
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
danishaniCommented:
You mean the actual name is "qry2005" instead of qry2005?

Then something like this;
If txtYear = 2005 then
  QueryName = Chr(34) & "qry2005" & Chr(34)
elseif txtYear = 2006 then
  QueryName = Chr(34) & "qry2006" & Chr(34)
etc

Me!Subform1.Form.RecordSource = QueryName

DoCmd.OpenForm "frmForm2"

HTH,
Daniel
0
 
dbfromnewjerseyAuthor Commented:
oops. nevermind. I found out why I was getting that error.  Like my usual stupid self, I assigned QueryName as the record source of Form2 in the properties window and forgot to remove it.
I'm getting no error messages but I'm also getting no output. I'll close the question though since my getting no output is probably another issue. oh boy.  Thanks.
0
 
peter57rCommented:
What you say cannot be true.  Access would not give the same error message even if the 'without-the-quotes' version produced an error.

Assuming the 'if then else' code is what you say it is then :

Forms("frmForm2").subform1.Form.RecordSource = QueryName

is correct.

0
 
GRayLCommented:
You need this:

Me!Subform1.Form.RecordSource = "Query.QueryName"
0
 
Dale FyeCommented:
BTW, you could probablye get rid of all the IF/Else statements with something like:

If val(me.txtYear) < 2005 or val(me.txtYear) > 2012 Then
    msbox "Year is outside of acceptable range"
    Exit Sub
Else
    QueryName = "qry" & me.txtYear
End if

Or, an even better way to do this would be to have a single query, and include a reference to the textbox in the query, so that you would not have to define specific queries for each year.

SELECT * FROM yourTable WHERE [YearFieldName] = Forms!formname!txtYear

This way, you only have one query and all you need to do in the afterupdate event of the textbox or combo box is execute the Requery method.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.