Form called from VBA doesn't show all fields in query

[gbergeson]

Please be patient with me. I'm a newbie and the LAN Nazi's have blocked this site. This means I can only asked questions from home and, of course, the code for this project is at work.

I'm building an Access db on an XP box. I'm currently working on the search function. I've created a form that allows the user to enter data into one or more of 5 fields allowed for searching. When the user selects the "Search" button the data from this form is inserted into a "SearchRequirements" table and a query is run. The query results are then displayed in a tabular form.

The query was written using the GUI query tool and looks something like this:

SELECT General.WorkOrderNumber, General.MissionName, General.MissionDate, General,JON, General.MissionCode, General.PIN FROM [General] Where ((General.WorkOrderNumber) LIKE (SearchRequirements.WorkOrderNumber))
 

In my test db I have 34 records. The work order number for all of then starts 7. When I put "7*" into this query all 34 records are returned. When I call the tabular form, "SearchResults", that is based upon this query I get a variable number of records depending upon the length of the form when I last saved it. If, however, I put a break in the code just after calling the form, all 34 records are present.

My first impression was this was a timing issue. So I built a Pause function and called it. I've tried pauses varying from 0.1 seconds to 2 seconds. None of these pauses give all the records. Only the break, or the Stop command, does.

Any idea???

[MMcCarthy]

Can you post the code behind the search button.

As for the LAN Nazi's. Explain to them that you need access to this site for development purposes. If they have some other reason for blocking it please let us know and we'll see what we can do.

[gbergeson]

It was two forms. But it's a moot point now, unless you want to see what was going on for educational purposes. If so, let me know and I'll post the code.

I solved the problem by a course change. I went to the tutorials and learned about dynamic SQL statements. So I modified my SearchResults form to point to the table instead of the query and put a dynamic filter string into the form as it opens. This gives the desired results.

As for the LAN Nazi's. Their Air Force and don't care what I need for development. It's all, "Just do your job and don't complain about the technology you can't have because you can't have it!" Oh well, it pays the bills.

[MMcCarthy]

It was two forms. But it's a moot point now, unless you want to see what was going on for educational purposes. If so, let me know and I'll post the code.

I solved the problem by a course change. I went to the tutorials and learned about dynamic SQL statements. So I modified my SearchResults form to point to the table instead of the query and put a dynamic filter string into the form as it opens. This gives the desired results.

As for the LAN Nazi's. Their Air Force and don't care what I need for development. It's all, "Just do your job and don't complain about the technology you can't have because you can't have it!" Oh well, it pays the bills.

Glad you found a solutions.