Very strange behaviour when opening report

[paulig2001]

When I open my report from the main form using the code:

Private Sub Command55_Click()

strIDRecSource = "SELECT tblEmployee.lngStaffIDPK, tblEmployee.strFirstName, tblEmployee.strSurname, tblEmployee.dtmDateBirth, tblEmployee.lngWorkCatagoryID, tblEmployee.lngContractorID, tblEmployee.strPhotoName, tblEmployee.strSigName, qryNTCPcourses.Expr1, tblEmployee.dtmExpiryDate, qryNTCPcourses.Expr2 FROM tblEmployee INNER JOIN qryNTCPcourses ON tblEmployee.lngStaffIDPK = qryNTCPcourses.lngStaffIDPK;"

DoCmd.OpenReport "rptIDCard", acPreview, , "lngStaffIDPK = " & Me.lngStaffIDPK

End Sub

my ID card report opens fine. But I want the user to be able to click a button on the main form, opening another window that allows them to select from the various reports available using option buttons, I use this code

If Me.optEmp = 1 And Me.optRpt = 1 Then

'opens the report on the current record if that option is checked

strIDRecSource = "SELECT tblEmployee.lngStaffIDPK, tblEmployee.strFirstName, tblEmployee.strSurname, tblEmployee.dtmDateBirth, tblEmployee.lngWorkCatagoryID, tblEmployee.lngContractorID, tblEmployee.strPhotoName, tblEmployee.strSigName, qryNTCPcourses.Expr1, tblEmployee.dtmExpiryDate, qryNTCPcourses.Expr2 FROM tblEmployee INNER JOIN qryNTCPcourses ON tblEmployee.lngStaffIDPK = qryNTCPcourses.lngStaffIDPK;"

DoCmd.OpenReport "rptIDCard", acPreview, , "lngStaffIDPK = " & Form_frmEmployeeInformation.lngStaffIDPK

...

etc.

To me this looks like it should do the same thing, but what seems to happen is that the report momenteraly flashes up on the screen (I'm not sure if it is properly generated cause its only there for a fraction of a second) and disappears again!

Any idea what the hell is going on here????????????????

 

[paulig2001]

Why are you referencing the form field this way - Form_frmEmployeeInformation.lngStaffIDPK? If you are referencing it from the form's own class module use Me.lngStaffIDPK as you did in the previous example. If the form isn't open, you can't reference controls at all!! They wouldn't contain a value.

PS - is there some point to the SQL string? It isn't needed in the code you posted.

Yo, I'm using Form_frmEmployeeInformation.lngStaffIDPK cause as I'm opening the report from a seperate window to the main form, as in a window that only shows you the option's for which report you want to open, I assumed you had to reference it that way. The main form is still open in the background...

The SQL string is there because for some options it needs to be changed...

I still haven't managed to work out what is going on here though!