Data Member not found

S

Siew-Ming

Hi,

I've stumbled on this for quite awhile. It'll be fantastic if someone can
help me on this.

Created a 3 column combo box on a form. If a user select a different
quarter from the combo box, then the value of the quarter will be passed to
the record source of a report. The record source will then select the query
that carries the appropriate quarter.

However, whenever I run the command button of the report on form, I get this
error .."Compile Error. Metod or Data member not found" on the last line
of the Report_Open.


Thanks,
Sming


Public grst As Recordset
Public Sub Report_rptLetter8th()
Dim frm As Form, ctl As String
Dim tblFirst As String


Set frm = Form!frmMainFormTest
Set rpt = Report!Report_rptLetter8th
Set ctl = frm!cbo27.Column(1)
Set tblFirst = "6qry all Permnum w totalpoints table"

Select Case ctl
Case 1
Set grst = CurrentDb.OpenRecordset( _
"SELECT [" & tblFirst & "].[PERMNUM.PERMNUM], [" & tblFirst &
"].[LASTNAME], [" & tblFirst & "].[FIRSTNAME], [" & tblFirst & "].[COURSE],
[" & tblFirst & "].[ABBREVNAME], [" & tblFirst & "].[GRADE], [" & tblFirst &
"].[PRNTGUARD], [" & tblFirst & "].[MAILADDR], [" & tblFirst & "].[CITY], ["
& tblFirst & "].[STATE], [" & tblFirst & "].[ZIPCODE], [" & tblFirst &
"].[Qtr1Mark] FROM [" & tblFirst & "] WHERE (([" & tblFirst &
"].[Qtr1Mark])=[forms]![frmMainFormTest]![text30])=""Qtr1Mark"")) OR ((([" &
tblFirst & "].[Qtr1Mark])=""D"" Or ([" & tblFirst & "].[Qtr1Mark])=""D-"" Or
([" & tblFirst & "].[Qtr1Mark])=""D+"" Or ([" & tblFirst &
"].[Qtr1Mark])=""F"")))")
rpt!rptMark = [" & tblFirst & "].[Qtr1Mark]
Case 3
Set grst = CurrentDb.OpenRecordset( _
"SELECT [" & tblFirst & "].[PERMNUM.PERMNUM], [" & tblFirst &
"].[LASTNAME], [" & tblFirst & "].[FIRSTNAME], [" & tblFirst & "].[COURSE],
[" & tblFirst & "].[ABBREVNAME], [" & tblFirst & "].[GRADE], [" & tblFirst &
"].[PRNTGUARD], [" & tblFirst & "].[MAILADDR], [" & tblFirst & "].[CITY], ["
& tblFirst & "].[STATE], [" & tblFirst & "].[ZIPCODE], [" & tblFirst &
"].[Qtr2Mark] FROM [" & tblFirst & "] WHERE (([" & tblFirst &
"].[Qtr2Mark])=[forms]![frmMainFormTest]![text30])=""Qtr2Mark"")) OR ((([" &
tblFirst & "].[Qtr2Mark])=""D"" Or ([" & tblFirst & "].[Qtr2Mark])=""D-"" Or
([" & tblFirst & "].[Qtr2Mark])=""D+"" Or ([" & tblFirst &
"].[Qtr2Mark])=""F"")))")
rpt!rptMark = [" & tblFirst & "].[Qtr2Mark]
Case 5
Set grst = CurrentDb.OpenRecordset( _
"SELECT [" & tblFirst & "].[PERMNUM.PERMNUM], [" & tblFirst &
"].[LASTNAME], [" & tblFirst & "].[FIRSTNAME], [" & tblFirst & "].[COURSE],
[" & tblFirst & "].[ABBREVNAME], [" & tblFirst & "].[GRADE], [" & tblFirst &
"].[PRNTGUARD], [" & tblFirst & "].[MAILADDR], [" & tblFirst & "].[CITY], ["
& tblFirst & "].[STATE], [" & tblFirst & "].[ZIPCODE], [" & tblFirst &
"].[Qtr3Mark] FROM [" & tblFirst & "] WHERE (([" & tblFirst &
"].[Qtr3Mark])=[forms]![frmMainFormTest]![text30])=""Qtr3Mark"")) OR ((([" &
tblFirst & "].[Qtr3Mark])=""D"" Or ([" & tblFirst & "].[Qtr3Mark])=""D-"" Or
([" & tblFirst & "].[Qtr3Mark])=""D+"" Or ([" & tblFirst &
"].[Qtr3Mark])=""F"")))")
rpt!rptMark = [" & tblFirst & "].[Qtr3Mark]
Case 7
Set grst = CurrentDb.OpenRecordset( _
"SELECT [" & tblFirst & "].[PERMNUM.PERMNUM], [" & tblFirst &
"].[LASTNAME], [" & tblFirst & "].[FIRSTNAME], [" & tblFirst & "].[COURSE],
[" & tblFirst & "].[ABBREVNAME], [" & tblFirst & "].[GRADE], [" & tblFirst &
"].[PRNTGUARD], [" & tblFirst & "].[MAILADDR], [" & tblFirst & "].[CITY], ["
& tblFirst & "].[STATE], [" & tblFirst & "].[ZIPCODE], [" & tblFirst &
"].[Qtr4Mark] FROM [" & tblFirst & "] WHERE (([" & tblFirst &
"].[Qtr4Mark])=[forms]![frmMainFormTest]![text30])=""Qtr4Mark"")) OR ((([" &
tblFirst & "].[Qtr4Mark])=""D"" Or ([" & tblFirst & "].[Qtr4Mark])=""D-"" Or
([" & tblFirst & "].[Qtr4Mark])=""D+"" Or ([" & tblFirst &
"].[Qtr4Mark])=""F"")))")
rpt!rptMark = [" & tblFirst & "].[Qtr4Mark]

DoCmd.OpenReport "Report_rptLetter8th", acViewPreview
grst.Close
Set grst = Nothing
End Sub
Public grst As Recordset

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = grst.Name
 
J

Jeanette Cunningham

Try grst instead of grst.Name


Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = grst

Jeanette Cunningham

Siew-Ming said:
Hi,

I've stumbled on this for quite awhile. It'll be fantastic if someone
can
help me on this.

Created a 3 column combo box on a form. If a user select a different
quarter from the combo box, then the value of the quarter will be passed
to
the record source of a report. The record source will then select the
query
that carries the appropriate quarter.

However, whenever I run the command button of the report on form, I get
this
error .."Compile Error. Metod or Data member not found" on the last
line
of the Report_Open.


Thanks,
Sming


Public grst As Recordset
Public Sub Report_rptLetter8th()
Dim frm As Form, ctl As String
Dim tblFirst As String


Set frm = Form!frmMainFormTest
Set rpt = Report!Report_rptLetter8th
Set ctl = frm!cbo27.Column(1)
Set tblFirst = "6qry all Permnum w totalpoints table"

Select Case ctl
Case 1
Set grst = CurrentDb.OpenRecordset( _
"SELECT [" & tblFirst & "].[PERMNUM.PERMNUM], [" & tblFirst &
"].[LASTNAME], [" & tblFirst & "].[FIRSTNAME], [" & tblFirst &
"].[COURSE],
[" & tblFirst & "].[ABBREVNAME], [" & tblFirst & "].[GRADE], [" & tblFirst
&
"].[PRNTGUARD], [" & tblFirst & "].[MAILADDR], [" & tblFirst & "].[CITY],
["
& tblFirst & "].[STATE], [" & tblFirst & "].[ZIPCODE], [" & tblFirst &
"].[Qtr1Mark] FROM [" & tblFirst & "] WHERE (([" & tblFirst &
"].[Qtr1Mark])=[forms]![frmMainFormTest]![text30])=""Qtr1Mark"")) OR (((["
&
tblFirst & "].[Qtr1Mark])=""D"" Or ([" & tblFirst & "].[Qtr1Mark])=""D-""
Or
([" & tblFirst & "].[Qtr1Mark])=""D+"" Or ([" & tblFirst &
"].[Qtr1Mark])=""F"")))")
rpt!rptMark = [" & tblFirst & "].[Qtr1Mark]
Case 3
Set grst = CurrentDb.OpenRecordset( _
"SELECT [" & tblFirst & "].[PERMNUM.PERMNUM], [" & tblFirst &
"].[LASTNAME], [" & tblFirst & "].[FIRSTNAME], [" & tblFirst &
"].[COURSE],
[" & tblFirst & "].[ABBREVNAME], [" & tblFirst & "].[GRADE], [" & tblFirst
&
"].[PRNTGUARD], [" & tblFirst & "].[MAILADDR], [" & tblFirst & "].[CITY],
["
& tblFirst & "].[STATE], [" & tblFirst & "].[ZIPCODE], [" & tblFirst &
"].[Qtr2Mark] FROM [" & tblFirst & "] WHERE (([" & tblFirst &
"].[Qtr2Mark])=[forms]![frmMainFormTest]![text30])=""Qtr2Mark"")) OR (((["
&
tblFirst & "].[Qtr2Mark])=""D"" Or ([" & tblFirst & "].[Qtr2Mark])=""D-""
Or
([" & tblFirst & "].[Qtr2Mark])=""D+"" Or ([" & tblFirst &
"].[Qtr2Mark])=""F"")))")
rpt!rptMark = [" & tblFirst & "].[Qtr2Mark]
Case 5
Set grst = CurrentDb.OpenRecordset( _
"SELECT [" & tblFirst & "].[PERMNUM.PERMNUM], [" & tblFirst &
"].[LASTNAME], [" & tblFirst & "].[FIRSTNAME], [" & tblFirst &
"].[COURSE],
[" & tblFirst & "].[ABBREVNAME], [" & tblFirst & "].[GRADE], [" & tblFirst
&
"].[PRNTGUARD], [" & tblFirst & "].[MAILADDR], [" & tblFirst & "].[CITY],
["
& tblFirst & "].[STATE], [" & tblFirst & "].[ZIPCODE], [" & tblFirst &
"].[Qtr3Mark] FROM [" & tblFirst & "] WHERE (([" & tblFirst &
"].[Qtr3Mark])=[forms]![frmMainFormTest]![text30])=""Qtr3Mark"")) OR (((["
&
tblFirst & "].[Qtr3Mark])=""D"" Or ([" & tblFirst & "].[Qtr3Mark])=""D-""
Or
([" & tblFirst & "].[Qtr3Mark])=""D+"" Or ([" & tblFirst &
"].[Qtr3Mark])=""F"")))")
rpt!rptMark = [" & tblFirst & "].[Qtr3Mark]
Case 7
Set grst = CurrentDb.OpenRecordset( _
"SELECT [" & tblFirst & "].[PERMNUM.PERMNUM], [" & tblFirst &
"].[LASTNAME], [" & tblFirst & "].[FIRSTNAME], [" & tblFirst &
"].[COURSE],
[" & tblFirst & "].[ABBREVNAME], [" & tblFirst & "].[GRADE], [" & tblFirst
&
"].[PRNTGUARD], [" & tblFirst & "].[MAILADDR], [" & tblFirst & "].[CITY],
["
& tblFirst & "].[STATE], [" & tblFirst & "].[ZIPCODE], [" & tblFirst &
"].[Qtr4Mark] FROM [" & tblFirst & "] WHERE (([" & tblFirst &
"].[Qtr4Mark])=[forms]![frmMainFormTest]![text30])=""Qtr4Mark"")) OR (((["
&
tblFirst & "].[Qtr4Mark])=""D"" Or ([" & tblFirst & "].[Qtr4Mark])=""D-""
Or
([" & tblFirst & "].[Qtr4Mark])=""D+"" Or ([" & tblFirst &
"].[Qtr4Mark])=""F"")))")
rpt!rptMark = [" & tblFirst & "].[Qtr4Mark]

DoCmd.OpenReport "Report_rptLetter8th", acViewPreview
grst.Close
Set grst = Nothing
End Sub
Public grst As Recordset

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = grst.Name
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Message box if record is not found 8
REPOST Re: Problem running the example in MS KB Article 304428 - How to Use the Query by Form (QBF) 4
Error # 3265 - Desc. Item Not Found in this collection ERROR 1

Top