Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

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?



0
dbfromnewjersey
Asked:
dbfromnewjersey
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
peter57rCommented:
Remove the " " around QueryName.  The quotes make a literal string- you want a variable, so no quotes.
0
 
danishaniCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now