Why do I get a compile error?

  • Thread starter Thread starter Steven V. Olson
  • Start date Start date
S

Steven V. Olson

When I execute the below code I get the following error:

Compile Error:

Method or Data Member Not Found.

The VBA editor opens and has the text .RXInputForm
highlighted in the subroutine. RXInputForm is the name of
a nested subform in my mdb file. This name is under the
Other tab of the subform.

Why am I getting a compile error when I run the below
subroutine?

Private Sub SelectLensOrSegmentStyle_Click()
On Error GoTo Err_SelectLensOrSegmentStyle_Click

Dim stDocName As String
Dim stLinkCriteria As String

Me.Repaint
Me.Requery
With Me.RXInputForm!Form.RecordsetClone
'Make sure there is at least one record.
'RX Input Form is the name of the subform under
'the Other tab of the of the Property Sheet of
'the subform when the main form is opened in
'design view.
If Not (.EOF And .BOF) Then
'Moves to last record on the subform.
.MoveLast
Me.RXInputForm!Form.Bookmark = .Bookmark
End If
End With

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

stDocName = "Clear Lens Or Segment Styles Form"

stLinkCriteria = "[Rx#]=" & Me![RX#]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Restore

Exit_SelectLensOrSegmentStyle_Click:
Exit Sub

Err_SelectLensOrSegmentStyle_Click:
MsgBox Err.Description
Resume Exit_SelectLensOrSegmentStyle_Click

End Sub

Steve
 
Steven V. Olson said:
When I execute the below code I get the following error:

Compile Error:

Method or Data Member Not Found.

The VBA editor opens and has the text .RXInputForm
highlighted in the subroutine. RXInputForm is the name of
a nested subform in my mdb file. This name is under the
Other tab of the subform.

Why am I getting a compile error when I run the below
subroutine?

Private Sub SelectLensOrSegmentStyle_Click()
On Error GoTo Err_SelectLensOrSegmentStyle_Click

Dim stDocName As String
Dim stLinkCriteria As String

Me.Repaint
Me.Requery
With Me.RXInputForm!Form.RecordsetClone
'Make sure there is at least one record.
'RX Input Form is the name of the subform under
'the Other tab of the of the Property Sheet of
'the subform when the main form is opened in
'design view.
If Not (.EOF And .BOF) Then
'Moves to last record on the subform.
.MoveLast
Me.RXInputForm!Form.Bookmark = .Bookmark
End If
End With

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

stDocName = "Clear Lens Or Segment Styles Form"

stLinkCriteria = "[Rx#]=" & Me![RX#]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Restore

Exit_SelectLensOrSegmentStyle_Click:
Exit Sub

Err_SelectLensOrSegmentStyle_Click:
MsgBox Err.Description
Resume Exit_SelectLensOrSegmentStyle_Click

End Sub

Steve
Click to expand...

Is this code running behind the main form that contains the
"RXInputForm" subform control?
 
Steven V. Olson said:
-----Original Message-----
Steven V. Olson said:
When I execute the below code I get the following error:

Compile Error:

Method or Data Member Not Found.

The VBA editor opens and has the text .RXInputForm
highlighted in the subroutine. RXInputForm is the name of
a nested subform in my mdb file. This name is under the
Other tab of the subform.

Why am I getting a compile error when I run the below
subroutine?

Private Sub SelectLensOrSegmentStyle_Click()
On Error GoTo Err_SelectLensOrSegmentStyle_Click

Dim stDocName As String
Dim stLinkCriteria As String

Me.Repaint
Me.Requery
With Me.RXInputForm!Form.RecordsetClone
'Make sure there is at least one record.
'RX Input Form is the name of the subform under
'the Other tab of the of the Property Sheet of
'the subform when the main form is opened in
'design view.
If Not (.EOF And .BOF) Then
'Moves to last record on the subform.
.MoveLast
Me.RXInputForm!Form.Bookmark = .Bookmark
End If
End With

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

stDocName = "Clear Lens Or Segment Styles Form"

stLinkCriteria = "[Rx#]=" & Me![RX#]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Restore

Exit_SelectLensOrSegmentStyle_Click:
Exit Sub

Err_SelectLensOrSegmentStyle_Click:
MsgBox Err.Description
Resume Exit_SelectLensOrSegmentStyle_Click

End Sub

Steve
Click to expand...

Is this code running behind the main form that contains the
"RXInputForm" subform control?
Click to expand...


No! This code is running behind a command button on the subform
RXInputForm.
Click to expand...

Then that's your problem. "Me" in the context of this subroutine
already refers to the subform, which thus doesn't have a subform named
"RXInputForm". All you need for the form references thus is "Me.",
instead of "Me.RXInputForm!Form.".

I'm curious as to what all this rigmarole with the Repaint, Requery, and
then moving to the last record is in aid of. And I don't know offhand
what

is doing, but there's usually a better way to do such things than
DoMenuItem. Would you be interested in explaining a bit?
 
-----Original Message-----
Steven V. Olson said:
-----Original Message-----
When I execute the below code I get the following error:

Compile Error:

Method or Data Member Not Found.

The VBA editor opens and has the text .RXInputForm
highlighted in the subroutine. RXInputForm is the name of
a nested subform in my mdb file. This name is under the
Other tab of the subform.

Why am I getting a compile error when I run the below
subroutine?

Private Sub SelectLensOrSegmentStyle_Click()
On Error GoTo Err_SelectLensOrSegmentStyle_Click

Dim stDocName As String
Dim stLinkCriteria As String

Me.Repaint
Me.Requery
With Me.RXInputForm!Form.RecordsetClone
'Make sure there is at least one record.
'RX Input Form is the name of the subform under
'the Other tab of the of the Property Sheet of
'the subform when the main form is opened in
'design view.
If Not (.EOF And .BOF) Then
'Moves to last record on the subform.
.MoveLast
Me.RXInputForm!Form.Bookmark = .Bookmark
End If
End With

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70

stDocName = "Clear Lens Or Segment Styles Form"

stLinkCriteria = "[Rx#]=" & Me![RX#]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Restore

Exit_SelectLensOrSegmentStyle_Click:
Exit Sub

Err_SelectLensOrSegmentStyle_Click:
MsgBox Err.Description
Resume Exit_SelectLensOrSegmentStyle_Click

End Sub

Steve

Is this code running behind the main form that contains the
"RXInputForm" subform control?
Click to expand...


No! This code is running behind a command button on the subform
RXInputForm.
Click to expand...

Then that's your problem. "Me" in the context of this subroutine
already refers to the subform, which thus doesn't have a subform named
"RXInputForm". All you need for the form references thus is "Me.",
instead of "Me.RXInputForm!Form.".

I'm curious as to what all this rigmarole with the Repaint, Requery, and
then moving to the last record is in aid of. And I don't know offhand
what

is doing, but there's usually a better way to do such things than
DoMenuItem. Would you be interested in explaining a bit?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
That was it! Thanks Dirk. I'd be happy to explain.DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
is an old wizard used by access to (I believe) repaint.
It's not a very efficient way to handle the problem, since
me.repaint would be much cleaner. I was having a previous
problem with corrupted form data during data entry. I.E.,I
was calling multiple command button forms to select
entries. After several of the forms were called the
entered data became corrupted, and resulted in starting
over on the data entry. I tried several fixes until I came
upon the requery option, which solved that problem. But
requerying the form resulted in reseting the recorset to
the first record, which is not where I wanted the bookmark
to be. Your help solved that problem. All the other stuff
including the DoCmnd will be gone now that the
RecordSetClone issue is resolved.

I realize that my naming syntax is not the best, however,
I'm still learning VBA on the fly while trying to run a
business in the interim. Another programmer was kind
enough to turn me on to the book Access 2000 Developer's
Handbook by Getz, Litwin and Gilbert. That has helped
immesurably in understanding recordsets. But, I realize my
programing skills could use some improvement.

Sincerely,

Steve
 
Steven V. Olson said:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
is an old wizard used by access to (I believe) repaint.
It's not a very efficient way to handle the problem, since
me.repaint would be much cleaner. I was having a previous
problem with corrupted form data during data entry. I.E.,I
was calling multiple command button forms to select
entries. After several of the forms were called the
entered data became corrupted, and resulted in starting
over on the data entry. I tried several fixes until I came
upon the requery option, which solved that problem. But
requerying the form resulted in reseting the recorset to
the first record, which is not where I wanted the bookmark
to be. Your help solved that problem. All the other stuff
including the DoCmnd will be gone now that the
RecordSetClone issue is resolved.
Click to expand...

Huh. I don't have the background on the original problem so I can't
address that, but I wonder if you tried Refresh before you tried Requery
to solve it? Like Requery, Refresh forces the current modified data to
be written and gets a new copy of all the records currently in the form,
but it doesn't bring in new records or -- more importantly for your
purposes -- move the current-record pointer. So *if* it would work to
prevent the original problem, Refresh would keep you from having to
relocate the record you were on.

I'd also point out that your current code always moves to the last
record on the form (subform in this case), which isn't guaranteed by any
of the code I've seen to be the record you were on before the requery.
Assuming you do have to requery the form, then if the form's
recordsource has a primary key, maybe it would be better to save that
key in a variable, then after the requery use RecordsetClone.FindFirst
to navigate to that record, rather than just automatically going to the
last record. This recommendation is based on the assumption that you're
trying to get back to the record you were on before the requery. Code
for the FindFirst would not be all that different from your current
code; it would look something like this:

Dim varID As Variant ' or whatever type is appropriate

With Me.RecordsetClone
.FindFirst "ID=" & varID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
I realize that my naming syntax is not the best, however,
I'm still learning VBA on the fly while trying to run a
business in the interim. Another programmer was kind
enough to turn me on to the book Access 2000 Developer's
Handbook by Getz, Litwin and Gilbert. That has helped
immesurably in understanding recordsets. But, I realize my
programing skills could use some improvement.
Click to expand...

The ADH is a great book. Keep going the way you're going; you're doing
fine.
 
Back
Top