TRANSFERRING KEY FIELD VALUE FROM FORM TO CODE 1

[WalksWithSky]

Hello, all:

I have created the following code attached to a command button on a form to print out a report in Word. The code works great (source is the FAQ703-760 by Telsa -- thanks heaps!) in that it sends everything to Word where it is supposed to go. The problem I am having is passing the ReportID value from the current record on the form to the code.

Here’s the code on the button now:

Private Sub Command65_Click()
Dim db As DAO.Database
Dim recReview As DAO.Recordset
Dim strSQL As String
strREPORTID = Me.ReportID

strSQL = "SELECT * FROM qryReports WHERE ReportID = 1 "
Set db = CurrentDb()
Set recReview = db.OpenRecordset(strSQL)

CreateReview recReview
End Sub

The only problem is the code is locked onto ReportID 1. The FAQ suggested this instead:

Dim db As DAO.Database
Dim recReview As DAO.Recordset
Dim strSQL As String
strREPORT = Me.ReportID

strSQL = "SELECT * FROM qryReports WHERE ReportID = ‘ " & strREPORT & ‘ " ;"
Set db = CurrentDb()
Set recReview = db.OpenRecordset(strSQL)

CreateReview recReview
End Sub

This is what I would like, for the code to grab the current ReportID, but when I tried it, the following error was produced:

Run-time Error ‘3464’
Data type mismatch in criteria expression

The error is occurring on this line of code:

strSQL = "SELECT * FROM qryReports WHERE ReportID = ‘ " & strREPORT &‘ ";"

ReportID is an AutoNumber field in the table. Does that
matter, or make a difference? As well, on the form, the ReportID text box is hidden. Does that matter?

Any help anyone can provide with this would be great. The rest of the code works fine when I lock in a ReportID value, but when I ask it to get the value from the form, then it halts at that line of code.

Thanks in advance (and thanks to Telsa for an awesome FAQ!)

Walks With Sky

 

[Darrylles]

Hiya,

Look at the ReportID field wherever it is defined in all forms and tables.

All ReportID fields should be defined Long Int(eger).

Seems that you have it defined otherwise (or maybe not at all) in one place.

Try defining strReportID e.g.

Dim strReportID as Long

(Although this should be lngReportId and maybe this is a pointer to your problem. The lng and str prefix doesn't matter at all, but it is meant to indicate what data type it holds. strReportID indicates string, lngReportID indicates Long Integer (although both can hold ANY type).
Main thing is to define it as LONG.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." AND "A pointer to a solution is better than the solution - you'll remember the solution via a pointer". darrylles@totalise.co.uk

 

[WalksWithSky]

Hey, Darrylle:

Thanks SO MUCH! I never even thought it was something as simple as defining the variable in the code. I guess Access VBA isn't that much different from Word or Excel VBA.

Again, thank you!

Walks With Sky