Adding a new entry to a linked table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an order entry form that has a field for the customer name. The
customer name is kept in a separate table from the orders. I want to have my
users be able to select a name from a list and if it's not already in the
table then have it automatically add it using a macro. The field is set up as
a combo box right now so the users can pull down the list and see if it's
there, if it is then they select it, and if not then I want them to be able
to type it in and then a macro will automatically run to add it to the
customer table. Can this be done?
 
You can programmatically add the new name to the Customer table in the
NotInList event of the combo, but only if the combo's bound column is
visible (not if it is zero-width, so that the entry being typed is not the
key value.)

Details in this article:
NotInList: Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

If you need to enter more than one field, or the bound column is zero-width,
use the DblClick event of the combo to OpenForm so the user can complete the
new entry. Then in the AfterUpdate event of that form, Requery the combo,
e.g.:
Forms![Form1].[Combo1].Requery

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
I tried to use the instructions you pointed out but I am getting an error
code from the module. When I try and add a new entry I get this error from
the module:

Compile Error: User-defined type not defined

And then it points to this line in the module code:
Dim rst As DAO.Recordset

Allen Browne said:
You can programmatically add the new name to the Customer table in the
NotInList event of the combo, but only if the combo's bound column is
visible (not if it is zero-width, so that the entry being typed is not the
key value.)

Details in this article:
NotInList: Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

If you need to enter more than one field, or the bound column is zero-width,
use the DblClick event of the combo to OpenForm so the user can complete the
new entry. Then in the AfterUpdate event of that form, Requery the combo,
e.g.:
Forms![Form1].[Combo1].Requery

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I have an order entry form that has a field for the customer name. The
customer name is kept in a separate table from the orders. I want to have
my
users be able to select a name from a list and if it's not already in the
table then have it automatically add it using a macro. The field is set up
as
a combo box right now so the users can pull down the list and see if it's
there, if it is then they select it, and if not then I want them to be
able
to type it in and then a macro will automatically run to add it to the
customer table. Can this be done?
Click to expand...
Click to expand...
 
You must be using Access 2000 or 2002. By default, those two versions of
Access do not include a reference to the DAO library.

With any code library open, select Tools | References from the menu. Scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library. Select it (put a check beside it), then
back out.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Secret Squirrel said:
I tried to use the instructions you pointed out but I am getting an error
code from the module. When I try and add a new entry I get this error from
the module:

Compile Error: User-defined type not defined

And then it points to this line in the module code:
Dim rst As DAO.Recordset

Allen Browne said:
You can programmatically add the new name to the Customer table in the
NotInList event of the combo, but only if the combo's bound column is
visible (not if it is zero-width, so that the entry being typed is not the
key value.)

Details in this article:
NotInList: Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

If you need to enter more than one field, or the bound column is zero-width,
use the DblClick event of the combo to OpenForm so the user can complete the
new entry. Then in the AfterUpdate event of that form, Requery the combo,
e.g.:
Forms![Form1].[Combo1].Requery

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I have an order entry form that has a field for the customer name. The
customer name is kept in a separate table from the orders. I want to have
my
users be able to select a name from a list and if it's not already in the
table then have it automatically add it using a macro. The field is set up
as
a combo box right now so the users can pull down the list and see if it's
there, if it is then they select it, and if not then I want them to be
able
to type it in and then a macro will automatically run to add it to the
customer table. Can this be done?
Click to expand...
Click to expand...
Click to expand...
 
Is it possible to have 2 modules for the "NotInList" function? There are two
different fields that I need to use this function for and they are not the
same name. They are two totally different tables & forms. How would I specify
the modules so they don't conflict?

Allen Browne said:
You can programmatically add the new name to the Customer table in the
NotInList event of the combo, but only if the combo's bound column is
visible (not if it is zero-width, so that the entry being typed is not the
key value.)

Details in this article:
NotInList: Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

If you need to enter more than one field, or the bound column is zero-width,
use the DblClick event of the combo to OpenForm so the user can complete the
new entry. Then in the AfterUpdate event of that form, Requery the combo,
e.g.:
Forms![Form1].[Combo1].Requery

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I have an order entry form that has a field for the customer name. The
customer name is kept in a separate table from the orders. I want to have
my
users be able to select a name from a list and if it's not already in the
table then have it automatically add it using a macro. The field is set up
as
a combo box right now so the users can pull down the list and see if it's
there, if it is then they select it, and if not then I want them to be
able
to type it in and then a macro will automatically run to add it to the
customer table. Can this be done?
Click to expand...
Click to expand...
 
The code example assumes that the combo's RowSource is the name of the
lookup table, and the combo's Name is the same as the name of the foreign
key field.

If yours matches those assumptions, you can use the code as is. If not, you
can modify it (e.g. to pass in arguments for the table and field names), or
create a similar function with a different name if you prefer.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Is it possible to have 2 modules for the "NotInList" function? There are
two
different fields that I need to use this function for and they are not the
same name. They are two totally different tables & forms. How would I
specify
the modules so they don't conflict?

Allen Browne said:
You can programmatically add the new name to the Customer table in the
NotInList event of the combo, but only if the combo's bound column is
visible (not if it is zero-width, so that the entry being typed is not
the
key value.)

Details in this article:
NotInList: Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

If you need to enter more than one field, or the bound column is
zero-width,
use the DblClick event of the combo to OpenForm so the user can complete
the
new entry. Then in the AfterUpdate event of that form, Requery the combo,
e.g.:
Forms![Form1].[Combo1].Requery

message
I have an order entry form that has a field for the customer name. The
customer name is kept in a separate table from the orders. I want to
have
my
users be able to select a name from a list and if it's not already in
the
table then have it automatically add it using a macro. The field is set
up
as
a combo box right now so the users can pull down the list and see if
it's
there, if it is then they select it, and if not then I want them to be
able
to type it in and then a macro will automatically run to add it to the
customer table. Can this be done?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top