create a look up

  • Thread starter Thread starter JMD.Park
  • Start date Start date
J

JMD.Park

I would like to create a look up for a field in a form. I would like to put
in a customer number that will bring up their name and address info. How can
I do this? Thanks in advance for your assistance.
 
JMD.Park said:
I would like to create a look up for a field in a form. I would like to put
in a customer number that will bring up their name and address info. I would like the address and info to come from a table in the same database. How can
I do this? Thanks in advance for your assistance.
Click to expand...
 
I would like to create a look up for a field in a form. I would like to put
in a customer number that will bring up their name and address info. How can
I do this? Thanks in advance for your assistance.
Click to expand...

It depends.
If The [CustomerNumber] field's datatype is a number, then:
=DLookUp("[Address]","TableName","[CustomerNumber] = " &
Me.[CustomerNumber])

However, if the [CustomerNumber] field is a Text datatype, then:

=DLookUp("[Address]","TableName","[CustomerNumber] = '" &
Me.[CustomerNumber] & "'")

Do the same for the Customer's Name field.

Change CustomerNumber to whatever the actual field name is.
 
I would like to create a look up for a field in a form. I would like to put
in a customer number that will bring up their name and address info. How can
I do this? Thanks in advance for your assistance.
Click to expand...

Open the Toolbox; select the magic wand icon. Then select the Combo Box tool
and put a combo box on the form. Use the wizard option "use this combo to find
a record".
 
JMD.Park said:
I would like to create a look up for a field in a form. I would like to put
in a customer number that will bring up their name and address info. How
can
I do this? Thanks in advance for your assistance.
Click to expand...
 
I am new to this. What is Address?

fredg said:
I would like to create a look up for a field in a form. I would like to put
in a customer number that will bring up their name and address info. How can
I do this? Thanks in advance for your assistance.
Click to expand...

It depends.
If The [CustomerNumber] field's datatype is a number, then:
=DLookUp("[Address]","TableName","[CustomerNumber] = " &
Me.[CustomerNumber])

However, if the [CustomerNumber] field is a Text datatype, then:

=DLookUp("[Address]","TableName","[CustomerNumber] = '" &
Me.[CustomerNumber] & "'")

Do the same for the Customer's Name field.

Change CustomerNumber to whatever the actual field name is.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.
Click to expand...
 
I will try to explain myself better:
I would like this "Customer"=number in Form 1 called "Distribution" to bring
up these fields from "Historical" table that is linked by the way. The
following fields are for me to verify that I have the correct customer
number. The fields are all text and are as follows: MailingAddress1, Mailing
Address2, MailingCity, MailingState, MailingZip, and Phone. I tried this:
=DLookUp("[Distribution]","Historical","[CustomerNumber] = "
&Me.[CustomerNumber])
I receive an error.


JMD.Park said:
I am new to this. What is Address?

fredg said:
I would like to create a look up for a field in a form. I would like to put
in a customer number that will bring up their name and address info. How can
I do this? Thanks in advance for your assistance.
Click to expand...

It depends.
If The [CustomerNumber] field's datatype is a number, then:
=DLookUp("[Address]","TableName","[CustomerNumber] = " &
Me.[CustomerNumber])

However, if the [CustomerNumber] field is a Text datatype, then:

=DLookUp("[Address]","TableName","[CustomerNumber] = '" &
Me.[CustomerNumber] & "'")

Do the same for the Customer's Name field.

Change CustomerNumber to whatever the actual field name is.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.
Click to expand...
Click to expand...
 
Also, is there a specific class I can take that covers these types of
questions?

fredg said:
I would like to create a look up for a field in a form. I would like to put
in a customer number that will bring up their name and address info. How can
I do this? Thanks in advance for your assistance.
Click to expand...

It depends.
If The [CustomerNumber] field's datatype is a number, then:
=DLookUp("[Address]","TableName","[CustomerNumber] = " &
Me.[CustomerNumber])

However, if the [CustomerNumber] field is a Text datatype, then:

=DLookUp("[Address]","TableName","[CustomerNumber] = '" &
Me.[CustomerNumber] & "'")

Do the same for the Customer's Name field.

Change CustomerNumber to whatever the actual field name is.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.
Click to expand...
 
Forgot to include BusinessName is text as well.

JMD.Park said:
I will try to explain myself better:
I would like this "Customer"=number in Form 1 called "Distribution" to bring
up these fields from "Historical" table that is linked by the way. The
following fields are for me to verify that I have the correct customer
number. The fields are all text and are as follows: MailingAddress1, Mailing
Address2, MailingCity, MailingState, MailingZip, and Phone. I tried this:
=DLookUp("[Distribution]","Historical","[CustomerNumber] = "
&Me.[CustomerNumber])
I receive an error.


