New question about lookups

  • Thread starter Thread starter Don Sealer
  • Start date Start date
D

Don Sealer

I'm beginning to understand a little about lookups, but just a little.
Here's another question I'm wondering about.
I want to input a part# and then input a department code (G,D,B) and
depending on the department code have Access find the corresponding file to
lookup. In other words I could have a scenario like this
Part# Department# lookup File
123456 G C:\File1
123456 D C:\File2
123456 B C:\File3
So basically I have 3 differeent "lookup" files for each part# but which
file that pops up is determined by the Department code.
How would I make this happen?
Thanks for your help,
Don..............
 
You can use more than one criteria in a DLookup function. In this example,
the code will ask the user to input the desired values (using InputBox
function):

DLookup("lookup File", "Tablename", "[Part#]=" & _
InputBox("Enter part number:") & " And [Department#]='" & _
InputBox("Enter department number:") & "'")


The above assumes that Part# is a numeric field and Department# is a text
field. If Part# also is a text field:

DLookup("lookup File", "Tablename", "[Part#]='" & _
InputBox("Enter part number:") & "' And [Department#]='" & _
InputBox("Enter department number:") & "'")


In both cases, you can replace the InputBox function with a control
reference on the form.
 
Ken,
I've been struggling with this. I'm new at most of this and so I'm not
sure it's not working because I don't understand how to do what you tell me
or because I didn't explain what I want to do very well. In either case
please forgive me for not getting it. Hopefully I will eventually. Let me
try to explain what I'm trying to do a little better.
In my form I'd like to have people enter a part number, choose which
department/operation is appropriate and then have a hyperlink file pop up.
This file would have drawing information and work procedures in it. The
problem I'm stuck on is, each part number is unique, each part number is
associated with three departments/operations, and each department/operation
has it's own unique drawing for that part number. So how could I make a
form work in which I would input the part#, choose the dept/oper and have
the drawing for that part# and dept/oper pop up?
One side note: I already use the DLookup function to populate a field
called Part Desc. It works like this, when I input a Part# it looks up the
description for that Part# and populates the field called Part Desc.
Hope that doesn't confuse things,
Thanks for all your help,
Don...............
This is an short example of what the options would be
Part # Dept/Oper Drawing
135 B Dwg A
135 G Dwg B
135 D Dwg C
246 B Dwg D
246 G Dwg E
246 D Dwg F
103050 B Dwg G
103050 G Dwg H
103050 D Dwg I
204060 B Dwg J
204060 G Dwg K
204060 D Dwg L
etc etc etc
Ken Snell said:
You can use more than one criteria in a DLookup function. In this example,
the code will ask the user to input the desired values (using InputBox
function):

DLookup("lookup File", "Tablename", "[Part#]=" & _
InputBox("Enter part number:") & " And [Department#]='" & _
InputBox("Enter department number:") & "'")


The above assumes that Part# is a numeric field and Department# is a text
field. If Part# also is a text field:

DLookup("lookup File", "Tablename", "[Part#]='" & _
InputBox("Enter part number:") & "' And [Department#]='" & _
InputBox("Enter department number:") & "'")


In both cases, you can replace the InputBox function with a control
reference on the form.
--

Ken Snell
<MS ACCESS MVP>


Don Sealer said:
I'm beginning to understand a little about lookups, but just a little.
Here's another question I'm wondering about.
I want to input a part# and then input a department code (G,D,B) and
depending on the department code have Access find the corresponding file
to
lookup. In other words I could have a scenario like this
Part# Department# lookup File
123456 G C:\File1
123456 D C:\File2
123456 B C:\File3
So basically I have 3 differeent "lookup" files for each part# but which
file that pops up is determined by the Department code.
How would I make this happen?
Thanks for your help,
Don..............
Click to expand...
Click to expand...
 
What I'd suggest for your setup is to put two combo boxes and a command
button on your form.

The first combo box should display all the possible part numbers.

The second combo box should display the choices of departments for the part
number chosen in the first combo box. This can be done using programming
that is described in this article on The ACCESS Web:
http://www.mvps.org/access/forms/frm0028.htm

For the second combo box, I'd use a query that returns the department code
and the drawing name (the drawing name can be the second column in the
query). This will give your form immediate access to the drawing information
without the need for a (slower) DLookup function call later.

Then you can program the command button to do the part about the hyperlink.
I'm not sure if you want to actually show the image/drawing at that point,
or just to display the drawing name. If you can provide more info about
this, we can help.

Try to set up the first two combo boxes and the programming, and then post
back.

--

Ken Snell
<MS ACCESS MVP>

Don Sealer said:
Ken,
I've been struggling with this. I'm new at most of this and so I'm not
sure it's not working because I don't understand how to do what you tell
me
or because I didn't explain what I want to do very well. In either case
please forgive me for not getting it. Hopefully I will eventually. Let
me
try to explain what I'm trying to do a little better.
In my form I'd like to have people enter a part number, choose which
department/operation is appropriate and then have a hyperlink file pop up.
This file would have drawing information and work procedures in it. The
problem I'm stuck on is, each part number is unique, each part number is
associated with three departments/operations, and each
department/operation
has it's own unique drawing for that part number. So how could I make a
form work in which I would input the part#, choose the dept/oper and have
the drawing for that part# and dept/oper pop up?
One side note: I already use the DLookup function to populate a field
called Part Desc. It works like this, when I input a Part# it looks up
the
description for that Part# and populates the field called Part Desc.
Hope that doesn't confuse things,
Thanks for all your help,
Don...............
This is an short example of what the options would be
Part # Dept/Oper Drawing
135 B Dwg A
135 G Dwg B
135 D Dwg C
246 B Dwg D
246 G Dwg E
246 D Dwg F
103050 B Dwg G
103050 G Dwg H
103050 D Dwg I
204060 B Dwg J
204060 G Dwg K
204060 D Dwg L
etc etc etc
Ken Snell said:
You can use more than one criteria in a DLookup function. In this
example,
the code will ask the user to input the desired values (using InputBox
function):

DLookup("lookup File", "Tablename", "[Part#]=" & _
InputBox("Enter part number:") & " And [Department#]='" & _
InputBox("Enter department number:") & "'")


The above assumes that Part# is a numeric field and Department# is a text
field. If Part# also is a text field:

DLookup("lookup File", "Tablename", "[Part#]='" & _
InputBox("Enter part number:") & "' And [Department#]='" & _
InputBox("Enter department number:") & "'")


In both cases, you can replace the InputBox function with a control
reference on the form.
--

Ken Snell
<MS ACCESS MVP>


Don Sealer said:
I'm beginning to understand a little about lookups, but just a little.
Here's another question I'm wondering about.
I want to input a part# and then input a department code (G,D,B) and
depending on the department code have Access find the corresponding
file
to
lookup. In other words I could have a scenario like this
Part# Department# lookup File
123456 G C:\File1
123456 D C:\File2
123456 B C:\File3
So basically I have 3 differeent "lookup" files for each part# but
which
file that pops up is determined by the Department code.
How would I make this happen?
Thanks for your help,
Don..............
Click to expand...
Click to expand...
Click to expand...
 
Back
Top