Adding Multiple Records with a CheckBox

[puppygirl3939]

I have 4 tables tblLead, tblReferral, tblCountySent and lstCounty. One Referral can be sent to one or up to 67 counties.

Lead Table - LeadID,LeadDate,Source,Company Address
Referral Table - LeadID,ReferralID,DateSent,Statewide
CountySent - CtySentID,LeadID,ReferralID,CountyID
County - CountyID,CountyName (List Table that contains all 67 counties in my state)

The Main Form is a Tab Form. Tab1 has the fields from tblLead, Tab2 has the fields from tblReferral and a Subform in a datasheet format with the fields of the tblCountySent.

When the user clicks on the Statewide Checkbox in Tab2 which is a field from tblReferral I want the system to add all 67 counties in the CountySent Table and update the CountySent Subform. If they click on it again it must delete all 67 counties from the CountySent Table.

It's basically a ON/OFF switch. Can someone give me some clues?

 

[KevCon]

before I suggest a way for you to do this:
1)is your tblCountySent table linked (one-to-many relationship) to your tblReferral table?
2)if so, is the subform linked (master/child) as well?
3)how comfortable are you with vb/vba?

 

[puppygirl3939]

Several months ago I posted this thread and I still need help with it. The answer to the questions posted by KevCon are:

1)is your tblCountySent table linked (one-to-many relationship) to your tblReferral table? YES
2)if so, is the subform linked (master/child) as well? YES
3)How comfortable are you with vb/vba? SOMEWHAT WITH VB/VBA

 

[danvlas]

You don't need to add all records for StateWide referrals.


Query1 (gets all records for non-state-wide referrals):
SELECT Referral.LeadID, Referral.ReferralID, Referral.DateSent, County.CountyID, County.CountyName
FROM (Referral INNER JOIN CountySent ON Referral.LeadID = CountySent.LeadID) INNER JOIN County ON CountySent.CountyID = County.CountyID
WHERE (((Referral.StateWide)=0));


Query2 (gets a cartesian product between State-Wide referrals and Counties, which is obtained by adding two tables without any join):
SELECT Referral.LeadID, Referral.ReferralID, Referral.DateSent, County.CountyID, County.CountyName
FROM Referral, County
WHERE (((Referral.StateWide)<>0));


Query 3 (a Union between the 2 queries above):

Select * From Query1 Union Select * From Query2;


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html