Populate multiple records via form & come back to the form for remaining

[recyan]

Hi,
One of those many days occurring frequently lately, when I just can't seem to think straight
I have 3 tables :
tblClients

ClientID
A
B
C
D
E
F
G
H
I
J 

tblDiscounts

DiscountID    DiscountName
1    Off Season
2    Christmas
3    NewYear
4    Garage Sale 

tblRecords

Client   Date    DiscountID    DiscountPercent 

I have a form, in which I enter the Date, Discount Percent & select the Clients to whom it is applicable, via a checkbox(perhaps):
_______________ Start of Form _____________________________

Date 12/21/2012

DiscountID DiscountName DiscountPercent
1 Off Season 10
2 Christmas 0
3 NewYear 20
4 Garage Sale 0




ClientID SELECT
A Yes
B
C
D Yes
E
F Yes
G
H Yes
I Yes


J

___________ End of Form _____________________________

How do I make the 3rd table

tblRecords

Client    Date    DiscountID    DiscountPercent 

get populated like below (order of records not important)

Client    Date    DiscountID    DiscountPercent
A    12/21/2012    1    10
D    12/21/2012    1    10
F    12/21/2012    1    10
H    12/21/2012    1    10
I    12/21/2012    1    10
A    12/21/2012    3    20
D    12/21/2012    3    20
F    12/21/2012    3    20
H    12/21/2012    3    20
I    12/21/2012    3    20 

Then, I should be able to go back to refreshed form & the form should not have the Clients who have been already entered in tblRecords for the date 12/21/2012, to do entry for the remaining Clients.

Thanks

[June7]

Looks complicated. Maybe this will give you some ideas. The form needs a RecordSource that includes the clients table with a checkbox bound to Yes/No field and includes filter criteria that shows only those clients that don't have records in tblRecords for the given date. Not quite sure how the RecordSource can be built to allow that filtering, maybe with a subquery. Now is there 4 unbound textboxes for entering the discounts or a subform bound to tblDiscounts with a field for the percent? Have code in a loop to read each discount textbox (or record) and run SQL INSERT SELECT action if the discount is greater than 0. Refresh form.

[recyan]

Maybe this will give you some ideas. The form needs a RecordSource that includes the clients table with a checkbox bound to Yes/No field and includes filter criteria that shows only those clients that don't have records in tblRecords for the given date. Not quite sure how the RecordSource can be built to allow that filtering, maybe with a subquery. .....................Have code in a loop to read each discount textbox (or record) and run SQL INSERT SELECT action if the discount is greater than 0. Refresh form.

Thanks June,
Definitely gives me something to start off.

Now is there 4 unbound textboxes for entering the discounts or a subform bound to tblDiscounts with a field for the percent?

Yes, there are 4 unbound text boxes. Have not thought about sub-form so far. Mind you, all the tables and form are in my mind at present. Have not got down to physically trying things.

Will have a go when my smogged out brain clears a bit & keep you posted.

Thanks

[recyan]

Update :

Have created 2 MultipleItems forms :

frmDiscounts

displaying
DiscountID, DiscountName, DiscountPercent (Unbound Text Box)

frmClient

displaying
Client , Checkbox(Unbound)

frmClient is based on a query using a sub-query which gets all clients from tblClients who are not there in tblRecords for the date under consideration.

The above 2 forms, I have incorporated on a single form, frmRecords.

Now, this is the point where I am stuck.
How do I capture the values input on the form frmRecords & insert them in to the table tblRecords.

You know my VBA skills are not worth discussing.

Can we capture the DiscountID & the DiscountPercent in to an array & also the Clients selected from the form frmRecords in to an array.
Perhaps then, we could loop thro the Client array & insert each DiscountID, DiscountPercent in to the tblRecords along with each Client and the Date.

Thanks

[June7]

DiscountPercent and Checkbox have to be bound to fields. Otherwise the same value will display in all instances (all records) of the controls.

Use RecordsetClone to loop through the Discounts.

I always give subform container control a name different from the object it holds, like ctrDiscounts. Then code behind the main form in button click event, like:

With Me.ctrDiscounts.Form.RecordsetClone
While Not .EOF
If .DiscountPercent > 0 Then
CurrentDb.Execute "INSERT INTO tblRecords(ClientID, DiscountID, DiscountPercent, [Date]) SELECT ClientID, " & .DiscountID & " As DiscID, " & .DiscountPercent & " As DiscPct, #" & Me.tbxDate & "# As DiscDate FROM tblClients WHERE Checkbox=True"
End If
.MoveNext
Wend
End With
CurrentDb.Execute "UPDATE tblClients SET Checkbox = False"
CurrentDb.Execute "UPDATE tblDiscounts SET DiscountPercent=0"
Me.ctrClients.Requery
Me.ctrDiscounts.Requery

BTW, Date is a reserved word and should not use reserved words as names, hence the [] around fieldname.

[recyan]

Will have a go at what you have suggested.

BTW, Date is a reserved word and should not use reserved words as names, hence the [] around fieldname.

Not a problem, it's still all in my head. Will take care.

Thanks