DLookup

L

lmcc007

I have a query that I created from my tblAddresses. It has five fields:



1) AddressID

2) CompanyID

3) TypeofAdd_Lkp

4) MailingAddress

5) FullAddress (an expression)



I created a form from the query.



Now I am working with the TypeofAdd_Lkp field. This field has three
choices: Home, Business, Other.



So, if I choose Other, I want the address to show for Other; if I choose
Business, I want the address to show for Business and so on.



I entered the following code on the TypeofAdd_Lkp field--On Click, I entered
an Event Procedure.



Private Sub TypeofAdd_Lkp_Click()

Dim AddressChoice



AddressChoice = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]")

End Sub



It is not doing anything. Did I miss something?



Thanks!
 
D

Duane Hookom

Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event. Where do
you want the looked up address to go? Your code sticks it in a memory
variable without selecting a specific AddressID.

Assuming AddressID is numeric, your code might look like:
Me.txt???? = DLookup("[TypeofAdd_Lkp]", "qryFullAddress", "[AddressID] =
" & Me.AddressID)
 
L

lmcc007

Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event.

No, all the addresses are stored in tblAddresses.

Where do you want the looked up address to go? Your code sticks it in a
memory variable without selecting a specific AddressID.

FullAddress field

I tried this code, but it gets an error:

Me.FullAddress = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]=" & Me.AddressID)

Basically, I am looking at CompanyID 1461, went to the TypeofAdd_Lkp combo
box field and chose a type. After choosing the type I want the address for
that type to be displayed in the FullAddress field. CompanyID 1461 has 3
different addresses, which are stored in AddressID 1987, 1461, and 1986.

AddressID 1987 = Other
AddressID 1461 = Business
AddressID 1986 = Home

If the type chosen has not been used, just show a blank in FullAddress field.


Thanks Duane for your help!

Duane Hookom said:
Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event. Where do
you want the looked up address to go? Your code sticks it in a memory
variable without selecting a specific AddressID.

Assuming AddressID is numeric, your code might look like:
Me.txt???? = DLookup("[TypeofAdd_Lkp]", "qryFullAddress", "[AddressID] =
" & Me.AddressID)

--
Duane Hookom
Microsoft Access MVP


lmcc007 said:
I have a query that I created from my tblAddresses. It has five fields:



1) AddressID

2) CompanyID

3) TypeofAdd_Lkp

4) MailingAddress

5) FullAddress (an expression)



I created a form from the query.



Now I am working with the TypeofAdd_Lkp field. This field has three
choices: Home, Business, Other.



So, if I choose Other, I want the address to show for Other; if I choose
Business, I want the address to show for Business and so on.



I entered the following code on the TypeofAdd_Lkp field--On Click, I entered
an Event Procedure.



Private Sub TypeofAdd_Lkp_Click()

Dim AddressChoice



AddressChoice = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]")

End Sub



It is not doing anything. Did I miss something?



Thanks!
Click to expand...
Click to expand...
 
D

Duane Hookom

I can't tell from your posts whether you want to return a value of an actual
address or a string value like "Other", "Business", or "Home". If you want an
actual address, it seems to me you should include the CompanyID in the lookup.
--
Duane Hookom
Microsoft Access MVP


lmcc007 said:
Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event.

No, all the addresses are stored in tblAddresses.

Where do you want the looked up address to go? Your code sticks it in a
memory variable without selecting a specific AddressID.

FullAddress field

I tried this code, but it gets an error:

Me.FullAddress = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]=" & Me.AddressID)

Basically, I am looking at CompanyID 1461, went to the TypeofAdd_Lkp combo
box field and chose a type. After choosing the type I want the address for
that type to be displayed in the FullAddress field. CompanyID 1461 has 3
different addresses, which are stored in AddressID 1987, 1461, and 1986.

AddressID 1987 = Other
AddressID 1461 = Business
AddressID 1986 = Home

If the type chosen has not been used, just show a blank in FullAddress field.


