Parameter Query

[Michael J Ross]

Where it says #Name? this means those fields are missing from the query, you need to add the fields as columns in the query design grid.

The new form will automatically be updated as it is sourced from the query which queries your table of sites.

As you are searching for either ref no or postcode you will need to modify your query so it runs if either the ref no or postcode or not entered, change the refno criteria to [Forms]![FRM_Search]![txtSearch] Or [Forms]![FRM_Search]![txtSearch] Is Null and do the same for your postcode search criteria.

 

[Michael J Ross]

Hi,

If you have a look at this it does what you want I think.

I have amended your query so it only queries on Tab 1: Factual table and brings back all the fields so the Name error disappears.

I have also changed the code on the search button fot the ref no so it closes the form if it is currently open so that it can be requeried and the results of the new search displayed.

Hopefully it works how you want.

 

[jojo86]

The #Name? thing is now sorted, thank you very much.

However, the last thing that was stated about the Null entry and such, I have put that into the queries and tested it out to see if it would show the form even if nothing is entered, and with the Ref Number, it brings up "Syntax Error (missing operator) in query expression 'RTP_ID= '".
I tried it with the postcode search and it pops up my msgbox saying that it is not a valid postcode (as though i have written something in the box, when i didn't).

And finally, it still sends to searches to the very last record...

 

[jojo86]

Have you attached something? Nothing is showing up... But thanks for this.

 

[Michael J Ross]

I can see it attached 2 posts up?

 

[jojo86]

Yes, I can see it now (for some reason, it was shown without any attachment.. spooky!).

I now have a new problem... instead of it going to the site I have searched for, it opens a new record. How can I solve this? I so appreciate all this help by the way.

 

[Michael J Ross]

I don't know why it opens a new record.

If you have a look at mine it brings the correct record back if you put the site no in. It won't let you search without any criteria which is what you would expect?

Perhaps you could post your db to see if I can spot why?

No problem helping its keeping me busy at work

 

[jojo86]

Yep - here it is. I used the same code as you... maybe i forgot something, that might be it. I am doing this through work too. I'm going to be doing it this evening too, which bites (would rather have it all finished with as soon as possible)

 

[Michael J Ross]

Hi,

Nothing worse than working on an evening is there?

Ok I've commented out some code on the on click event of the Ref No search button (highlighted in green) this seems to make it work ok, It was the go to record line which was causing the problem.

You'll need to do the same for the post code search button, btw theres no need to create a separate query for the postcode search you can do it in the same query if you have a look at the last example I posted. Otherwise you'll have create another FRM_SEARCH_P and set its sourcce to the post code query and call it from the on click of the post code button search.

I think we're nearly there

 

[jojo86]

Well, the Ref No search works now... It doesn't let me scroll through to the other records I have (which is good) but if you click on next record, it opens up a new record... Not too sure that is good!

as for the postcode one, once I have changed the code so it has comments on like with the ref no search, it takes me to the first record in the db, rather than going to the specified record with the postcode searched for. If I leave the code as it was, but use the RTP query, it takes me to the correct record. I have left the old code in and using the RTP query - seems to work fine at the moment.

Thank you!

 

[Michael J Ross]

Why don't you just take the scroll and new record buttons off the form FRM_SEARCH_P, you don't need them and it will stop people clicking it and creating new records.

Glad we've got something your happyish with.

 

[jojo86]

oh yeah... never thought about that! I am very happy with all the help you have given me, thank you very much!

However, there is still one problem... the postcode search. It takes the user to the very last record, even if the postcode that has been entered is in the first record. The Ref No works fine!

 

[Michael J Ross]

Ok can you post your mdb as it is now and we'll see if we can get it sorted

 

[jojo86]

Yep sure. Thanks for this.

 

[Michael J Ross]

Hi,

Right I think we're sorted , I have amended the query slightly (to include the post code as criteria and commented out some code (goto record) on the on click of the search button and it seems to be working ok I searched on the 1st postcode and it opened that record.

 

[jojo86]

Soooo close! It now finds the first record, but only the first record. I tried two postcodes that are stored, and it returned the first record only (but no error message arose, which is good!).

We are nearly there, yay!

 

[Michael J Ross]

So close but so far!

I'll get back to you later, I can't quite solve it at the moment.

 

[Michael J Ross]

Touch wood we now have a fully working search.

Try not to open the query in design view but if you do you will have delete the criteria that appears and put the following criteria in.

under rtp_id

[Forms]![FRM_SEARCH]![txtSearchRTP] Or [Forms]![FRM_SEARCH]![txtSearchRTP] Is Null

and under postcode

[Forms]![FRM_SEARCH]![txtSearchPC] Or [Forms]![FRM_SEARCH]![txtSearchPC] Is Null

I changed the post code slightly in record 2 so I could test the search as it wouldnt let me enter the postcode you had in because of the input mask.

I have tested the searches and they appear to be working correctly.

 

[jojo86]

Thank you! Your example does work, but what exactly have you done? I can't see anything different - apart from yours works and mine doesn't, lol

 

[Michael J Ross]

You're welcome glad to have helped.

I just rebuilt the query (to exactly what I had before which didn't work) and it now works. I'd just import my query and search form into your version and see if it works ok.