docmd.openrecord

[TxRedRaider]

This is driving me insane. I have a batch of code to open up a report. I want that report to be based on a query. ive done this multiple other times in this particular database, but for some reason when i run this particular batch of code it pops up with a box that asking you to put in a value for personaldata.platoon:

this is the busted code:

Private Sub Command3_Click()
Dim strSection As String
Dim edb As DAO.Database
Dim eqdf As DAO.QueryDef
Dim mysql As String


Set edb = CurrentDb
Set eqdf = edb.QueryDefs("NCOERDueDates")
Me.RecordSource = "NCOERDueDates"

Combo1.SetFocus
strSection = Combo1.text

mysql = "SELECT personaldata.*" & _
"FROM personaldata " & _
"WHERE PersonalData.Platoon = '" & strSection & "' ;"

eqdf.SQL = mysql

Set qdf = Nothing
Set db = Nothing
DoCmd.OpenReport "NCOER Due Dates all", acViewReport, "NCOERDueDates", , acDialog
DoCmd.Close acForm, "frmNCOERDueDateQuery"
End Sub

and here is an example of code that works:

Private Sub Command3_Click()
Dim strSection As String
Dim edb As DAO.Database
Dim eqdf As DAO.QueryDef
Dim mysql As String


Set edb = CurrentDb
Set eqdf = edb.QueryDefs("BarracksFilter")
Me.RecordSource = "BarracksFilter"

Combo1.SetFocus
strSection = Combo1.text

mysql = "SELECT personaldata.*" & _
"FROM personaldata " & _
"WHERE personaldata.platoon = '" & strSection & "' AND personaldata.barracks = TRUE;"
eqdf.SQL = mysql

Set qdf = Nothing
Set db = Nothing
DoCmd.OpenReport "Barracks Report All", acViewReport, "BarracksFilter", , acDialog
DoCmd.Close acForm, "frmBarracksQuery"
End Sub



any ideas?

 

[lman]

first guess is that platoon is not a field in the personaldata table, if it IS a field then check your spelling of the field, might be a typo.

 

[jdraw]

You need a space at the end of first line

mysql = "SELECT personaldata.[COLOR="Red"]*" [/COLOR]& _
"FROM personaldata " & _

 

[TxRedRaider]

I tried both suggestions with no luck. All of the variable and field names are spelled correctly and the "space" in the syntax made no difference. I think im going to throw my laptop out the window to see if that helps....

 

[TxRedRaider]

The actual query and the sql are accurate. It changes the query based on the input, however the issue is with the docmd.openreport command. thats where the debugger stops. The sql works for the query, however when i try to open the report based on the query, thats when the issue arises of an undefined variable.

 

[boblarson]

I would suggest to not use the filter of the DoCmd.OpenReport part. Use the WHERE CONDITION instead.

DoCmd.OpenReport "NCOER Due Dates all", acViewReport, "NCOERDueDates", , acDialog

So something like this:


DoCmd.OpenReport "NCOER Due Dates all", acViewReport, WhereCondition:="[FieldNameHere]=" & Me.SomeControl & " AND [SomeOtherField] = '" & Me.SomeOtherControl & "'", acDialog


I've seen too many issues with people trying to use the filter argument.