NotInList

[Secret Squirrel]

Ok here's my dilema....I have 2 separate tables and I keep my customers in
one table and their part numbers in another. When my users enter a new order
if the part number isn't in the table then it asks them if they want to add
it. And the same goes for the customer as well. What I want to know is how
can I "link" the two files together so if someone was to run a query on the
customer table then it will list all the part numbers that belong to that
customer? Should I add a field in the part number table for the customer
name? Obviously the customer name can't be duplicated in the customer table
but it can be in the part number table. Is there an easy way to link these
two tables?

 

[Tom Wickerath]

Hi SS,

Ok here's my dilema....I have 2 separate tables and I keep my customers in
one table and their part numbers in another. When my users enter a new order

You should likely have at least 4 tables: one for Customer information, one
for Orders, one for OrderDetails, and one for Parts information. The Orders
table stores information about the order, such as ShipName, ShipAddress, and
Date Placed. The OrderDetails table stores detail information regarding each
item order (quantity, unitprice, etc.). Take a look at the relationships in
the Northwind sample database.

What I want to know is how can I "link" the two files together...

Do you mean link the two tables together?

Should I add a field in the part number table for the customer name?

Add a foreign key to an Orders table.

Obviously the customer name can't be duplicated in the customer table...

Not so obvious to me. What happens if you have two customers named John Smith?



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[fredg]

Ok here's my dilema....I have 2 separate tables and I keep my customers in
one table and their part numbers in another. When my users enter a new order
if the part number isn't in the table then it asks them if they want to add
it. And the same goes for the customer as well. What I want to know is how
can I "link" the two files together so if someone was to run a query on the
customer table then it will list all the part numbers that belong to that
customer? Should I add a field in the part number table for the customer
name? Obviously the customer name can't be duplicated in the customer table
but it can be in the part number table. Is there an easy way to link these
two tables?

Add a CustomerID field to the Part Number table (same datatype as the
CustomerID field in the Customer table) and set the relationship from
the Customer table CustomerID field to the CustomerID field in the
Parts table as one to many.

 

[Secret Squirrel]

Hi Tom,
I keep running into you on here! lol

Here's what I'm trying to do. I have a search form that allows users to
search for records using combo boxes. It them lists the record results in a
continuous subform. Then they can click on a specific record to open the
actual order form. The problem I'm having is that they want to be able to use
a combo box on the search form that lists all the part numbers in a table and
have it also show the customer who the part number belongs to. So basically
the combo box would have two columns in it. One with the part number and the
other with the corresponding customer. This isn't a customer order type
database. It's a customer return database. The part number is the customers
part number or blue print number. Right now I have a combo box on my search
form for the customer and another for the part number. When they pull down
the menu on the part number it just shows the part number and they have no
idea which customer it belongs to. So you see how I want to link the customer
name some how so when they pull down the menu they can see who the customer
is. I hope this makes sense.

SS

 

[Tom Wickerath]

Hi SS,

Is this your Grind Certs database? If so, try the following rowsource for
the combo box to find a record:

SELECT CertID, [CertID] & ": " & [BillToName] AS Customer FROM tblCustomers
INNER JOIN tblGrindAllCerts ON tblCustomers.CustomerID =
tblGrindAllCerts.fkCustomerID ORDER BY CertID, BillToName;

Set the column count to 2, and column widths to 0";2"



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Secret Squirrel]

No this is another database I've been working on for quite some time. How
would I incorporate what you said into this database? I'm not sure it's as
simple as you wrote. When someone selects a part number for the combo box and
then clicks a command button it lists all the records that have that
particular part number in it. I think it would be easier to store the
customer name in the part number table with the corresponding part number.
That's why I was thinking of the "NotInList" procedure that I currently use.
I was thinking every time someone enters a new part number then I want it to
add the customer from that record automatically into the part number table.

For example if someone was to create a new return for an existing customer
they would choose the customer from the combo box and then type in the part
number into the part number control. If the part number is not in the table
then it asks them to add it to the table. When they click "Ok" to add that
part number to the table I want it to take the customer from that record and
add that to the part number table as well. What do you think?

Hi SS,

Is this your Grind Certs database? If so, try the following rowsource for
the combo box to find a record:

SELECT CertID, [CertID] & ": " & [BillToName] AS Customer FROM tblCustomers
INNER JOIN tblGrindAllCerts ON tblCustomers.CustomerID =
tblGrindAllCerts.fkCustomerID ORDER BY CertID, BillToName;

Set the column count to 2, and column widths to 0";2"



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom,
I keep running into you on here! lol

Here's what I'm trying to do. I have a search form that allows users to
search for records using combo boxes. It them lists the record results in a
continuous subform. Then they can click on a specific record to open the
actual order form. The problem I'm having is that they want to be able to use
a combo box on the search form that lists all the part numbers in a table and
have it also show the customer who the part number belongs to. So basically
the combo box would have two columns in it. One with the part number and the
other with the corresponding customer. This isn't a customer order type
database. It's a customer return database. The part number is the customers
part number or blue print number. Right now I have a combo box on my search
form for the customer and another for the part number. When they pull down
the menu on the part number it just shows the part number and they have no
idea which customer it belongs to. So you see how I want to link the customer
name some how so when they pull down the menu they can see who the customer
is. I hope this makes sense.

SS

 

[Tom Wickerath]

No this is another database I've been working on for quite some time. How

would I incorporate what you said into this database?

Darn. Okay, try what I gave you on your Grind Cert database. Perhaps that
will give you some ideas on applying the technique to your current database.

I was thinking every time someone enters a new part number then I want it
to add the customer from that record automatically into the part number table.

Take a look at the Northwind Orders form. When one creates an order, the
customer that is selected is written into the Orders table (ShipName,
ShipAddress, ShipCity, etc. fields). This sounds pretty similar to what you
are asking for.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Secret Squirrel]

Thanks Tom, I will check things out in the Northwind database. I'll let you
know how I make out. It's late here so I'll try it out in the morning.

Thanks again!

 

[Secret Squirrel]

Hi Tom,
I took a look at the northwind db and I see what you mean but I don't think
that will work for me. The reason why is because when I add a new part number
to my part number table via the "Return Material Form" it only asks me to add
it to the table. it doesn't open another form for me to enter it. I'm using
the NotInList code from Allen Browne's web site. I don't want to open another
form. I would rather it just take the value that is in the customer field and
put it into the part number table for that particular part number.

 

[Secret Squirrel]

Well I think I found a solution. What I did was create another form that is
opened when the user wants to add a new part number. When they are prompted
to add it to the list via the NotInList event then it opens this form and
carries over the customer name from that form onto the add form. Then that
customer is stored in the table with the part number. It seems to work fine
and does what I need so I'll stick to this. What do you think Tom?

 

[Tom Wickerath]

What do you think Tom?

I dunno....it's kind of difficult for me to visualize the situation, without
having a copy of your database to examine. I would say test it out throughly.
If it seems to serve your needs, then go with it.

By the way, were you able to solve the problem from the other thread with
setting the backcolor of certain controls? I haven't noticed any new
postings to that thread.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Secret Squirrel]

Hi Tom,
It seems to work for what I need. The only problem would be if they change
the customer on the main form then it won't update the part number table but
the chances of that are slim to none and slim just left town.

As for the backcolor thing...I think I have that working as well. I forgot
to go back and respond on the other thread.

On to my next task......See ya around the board!

Thanks again Tom!

SS