A new question with lookups

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..............

re: A new question with lookups

You need a table with the 3 fields you suggested: PartNo, DeptNo, and
LookupFile. For the LookupFile, you could us a Hyperlink type field. If
that's new, see:
Introduction to Hyperlink fields
at:
http://members.iinet.net.au/~allenbrowne/casu-09.html

In your data entry form, it's easy to choose the file that goes into the
hyperlink field, but right-clicking, using the Insert menu, or you could put
a command button beside the text box, with these 2 lines in its Click event
procedure:
Me.[LookupFile].SetFocus
RunCommand acCmdInsertHyperlink

If you want to programmatically fire up the hyperlink file, use
FollowHyperlink.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Don Sealer" <vze2t273@verizon.net> wrote in message
news:233yd.6243$152.2248@trndny01...[color=blue]
> 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..............[/color]

re: A new question with lookups

On Wed, 22 Dec 2004 00:18:06 GMT, "Don Sealer" <vze2t273@verizon.net>
wrote:
[color=blue]
>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..............
>
>l[/color]
Hi
part = 1234 'say
dept = "A" 'say

criterion = "[Part#]=" & part & " AND [Department#]='" & dept & "'"

lookupfile = Dlookup("[lookup File]", "yourtablename", criterion)

Hope no typos! Note in the "where clause" text items are put in single
quotes and numbers not.

David

re: A new question with lookups

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

"David Schofield" <d.REMOVEschofield@blueyonder.co.uk> wrote in message
news:41ca970c.615595980@localhost...[color=blue]
> On Wed, 22 Dec 2004 00:18:06 GMT, "Don Sealer" <vze2t273@verizon.net>
> wrote:
>[color=green]
> >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[/color][/color]
to[color=blue][color=green]
> >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..............
> >
> >l[/color]
> Hi
> part = 1234 'say
> dept = "A" 'say
>
> criterion = "[Part#]=" & part & " AND [Department#]='" & dept & "'"
>
> lookupfile = Dlookup("[lookup File]", "yourtablename", criterion)
>
> Hope no typos! Note in the "where clause" text items are put in single
> quotes and numbers not.
>
> David
>
>[/color]

re: A new question with lookups

I'm really pretty naive about these things. I posted this message to a newwgroup and when I went searching online I find it posted here. So with that in mind here's my next question on this. Although it will probably post here from the newsgroup anyway.


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

--
Message posted via http://www.accessmonster.com

re: A new question with lookups

On Fri, 24 Dec 2004 13:11:56 GMT, "Don Sealer" <vze2t273@verizon.net>
wrote:
[color=blue]
> 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
>
>"David Schofield" <d.REMOVEschofield@blueyonder.co.uk> wrote in message
>news:41ca970c.615595980@localhost...[color=green]
>> On Wed, 22 Dec 2004 00:18:06 GMT, "Don Sealer" <vze2t273@verizon.net>
>> wrote:
>>[color=darkred]
>> >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[/color][/color]
>to[color=green][color=darkred]
>> >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..............
>> >
>> >l[/color]
>> Hi
>> part = 1234 'say
>> dept = "A" 'say
>>
>> criterion = "[Part#]=" & part & " AND [Department#]='" & dept & "'"
>>
>> lookupfile = Dlookup("[lookup File]", "yourtablename", criterion)
>>
>> Hope no typos! Note in the "where clause" text items are put in single
>> quotes and numbers not.
>>
>> David
>>
>>[/color]
>[/color]
Hi
I think you should just have a table with columns

Part Dept Drawing Filename
135 B Dwg A aaaa
135 G Dwg B bbbb
135 D Dwg C cccc
246 B Dwg D dddd

Part and Dept form the key

then you have a form with combo boxes to select Part and Dept, and
show the corresponding other columns (and open the file).
That way you don't need dlookup at all.

It is a good general rule that you don't need Dlookup if the criteria
come from a query, as you could have added the field to be looked up
to the query via an extra join if necessary.

David