Qualifier: I am VERY NEW at VBA programming! And I am basically teaching it 

to myself using the book "Beginning Access 2000 VBA" (great book by the 

way!). Having said that, here's my problem:



I'm using code I've used before in other databases, changing the field 

names as needed. I've got a form that gathers info on a person & stores it 

in a table. I'm trying to set an unbound field on the form that allows the 

user to look for a particular record. It goes like this (MemberID is the 

primary key):



Private Sub MemberSearch_AfterUpdate()

Dim rs as Object



Set rs = Me.Recordset.Clone

rs.FindFirst "[MemberID] = '" & Me![MemberSearch] & "'"

Me.Bookmark = rs.Bookmark



End Sub



Then, to set the current record:



Private Sub Form_Current()

[MemberSearch] = [MemberID]

End Sub



When I select a Member from the dropdown list, I get Run Time error 3021 

"No Current Record". When I go to Debug - it's highlighted the Me.Bookmark 

= rs.Bookmark line.



HELP!!



Thanks to any & all.



+Tammy

Is MemberID a numeric?  If so, you'll want to drop the single-quotes that

get put around the value of MemberSearch on your call to .FindFirst.  Also,

if you want to make the code a little safer, try checking to see whether the

FindFirst found a match with code like so:



    If rs.NoMatch Then

      MsgBox "Could not find a member with ID = " & MemberSearch

    Else

      Me.Bookmark = rs.Bookmark

    End If



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Tammy Tappan [mailto:GRTappan@e...]

Sent: Saturday, November 17, 2001 7:06 PM

To: Access

Subject: [access] Run Time Error 3021: No Current Record





Qualifier: I am VERY NEW at VBA programming! And I am basically teaching it 

to myself using the book "Beginning Access 2000 VBA" (great book by the 

way!). Having said that, here's my problem:



I'm using code I've used before in other databases, changing the field 

names as needed. I've got a form that gathers info on a person & stores it 

in a table. I'm trying to set an unbound field on the form that allows the 

user to look for a particular record. It goes like this (MemberID is the 

primary key):



Private Sub MemberSearch_AfterUpdate()

Dim rs as Object



Set rs = Me.Recordset.Clone

rs.FindFirst "[MemberID] = '" & Me![MemberSearch] & "'"

Me.Bookmark = rs.Bookmark



End Sub



Then, to set the current record:



Private Sub Form_Current()

[MemberSearch] = [MemberID]

End Sub



When I select a Member from the dropdown list, I get Run Time error 3021 

"No Current Record". When I go to Debug - it's highlighted the Me.Bookmark 

= rs.Bookmark line.



HELP!!



Thanks to any & all.



+Tammy

Tammy,



Change your statements to



Dim rs as recordset

Set rst = Me.RecordsetClone

rs.FindFirst "[MemberID] = '" & Me!MemberSearch & "'"

If rst.NoMatch Then

	MsgBox "No Record Found for " & MemberSearch

Else

	Me.Bookmark = rst.Bookmark

End If



John Ruff - The Eternal Optimist :-)







-----Original Message-----

From: Tammy Tappan [mailto:GRTappan@e...] 

Sent: Sunday, November 18, 2001 3:06 AM

To: Access

Subject: [access] Run Time Error 3021: No Current Record





Qualifier: I am VERY NEW at VBA programming! And I am basically teaching

it 

to myself using the book "Beginning Access 2000 VBA" (great book by the 

way!). Having said that, here's my problem:



I'm using code I've used before in other databases, changing the field 

names as needed. I've got a form that gathers info on a person & stores

it 

in a table. I'm trying to set an unbound field on the form that allows

the 

user to look for a particular record. It goes like this (MemberID is the



primary key):



Private Sub MemberSearch_AfterUpdate()

Dim rs as Object



Set rs = Me.Recordset.Clone

rs.FindFirst "[MemberID] = '" & Me![MemberSearch] & "'" Me.Bookmark 

rs.Bookmark



End Sub



Then, to set the current record:



Private Sub Form_Current()

[MemberSearch] = [MemberID]

End Sub



When I select a Member from the dropdown list, I get Run Time error 3021



"No Current Record". When I go to Debug - it's highlighted the

Me.Bookmark 

= rs.Bookmark line.



HELP!!



Thanks to any & all.



+Tammy



---

You are currently subscribed to access as: papparuff@c... To

unsubscribe send a blank email to $subst('Email.Unsub')

Tammy,



Why not use the forms Filter properties? 



In the Form Header add a combobox and a button.  When you create the

combobox use the wizard to capture the MemberID and MemberNames from

your Members Table.  If you don't want the user to see the MemberID,

then for the Column Widths use 0;1.  Call the combobox cboSearch and cut

and past the following code:



Private Sub cboSearch_AfterUpdate()

On Error GoTo Err_cboSearch_AfterUpdate



    ' Turn filtering on    

    Me.FilterOn = True

    ' Search for Member

    Me.Filter = "MemberID = " & cboSearch

    ' Set focus to FName control on the form 

    ' (or whatever control you want to have the focus)

    FName.SetFocus

    

Exit_cboSearch_AfterUpdate:

    Exit Sub



Err_cboSearch_AfterUpdate:

    MsgBox Err.Description

    Resume Exit_cboSearch_AfterUpdate

End Sub



(Make sure you select [Procedure] for the cboSearch AfterUpdate

property)



Name the commandbutton cmdViewAll and cut and past the following code:



Private Sub cmdViewAll_Click()

On Error GoTo Err_cmdViewAll_Click



    ' Displays all the records

    DoCmd.ShowAllRecords

    ' Remove the search name from the cboSearch control

    cboSearch = ""

    ' Set focus to FName control on the form 

    ' (or whatever control you want to have the focus) 

   FName.SetFocus

    

Exit_cmdViewAll_Click:

    Exit Sub



Err_cmdViewAll_Click:

    MsgBox Err.Description

    Resume Exit_cmdViewAll_Click

    

End Sub



(Make sure you select [Procedure] for the cmdViewAll OnClick property)



I hope this helps,



John Ruff - The Eternal Optimist :-)







-----Original Message-----

From: Tammy Tappan [mailto:GRTappan@e...] 

Sent: Sunday, November 18, 2001 3:06 AM

To: Access

Subject: [access] Run Time Error 3021: No Current Record





Qualifier: I am VERY NEW at VBA programming! And I am basically teaching

it 

to myself using the book "Beginning Access 2000 VBA" (great book by the 

way!). Having said that, here's my problem:



I'm using code I've used before in other databases, changing the field 

names as needed. I've got a form that gathers info on a person & stores

it 

in a table. I'm trying to set an unbound field on the form that allows

the 

user to look for a particular record. It goes like this (MemberID is the



primary key):



Private Sub MemberSearch_AfterUpdate()

Dim rs as Object



Set rs = Me.Recordset.Clone

rs.FindFirst "[MemberID] = '" & Me![MemberSearch] & "'" Me.Bookmark 

rs.Bookmark



End Sub



Then, to set the current record:



Private Sub Form_Current()

[MemberSearch] = [MemberID]

End Sub



When I select a Member from the dropdown list, I get Run Time error 3021



"No Current Record". When I go to Debug - it's highlighted the

Me.Bookmark 

= rs.Bookmark line.



HELP!!



Thanks to any & all.



+Tammy



---

You are currently subscribed to access as: papparuff@c... To

unsubscribe send a blank email to $subst('Email.Unsub')