Connecting a Parameterized Query in to a form

[saleemMSMS]

i have a parameterized query.. it is "Select Description from Books where BookID=<a parameter>"

i want to link this with a form and i'd like to pass the parameter from a text box in the form.
i need to connect this query in to a button click event where the parameter will get the value from the text box at that time..
is there any way of doing this?
if so please let me know

 

[saleemMSMS]

c'mon !! reply to this

 

[Banana]

c'mon !! reply to this

Uh, wow. I sure didn't know there was a stipulation that a board consisted of volunteers who are willing to help out other out on their own time must reply within an hour. Sure didn't get a contract on that one.

Seriously? That was rude. If you are desperate for help and short on time, then I suggest you go and locate a local consultant to do the job for you on your own timeline. Otherwise, click the link on my signature and learn how to ask questions that do get answered.

For your question, you need to use VBA if it's a generic parameter query. If this is a query that will live & die by the form, then you may find it easier to just reference the form's control inside the query. But if you need to have it portable between different forms, or maybe by same form that could be either a standalone form or a subform, then you need to set the parameter in VBA. Look at the VBA Editor's help file (not Access's help) for QueryDef object's Parameters collection to see how they are used.

 

[Galaxiom]

Some posters wait hours or days for someone to find the time to read, understand, analyse and answer their question. Some never get answered. All of us here are volunteers and I suggest if you would like our help that you learn some better manners.

Moreover I have already answered this question three hours before you reposted it here and just nine minutes after you first asked.

http://www.access-programmers.co.uk/forums/showthread.php?t=178375

Please avoid double posting questions particularly if you can't even be bothered checking for answers or providing feedback to those who have tried to help.

I do know how frustrating Access can be particularly when starting out. Most of us have been there. But if you need to release your frustrations I would suggest a short walk rather than being rude to the very people who would help you.

 

[saleemMSMS]

alright i am extremely sorry ok ??? i was frustrated at that time..
it wont happen again..
Mr.GalaxiomAtHome, ur answer is not clear.. i mean the previous one..
once again, sorry....

 

[Galaxiom]

Instead of using the parameter in the query you can simply replace it with a direct reference to the textbox. This is what is usually done.

To refer to a textbox on a form in SQL you use:

Forms!formname.Form!textboxname
which can usually be shortened to:
Forms!formname!textboxname

If it is on a subform this is more complex.
Here is a reference for referring to controls on forms.
http://mvps.org/access/forms/frm0031.htm

Otherwise you have to do in VBA as Banana said.
But as you don't appear to have much experience in VBA I would suggest you don't start out by trying to create a parameterised querydef.

 

[saleemMSMS]

can i redirect the output to another textbox ? (if its just one value)

 

[Banana]

Will it be a one-row, one-column resultset? I suppose for non-VBA method, you could use DLookUp to accomplish this. Consult the help file on the "DLookup" for the syntax.

But I have to ask... what exactly are you doing with this the parameter query? I think having more context may help us provide more accurate and better answer.

 

[Galaxiom]

can i redirect the output to another textbox ? (if its just one value)

Not sure I understand.

Do you mean to redirect the output from the query after using the textbox on the form in place of the parameter?

If you are just wanting to get the description from the table and put it in a textbox you can simply use a DLookUp function.

DLookUp("Description","Books","Books.BookID=Forms!your_form_name!input_textbox")

Either put this in the ControlSource (with an equal sign before it) of your target textbox. Or use an event to set the value of the target box to this expression.

You have to use the event if the target textbox is bound to the RecordSet (table or query in the RecordSource property of the form).

 

[saleemMSMS]

well, this is what i want to do.. i have 2 tables
Books (bookID, title, authorID, price)
Authors (AuthorID, AuthorName)
when i generate 2 forms using the form wizard, 2 forms will be generated.. but in the books form, there will be a text box to enter the authorID. what i need is to have a reference to that point so that the data entry operator will not get confused.
so i thought of placing a combo box in front of the authorID text box in the Books Form. this combo box will have all the author names in it. whenever the used selects an author name, the Author ID text box should get filled with the respective author ID of the selected author name.
so i built a query saying select AuthorID form Authors where AuthorName=<a parameter>
but i'm unable to link this query with the combo box's onChange event. this is what made me to ask this question.

so PLEASE can u guys tell me is my approach wrong ? if it is not wrong, what am i missing here??
if its wrong, PLEASE explain me the correct way...

THANK YOU VERY MUCH FOR DEVOTING TIME ON THIS QUESTION
sorry again for being rude in previous occasions

 

[Banana]

I think you will find it much easier if you create a form with a subform, the parent form containing authors and the subform the books. If you use wizard, Access will perform all the linking setup so the subform is always automatically filtered based on the current record of the parent record.

In design view of say, authors form, make sure the magic wand button is highlighted, then add a subform. Wizard will appear and guide you through the steps for setting up the subform.

 

[saleemMSMS]

can i use a combo box instead of the text box

 

[saleemMSMS]

the DLookup thing returns an error
runtime error 2001 "You Cancelled the previous operation"