Thanks Duane for your help!

Duane Hookom said:
Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event. Where do
you want the looked up address to go? Your code sticks it in a memory
variable without selecting a specific AddressID.

Assuming AddressID is numeric, your code might look like:
Me.txt???? = DLookup("[TypeofAdd_Lkp]", "qryFullAddress", "[AddressID] =
" & Me.AddressID)

--
Duane Hookom
Microsoft Access MVP


lmcc007 said:
I have a query that I created from my tblAddresses. It has five fields:



1) AddressID

2) CompanyID

3) TypeofAdd_Lkp

4) MailingAddress

5) FullAddress (an expression)



I created a form from the query.



Now I am working with the TypeofAdd_Lkp field. This field has three
choices: Home, Business, Other.



So, if I choose Other, I want the address to show for Other; if I choose
Business, I want the address to show for Business and so on.



I entered the following code on the TypeofAdd_Lkp field--On Click, I entered
an Event Procedure.



Private Sub TypeofAdd_Lkp_Click()

Dim AddressChoice



AddressChoice = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]")

End Sub



It is not doing anything. Did I miss something?



Thanks!
Click to expand...
Click to expand...
Click to expand...
 
L

lmcc007

Okay, I was thinking CompanyID may need to go somewhere but I am lost.

I found a DLookup function where the address fields are automatically
populated when the customer is changed using the combo box. This is similar
to what I am trying to do but I think I am missing something. Yep, been
working on it a while but I keep asking myself the same question.

Duane Hookom said:
I can't tell from your posts whether you want to return a value of an actual
address or a string value like "Other", "Business", or "Home". If you want an
actual address, it seems to me you should include the CompanyID in the lookup.
--
Duane Hookom
Microsoft Access MVP


lmcc007 said:
Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event.

No, all the addresses are stored in tblAddresses.

Where do you want the looked up address to go? Your code sticks it in a
memory variable without selecting a specific AddressID.

FullAddress field

I tried this code, but it gets an error:

Me.FullAddress = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]=" & Me.AddressID)

Basically, I am looking at CompanyID 1461, went to the TypeofAdd_Lkp combo
box field and chose a type. After choosing the type I want the address for
that type to be displayed in the FullAddress field. CompanyID 1461 has 3
different addresses, which are stored in AddressID 1987, 1461, and 1986.

AddressID 1987 = Other
AddressID 1461 = Business
AddressID 1986 = Home

If the type chosen has not been used, just show a blank in FullAddress field.


Thanks Duane for your help!

Duane Hookom said:
Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event. Where do
you want the looked up address to go? Your code sticks it in a memory
variable without selecting a specific AddressID.

Assuming AddressID is numeric, your code might look like:
Me.txt???? = DLookup("[TypeofAdd_Lkp]", "qryFullAddress", "[AddressID] =
" & Me.AddressID)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that I created from my tblAddresses. It has five fields:



1) AddressID

2) CompanyID

3) TypeofAdd_Lkp

4) MailingAddress

5) FullAddress (an expression)



I created a form from the query.



Now I am working with the TypeofAdd_Lkp field. This field has three
choices: Home, Business, Other.



So, if I choose Other, I want the address to show for Other; if I choose
Business, I want the address to show for Business and so on.



I entered the following code on the TypeofAdd_Lkp field--On Click, I entered
an Event Procedure.



Private Sub TypeofAdd_Lkp_Click()

Dim AddressChoice



AddressChoice = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]")

End Sub



It is not doing anything. Did I miss something?



Thanks!
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

If you want more assistance, please start over with a definition of what you
have and what you have. We need table and field names as well as data types.
It would help if you provided some sample records.

--
Duane Hookom
Microsoft Access MVP


lmcc007 said:
Okay, I was thinking CompanyID may need to go somewhere but I am lost.

I found a DLookup function where the address fields are automatically
populated when the customer is changed using the combo box. This is similar
to what I am trying to do but I think I am missing something. Yep, been
working on it a while but I keep asking myself the same question.

