automatically create new record in lookup table if data does not e

[Gillian]

I have a simple database of products and suppliers and a couple of small
lookup tables. The products and suppliers tables are linked in a
relationship using the supplier's Sage code and this field is also used as a
lookup from suppliers when adding data to products. When I set up new
products it is quite likely that there will be some new suppliers also. In
these cases I would like to be able to enter the new supplier Sage code in
the products table for the new product item and for the supplier table to
automatically create a new record with the new supplier Sage code. I would
then go and complete the new supplier table record at a later date. How do I
go about doing this?

 

[Arvin Meyer [MVP]]

Typically, you would use a combo box to select a supplier. These won't work
if you have the NotInList property set to Yes, so change it to no, and use
the code here, to open your supplier's form:

http://www.datastrat.com/Code/NotInListCode.txt

 

[Gillian]

Forgive my ignorance, I have only used Access for ODBC type queries in the
past and not created tables and forms for adding data and I am certainly not
a true programmer.
Do I start a new macro from within the Supplier code field in my products
table and then copy and paste in the entire text from the link you sent?
Gillian

 

[John W. Vinson/MVP]

I have a simple database of products and suppliers and a couple of small
lookup tables. The products and suppliers tables are linked in a
relationship using the supplier's Sage code and this field is also used as a
lookup from suppliers when adding data to products. When I set up new
products it is quite likely that there will be some new suppliers also. In
these cases I would like to be able to enter the new supplier Sage code in
the products table for the new product item and for the supplier table to
automatically create a new record with the new supplier Sage code. I would
then go and complete the new supplier table record at a later date. How do I
go about doing this?

Use a Form rather than trying to work in the very limiting environment
of table datasheets; and use the combo box's Not In List event to let
you add a new supplier as needed. See the online help for Not In List
or post back with more details about your form and table structure.

 

[Arvin Meyer [MVP]]

I've prepared a demo that you can use. You can even copy everything into
your database.

http://www.accessmvp.com/Arvin/NotInListDemo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Gillian]

Hi Arvin,
Thank you for the demo which I've just tried out this morning.
When I attempt to add a new customer to the events list there is a pop up
that tells me to choose from the existing list of customers. What I want to
do is add a new customer to the list and have that customer automatically
added to the customer list. How do I change the code to make it do that?
Gillian

 

[Gillian]

Hi John,
I should say at this stage that my only previous experience in Visual Basic
is modifying macros based on keystroke recording in Excel.
I have two tables: One is 'products'. This contains product ID, product
name, selling price, buying price, VAT rate, supplier code, supplier part no
and a few other fields. The other table is 'suppliers'. This contains the
supplier code (as referred to from 'products'), supplier name and then fields
for supplier contact details. I have created a form for products using all
the fields in the products table. The 'supplier code' field uses a combo box
with the list generated from the 'suppliers' table. When setting up new
products there will be occasions when the supplier is new and will not yet be
in the suppliers table. When setting up a new product and attempting to add
new supplier, I would like a message/pop-up (or the Access 2000 equivalent,
as that's what I'm using at work; I'm using 2007 at home) to say 'this
supplier does not exist, would you like to add it?' and if the answer is
'yes' then a new record to be created in the suppliers table with that single
field of data entered. I would then add the rest of the supplier data later.
Q: do I need to create a suppliers form for this to happen or can I get it to
write directly to the suppliers table?
I have tried using the Expression Builder, but I am fumbling in the dark, as
I don't know which bit to put in the 'On Not In List' and what to put
elsewhere.

 

[Arvin Meyer [MVP]]

No it doesn't. If you start typing a new name, a message box informs you
that the customer isn't in the list and asks if you want to add him. If you
say yes (the default) the customer form opens with the name already typed in
and allows you to fill in the additional details. Upon closing, you are back
at the Events form with the customer filled in.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Gillian]

Hi Arvin,
It did work, eventually. I have only recently installed Office 2007 and not
used Access 2007 much and didn't realise the implications of 'Security
Warning Certain content in the database has been disabled'. Allowing the
content solved the problem.
Is there any way to turn off this warning and always allow the content?
Gillian

 

[Arvin Meyer [MVP]]

In Access 2007, code does not run by default. That means that every
professional Access application, plus any serious app written by users won't
work in this environment. Fortunately, Microsoft created the Trusted
Location (available from the Office button on the upper left corner) to
allow apps to run without further prompting. In earlier versions (XP and
2003), setting the security prompts to Low and accepting the security
warning once, does the same thing.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com