Searchbox based off a query

[jjake]

I have a 'mainform' generating a number in a textbox [PONumber] based off a date and [OrderID]

=Format([OrderDate],"mmddyy") & Format([OrderID],"00") (Thanks Pbaldy)

I have created another form 'searchform'

It has a textbox to enter the PONumber I would like to search for on the 'mainform'.

The goal is to be able to enter the number and hit search and it will pull up the record for that form but whenever I try it, it always seems to bring up a new record.

I was thinking of trying to the save the result from the query into my table and search for it that way but I have read numerous times that some data should never be saved in a table and left up to a query to find it.

 

[chellebell1689]

Hello,
What I usually do for searches is create a drop down menu that will show all possible records for me to search for. When you hit the "combo box" button and add it to your form, a wizard will pop up and you can create a query to pull the info you need for it. Once the combo box is set up, then you can modify the "after update" event on the combo box to apply a filter that matches the updated info.

If you want to use this method and/or need a bit more clarity, let me know.

 

[jdraw]

jjake,

Do you have a clear description of what you are trying to do?
Do you have a data model based on that description which serves as a blueprint to your database design?

I prefer 1 fact in 1 field. I do not recommend concocting codes when a simple field will suffice. I understand you can concoct all sorts of things, but because you can doesn't necessarily mean you should.

There is more to an application than search.

For this:

The goal is to be able to enter the number and hit search and it will pull up the record for that form but whenever I try it, it always seems to bring up a new record.

see Allen Browne's article.

Good luck.

 

[AccessBlaster]

I have a 'mainform' generating a number in a textbox [PONumber] based off a date and [OrderID]

How do you store this PONumber? In one field or two.

I was thinking of trying to the save the result from the query into my table and search for it that way but I have read numerous times that some data should never be saved in a table and left up to a query to find it.

Refer back to the first question.

 

[jjake]

How do you store this PONumber? In one field or two.




Refer back to the first question.

It isn't stored. It is merely projected into a textbox from the expression.

 

[AccessBlaster]

Then searching the OrderID should produce the results your looking for.

 

[AccessBlaster]

Like [forms]![Your Form].[OrderID] & "*"

something like this in the criteria field under the orderID in a search query should do it.

 

[jjake]

Then searching the OrderID should produce the results your looking for.

I uploaded a streamlined version to maybe better explain. I tried searching the OrderID but it brings up the last record.

 

[AccessBlaster]

You are starting to see why jdraw said he doesn't like to "concoct fields" in this manner.

 

[AccessBlaster]

See if this helps.

 

[AccessBlaster]

 

[AccessBlaster]

I think the expression should be something like this. Never really played with dates and numbers concatenated.

PONumber: [orderID] & Format([orderdate],"ddmmyy")

 

[jjake]

I modified the format so it would display the PO how I would like, which works fine when I run the query. I notice I get this parameter popup? not sure if that is normal.

Also how do I incorporate this into my search function?

 

[AccessBlaster]

I modified the format so it would display the PO how I would like, which works fine when I run the query. I notice I get this parameter popup? not sure if that is normal.

Also how do I incorporate this into my search function?

If it were me I probably would increment my Purchase Order numbers and save that portion to a table. Then run a query to concatenate the date portion to the po field for presentation purposes. Then searching and saving might not be such a pain.

As far as the parameter popup goes, recheck the spelling of the search field or the form name in the query criteria field. Or the query name behind the button.

 

[jdraw]

Did you get any insight from Allen Browne's article I mentioned in post #3?

 

[AccessBlaster]

This link has a lot of good ideas for combing fields, like dates, numbers or letters.
http://regina-whipp.com/blog/

 

[jjake]

Did you get any insight from Allen Browne's article I mentioned in post #3?

I got a headache

 

[jjake]

If it were me I probably would increment my Purchase Order numbers and save that portion to a table. Then run a query to concatenate the date portion to the po field for presentation purposes. Then searching and saving might not be such a pain.

As far as the parameter popup goes, recheck the spelling of the search field or the form name in the query criteria field. Or the query name behind the button.

This is currently what I do. A portion of the PO# is the orderID autonumber. Then I concatenate it with the date in a ''mmddyy'' format and display it on the report.

The problem is being able to take the number that is displayed on the report and being able to search for it later.

 

[AccessBlaster]

Not sure if you have looked at this link http://regina-whipp.com/blog/ it's well worth the effort.

Lets us know what you think.

 

[Minty]

The problem is being able to take the number that is displayed on the report and being able to search for it later.

Simply reverse the process - you know the date portion so remove that then you have your record ID back?