Full Version: Lookup
UtterAccess Forums > Microsoft® Access > Access Forms
Janene_P
I am trying to put a control on my form that will let me lookup a record according to the information i type in. When i place the control i select find a record on my form based on the information i type in, i get next select the field to check which is [Requisitions]. I want to be able to type in the requisition # and it take me to that record. However, upon selecting the requision field, i get the error message: "No value given for one or more required parameters"
How do I accomplish what i'm trying to do? when i open the form, i am prompted to type in the Employee ID the individual can see only those records. - does this error have something to do with that?
Thanks for your help
ScottGem
The easiest way to do this, is to use the Combobox wizard and create a search combo. The wizard will walk you thru the process.
TH
Janene_P
That is what i'm using, but when i get to selecting the field to look through i get the parameter error.
ScottGem
Try deleting the control and recreating it.
tlclady
Bear with me. I'm a newbie to Access development. I am however a database designer. Here's my question using a simple example: I have a state/prov table that has a FK of Country Code. In turn, I have a Country table that has the PK of Country Code that identifies the Country Name. For the data entry screen for State/Prov I want the user to be able to select the desired country by showing them the list of Country Names which in turn populates the State/Prov row with the applicable Country Code. Very basic relational DB stuff, but how do I make it happen in the Access form this way??
Thanks in advance!
ScottGem
Open the Properties dialog of the State combo. Click on the elippses [...] next to the RowSource in the data tab. This opens query design mode. The first column should be the stateID, the second should be the state name. The third column should be the country code. That column should have a criteria of:
Forms!formname!controlname where formname is the name of your form and controlname the name of the country combo
Uncheck the Show box for that column.
Finally, in the After Update event of the Country combo put a Code expression of:
Me!cboState.Requery use the actual name of the state combo
This technique is called Cascading combos
HTH
tlclady
Let me clarify your instructions because I'm doing something wrong:
pen the Properties dialog of the State combo (are we in the table design or the form design?? Which field are we talking about? State Prov ID or State Prov Name??)
Click on the elippses [...] next to the RowSource in the data tab. This opens query design mode. (I understand that).
The first column should be the stateID, second should be the state name.The third column should be the country code. (That's the layout of my State Prov Table. But where does the join to the Country table come in???)
That column should have a criteria of:
=Forms!formname!controlname where formname is the name of your form and controlname the name of the country combo (I entered =Forms!State Prov!cntry name in the criteria for the cntry code field, but I got a syntax error)
Uncheck the Show box for that column. (Understood)
Finally, in the After Update event of the Country combo put a Code expression of:
Me!cboState.Requery use the actual name of the state combo
What I had done before was, on the State Prov data entry form, I had the Country Code field defined as a combo, the query read the Country Table (which contained Country Code (PK), Country Name. I unchecked Country Code. The drop down list show the Country Name, I selected a Country Name, but then got an error because the program then tried to populat the Country Code field in the State Prov table with the Country Name instead of with the corresponding Country Code. This last step is my problem. I don't understand (logically) how your description solves that problem??
ScottGem
First, I'm taking form design mode. You should NOT be setting lookups on the table level. This cause more problems then its worth. If you have lookups on the table level, you should remove them. All lookups should be done by creating comboboxes on the form level.
I'm not clear what forms you are inputting to at this point (you might want to attach your db). But assuming you have a bound form, your Country combo should be bound to the CountryID field. Its RowSource should look like this:
SELECT CountryCode, CountryName FROM tblCountry ORDER BY CountryName
The RowSource of your State/Prov combo should look like this:
SELECT StateCode. StateName FROM tblStateProv WHERE CountryCode = Forms!formname!cboCountry ORDER BY StateName
tlclady
I can't believe I'm being so dense. Not enough coffee yet, I guess. I am attaching a sample database. Again, the intent of the form is to POPULATE the state prov table (with State Prov Code(PK), State Prov Name, Country Code (FK)). In order to enter the Country Code to which the State belongs, I want a drop down list to appear with the Country NAMES showing (from the Country Table), from which the user selects the appropriate NAME which should in turn POPULATE the State Prov row with the corresponing/correct Country CODE.
ScottGem
What a minute. I think I was one being dense. Part of the problem here is that you piggybacked your question on someone else's thread that had to do with Cascading Combos so I thought that's what you were asking about.
That you were actually asking about is how to create a SINGLE combo that looks up against another table. All you needed to do was use the Combobox wizard to do it.
I've attached your sample with the form as I think you wanted it. I just deleted the control you had and used the wizard.
tlclady
I'm sorry about the thread confusion. Okay, your sample works perfectly. I tried to recreate/mimic what you did, but I do not get the same results. I can't for the life of me figure out what I'm doing differently. I have attached the DB. What am I missing????????
ScottGem
Did you use the Wizard? I see what's wrong, you aren't including the Country Code in the query behind the combo. I just ran thru the wizard again and here's what happened:
) selected top option (lookup values in table), pressed Next
2) selected table country, press Next
3) double clicked country name from left side, both fields copied to right side, pressed Next
4) Made sure Hide Key was checked and sized column, pressed Next
5) Selected 2nd option to Store value in field and chose Country Code, press next
6) Typed in Country as label, pressed Finish.
HTH
tlclady
You must be sick to death of me by now, but I REALLY appreciate your working thru this with me. It's so simple, it's maddening. First of all I DID include Country Code and even checked that the SQL code looked exactly like yours, but when I go back to look it's county Code is GONE!!! So I see why you think I didn't include it.
I'm working in 2003 which may be important to note, because I am NOT seeing the things you very clearly specified in your last reply. I use FORM WIZARD to create the form. (Which Wizard are you talking about????) I choose all columns from the STATE PROV table. I then change the COUNTRY CODE field (by right clicking) to COMBO. I open PROPERTIES, go to ROWSOURCE, choose COUNTRY table, include COUNTRY CODE and NAME, sort by NAME. When I go back into Form View mode to enter data, the COUNTRY CODE is displayed in drop down and NOT the COUNTRY NAME like it does in your example. I am sending you actual screen prints of each of my screen, so you can see that they look the same as yours, but work different...ARGGGGG!
1) selected top option (lookup values in table), pressed Next (WHERE IS THIS?????)
ScottGem
Ahhhh, you aren't using the Combobox wizard. On the Control toolbox is a combobox wizard. That's what I'm referring to and that will lead you thru the 6 steps. To do it the way you are you need to add a few more steps. You are fine up to creating the query. After you close the Query Design mode, make sure the Bound column is set to 1. Then go to the Format tab and set the Column count to 2. Finally, set the Column Widths property to 0";x" where x is the width of the control. That's how to do it manually.
tlclady
You wrote: Then go to the Format tab and set the Column count to 2. Finally, set the Column Widths property to 0";x" where x is the width of the control. That's how to do it manually.
UREKA!! My hero! Thank you so much! What I don't know is WHY that worked?
Can you explain what it's doing?
Also, not sure why I can'f find Combobox wizard? Could it have something to do with how my Access was installed? There is a box in my toolbox that says Control Wizards, but nothing seems to happen when I click it except that it turns orange or not.
And one last question: how do I reset default values. For example when I create a form via form wizard, I'd like it to use a different font style and size. How do I reset that?
Opromise to give you a break now. Thank you again!
ScottGem
Yeah sounds like you have a bum install. You have to be in Form Design Mode, then right click on a blank area of the menu bar. There should be a listing for Toolbox, make sure its checked. If it isn't there, then you should reinstall.
Is to why it works, its fairly simple. You can create a query behind any combo and put any number of columns in the query. The Column count and Column Widths properties control how those columns are displayed. The Combobox will display the first column of the query that doesn't have a 0 for its width.
There are two ways to change the defaults for controls. You can right clikc on a control in the Toolbox (I know YOU can't right now) or you can create a control on a form, then use Format>Set Control Defaults from the menu.
tlclady
Thank you so much for all your help. You are great!
ScottGem
glad to assist
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.