Duane Hookom said:
I can't tell from your posts whether you want to return a value of an actual
address or a string value like "Other", "Business", or "Home". If you want an
actual address, it seems to me you should include the CompanyID in the lookup.
--
Duane Hookom
Microsoft Access MVP


lmcc007 said:
Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event.

No, all the addresses are stored in tblAddresses.

Where do you want the looked up address to go? Your code sticks it in a
memory variable without selecting a specific AddressID.

FullAddress field

I tried this code, but it gets an error:

Me.FullAddress = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]=" & Me.AddressID)

Basically, I am looking at CompanyID 1461, went to the TypeofAdd_Lkp combo
box field and chose a type. After choosing the type I want the address for
that type to be displayed in the FullAddress field. CompanyID 1461 has 3
different addresses, which are stored in AddressID 1987, 1461, and 1986.

AddressID 1987 = Other
AddressID 1461 = Business
AddressID 1986 = Home

If the type chosen has not been used, just show a blank in FullAddress field.


Thanks Duane for your help!

:

Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event. Where do
you want the looked up address to go? Your code sticks it in a memory
variable without selecting a specific AddressID.

Assuming AddressID is numeric, your code might look like:
Me.txt???? = DLookup("[TypeofAdd_Lkp]", "qryFullAddress", "[AddressID] =
" & Me.AddressID)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that I created from my tblAddresses. It has five fields:



1) AddressID

2) CompanyID

3) TypeofAdd_Lkp

4) MailingAddress

5) FullAddress (an expression)



I created a form from the query.



Now I am working with the TypeofAdd_Lkp field. This field has three
choices: Home, Business, Other.



So, if I choose Other, I want the address to show for Other; if I choose
Business, I want the address to show for Business and so on.



I entered the following code on the TypeofAdd_Lkp field--On Click, I entered
an Event Procedure.



Private Sub TypeofAdd_Lkp_Click()

Dim AddressChoice



AddressChoice = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]")

End Sub



It is not doing anything. Did I miss something?



Thanks!
Click to expand...
Click to expand...
Click to expand...
 
L

lmcc007

Okay.

How do you attached a db here?

Duane Hookom said:
If you want more assistance, please start over with a definition of what you
have and what you have. We need table and field names as well as data types.
It would help if you provided some sample records.

--
Duane Hookom
Microsoft Access MVP


lmcc007 said:
Okay, I was thinking CompanyID may need to go somewhere but I am lost.

I found a DLookup function where the address fields are automatically
populated when the customer is changed using the combo box. This is similar
to what I am trying to do but I think I am missing something. Yep, been
working on it a while but I keep asking myself the same question.

Duane Hookom said:
I can't tell from your posts whether you want to return a value of an actual
address or a string value like "Other", "Business", or "Home". If you want an
actual address, it seems to me you should include the CompanyID in the lookup.
--
Duane Hookom
Microsoft Access MVP


:

Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event.

No, all the addresses are stored in tblAddresses.

Where do you want the looked up address to go? Your code sticks it in a
memory variable without selecting a specific AddressID.

FullAddress field

I tried this code, but it gets an error:

Me.FullAddress = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]=" & Me.AddressID)

Basically, I am looking at CompanyID 1461, went to the TypeofAdd_Lkp combo
box field and chose a type. After choosing the type I want the address for
that type to be displayed in the FullAddress field. CompanyID 1461 has 3
different addresses, which are stored in AddressID 1987, 1461, and 1986.

AddressID 1987 = Other
AddressID 1461 = Business
AddressID 1986 = Home

If the type chosen has not been used, just show a blank in FullAddress field.


Thanks Duane for your help!

:

Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event. Where do
you want the looked up address to go? Your code sticks it in a memory
variable without selecting a specific AddressID.

