adding a new record by code

[YuXuan-ji]

I'm using a form to enter data in several fields, such as the category,
product name, price, & etc. Obviously the "category" field will have repeated
values. I want to have a table, say named "Category", to collect all the
different values of Category I entered. I'm thinking at the Before/or After
update event of the form, add the following code to check if the value
entered in the "Category" field already exist in the table.
lngCount = DCount("[Category_Name]", "Category", value_entered)
If lngCount =0, this means a new value has been entered into the "Category"
field of the form. So I want to add this new value into the table "Category"..
How do I do the code for adding a new record with SQL and embedded into the
VB? Or there might another way of doing this?

 

[Allen Browne]

If the value you type into the combo is actually the value stored (i.e. it
is not bound to a hidden column), you can use the combo's NotInList event to
add the value.

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

 

[YuXuan-ji]

Thanks a lot. That works! Now I have another question: How do I delete that
record from the lookup table automaticlly once I delete all the related
records from the data entry form?

 

[Allen Browne]

You cannot automatically delete in that direction.

You can use cascading deletes in the other direction, i.e. if you delete
from the lookup table, all records using that value are deleted as well. Or,
instead of losing the records, you can cascade them to Null:
http://allenbrowne.com/ser-64.html