How do I connect to a lookup table in another database?

[SteveP]

I am in the process of creating a database for my job where I need to access
a table in a seperate database already in use. Does anybody know how to go
about doing that? Any help would be appreciated.

 

[Klatuu]

Link to it like you would any other table. If it is not an mdb, you will
need to set up an ODBC connection to it.

 

[SteveP]

Thanks.

Link to it like you would any other table. If it is not an mdb, you will
need to set up an ODBC connection to it.

 

[SteveP]

I went ahead and imported the data I need into a query in the database I am
designing now. However, I have now run into a new problem. Following is the
code I am using to populate the second combo box from the selection in the
first combo box.

Dim strSQL As String
strSQL = "Select " & Me!cbxItem
strSQL = strSQL & " from Inventory06"
Me!cbxDescription.RowSourceType = "Table/Query"
Me!cbxDescription.RowSource = strSQL

My problem is that it is grabbing the column HEADS to populate the first
combo box with. What I am wanting is for it to grab the information from the
first column and grab the corresponding information from the second column
and populate the second combobox with that info.

I guess a slight bit of background info would be helpful. I have an Item #
field (first combo box) that I want people to be able to select the part
number and then the description will automatically appear in the second
combobox. And yes, there can be multiple descriptions for the same item #
based upon Rev level of the product.

 

[Klatuu]

I am not sure what you are trying to do, because this has nothing in common
with the original question; however, the code has some errors.

strSQL = "Select " & Me!cbxItem
As written, it is expecting your combo to contain a value that is the name
of a field in Inventory06

strSQL = strSQL & " from Inventory06"
'This part is okay, but there is no WHERE predicate, so the query is
unfiltered.

'Why not do this in design view?
Me!cbxDescription.RowSourceType = "Table/Query"

'The only reason not to do this in design view is if you want to filter the
combo based on a business rule
Me!cbxDescription.RowSource = strSQL

If you can explain what you are after, I think I can show you.

 

[SteveP]

This will be an Inspection History Database. The user will be able to pick
the part number. The part description will appear in the second combo box in
order of rev levels. When the desired rev is chosen, every instance of that
part number being recieved will show including Purchase Order #, receipt,
quantity, and any accompanying DMR #s for bad product recieved. From there,
the user will be able to choose Inspection Data sheets and/or First Article
Inspection Reports to give a full background of that product throughout its
history with our company.

The change in approaches came from wanting to see if I could get the product
into the database and work with it that way. I figured once I got it up and
running, I could worry about using the inventory management system we have as
a lookup table. I obviously don't have much experience with this, but my
boss has recently asked me to work on creating something useable.

And thank you for your input so far.

 

[Klatuu]

I don't understand why the part number description you just selected has to
show in the second combo. Are you wanting the user to be able to select by
description? or is it you want them to be able to select by either part
number or description?

 

[SteveP]

The part # combo would contain something like "123-00456-00". When that is
chosen, the description combo will populate with the part description
including all Revs for that part. This means that if a part is up to Rev F,
revs A,B,C,D,E, and F will show up in the combo box. When that is chosen,
I'm hoping for a second tab in the window to be populated with a list of
every PO#, Receipt #, date received, quantity received, and any DMRs for that
Rev. If I am looking at a certain dimension under one rev, I don't
necessarily care what that dimension was under a different rev. Will make
for a very in depth inspection history, but apparently they are going to this
company wide for tracebility and performance measures.

 

[Klatuu]

I am still confused, Steve. Why do you need a description combo? are there
more than one description for a part? If so, how is the description
associated to the part?

Are you saying the description is associated with the Rev?

--
Dave Hargis, Microsoft Access MVP

The part # combo would contain something like "123-00456-00". When that is
chosen, the description combo will populate with the part description
including all Revs for that part. This means that if a part is up to Rev F,
revs A,B,C,D,E, and F will show up in the combo box. When that is chosen,
I'm hoping for a second tab in the window to be populated with a list of
every PO#, Receipt #, date received, quantity received, and any DMRs for that
Rev. If I am looking at a certain dimension under one rev, I don't
necessarily care what that dimension was under a different rev. Will make
for a very in depth inspection history, but apparently they are going to this
company wide for tracebility and performance measures.

 

[SteveP]

The way our system is set up, the rev is the last part of the description
(ex. "PHILLIPS SCR DR #0 X 2" Rev A"..."PHILLIPS SCR DR #0 X 2" Rev B". No,
that is not an actual part of ours), so there will be more than one
description for every part. The only actual difference will be the Rev, but
it can be significant.

 

[Klatuu]

Excuse my poor old thick skull, but now it is starting to make sense.
Now I get what you are after. The tecnhique is called "cascading combos".
That is, basing the row source of one combo on the value in another. So if
you want to pick a part, then get the description from the table with the
revs in it, you would set your first combo up to return all the part numbers.
The second combo would then be set up to return the field(s) you need for
the Rev table, but only for the part in combo1, so the row source for combo2
has to be a query that filters on the content in combo1. Something like
"SELECT [REV_NO] FROM tblRevHistory WHERE [PART_NO] = '" & Me.Combo1 & "'"

Then to update the list for combo2, requery it in the After Update event of
combo1.

Me.Combo2.Requery

Is this what you are after?
--
Dave Hargis, Microsoft Access MVP

