 | |
DoCmd.Findrecord. How do you specify the field.
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
DoCmd.Findrecord. How do you specify the field. -
02-02-2012
, 08:19 AM
#2
| |||
| |||
|
#3
| |||
| |||
|
|
After you a command button to requery the data I want to return to the record I have been working in but at present it just goes to the first record in the background table. Do I use 'Findrecord', and if so how do I specify which field contains the unique identifier? The column containing the unique identifier is called 'unique_record_number' and the text box on the form is named 'txtURN_1'. |
Yes, you would use Findrecord and specify a WHERE clause (without the
WHERE keyword) as a parameter. Assuming the field in the table is called
'unique_record_number' and the record you want to return to is still
selected when the button is pressed, the code to requery a form and
return to the last record might look something like this :
Private Sub MyCommandButton_Click()
Dim lngID As Long
Dim rstData As Recordset
lngID = Nz(unique_record_number, 0) 'Save the ID
of the current record
Me.Requery 'Requery the form
Set rstData = Me.Recordset.Clone 'Create a copy
of the recordset
rstData.FindFirst "unique_record_number = " & lngID 'Locate the
old record
If Not rstData.EOF Then 'If the old ID
was found
Me.Bookmark = rstData.Bookmark ' Then set
the form's current record to the one found in the copy
Else 'Else tell the
user
MsgBox "The ID no longer exists in the recordset", vbInformation
End If
rstData.Close
Set rstData = Nothing
End If
HTH
Jan T
#4
| |||
| |||
|
|
On 2012-02-02 3:19 PM, FireyColin wrote: After you a command button to requery the data I want to return to the record I have been working in but at present it just goes to the first record in the background table. *Do I use 'Findrecord', and if so how do I specify which field contains the unique identifier? The column containing the unique identifier is called 'unique_record_number' and the text box on the form is named 'txtURN_1'. Yes, you would use Findrecord and specify a WHERE clause (without the WHERE keyword) as a parameter. Assuming the field in the table is called 'unique_record_number' and the record you want to return to is still selected when the button is pressed, the code to requery a form and return to the last record might look something like this : Private Sub MyCommandButton_Click() * * Dim lngID As Long * * Dim rstData As Recordset * * lngID = Nz(unique_record_number, 0) * * * * * * ** * 'Save the ID of the current record * * Me.Requery * * * * * * * * * * * * * * * * * * * * * *'Requery the form * * Set rstData = Me.Recordset.Clone * * * * * * * * * * *'Create a copy of the recordset * * rstData.FindFirst "unique_record_number = " & lngID * 'Locatethe old record * * If Not rstData.EOF Then * * * * * * * * * * * * * * * 'If the old ID was found * * * *Me.Bookmark = rstData.Bookmark * * * * * * * * * * ' * Then set the form's current record to the one found in the copy * * Else * * * * * * * * * * * * * * * * * * * * * * * * *'Else tell the user * * * *MsgBox "The ID no longer exists in the recordset", vbInformation * * End If * * rstData.Close * * Set rstData = Nothing End If HTH Jan T |
worked.
#5
| |||
| |||
|
|
On Feb 2, 6:01*pm, Jan T <access... (AT) yahoo (DOT) com> wrote: On 2012-02-02 3:19 PM, FireyColin wrote: After you a command button to requery the data I want to return to the record I have been working in but at present it just goes to the first record in the background table. *Do I use 'Findrecord', and if so how do I specify which field contains the unique identifier? The column containing the unique identifier is called 'unique_record_number' and the text box on the form is named 'txtURN_1'. Yes, you would use Findrecord and specify a WHERE clause (without the WHERE keyword) as a parameter. Assuming the field in the table is called 'unique_record_number' and the record you want to return to is still selected when the button is pressed, the code to requery a form and return to the last record might look something like this : Private Sub MyCommandButton_Click() * * Dim lngID As Long * * Dim rstData As Recordset * * lngID = Nz(unique_record_number, 0) * * * * * * * * * 'Save the ID of the current record * * Me.Requery * * * * * * * * * * * * * * * * * * * * * *'Requery the form * * Set rstData = Me.Recordset.Clone * * * * * * * * * * *'Create a copy of the recordset * * rstData.FindFirst "unique_record_number = " & lngID * 'Locate the old record * * If Not rstData.EOF Then * * * * * * * * * ** * * * * 'If the old ID was found * * * *Me.Bookmark = rstData.Bookmark * * * * * ** * * * ' * Then set the form's current record to the one found in the copy * * Else * * * * * * * * * * * * * * * * * * * * * * * * *'Else tell the user * * * *MsgBox "The ID no longer exists in the recordset", vbInformation * * End If * * rstData.Close * * Set rstData = Nothing End If HTH Jan T THanks Christian and Jan tried both of those options but neither worked.- Hide quoted text - - Show quoted text - |
a satellite and earth. Anyway...try
Not rstdata.NoMatch
instead of
Not rstdata.EOF
#6
| |||
| |||
|
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.