Unbound field on form not passing its value to query consistently

[andrewnp]

I’d appreciate comments/help on this problem.

I’ve lately moved to Access 2010 after using and hobby programming Paradox for 10-15 years.

I’m trying to create a query which outputs selected records from an Access Table after it receives user input via a second form. Here are some more details:

The Access table is named “RBProjects” and has an automatic key field (per Access) called “ReferenceID”, and several alphanumeric fields named “Category”, “Subcategory” and “Project”.

I’m interested in running a query by searching of RBProjects by presenting a second search form bound to RBProjects called “FrmSearchProjects” which includes three unbound text boxes, named “Category”, “Subcategory” and “Project”. This second search form is bound to RBProjects.

I’ve generated a new query, based on RBProjects which includes all of the fields of RBProjects. In the query, for the “Project” column in the Criteria: row I have the following:

=[Forms]![RBProject]![Project]

There are no other statements in the Criteria: row for any other field. I then used the wizard to prepare a button to run the query, so that clicking on it should pass the value in the unbound “Project” field of FrmSearchReports to the query, and display the records of RBProjects having the matching value of “Project”.

Here’s where I have problems….

I can enter a known value (known from ‘Projects’ from the table RBProjects) into the unbound field “Projects” on the FrmSearchReports, and click the button to run the query…. however, this does not work, and the results of the query show no change.

However, with the same value in the unbound “Projects” field of FrmSearchReports and that form open, I go to the query, switch to design view, and click “Run” (red exclamation mark) and the query now runs properly, and the records from RBProjects having the same value as in the unbound “Projects” field of FrmSearchReports to the query are now displayed.

A: Is there something wrong with my query ?

B: Any ideas why the value of the unbound field doesn’t work in the query when run from the button, but does work when run directly from the (design view) of the query ?

C: Any other insights/suggestions would be appreciated……..

 

[andrewnp]

Thanks for looking at this Pat.

I'm not savvy enough to do some VBA programming (but I do want to get there) as I'm only working with Access a few weeks (some hobby programming).

What I was trying to achieve is shown in this YouTube video which demonstrated passing the variables from one or more unbound fields to a query, which would then only show the records of the table which matched the user's input into the unbound field or fields.

As I'm too new here, I can't post a direct link... but you can find it on YouTube by searching on these keywords: "599cd" "Multi-Field Search Form"

In my case, I did double doublecheck all of my queries against what's shown in that video, so that seems fine. It's when I went to troubleshoot where I noted the behavior in my first post, and I rewrote my input form to have one unbound field and my query to have one "Criteria" to reference to the value of that one unbound field, and no wildcards in that criteria string. (Didn't want to introduce any other possible other sources for errors....!) I also used the built in Access Wizard to generate a push button on the search form to run the query. (Perhaps that's the trouble.. but in the YouTube video it works flawlessly.)

Running the input form, entering a (known value) into my query, then pressing the button, .... I then checked the output of the query ... nothing worked.

But, when staying on the query, entering "Design Mode" and pressing "Run !", the query would work just fine, and show results based on the value in the unbound field of my form.

Any other thoughts ? Pat ? Anyone else out there ?

Thanks !

-- Andrew