The way our system is set up, the rev is the last part of the description
(ex. "PHILLIPS SCR DR #0 X 2" Rev A"..."PHILLIPS SCR DR #0 X 2" Rev B". No,
that is not an actual part of ours), so there will be more than one
description for every part. The only actual difference will be the Rev, but
it can be significant.

 

[SteveP]

OK...I have that working now, but how do I make it so that if I decide to
choose another part number it does not automatically update the part number
for the displayed description? Example...I pick 123-00123-00 and it displays
the different revs for "Staples #2 pencil, soft Rev A, B, etc.", but then I
want to choose 124-00124-00 without it updating the part number for "Staples
#2 pensil, soft"? I do not want the combo boxes to actually change anything.
I just want to be able to view and edit the information associated with the
description.

And thanks for all of your help so far.

Excuse my poor old thick skull, but now it is starting to make sense.
Now I get what you are after. The tecnhique is called "cascading combos".
That is, basing the row source of one combo on the value in another. So if
you want to pick a part, then get the description from the table with the
revs in it, you would set your first combo up to return all the part numbers.
The second combo would then be set up to return the field(s) you need for
the Rev table, but only for the part in combo1, so the row source for combo2
has to be a query that filters on the content in combo1. Something like
"SELECT [REV_NO] FROM tblRevHistory WHERE [PART_NO] = '" & Me.Combo1 & "'"

Then to update the list for combo2, requery it in the After Update event of
combo1.

Me.Combo2.Requery

Is this what you are after?

 

[SteveP]

Ignore my last reply. I figured out where I was going wrong. Would help if
I followed instructions better. I put the "SELECT" statement in the correct
place this time. I do have another issue tho. When I click on the arrow
for combobox1, it gives me an error regarding not being able to find the
macro 'Me.' Is this an issue with Access 2000?

OK...I have that working now, but how do I make it so that if I decide to
choose another part number it does not automatically update the part number
for the displayed description? Example...I pick 123-00123-00 and it displays
the different revs for "Staples #2 pencil, soft Rev A, B, etc.", but then I
want to choose 124-00124-00 without it updating the part number for "Staples
#2 pensil, soft"? I do not want the combo boxes to actually change anything.
I just want to be able to view and edit the information associated with the
description.

And thanks for all of your help so far.

Excuse my poor old thick skull, but now it is starting to make sense.
Now I get what you are after. The tecnhique is called "cascading combos".
That is, basing the row source of one combo on the value in another. So if
you want to pick a part, then get the description from the table with the
revs in it, you would set your first combo up to return all the part numbers.
The second combo would then be set up to return the field(s) you need for
the Rev table, but only for the part in combo1, so the row source for combo2
has to be a query that filters on the content in combo1. Something like
"SELECT [REV_NO] FROM tblRevHistory WHERE [PART_NO] = '" & Me.Combo1 & "'"

Then to update the list for combo2, requery it in the After Update event of
combo1.

Me.Combo2.Requery

Is this what you are after?
--
Dave Hargis, Microsoft Access MVP

The way our system is set up, the rev is the last part of the description
(ex. "PHILLIPS SCR DR #0 X 2" Rev A"..."PHILLIPS SCR DR #0 X 2" Rev B". No,
that is not an actual part of ours), so there will be more than one
description for every part. The only actual difference will be the Rev, but
it can be significant.

:

I am still confused, Steve. Why do you need a description combo? are there
more than one description for a part? If so, how is the description
associated to the part?

Are you saying the description is associated with the Rev?

 

[Klatuu]

Sounds like the combo boxes are bound controls. They should not be. They
should be unbound and only used for lookups.
--
Dave Hargis, Microsoft Access MVP

OK...I have that working now, but how do I make it so that if I decide to
choose another part number it does not automatically update the part number
for the displayed description? Example...I pick 123-00123-00 and it displays
the different revs for "Staples #2 pencil, soft Rev A, B, etc.", but then I
want to choose 124-00124-00 without it updating the part number for "Staples
#2 pensil, soft"? I do not want the combo boxes to actually change anything.
I just want to be able to view and edit the information associated with the
description.

And thanks for all of your help so far.

Excuse my poor old thick skull, but now it is starting to make sense.
Now I get what you are after. The tecnhique is called "cascading combos".
That is, basing the row source of one combo on the value in another. So if
you want to pick a part, then get the description from the table with the
revs in it, you would set your first combo up to return all the part numbers.
The second combo would then be set up to return the field(s) you need for
the Rev table, but only for the part in combo1, so the row source for combo2
has to be a query that filters on the content in combo1. Something like
"SELECT [REV_NO] FROM tblRevHistory WHERE [PART_NO] = '" & Me.Combo1 & "'"

Then to update the list for combo2, requery it in the After Update event of
combo1.

Me.Combo2.Requery

Is this what you are after?
--
Dave Hargis, Microsoft Access MVP

The way our system is set up, the rev is the last part of the description
(ex. "PHILLIPS SCR DR #0 X 2" Rev A"..."PHILLIPS SCR DR #0 X 2" Rev B". No,
that is not an actual part of ours), so there will be more than one
description for every part. The only actual difference will be the Rev, but
it can be significant.

:

I am still confused, Steve. Why do you need a description combo? are there
more than one description for a part? If so, how is the description
associated to the part?

Are you saying the description is associated with the Rev?