JMD.Park said:
I am new to this. What is Address?

fredg said:
On Fri, 12 Mar 2010 08:51:02 -0800, JMD.Park wrote:

I would like to create a look up for a field in a form. I would like to put
in a customer number that will bring up their name and address info. How can
I do this? Thanks in advance for your assistance.

It depends.
If The [CustomerNumber] field's datatype is a number, then:
=DLookUp("[Address]","TableName","[CustomerNumber] = " &
Me.[CustomerNumber])

However, if the [CustomerNumber] field is a Text datatype, then:

=DLookUp("[Address]","TableName","[CustomerNumber] = '" &
Me.[CustomerNumber] & "'")

Do the same for the Customer's Name field.

Change CustomerNumber to whatever the actual field name is.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.
Click to expand...
Click to expand...
Click to expand...
 
Lets try this again. I am trying to use DLookup to bring up several fields
from a table into a form. Can someone please let me know if this is possible
and tell me how to do this? I am new to expression builder so please be
patient with me and specific as well as where to build.
I would like this "CustomerName" that is a number field in Form called
"Distribution" to bring up these fields from "Historical" table that is
linked by the way. The following fields are for me to verify that I have the
correct customer
number. The fields are all text and are fields of there own in the form.
They are as follows: BusinessName ,MailingAddress1, Mailing
Address2, MailingCity, MailingState, MailingZip, and Phone. I tried this:
=DLookUp("[Distribution]","Historical","[CustomerNumber] = "
&Me.[CustomerNumber])
I receive an error. I have also tried the wand. It does not bring up the
correct customer with number and only brings up several fields that are not
placed in the forms table.
 
Lets try this again. I am trying to use DLookup to bring up several fields
from a table into a form. Can someone please let me know if this is possible
and tell me how to do this? I am new to expression builder so please be
patient with me and specific as well as where to build.
I would like this "CustomerName" that is a number field in Form called
"Distribution" to bring up these fields from "Historical" table that is
linked by the way. The following fields are for me to verify that I have the
correct customer
number. The fields are all text and are fields of there own in the form.
They are as follows: BusinessName ,MailingAddress1, Mailing
Address2, MailingCity, MailingState, MailingZip, and Phone. I tried this:
=DLookUp("[Distribution]","Historical","[CustomerNumber] = "
&Me.[CustomerNumber])
I receive an error. I have also tried the wand. It does not bring up the
correct customer with number and only brings up several fields that are not
placed in the forms table.


fredg said:
I would like to create a look up for a field in a form. I would like to put
in a customer number that will bring up their name and address info. How can
I do this? Thanks in advance for your assistance.
Click to expand...

It depends.
If The [CustomerNumber] field's datatype is a number, then:
=DLookUp("[Address]","TableName","[CustomerNumber] = " &
Me.[CustomerNumber])

However, if the [CustomerNumber] field is a Text datatype, then:

=DLookUp("[Address]","TableName","[CustomerNumber] = '" &
Me.[CustomerNumber] & "'")

Do the same for the Customer's Name field.

Change CustomerNumber to whatever the actual field name is.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.
Click to expand...
 
Lets try this again. I am trying to use DLookup to bring up several fields
from a table into a form. Can someone please let me know if this is possible
and tell me how to do this? I am new to expression builder so please be
patient with me and specific as well as where to build.
I would like this "CustomerName" that is a number field in Form called
"Distribution" to bring up these fields from "Historical" table that is
linked by the way. The following fields are for me to verify that I have the
correct customer
number. The fields are all text and are fields of there own in the form.
They are as follows: BusinessName ,MailingAddress1, Mailing
Address2, MailingCity, MailingState, MailingZip, and Phone. I tried this:
=DLookUp("[Distribution]","Historical","[CustomerNumber] = "
&Me.[CustomerNumber])
Click to expand...

Let's try this again.

1. What is the Recordsource of the Form? What table? Could you post the SQL?
2. What is the Rowsource of the combo box? Could you post its SQL?
3, and most important: are you trying to *COPY* the CustomerName and other
fields from the combo box's table into the form's (different) table, or are
you just trying to *DISPLAY* them? The latter would be correct, so I'll give
instructions:

Include all the fields that you want to see in the Combo Box's Rowsource.
Guessing in the dark, I'll say that the row source might be

SELECT CustomerNumber, BusinessName, MailingAddress1, MailingAddress2,
MailingCity, MailingState, MailingZip, Phone FROM Historical ORDER BY
BusinessName;

