recordset - setting foreign key

[pteare]

Hello,

I'm strugging with recordsets. I have two tables - a table of People, and a table of correspondence messages.

When I'm trying to do is go through each person and add a new correspondence message for each one:

        Dim db As Database
        Set db = CurrentDb()
               
        'get rs of all people to send to
        Dim rsPeopleUsingDB As Recordset
        Set rsPeopleUsingDB = db.OpenRecordset("SELECT tblPerson.* FROM tblPerson WHERE (((tblPerson.CurrentlyUsingDB)=True));")
        
        rsPeopleUsingDB.MoveFirst
        
        'get rs of correspondence
        Dim rsCorrespondence As Recordset
        Set rsCorrespondence = db.OpenRecordset("SELECT * from tblCorrespondence")
        
        
        'for each person
        Do While Not rsPeopleUsingDB.EOF
            'create correspondence record
            rsCorrespondence.AddNew
            
            rsCorrespondence!Subject = Me.txtSubject
            rsCorrespondence!Content = Me.txtEmailContents
            rsCorrespondence!DateSentReceived = Now
            rsCorrsepondence!Person = rsPeopleUsingDB!ID
                        
            rsCorrespondence.Update
                
        
            rsPeopleUsingDB.MoveNext
        Loop

That's my code so far. It should loop for each person, adding a new correspondence for each one.

My problem is with this line:

rsCorrsepondence!Person = rsPeopleUsingDB!ID

that gives me an "Object Required" Error.

The person field is the foreign key - it's an Integer. It's the foreign key so you know which person this correspondence belongs to.

Any ideas what I'm doing wrong, or how to set a foreign key in a recordset?

Thanks!

 

[pbaldy]

That looks okay offhand. Is "ID" the name of the field in the person table?

Since all you're doing is adding records to the correspondence table, I'd either open the recordset with the append only option or do this:

Set rsCorrespondence = db.OpenRecordset("SELECT * from tblCorrespondence WHERE 1=0")

so the recordset didn't pull all existing records in.

 

[pteare]

thanks for the reply. I've put in that where 1 = 0, good idea there thanks.

my other problem turned out to be a typo. so sorry to take your time with that. I'd spent ages working on it as well! When I was putting in the "where 1 = 0" bit the typo caught my eye.

thanks again for your help.

 

[pbaldy]

No problem, glad you got it sorted out. The way you were opening the recordset would work fine, until/unless there were a lot of records in that table. I know because I did it. :banghead: