INSERT INTO/SELECT with VB variable

[steve-oc]

Hi

This could be for several forums (queries, sql) but VB seems best...

What I have is a form (frmNewMailGroup) bound to table (tblMailingGroup) in which the user chooses to add a new record, names the new mail group and some other details and it is assigned an ID (MailGroupID). They then open a query (qryBuildNewGroup) to choose who is included in the new mail group - having built the list they save and close the query (back to frmNewMailgroup) and click a button to write into a junction table tblMailgroupMembers the following data - (DISTINCT) personID from the query and MailgroupID from the form .

The problem I have is incorporating the two different data sources - how do I incorporate the MailGroupID into
INSERT INTO tblMailGroupMembers(PersonID, MailgroupID) SELECT DISTINCT personID FROM qryBuildNewMailgroup...

I also tried including the MailGroupID as a field in the qryBuildMailGroup ([Forms]![frmNewMailGroup]![MailGroupID]) but it seems to read it as a null value. I currently have a VB variable intNewGroupID = [Forms]![frmNewMailGroup]![MailGroupID] which has the correct value but can't figure how to include this in the SQL.

Any suggestions???
Many thanks
Steve

 

[Adrianna]

Hmmm...WHere are you holding the PersonID fields for the people selected to be in the mail group. If you save their PersonIDs into a recordset then you can append to your tblMailGroupMembers by using the intNewGroupID as a constant variable while using the "rst.movenext" in your recordset to scroll through the PersonIDs to be added.

 

[steve-oc]

hi - thanks for the reply

i understand the idea of the recordset but any time i've seen it used it been to skip through the records, counting them or looking for one. so i'd do something like this, yes?:

Dim dbs As dao.database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.openrecordset("SELECT DISTINCT person_id FROM qryBuildMailGroup")

then i need to say:
INSERT INTO tblMailGroupMembers (person_ID, MailGroupID)
... each of the above personID's from the rst and also my constant intNewGroupID

this is the bit that i'm lost at... do i cycle through the records doing a INSERT INTO on each one?

thank you!

 

[Adrianna]

Steve,
Please check out the attached example...made just for you If you have any questions, just ask. I don't have all the time in the world on my hands, but I'll do my best to check back soon.

Adrianna

 

[steve-oc]

adrianna - thank you so much for this. i've had a quick look and think i understand what's happening but will try to pick it apart a bit more later. the rst method seems to offer a lot of functionality. thanks again for your time!
steve