You can set the ColumnWidths property of the combo so that columns you don't
want to see have zero width; only the first nonzero column will be visible
when the combo isn't dropped down. So you might have a ColumnWidths property
like

0";1.25";0";0";1";0.25";0"

so the user can see the BusinessName, City and State when the combo is
dropped, and only the BusinessName when it's not.

To display the other fields' data on the form, have a textbox for (say)
MailingAddress1. Set its Control Source property to

=comboboxname.Column(2)

This will display the third column of the combo (it's zero based) - the
mailingaddress1 field.

If you already have a MailingAddress1 control on the form, bound to a
MailingAddress1 field in your table, and this is a different table than the
one upon which the combo box is based... *you're making a bad mistake* or else
need to explain why you would want to violate relational design principles by
storing the same information redundantly in two different table!
 
Where do I find the record source and sql?

John W. Vinson said:
Lets try this again. I am trying to use DLookup to bring up several fields
from a table into a form. Can someone please let me know if this is possible
and tell me how to do this? I am new to expression builder so please be
patient with me and specific as well as where to build.
I would like this "CustomerName" that is a number field in Form called
"Distribution" to bring up these fields from "Historical" table that is
linked by the way. The following fields are for me to verify that I have the
correct customer
number. The fields are all text and are fields of there own in the form.
They are as follows: BusinessName ,MailingAddress1, Mailing
Address2, MailingCity, MailingState, MailingZip, and Phone. I tried this:
=DLookUp("[Distribution]","Historical","[CustomerNumber] = "
&Me.[CustomerNumber])
Click to expand...

Let's try this again.

1. What is the Recordsource of the Form? What table? Could you post the SQL?
2. What is the Rowsource of the combo box? Could you post its SQL?
3, and most important: are you trying to *COPY* the CustomerName and other
fields from the combo box's table into the form's (different) table, or are
you just trying to *DISPLAY* them? The latter would be correct, so I'll give
instructions:

Include all the fields that you want to see in the Combo Box's Rowsource.
Guessing in the dark, I'll say that the row source might be

SELECT CustomerNumber, BusinessName, MailingAddress1, MailingAddress2,
MailingCity, MailingState, MailingZip, Phone FROM Historical ORDER BY
BusinessName;

You can set the ColumnWidths property of the combo so that columns you don't
want to see have zero width; only the first nonzero column will be visible
when the combo isn't dropped down. So you might have a ColumnWidths property
like

0";1.25";0";0";1";0.25";0"

so the user can see the BusinessName, City and State when the combo is
dropped, and only the BusinessName when it's not.

To display the other fields' data on the form, have a textbox for (say)
MailingAddress1. Set its Control Source property to

=comboboxname.Column(2)

This will display the third column of the combo (it's zero based) - the
mailingaddress1 field.

If you already have a MailingAddress1 control on the form, bound to a
MailingAddress1 field in your table, and this is a different table than the
one upon which the combo box is based... *you're making a bad mistake* or else
need to explain why you would want to violate relational design principles by
storing the same information redundantly in two different table!
Click to expand...
 
Where do I find the record source and sql?
Click to expand...

Open the form in design view.
View its Properties (rightclick the little square at the upper left
intersection of the rulers and choose Properties, or use the View menu
option).
The Recordsource is the first row on the Data tab.
Click the ... icon by it; if Access asks if you want to open a query, do so.
Select View... SQL from the menu, or choose SQL from the leftmost dropdown in
the toolbar.
Copy and paste the SQL to a message here.

Did you try my suggestion? (you certainly didn't answer my questions).
 
SELECT *
FROM [TRIP TICKETS ISSUED TO];

John W. Vinson said:
Open the form in design view.
View its Properties (rightclick the little square at the upper left
intersection of the rulers and choose Properties, or use the View menu
option).
The Recordsource is the first row on the Data tab.
Click the ... icon by it; if Access asks if you want to open a query, do so.
Select View... SQL from the menu, or choose SQL from the leftmost dropdown in
the toolbar.
Copy and paste the SQL to a message here.

Did you try my suggestion? (you certainly didn't answer my questions).
Click to expand...
 
I would like to create a look up for a field in a form. I would like to put
in a customer number that will bring up their name and address info. How can
I do this? Thanks in advance for your assistance.
Click to expand...

A "Look Up" *IS* usually a combo box. (It doesn't have to be but that's most
convenient).

Use the Toolbox Wizard to put a combo box on your form. Choose the option "Use
this combo to find a record."
 
This is beyond my knowledge of access. Thanks to everyone for their
suggestions. I have located someone from a local college to assist me. So
again, thanks to everyone who tried to help.
 
Back
Top