How do I use DLookUp?

[slenish]

Hello,

I have been trying to play with this for several days now and can not get it to work right. I am trying to use the DLookUp function (on my form) so that I can type in one item such as an ID number and it 2 seperate text or combo boxes it will display a name in one box and in the other box an address. I keep trying to use the DLookUp function but I cant seem to get it to work.

I dont understand if I need to put it in every single box, or only one box, or only on the form??

Any help would be great!

Here is what I have so far,

Private Sub Combo8_AfterUpdate()

xVal = Value
xVal = DLookup("FAddress", "FAddressTest", "Combo8")

xVal = Combo12

End Sub

From one thing I read it said you had to declare a value. So i used xVal to equal a value and then I tried to use xVal to equal another box, then I tried to use that value in the combo12 box to lookup another value. I hope im explaining that right.

All i know is im lost. Because everything I read all it says, "is oh yeah just put,

=DLOOKUP (Field, Domain, Criteria)

And off you go!! Well that is not working!

[NeoPa]

I'm afraid I'm unable to work out exactly what you're asking for, so I will explain what I can and see if that helps you. If not, we can at least proceed from there.

Firstly, all parameters are strings.

Field is a string parameter with the name of a field in it, or an expression, relative to the Domain, that resolves to a value.
EG "[StudentID]", "[SalesVal]-[CostVal]", "[FirstName] & ' ' & [LastName]".

Domain is a string parameter that is the name of a table or query. It will not work with a SQL string.
EG "[tblStudent]", "[qryYearlySales]"

Criteria, when used, is a string parameter that specifies criteria just as you would use in a SQL WHERE clause, but without the word "WHERE".
EG "[StudentID]=32667183", "[LastName]='Brown'", "[StudentID]=32667183 OR [LastName]='Brown'"

Criteria of "Combo8" is not right by a way. What you actually want is unclear, but you'd certainly want the value of Me.Combo8 to be included somewhere. "Combo8" doesn't resolve to anything as the string ends up exactly as "Combo8".

[slenish]

Hi NeoPa,

I appreciate you replying back! :D

Ok so when using the DLookUp. I have the Field, Domain, and Criteria. I think I understand that much. You would use the Field part for what you want the box to return or display right? So if I want the box to display a Name from a table I would use the the Column heading "First_Name ", then the Domain would be what table its in like "Name_List" , then the criteria is How to reference to it like "NameID = 5".

But from the way this looks I would have to create a setting for every record in list. How would I do it in a more general way. Where i can leave it more open such as,

The Field is ID Number (but it could be any Id number), then The Domain is NumberTable(whi ch Im does not really change), Then the Criteria will be what is pulled up by the ID number which would need to be open as well. This way you type in some ID number, it looks in the ID number Table and then pulls up a matching Name to go along with the ID number.

Also What I want to be able to do it make is so you can type in the ID number in one box and it will pull up the name in one box and then and an address in another box. Do you just keep the first part the same with the Field and Domain but just add on Criteria in order to do this??

Thanks again for the help. Hope im asking this the right way.

[NeoPa]

Let's take this one step at a time. I don't easily understand what all your problems are so let's keep it as simple as possible and we can make good progress I'm sure.

Let's start with :

The Field is ID Number (but it could be any Id number), ...

Do you actually have many fields in your recordset that are variants of [ID Number]? or are you just saying there may be many values in this field? If the latter then the value for field should be very simple. "[ID Number]". Variations of the data - IE which record to select - are handled by the Criteria parameter. If it's the former than you will need to explain the situation as it's currently not clear.

[drhowarddrfine]

I use DFinger and DEyes to do DLookup in DIndex.

[NeoPa]

I can just feel you bursting to get that one out Doc.

@Slenish. A joke that you needn't worry about. The Doc's in a playful mood :D

[drhowarddrfine]

Sometimes I just can't help myself.

If I dood it, I get a wheepen.
I dood it.
--Red Skelton

[slenish]

Hi NeoPa,

Ok I hope im going to explain this right. I wasnt sure the best approch to this so I broke up my information in to 3 seperate tables. I have one table that has 3 columns, First column is a ZipCode that I am using as an ID number, second column is a city, third is a state. The second table has two columns first ZipCode (or ID number), second only the state. Third table is ZipCode again and only the city. What I want to do is type in the ZipCode and then it will pull the information based on that ZipCode to match it with the correct city and state.

Im going to try and make this a little visual in hopes it helps
Im going to use brackets as box outlines they are not part of any code or anything. Also periods are to seperate out the brackets they are also not part of anything.

So example of the form;

[Zip Code].......[City]...........[State]
you type in the ZipCode

[43081].....Then it pulls from the table(s) the rest of the information and auto populates the other text boxes

[43081].......[Westerville].......[OH]

This way the user does not have to type in any of the other information the program knows that from the zip code which city and state to use to fill out the rest of the boxes. I dont know which is better text boxes or combo boxes for this purprose. Right now im using combo boxes.

I hope this helps to understand my problem better. Appercaite your help on this.

Also drhowarddrfine nice one..haha :D

im about to use dhand into dmonitor to fix the problem or dtower on to dcement! haha

[NeoPa]

It seems to me the value of Field will be determined by which information you require. Unless you are using different names in here from what is actually in your table ([ID Number] instead of [ZipCode]) then you will need variously, [City] and [State]. I would guess the Criteria would always be set to something like :

"[ZipCode]='" & Me.ZipCodeControl & "'"

This all rather leaves the question :
Why would you consider using multiple DLookup()s in place of building your form on a query designed to show all the data you need?

It doesn't seem like this would be very complicated in the circumstances.

[slenish]

Hi NeoPa,

Would a query be better to display the data on the form instead of using the DLookUp function?

I cant go in to to much detail about what im buidling but the jist of it is I am building a form for data entry purposes. The people who will be entering in the data have no access to the back end. I want to make it easier for them to enter in the data to where they can type in a zip code and it will fill in the rest of the boxes on the form with the city and state and a few other things as well then they can hit the add record button and it will save everything to a table on the back end I can print a report from later. Also from other forums I posted earlier my list of zip codes with cities and states is not complete so I made is so if a record is not there they can add it to the list for later use. this way if they get the same information they can just type the zip and everything else comes up. I want to make it so they dont have to sit there and key the same thing over and over, and make it easier for the user.

Also what is actually on my form and on my table is ZipCode and not ID number. But I want to use the ZipCode like an ID number.

So what do you think is the better way to do this with a query or Dlookup function?

[NeoPa]

I would suggest using a query if you can, but make sure the query is updatable (Reasons for a Query to be Non-Updatable).