Assuming AddressID is numeric, your code might look like:
Me.txt???? = DLookup("[TypeofAdd_Lkp]", "qryFullAddress", "[AddressID] =
" & Me.AddressID)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that I created from my tblAddresses. It has five fields:



1) AddressID

2) CompanyID

3) TypeofAdd_Lkp

4) MailingAddress

5) FullAddress (an expression)



I created a form from the query.



Now I am working with the TypeofAdd_Lkp field. This field has three
choices: Home, Business, Other.



So, if I choose Other, I want the address to show for Other; if I choose
Business, I want the address to show for Business and so on.



I entered the following code on the TypeofAdd_Lkp field--On Click, I entered
an Event Procedure.



Private Sub TypeofAdd_Lkp_Click()

Dim AddressChoice



AddressChoice = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]")

End Sub



It is not doing anything. Did I miss something?



Thanks!
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

You don't attach a file in news groups. You type all significant information
into your message. You must learn how to provide enough information and
sample records so that someone can provide help.

--
Duane Hookom
Microsoft Access MVP


lmcc007 said:
Okay.

How do you attached a db here?

Duane Hookom said:
If you want more assistance, please start over with a definition of what you
have and what you have. We need table and field names as well as data types.
It would help if you provided some sample records.

--
Duane Hookom
Microsoft Access MVP


lmcc007 said:
Okay, I was thinking CompanyID may need to go somewhere but I am lost.

I found a DLookup function where the address fields are automatically
populated when the customer is changed using the combo box. This is similar
to what I am trying to do but I think I am missing something. Yep, been
working on it a while but I keep asking myself the same question.

:

I can't tell from your posts whether you want to return a value of an actual
address or a string value like "Other", "Business", or "Home". If you want an
actual address, it seems to me you should include the CompanyID in the lookup.
--
Duane Hookom
Microsoft Access MVP


:

Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event.

No, all the addresses are stored in tblAddresses.

Where do you want the looked up address to go? Your code sticks it in a
memory variable without selecting a specific AddressID.

FullAddress field

I tried this code, but it gets an error:

Me.FullAddress = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]=" & Me.AddressID)

Basically, I am looking at CompanyID 1461, went to the TypeofAdd_Lkp combo
box field and chose a type. After choosing the type I want the address for
that type to be displayed in the FullAddress field. CompanyID 1461 has 3
different addresses, which are stored in AddressID 1987, 1461, and 1986.

AddressID 1987 = Other
AddressID 1461 = Business
AddressID 1986 = Home

If the type chosen has not been used, just show a blank in FullAddress field.


Thanks Duane for your help!

:

Do you really need to store the same address in more than one place in your
MDB? I would use the After Update event rather than the Click event. Where do
you want the looked up address to go? Your code sticks it in a memory
variable without selecting a specific AddressID.

Assuming AddressID is numeric, your code might look like:
Me.txt???? = DLookup("[TypeofAdd_Lkp]", "qryFullAddress", "[AddressID] =
" & Me.AddressID)

--
Duane Hookom
Microsoft Access MVP


:

I have a query that I created from my tblAddresses. It has five fields:



1) AddressID

2) CompanyID

3) TypeofAdd_Lkp

4) MailingAddress

5) FullAddress (an expression)



I created a form from the query.



Now I am working with the TypeofAdd_Lkp field. This field has three
choices: Home, Business, Other.



So, if I choose Other, I want the address to show for Other; if I choose
Business, I want the address to show for Business and so on.



I entered the following code on the TypeofAdd_Lkp field--On Click, I entered
an Event Procedure.



Private Sub TypeofAdd_Lkp_Click()

Dim AddressChoice



AddressChoice = DLookup("[TypeofAdd_Lkp]", "qryFullAddress",
"[AddressID]")

End Sub



It is not doing anything. Did I miss something?



Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Can't Get Dlookup To Work 1
Stumped by "one to many" in query, need some help 4
Address Design Structure 16
Change specific records' values 1
modelling a contact database 2
Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1
DLookup and Nz 0
Table Question 1

Top