DLOOKUP macro

  • Thread starter Thread starter Anita
  • Start date Start date
A

Anita

Hi

I know in theory how to use DLOOKUP but can't get it to
work.

I have a form with a combo box for department code. When
I select a code from the box I want it to complete the
field called Department. I have a table detup called
department which has 2 fields in it, one is code, the
other dept name. I want access to search for the code
and select the related dept name from this table. Any
help appreciated.

Thanks
 
Wouldn't that overwrite the department code in the combo
box though? I want to keep the dept code in the dept
code field but fill in the department field (which is a
separate field)based on the department code.

Thanks
 
Have you found a solution to this problem? I have been
trying to solve the same issue for three days now.
 
I have no DLOOKUP statement as of yet as I just learned
about the function DLOOKUP when reading through the
newsgroup. As I was searching the newsgroup to find an
answer to my problem, I located Anita's message which
referred to DLOOKUP.

My problem is similar - I have a form that I have created
that contains two combo boxes - product code and product
description. I would like to have the field "product
description" fill in automatically after the "product
code" has been selected from a drop-down list within
the "product code" combo box. The product code combo box
is working fine.

The product code combo box gets it's data from a table
that contains two columns - product code and product
description.

I want both the product code and the product description
to be displayed on the form.

I am familiar with VLOOKUP in Excel and use it quite
often. It seems that DLOOKUP is similar and is used in
Access, but I do not know how to use it. The help
function in Access offers no assistance. I am using
Access 2002.

Thanks so much!
 
DLookup
("[ProductDescription]", "yourtable", "[ProductCode] = " &
Me.comboProductCode & "")
this should Work. i copied out of one of my dbs and change
the wording for you
ta.
 
I have a form with a combo box for department code. When
I select a code from the box I want it to complete the
field called Department. I have a table detup called
department which has 2 fields in it, one is code, the
other dept name. I want access to search for the code
and select the related dept name from this table. Any
help appreciated.
Click to expand...

Anita, if you're storing the department Code in this table then you
*SHOULD NOT BE* storing the department name as well! It's redundant
and unnecessary.

To just *display* the department name on the form, set the control
source of a textbox to

=comboboxname.Column(1)

The Column property is 0 based so it will display the second column
(the department name).

John W. Vinson[MVP]
(no longer chatting for now)
 
Terri said:
The product code combo box gets it's data from a table
that contains two columns - product code and product
description.

I want both the product code and the product description
to be displayed on the form.
Click to expand...

Access is a relational database. There is no need for dLookUp.

If you are working in the table that contains both code and description, use
the option to go to the record you want when you build the combobox.

If not then the table containing the foreign key should be related to the
Product code and the description will fill in automatically.

NO code is needed to do this.
Help contains a *lot* of information about this and the Northwind database
has many examples of this.
 
You really only need one instance of the ID to propogate a display of all
the data in the table. If the Department Code is in the same table as as the
Department field and you need to see them both at the same time, all you
need to do is reference the combobox column in a textbox. Set the
controlsource of a textbox:

=Forms!MyForm!MyCombo.Column(1)

Which will display the second column of the combobox in the textbox. You do
not need to store this as it is always available.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
It looked easy to follow but something isn't right. i.e
I have a combo box showing dept code from a table
containing 2 fields. The first field is dept code the
second field is the dept name. The combo box works fine,
I changed the field properties as per your sheet.

When I do the text box on the form and change the control
source to =[dept code].[column](1) it shows up blank. If
I put =[dept code].[column](0) it shows the dept code (as
per the combo box). Why isn't it pulling through the
department name? I'm completely stumped!

Thanks for your help.
 
Did you set the column count for the first combo box to 2?

--

Ken Snell
<MS ACCESS MVP>

Anita said:
It looked easy to follow but something isn't right. i.e
I have a combo box showing dept code from a table
containing 2 fields. The first field is dept code the
second field is the dept name. The combo box works fine,
I changed the field properties as per your sheet.

When I do the text box on the form and change the control
source to =[dept code].[column](1) it shows up blank. If
I put =[dept code].[column](0) it shows the dept code (as
per the combo box). Why isn't it pulling through the
department name? I'm completely stumped!

Thanks for your help.
-----Original Message-----
See The ACCESS Web for a way to do this:
http://www.mvps.org/access/forms/frm0058.htm

--

Ken Snell
<MS ACCESS MVP>




.
Click to expand...
Click to expand...
 
Back
Top