Method or data member not found 2

[Fredgarner]

I have entered the following code in order to move to a record in my subform based on a selection in a combo box. The subform is called 'Shareholders' and the field name it is looking up is 'Account Designation'.


Private Sub Combo176_AfterUpdate()
'Moves Record Selector to record in subform that corresponds to the 'selection in this combo box.

Dim varName As Integer

varName = Me.Combo176.Column(0)

With Me.Shareholders.Form
.RecordsetClone.FindFirst "Account Designation = " & varName
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
End With

End Sub

When I try to carry out the action, it goes back to the code, with ".Shareholders" highlighted, and says "Method or data member not found".

 

[PHV]

Use the subform's control name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya Fredgarner . . . . .

I have great tendency to believe the error you received is premature. From what I see, the code would work if [blue]Account Designation[/blue] were enclosed in brackets [red][][/red] as prescribed by access for names including spaces. Also . . . since [blue]varName[/blue] is obviously text, you also need to include single quotes[red]''[/red]. This resolves the following modifications in [red]red[/red]:

[blue]    .RecordsetClone.FindFirst "[red][b][[/b][/red]Account Designation[red][b]][/b][/red] = [red][b]'[/b][/red]" & varName & "[red][b]'[/b][/red]"[/blue]

[purple]Give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .

 

[Fredgarner]

Hi AceMan! The same error is still showing up. The code in the line you mention probably was wrong and should be correct now I have altered it as you suggested, but I still need to get the shareholders line correct.

PH, I am not sure what you mean by the 'control name'? The subform's name is 'Shareholders'.

 

[Fredgarner]

i nowhave the following code and although no error message is coming up, nothing happens when I make a selection in the combo box.

Private Sub Combo176_AfterUpdate()
'Moves Record Selector to record in subform that corresponds to the 'selection in this combo box.

Dim varName As Integer

varName = Me.Combo176.Column(0)

With Me.[Shareholders subform].Form
.RecordsetClone.FindFirst "[Account Designation] = '" & varName & "'"
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
End With

End Sub

Miss Fred Garner

 

[TheAceMan1]

OK Fredgarner . . . . .

I can give you corrective code but I see other things (should've mentioned it in my prior post), and attempting to get you code working as it is.

[ol][li]Open the mainform in [blue]design view[/blue].[/li]
[li]To the left of the ruler, there's a small box. Double-Click it to [blue]call up the properties for the main form.[/blue][/li]
[li]Click the [blue]Other[/blue] tab . . . note the [blue]Name property[/blue] right at the top![/li]
[li]Now . . . click once anywhere on the subform and take note of the Name property. [purple]This is the name you should be using in the code![/purple][/li][/ol]
Make any necessary changes in the name and see if it works.

If it doesn't, in cases like this I alway go with full objectivity, as the code shows it'll have a problem across access platforms! Also be aware: the code shows [blue]combo176 is on the mainform.[/blue] With that, substitute the following if you still have problems:

[blue]Private Sub Combo176_AfterUpdate()
   Dim frm As Form, rst As DAO.Recordset, varName As Integer

   Set frm = Me![purple][b]subFormName[/b][/purple].Form
   Set rst = frm.RecordsetClone
   varName = Me.Combo176.Column(0)
   
   rst.FindFirst "[[purple][b]Account Designation[/b][/purple]] = '" & varName & "'"
   
   If Not rst.NoMatch Then
       frm.Bookmark = rst.Bookmark
   End If
   
   Set rst = Nothing
   Set frm = Nothing
   
End Sub[/blue]

Special Note: The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

[purple]Report any problems . . .[/purple]

See Ya! . . . . . .

 

[Fredgarner]

I have now substituted the following code but still the combo box is not doing anything! The code is obviously not faulty- why on earth is the combo box not finding my record? Any ideas would be gratefully received!

Private Sub Combo176_AfterUpdate()
'Moves Record Selector to record in subform that corresponds to the 'selection in this combo box.
Dim frm As Form, rst As DAO.Recordset, varName As Integer

Set frm = Me![Shareholders subform].Form
Set rst = frm.RecordsetClone
varName = Me.Combo176.Column(0)

rst.FindFirst "[Beneficiary] = '" & varName & "'"

If Not rst.NoMatch Then
frm.Bookmark = rst.Bookmark
End If

Set rst = Nothing
Set frm = Nothing

End Sub





Miss Fred Garner

 

[PHV]

Is MsgBox "'" & varName & "'" showing expected value ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

Fredgarner . . . . .

Enter [blue]PHV's[/blue] MsgBox line where you see it below:

[blue]   Set frm = Me![Shareholders subform].Form
   Set rst = frm.RecordsetClone
   varName = Me.Combo176.Column(0)
   [purple][b]MsgBox "'" & varName & "'"[/b][/purple][/blue]

I have a suspicion you may ant to refer to Column 1:

[blue]varName = Me.Combo176.Column([purple][b]1[/b][/purple])[/blue]

See Ya! . . . . . .