Query to show current record?

[aimee7]

So I have a form that when the "Save and Create new Record" button is clicked it will send an email of the current form and open up a new blank form.

What I have done so far is made a query of the form with all the controls, and I have copied my form as a report which uses the query as the record source. I'm using the following Do Cmd to send the report as an email when the button on the form is clicked.(the report basically looks exactly like the form).

DoCmd.SendObject acSendReport, "Cross Tee", acFormatPDF, "email@email.com", , , "Test", "testing", False

This works pretty great, however it sends all the records. So I have tried to put criteria on the query, under the Primary Key called RecordNumber, as follows: Forms![Cross Tee]![RecordNumber] Here's a screenshot below:


This just leaves my query to look like this and thus my report blank:


I did try to work around this and sort by Descending RecordNumber on the query and limiting it to 1 result. While this initially seemed to fix my issue, I realized when the button on the form is clicked it send the last record submitted and not the one the user is actually clicking the button on.

Any help I an get on this would be much appreciated! I'm not sure what I'm missing but I've come to no solution after working on it for days.



Thanks!!

[davegri]

It looks as though the new record is being created before the report is kicked off, thus the desired RecordNumber is no longer available on the form. You need to save the current RecordNumber before the new record is created. I would use a TempVar, and then have the query criteria look at the Tempvar. Alternatively, an unbound textbox on the form might work.

[aimee7]

Thanks for your response! And I see. I've never used Tempvar before... where is it created? In my form as a macro or vba I'm assuming, but on what event would you recommend? I was thinking under the form properties as on load perhaps; would that work?

And also, where in the query criteria would I reference the tempvar created? Under the Primary key, Autonumber?

[davegri]

Do you have a button to send the email?
If so, that button currently
1. sends the email
2. goes to a new record

Change that to
1. Save the current RecordNumber: Tempvars!tvRecNo = Me.RecordNumber.Value
2. Send the email (You need to modify the query to use Tempvars!tvRecNo as the criteria)
3. Go to new record

OR
1. Save the current RecordNumber (in a new textbox on the form named txtSaveRecNo; it can be invisible)
2. Send the email (You need to modify the query to use the above textbox: Forms![Cross Tee]!txtSaveRecNo
3. Go to new record

The unbound textbox will not be blanked out when going to a new record, as will the RecordNumber that you are currently using.
If this doesn't work, my assumptions are wrong and I'll probably need to see your DB to make informed comments.

[aimee7]

I think you've made the correct assumptions! I do have a button that sends the email and goes to new record. So I have now put the TempVars!tvRecNo = Me.recordNumber.value on the button. For the query, I have the criteria under RecordNumber as Tempcars!tvrecNo. However I am now receiving a blank report in an email, so I don't think the query is updating correctly still...

here's a picture of my code upon click of the button


And here 's the criteria I have on the query RecordNumber field

And there's a RecordNumber textbox on the form that is updating properly...

Thanks again for the help!!

[davegri]

I think it need re-arranging.
Save the tempvar
Save the record
send the email
goto newrec

And you can delete all that green comments junk the macro conversion included.
Also, remove both ON ERROR RESUME NEXT. Those statements will HIDE any error that occurs and make it impossible to find errors.

[aimee7]

Oh my gosh, that worked perfectly. Thank you so much for your help! I can't express how appreciative I am!

[davegri]

You're welcome. Good luck with the project.

[ssanfu]

Note that you SHOULD NOT use spaces in object names.
Nor should you use special characters or punctuation in object names (You have a field name of "MM/DD/YY" and "AM/PM".)