Adding Records To a Table

[KBNETGUY]

Hey folks I have a Customer Table CC_Customer with an associated form page. After adding a record on the form page I would like to Auto Insert Records into another table called CC_CustomerDisc

CC_CustomerDisc table looks like this:

Customer_Number - This is the unique ID for the newly added customer
Disc_ID - This is the ID associated to a table called CC_DiscCat. This table contains discount that each customer is eligible to receive

When a New Customer is added I would like CC_CustomerDisc table to be auto populated for example

New Customer gets added. The Customer_Number = 100
Then loop through the various CC_DiscCat records (in this case there are about 17 categories) and insert the Customer_Number along with each Disc_ID as a separate record. Alternatively I could use a button on the new page that says Add Customer Discounts. HOWEVER to prevent from hitting the button multiple times, there needs to be code that checks to see if the Customer_Number already exists. If it does then maybe dont even display the button. Not sure

CC_CustomerDisc (table)
Customer_Number | Disc_ID
100 | 1
100 | 2
100 | 3
100 | 4
100 | 5
100 | 6
100 | 7
100 | 8
100 | 9
100 | 10
100 | 11
100 | 12


100 | 13
100 | 14
100 | 15
100 | 16
100 | 17

[pbaldy]

You can just execute an append query that gets categories from the table containing them and the customer from the form. You can test first with a DCount().

[KBNETGUY]

You can just execute an append query that gets categories from the table containing them and the customer from the form. You can test first with a DCount().

Ok BUT, how do we combined into one procedure:
EXECUTE Append Query and looping through it to also add the Customer_Number .

[pbaldy]

You don't loop through anything. The append query would refer to the form for the customer number. Like:

INSERT INTO TargetTable(CustomerField, DiscountField)
SELECT Forms!FormName.TextboxName, Disc_ID
FROM Tablename

[KBNETGUY]

Gotcha. That seams to work just fine. DO you know id there is a way to disable all the prompts. For example the first one is "Your About to run an append query..."

The next one "Your are about to append 17 records..."

Is there a way to now show these prompts ad then to refresh the current record to display the data in the sub-form for the parent record.

Thanks again!~

[pbaldy]

The first method:

http://baldyweb.com/SQLWarnings.htm

Execute won't work with the form reference.

[pbaldy]

Oh, and you can requery the subform.