update query; want to also update the field that links the two tables

[MMcCarthy]

OK now having read through the thread again this is what I think is happening. You have data somewhere probably excel which contains the Notes data as in it is a record of all the notes so far. However, this information is not tied to the studentid in the database and therefore when you upload it there is no way to tie a note to a student.

If that is the case this takes us back to NeoPa's post no. 9. You have to have some way to relate the data you are uploading to the student or you just can't do what you want to do. As I see it your options are ...

• Add the studentid manually to each note record before uploading it or after.
• Find another field or fields which tie the records together like student name.

Otherwise you just can't do this.

[mjvm]

Thank you.

As I struggled to explain what I needed, I was starting to think it might not be possible.

Thank you MSquared, NeoPa and Adezii for your time and patience - and your skills!

[mjvm]

An alternative solution - would be to export the qryChase to excel including StudentID (from tblStudents), then add the notes information that I want, and import back to Access into tblNotes, renaming StudentID to NotesStudentID so that it provides the reference.
? Maybe

[MMcCarthy]

That should work but remember if you have more than one note for a student that is a separate record. You only need to add studentid to notes information.

[mjvm]

yep - I get that.

Thank you and till my next challenge...... .. I hope you all keep well!

[ADezii]

How about for every [StudentID] in qryChase, you programmaticall y Add a corresponding Record in tblNotes consisting of that [StudentID], then it is simply a matter of Updating tblNotes?

P.S. - tblNotes will now contain Records consisting of [NotesStudentID] for every [StudentID] in qryChase.

[mjvm]

That sounds like a good idea, but I don't know how to do that. Is it difficult or complicated to code?

Cheers,
Marcella

[ADezii]

The code assumes that there is only a single Record in qryChase relating to each Student:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("qryChase", dbOpenForwardOnly)

With rst
  Do While Not .EOF
    'Insert Only if StudentID does not already exist in tblNotes
    If DCount("*", "tblNotes", "[NoteStudentID] = " & ![StudentID]) = 0 Then
      CurrentDb.Execute "INSERT INTO tblNotes ([NoteStudentID]) VALUES (" & _
                         ![StudentID] & ")", dbFailOnError
    End If
      .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

[NeoPa]

As I struggled to explain what I needed, I was starting to think it might not be possible.

Sometimes (read nearly always) the biggest problem involved is the communication of the problem between member and experts.

Perhaps we should have a forum for problem expression (Only half joking). I'm saying you're certainly not alone in finding that difficult.

[ADezii]

I thought that it was only me that found this Thread difficult to pick apart.

[mjvm]

I am about to have a go at using the code - wish me luck!

I really thought that I was being as clear as I possibly could - written communication shouldn't be this hard!

:)

[ADezii]

You have to appreciate how difficult, as least for me, it is at times to understand a problem, then to come up with a viable solution at the other end of a Web Page.

[mjvm]

Of absolutely - I was completely aware that it was me not getting my message across and that I wasn't being clear or detailed enough for you guys.

What is difficult is not having the right words - the terminology - to ask the question. You don't get the answer you need if you don't - or can't - write the right question

[NeoPa]

On the other hand, though sometimes it's strained, I think we're all getting better at reading between the lines. I feel positively psychic sometimes.

Best results are always achieved when the question is expressed clearly though of course.

A technique I always use (Yes. I post technical questions too sometimes.) is to build up a logical structure. I avoid referring to anything in my explanation that is not already explained. Think of a pyramid. Very solid and stable. That's because everything that is not on the ground is on all the other stuff that's already solidly founded.

I always refer to items by the same name too. Never "... tblAccount ... The Accounts table ..." as this interferes with the reader's ability to catalogue their understanding internally.

[mjvm]

Oh my goodness! It worked.

Thank you so much for the code ADezii, and thank you to NeoPa, MSquared and ADezii for your patience and persistence in "unpicking the thread".

Regards,
marcella