add info to tablelookup?

[Guest]

good day all,

I have a form that I use for order entry, and the user selects the product
from a lookup table to my products table. What I want to be able to do, is
have the user have the opportunity to add information to that table with new
product information, Product Name, Size, Colours, etc.

How do I go about doing this?

Thanks,

Brook

 

[tina]

the user selects the product from a lookup table to my products table

do you mean you have a *combo box control* in your form, with the RowSource
property set to your Products table?

and, btw, do you have an actual Lookup field in your Orders table? if so,
you need to change it to an "ordinary" field. see
http://www.mvps.org/access/lookupfields.htm
for reasons why.

 

[Guest]

Good Day Tina,

Thanks for the information and the article link, the problem I have is
that the table that I am using for a lookup has bout 40-50 records and is
used in 2 different forms. I don't want to have to set up the drop down for
each form.

Any ideas?

Thanks,

Brook

 

[tina]

yes - do it the standard way that every experienced developer does it:
create a combo box in each form, not a Lookup field in the table. if i need
a combo box control for the same table field in 10 forms, then i build 10
combo boxes - or i copy the combo box control from one form to another. what
i don't ever do is use a Lookup field in a table.

to answer your original question re allowing the user to add a product to
the Products table "on the fly" during data entry in the Orders form, here's
one solution:

create a separate form bound to the Products table, for data entry - i'll
call it frmProducts.
in frmProducts, add the following code to the form's Load event, as

Me!ProductName = Me.OpenArgs

change ProductName to the name of whatever field in tblProducts stores each
product's name.

back in the Orders form, add the following code to the combo box's NotInList
event, as

If MsgBox("Do you want to add a new product to the list?", _
vbDefaultButton1 + vbYesNo) = vbYes Then
DoCmd.OpenForm "frmProducts", , , , _
acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!Combo0 = Null
Me!Combo0.Dropdown
End If

change Combo0 to the name of your combo box.

hth