Full Version: Looking up a value
UtterAccess Forums > Microsoft® Access > Access Forms
me12345
Hi,
I am creating a database for a swimming club and was wondering if when i enter the lesson ID into the "lessonID" field if it could display the price per term in the "PricePerTerm" field automatically, is there a function similar to VLOOKUP on Microsoft Excel? and how would i get it to work?
thanks.gif
P.S. Sorry if this is in the wrong place
Larry Larsen
Hi
Welcome to Utter Access Forums
Check out the: DLookup Usage Samples.
HTH's
thumbup.gif
me12345
thanks for replying so quickly, but i'm a bit confused, what does "Criteria = n" mean?
Larry Larsen
Hi
DLookup("FieldName" , "TableName" , "Criteria = n")
"Criteria = n" = "[FieldName] = 34"
On = number
Criteria is where you indicate the field name your evaluating against.
thumbup.gif
me12345
DLookup("LessonID" , "tblLessonCategory" , "Criteria = n")
I have that so far, sorry but i still don't understand the criteria = n bit :(
this is tblLessonCategory which i need to look up

notworthy.gif
Larry Larsen
Hi
To search out a price from the table above use this:
hePrice = DLookup("LessonID" , "tblLessonCategory" , "LessonID = '04A'")
Result = £40
HTH's
thumbup.gif
me12345
sorry but i dont want it to look up just 04A
want it so that if i type 05B into the lessonID that it will automatically put £55 into the priceperterm field
Larry Larsen
Hi
So you need to make a reference to a form control that holds the code..?
eg:
YourPriceControlName = DLookup("LessonID" , "tblLessonCategory" , "LessonID = '" & Me.ControlName & "'")
thumbup.gif
me12345
what code? what form?
I have a table with all the info in...
oakbarn
I have attached a same zip file with the db1.mdb in it.
It has two tables and a form
Open The form in design mode and right clcik on the LessonFKBox control.
Click events then AfterUpdate
Click the box with ... to show the code
Private Sub LessonFKBox_AfterUpdate()
PricePerItem = DLookup("[PricePerTerm]", "tblLessonId", "[LessonId]= LessonFKBox")
End Sub

You are setting the PricePerItem field to the PricePerTerm for the LessonID that you type in.
You will notice that I have named the LessonFKBox something different than LessonFK. I did that to show you that it is the Control Name that you are using as the Critierua nd not the field name.
me12345
sorry i'm not getting any of this, i think i preferred VLOOKUP in excel...
ScottGem
There is another way to do this, that you may find easier to grasp.
You have a form with a combobox where you choose the LessonID. Behind that combobox is a query of your table. This is shown in the Rowsource as:
SELECT LessonID FROM tblLessonCategory;
You should change this to:
SELECT LessonID, PricePerTeam FROM tblLessonCategory;
Change the Column Count (format tab) to 2 and the Column Widths to .5";0" (the first number depends on the width of the control.
Finally, add the following code to the After Update event of the combobox:
Me!txtPricePerTerm = Me!cboLessonID.Column(1)
What this does is sets the value of your PriceperTerm control to the second column (column counts start at 0) of the query behind the combo.
HTH
me12345
that seems simply :S if i new how to do a query or combo box
sorry
me12345
>
where does that go?
ScottGem
Use the Combobox wizard to create your combo, then compare the Rowsource property.
me12345
ok i think i did everything right but i get

whenever i try to click the button
me12345
don't worry im just going to do it like this

but how can i do it so that it can be different for each record? e.g. if i click 04B in record 1 it will b 04B for every record...
how do i change it so i can have different ones for each member?
ScottGem
Can you post a copy of your database? Are you using a contionuous form?
TW, that error means you entered a Code expression directly into the property box so Access is trying to interpret as a macro. You have to enter it as a code expression like I said. What you have is not going to work, because it doesn't follow my insttructions.
me12345
no, my database is in a mess!
Well i dont understand your instructions because i am fairly new to access
ScottGem
Excuse me, but there is a difference between understanding instructions and following them. If there is anything you don't understand then please feel free to ask for clarification.
I'm afraid your inexperience here is one of the major problems. You are trying to do things without building a proper foundation. You can't understand how to use Domain Aggregate functions (like DLookup) without understanding what criteria is. You can't understand how to build a form without understanding the different controls and how to use them.
Have you looked at the Northwinds sample database? The techniques you are asking about are illustrated in the sample. You might want to spend some time analyzing the sample to see how they do the things you want to do.
HTH
me12345
i just want to know one thing how i can select different ones for different members instead of it applying to all the records
ScottGem
If you set up the form correctly, binding the control to a field in the table, then it should work fine. Without knowing exactly what you did to setup the listbox (I did say to use the Combobox wizard, not the Listbox wizard) I don't know what you did wrong.
me12345
well comboboxes only display one column! and u can type in them which i dont want!
me12345
ok so how do you get 2 columns to a drop down list?
ScottGem
You can use the listbox if you want. However, you can setup to show multiple columns in a combo by adding the columns to your query. When you pull down the list you can see the columns you have chosen. After you make a selection it will show only the first non zero width column. While you can type in a combo, you can prevent it accepting a value not in the list by setting the Limit to List property.
me12345
ok thanks one last thing is there a way you can make a drop down list show the whole list without having to scroll?
ScottGem
You can set the number of rows in the properties of the drop down.
me12345
thanks! one very last thing confused.gif - i have a drop down list with "yes" and "no" in how can i make the "no" the value which appears in the box to start with? i've tried putting "No" in the default value in the properties box but it didnt work
thanks.gif
ScottGem
Depends on what the combo is bound to. If its bound to a Yes/No field, then setting the default to False, No or 0 should work. If its not bound to a Yes/No field then set the default to "No" with the quotes.
me12345
when i go into properties and type "No" in the default value box nothing happens, i didnt use the yes/no thing, i just typed them in
me12345
dont worry ive figured it out - i have to create new record for "No" to be default
ScottGem
Yes that's true. The default value is only inserted on a new record.
me12345
thanks
shocked.gif Help! When my form was open i accidently clicked "Records" then "Data Entry" now all my data has gone from the form! how do i get it back in the form? the data hasnt gone from the table just the form
ScottGem
Go into the properties of the form and set the Data Entry property to No. Data Entry is one of the worst named features. What Data Entry actually does is make a form only usable for entering new records. You can't view existing records.
me12345
ok i think im doing this right, even though i think i have a different version, i went into Properties in form view then went to the Data tab then i tried changing "Enabled" to "No" but it keeps saying "You can't disablle a control while it has the focus" confused.gif
me12345
dont worry, solved it, thanks
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.