Understanding recordsets strsql

[Ruegen]

I have

Dim db As dao.Database
Dim rs As dao.Recordset
Dim strsql As String


strsql = "SELECT tblTeacher.JoiningSchool, tblSchools.NewSchoolsID, tblSchools.SchoolName, tblSchools.[1ContactName], tblSchools.[1ContactSurname], tblSchools.SchoolAddress, tblSchools.SchoolPostalAddress, tblSchools.SchoolPostalSuburb, tblSchools.SchoolSuburb, tblSchools.SchoolPostCode, tblSchools.SchoolPhone, tblStates.SchoolState, tblSchools.MemoPerformerNotes, tblSchools.SchoolFax, tblSchools.SchoolEmail " & vbCrLf & _
"FROM (tblStates RIGHT JOIN (tblSchools RIGHT JOIN (tblJoiningSchoolStatus RIGHT JOIN tblTeacher ON tblJoiningSchoolStatus.JoiningSchoolStatusID = tblTeacher.JoiningSchoolStatusID) ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID) ON tblStates.StateID = tblSchools.StateID) RIGHT JOIN (tblStatus RIGHT JOIN ((tblShows RIGHT JOIN (tblBookings RIGHT JOIN tblJncTeacher ON tblBookings.BookingsID = tblJncTeacher.BookingsID) ON tblShows.ShowsID = tblBookings.ShowsID) LEFT JOIN (tblPerformers RIGHT JOIN tblJncShows ON tblPerformers.PerformersID = tblJncShows.PerformersID) ON tblShows.ShowsID = tblJncShows.ShowsID) ON tblStatus.StatusID = tblBookings.StatusID) ON tblTeacher.TeacherID = tblJncTeacher.TeacherID " & vbCrLf & _
"WHERE (((tblBookings.StatusID)<>5) AND (([tblBookings]![BookingDate]) Between [forms]![frmEndOfYearLetter]![txtFromDate] And [forms]![frmEndOfYearLetter]![txtUntilDate])) " & vbCrLf & _
"GROUP BY tblTeacher.JoiningSchool, tblSchools.NewSchoolsID, tblSchools.SchoolName, tblSchools.[1ContactName], tblSchools.[1ContactSurname], tblSchools.SchoolAddress, tblSchools.SchoolPostalAddress, tblSchools.SchoolPostalSuburb, tblSchools.SchoolSuburb, tblSchools.SchoolPostCode, tblSchools.SchoolPhone, tblStates.SchoolState, tblSchools.MemoPerformerNotes, tblSchools.SchoolFax, tblSchools.SchoolEmail " & vbCrLf & _
"ORDER BY tblTeacher.JoiningSchool DESC;"




Set db = CurrentDb()
Set rs = db.OpenRecordset(strsql)


rs.MoveFirst
Do Until rs.EOF
rs.Edit






rs.Update
rs.MoveNext
Loop
MsgBox "All schools have been emailed"
rs.Close
Set rs = Nothing

but I get an error.

I know that between rs.edit and update that there is no actions (I will add that later)

I don't know why I am getting an error so I was hoping someone can explain why and help me understand.

[June7]

Remove the vbCrLf's. Maybe they aren't the issue but they certainly are not necessary.

What is the error message?

Did you build the query with Access query builder first?

Need to concatenate variables. Reference to control on form is variable. Date parameters need # delimiter.

"WHERE tblBookings.StatusID<>5 AND [tblBookings]![BookingDate] Between #" & [forms]![frmEndOfYearLetter]![txtFromDate] & "# And #" & [forms]![frmEndOfYearLetter]![txtUntilDate] & _

[Ruegen]

Remove the vbCrLf's. Maybe they aren't the issue but they certainly are not necessary.

What is the error message?

Did you build the query with Access query builder first?



Need to concatenate variables. Reference to control on form is variable. Date parameters need # delimiter.

"WHERE tblBookings.StatusID<>5 AND [tblBookings]![BookingDate] Between #" & [forms]![frmEndOfYearLetter]![txtFromDate] & "# And #" & [forms]![frmEndOfYearLetter]![txtUntilDate] & _

this is my error



Yes I built from query first - it's working fine for the form to open a report.

I will update the date delimiter too

[Ruegen]

It points to this

[June7]

Oops! I missed the closing # for the end date:

"WHERE tblBookings.StatusID<>5 AND [tblBookings]![BookingDate] Between #" & [forms]![frmEndOfYearLetter]![txtFromDate] & "# And #" & [forms]![frmEndOfYearLetter]![txtUntilDate] & "#" & _

[Ruegen]

Oops! I missed the closing # for the end date:

"WHERE tblBookings.StatusID<>5 AND [tblBookings]![BookingDate] Between #" & [forms]![frmEndOfYearLetter]![txtFromDate] & "# And #" & [forms]![frmEndOfYearLetter]![txtUntilDate] & "#" & _

Is that all it is? my poor concatenation?

[June7]

That and the unnecessary vbCrLfs is all I can see.

Did you add the vbCrLf codes?

[Ruegen]

That and the unnecessary vbCrLfs is all I can see.

Did you add the vbCrLf codes?

no, to save time I made a form that converts sql - it adds the vbCrLf

I just paste it in and press convert.

[June7]

Okay, as I said, the vbCrLf probably not issue.

Just watch out for dynamic parameters when pasting from Access query to VBA.

[Ruegen]

Okay, as I said, the vbCrLf probably not issue.

Just watch out for dynamic parameters when pasting from Access query to VBA.

just realized it's a locked form (groupby) so no edit or update

Can you edit a table field if it has the same id?

say

if isnull(rs!SchoolEmail) then

find table with same id and edit field?

[Ruegen]

Also the trick I used to get

sendobject. acreport "frmReport" to send as pdf format to each individual ID was manipulating the criteria in the report's record source to refer to the current record ID [forms]![frmBookingsTermAheadEmails]![TeacherID]

however since I am using rs!

can you refer to a record source in the criteria of a query?

i.e. recordsource!rs!TeacherID

[Ruegen]

Also I am thinking if I make

dim storedID as string

then have the report record source change to strsql2 so

reports!rptYearLetter.recordsource = strsql2

storedID = rs!teacherid

and put say

strsql2 = the new recordsource but with storedID as criteria in the where

would that work?!

crosses fingers and hopes june says yes...

[June7]

Cannot refer to a recordset in a saved Access query object.

Cannot change the RecordSource of report if it is in Preview but can if it is in Report view.

[ItsMe]

Why do you have so many fields in your SQL? What happens if you build a select query that only has the fields you will be updating and a PK?

You could use a form's recordset clone and loop through it while nesting your update thing.

[Ruegen]

Why do you have so many fields in your SQL? What happens if you build a select query that only has the fields you will be updating and a PK?

The form filters those fields, lots of conditions - but I will look to making it smaller

You could use a form's recordset clone and loop through it while nesting your update thing.

I don't quite understand...