I'm a newbie on this Access stuff...

[marydoyle9]

I have a table (Master) that contains columns such as MsgLocation, MsgName, MsgScript, and MsgTxt. I want to create a form where the user would have an area to type in a 'string' and when they hit enter (or select a button), a (background) query would execute a lookup on master "where MsgTxt LIKE %string%"... For example, if a user wants a list of all MsgTxt values that contain "Please hold" they would type in a box "Please hold" and the query would use %Please hold% in the search criteria.

I don't have a clue on the first thing to do to get this done. And I'm new at this...

Thanks!!!!
Mary

 

[kilobyte]

If I understand your question right, you're already halfway there.

All you need to do is make the query with everything you want it to display, and then in the criteria reference the control on your form. So add something like this to the criteria row of your query on the MsgTxt column:

Like [Forms]![frmYourForm]![YourTextBox]

Where you replace frmYourform and YourTextBox with whatever your form and your control are named.

Hope that helped.

-Dave

 

[marydoyle9]

Another question....

Dave - thanks! Okay... I understand what I need to do with the query (actually I already added the criteria) but not sure what kind of control I should use. I need the user to be able to type in a string and hit a button (or something) and have the query reslults returned in a pop-up window or another box or something.

Thanks again!

 

[kilobyte]

Okay, right now you are using a textbox, right? That is fine.
Im not sure how you want to display the information from the query, that would depend on what your doing with it. But to make the query run you have two options.

1. Have it run when they type in the box and press enter.

To do this you add the code to run the query to the After Update event from the text box.

2. Have a command button to activate it.

To do this you make the button and add the code to the on click event.


I think probably the easiest and most efficient way to display the results of the query is to open a form based from that query in another window.

Hope that helped.

-Dave

 

[marydoyle9]

Ugh!

Okay - I added a text box, added the query name to the "after Update" event and created a simple macro to open the new form - put that on the "enter" event.

Now bear with me... I am clueless right now.... when I type in the word "Welcome" (which will be found in several MsgTxt values) and <enter>, nothing happens. When I click in the box, the new form opens and also a box telling I need a DELETE, INSERT, SELECT, etc.

I know I am missing a step... or two... or three. Right now I feel like I'm also missing my brain! Under the events for the "results form" am I supposed to be adding anything? The fields are tied to the result colums from my query.

Did I need the macro? Am I doing too much to get this to work? <sigh>

Is it Friday yet????


Thanks again!!!
Mary

 

[kilobyte]

Put the macro to open the form in the after update event of the textbox.
Make sure the form is based off the query you made that refers to the textbox.
Putting a query name in an event won't do anything so clear that and see if that works.

-dave

 

[marydoyle9]

Getting closer...

Well, I'm getting a little closer. Now don't laugh but whatever change I made now has values appearing in my text box on the SearchPane instead of the new form (SearchResults). And I get the message you see in the attached doc.

I tried changing the data source for the textbox to =SearchPromptQuery!MsgTxt Like [SearchPormpts]

SearchPrompts is the name of the text box. SearchPromptQuery searches my table and MsgTxt is the column that contains the prompt text. This is where I woudl try to search on the word welcome and HOPE to receive a lsit of all rows that had the word welcome somewhere in MsgTxt.
When I switch out of design mode, the value #Name? is in the box - don't know where that came from. and if I try to do anything in the box I get that lovely message I referred to above. Grrrrrr......

Any more ideas on what I've managed to do?

Thanks and I owe you big time!
Mary

 

[kilobyte]

Okay, I am heading to bed now, so I can't look at it too much tonight but I can throw out a few hints until I can check again.

#name? is an error, it means your referencing something wrong. Type #name? into the help box and it'll tell you the technical reasons for it.

Are you trying to display the results of the searchprompyquery in a text box or a whole new form? Do you get any results at all?

Just for review, these are the things you need:

Form1: A form with a text box, with some way to open Form2 (an event macro, either on a command button or the afterupdate event of the text box
Query1: A query showing all the fields, with a LIKE criteria refering to the text box on form1
Form2: A form based on query1. Show all the fields you want to display and set the display mode to continuous forms.

That should really do it. If I need to clarify more or give more instructions tell me.


If there is a way you could attach your database it would be most useful...

Hope that helps.

-Dave

 

[marydoyle9]

I'm gonna let you look....

Good morning - TGIF for sure! Before proceeding, I want to let you know I am truly grateful for your help on this! You've been really patient with me... thank you!

I think the error (#Name?) is becuase I when I defined the ControlSource for the SearchPane form (where user enter the search string) I was using a "non-defined field". So then I took that out and left ControlSource blank --- YIKES! When I would try to enter a search string, I got the macro error (not found or something). I'd click on OK then enter a string - when I would hit enter, the new form popped, along with the error "You need an Insert, Delete, Select.....".

I'm attaching the db... the two forms for this discussion are SearchPane and SearchPromptResultsForm. The table I need to search is Master, the macro name is PopResultsWindow and the query is named SearchPromptQuery.


Thank you!
Mary

 

[marydoyle9]

I'm gonna let you look....

Good morning - TGIF for sure! Before proceeding, I want to let you know I am truly grateful for your help on this! You've been really patient with me... thank you!

I think the error (#Name?) is becuase I when I defined the ControlSource for the SearchPane form (where user enter the search string) I was using a "non-defined field". So then I took that out and left ControlSource blank --- YIKES! When I would try to enter a search string, I got the macro error (not found or something). I'd click on OK then enter a string - when I would hit enter, the new form popped, along with the error "You need an Insert, Delete, Select.....".

I'm attaching the db... the two forms for this discussion are SearchPane and SearchPromptResultsForm. The table I need to search is Master, the macro name is PopResultsWindow and the query is named SearchPromptQuery.


Thank you!
Mary