Requery and stay on current record

  • Thread starter Thread starter JohnB
  • Start date Start date
J

JohnB

Hi.

I have the following code in a command buttons On Click event. The form that
it sits on has a control sorce showing two tables in a one-to-many
relationship. When the button is clicked, the many sided record is deleted,
which is what I want. I added the Me.Requery line to get the 'one' sided
record to reshow and this works but the records are reset to show the first
record. Is there any way I can requery the main recordset while staying on
the record that was being viewed when the command button was clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
 
Bookmark. Record the current record's primary key (to a variable) before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key data type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is bound to the
primary key field.
 
Hi Brian.
Thanks for this. Unfortunately I won't be able to try it til later today but
it looks good. Please watch this thread for a later reply. Cheers, JohnB

Brian said:
Bookmark. Record the current record's primary key (to a variable) before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key data type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is bound to the
primary key field.

JohnB said:
Hi.

I have the following code in a command buttons On Click event. The form that
it sits on has a control sorce showing two tables in a one-to-many
relationship. When the button is clicked, the many sided record is deleted,
which is what I want. I added the Me.Requery line to get the 'one' sided
record to reshow and this works but the records are reset to show the first
record. Is there any way I can requery the main recordset while staying on
the record that was being viewed when the command button was clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...
Click to expand...
 
Hi Brian. I think I've added your code correctly, see below, but I get a
Compile Error when I try to use it. It says "Invalid Attribute in Sub or
Function" and when I click OK, the word "Private" is highlighted. Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at home but
want to use it on an Access 2000 mdb in work. Can you see what's wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the primary key data type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Brian said:
Bookmark. Record the current record's primary key (to a variable) before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key data type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is bound to the
primary key field.

JohnB said:
Hi.

I have the following code in a command buttons On Click event. The form that
it sits on has a control sorce showing two tables in a one-to-many
relationship. When the button is clicked, the many sided record is deleted,
which is what I want. I added the Me.Requery line to get the 'one' sided
record to reshow and this works but the records are reset to show the first
record. Is there any way I can requery the main recordset while staying on
the record that was being viewed when the command button was clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...
Click to expand...
 
1. Change "Private KeyCurrent As Integer" to "Dim KeyCurrent As Integer" (Use
the Private... only if you put this line in the General section at the top of
your module, not in the Click sub itself). Make sure that this declaration
matches the data type of your primary key. I am assuming the primary key for
the table that is the RecordSource of this form is the StudentID field and
that there is a control on the form that is bound to this field (if there is
not, create one and hide it so that the value will be readily accessible but
will not confuse the user). If StudentID is an integer, make this "Dim
KeyCurrent As Integer"; if StudentID is a string, make this "Dim KeyCurrent
As String" instead. If StudentID is a Long Integer, use "Dim KeyCurrent As
Long". You might even get away with just "Dim KeyCurrent".

2. "PrimaryKeyName" was just a placeholder - replace it with the name of the
control on the current form that is bound to the primary key in the
underlying table; probably "StudentID".

So, it should probably look like this:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Integer
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

If StudentID is a string instead of an Integer, then the FindFirst line
should read thus:

Me.RecordsetClone.FindFirst "[StudentID] = '" & StudentID & "'"

JohnB said:
Hi Brian. I think I've added your code correctly, see below, but I get a
Compile Error when I try to use it. It says "Invalid Attribute in Sub or
Function" and when I click OK, the word "Private" is highlighted. Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at home but
want to use it on an Access 2000 mdb in work. Can you see what's wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the primary key data type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Brian said:
Bookmark. Record the current record's primary key (to a variable) before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key data type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is bound to the
primary key field.

JohnB said:
Hi.

I have the following code in a command buttons On Click event. The form that
it sits on has a control sorce showing two tables in a one-to-many
relationship. When the button is clicked, the many sided record is deleted,
which is what I want. I added the Me.Requery line to get the 'one' sided
record to reshow and this works but the records are reset to show the first
record. Is there any way I can requery the main recordset while staying on
the record that was being viewed when the command button was clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi again Brian.

Sorry about the mistakes earlier.

Now getting a message "Invalid Use of Null". I've used the code as last
posted by you but I changed the DimKeyCurrent line to As Long because
StudentID is a long interger. Also I have a txtbox on the form bound to
SchoolID. The code succeeds in deleting the record but still resets to the
first record. Thanks for sticking with me on this. JohnB

The code I'm now using is:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub


Brian said:
1. Change "Private KeyCurrent As Integer" to "Dim KeyCurrent As Integer" (Use
the Private... only if you put this line in the General section at the top of
your module, not in the Click sub itself). Make sure that this declaration
matches the data type of your primary key. I am assuming the primary key for
the table that is the RecordSource of this form is the StudentID field and
that there is a control on the form that is bound to this field (if there is
not, create one and hide it so that the value will be readily accessible but
will not confuse the user). If StudentID is an integer, make this "Dim
KeyCurrent As Integer"; if StudentID is a string, make this "Dim KeyCurrent
As String" instead. If StudentID is a Long Integer, use "Dim KeyCurrent As
Long". You might even get away with just "Dim KeyCurrent".

2. "PrimaryKeyName" was just a placeholder - replace it with the name of the
control on the current form that is bound to the primary key in the
underlying table; probably "StudentID".

So, it should probably look like this:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Integer
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

If StudentID is a string instead of an Integer, then the FindFirst line
should read thus:

Me.RecordsetClone.FindFirst "[StudentID] = '" & StudentID & "'"

JohnB said:
Hi Brian. I think I've added your code correctly, see below, but I get a
Compile Error when I try to use it. It says "Invalid Attribute in Sub or
Function" and when I click OK, the word "Private" is highlighted. Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at home but
want to use it on an Access 2000 mdb in work. Can you see what's wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the primary key data type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Brian said:
Bookmark. Record the current record's primary key (to a variable) before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key
Click to expand...
data
type
Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is bound to the
primary key field.

:

Hi.

I have the following code in a command buttons On Click event. The
Click to expand...
form
that
it sits on has a control sorce showing two tables in a one-to-many
relationship. When the button is clicked, the many sided record is deleted,
which is what I want. I added the Me.Requery line to get the 'one' sided
record to reshow and this works but the records are reset to show
Click to expand...
the
first
record. Is there any way I can requery the main recordset while
Click to expand...
staying
on
the record that was being viewed when the command button was clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Oops. I made a typo in there.

Replace this line: Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID

with this one:

Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent

The first one tries to call up StudentID, which is currently blank. We don't
want to find the first record where StudentID is StudentID (which is blank at
the time this statement is hit), but where StudentID is the same as
KeyCurrent.

We'll get there eventually...

JohnB said:
Hi again Brian.

Sorry about the mistakes earlier.

Now getting a message "Invalid Use of Null". I've used the code as last
posted by you but I changed the DimKeyCurrent line to As Long because
StudentID is a long interger. Also I have a txtbox on the form bound to
SchoolID. The code succeeds in deleting the record but still resets to the
first record. Thanks for sticking with me on this. JohnB

The code I'm now using is:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub


Brian said:
1. Change "Private KeyCurrent As Integer" to "Dim KeyCurrent As Integer" (Use
the Private... only if you put this line in the General section at the top of
your module, not in the Click sub itself). Make sure that this declaration
matches the data type of your primary key. I am assuming the primary key for
the table that is the RecordSource of this form is the StudentID field and
that there is a control on the form that is bound to this field (if there is
not, create one and hide it so that the value will be readily accessible but
will not confuse the user). If StudentID is an integer, make this "Dim
KeyCurrent As Integer"; if StudentID is a string, make this "Dim KeyCurrent
As String" instead. If StudentID is a Long Integer, use "Dim KeyCurrent As
Long". You might even get away with just "Dim KeyCurrent".

2. "PrimaryKeyName" was just a placeholder - replace it with the name of the
control on the current form that is bound to the primary key in the
underlying table; probably "StudentID".

So, it should probably look like this:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Integer
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

If StudentID is a string instead of an Integer, then the FindFirst line
should read thus:

Me.RecordsetClone.FindFirst "[StudentID] = '" & StudentID & "'"

JohnB said:
Hi Brian. I think I've added your code correctly, see below, but I get a
Compile Error when I try to use it. It says "Invalid Attribute in Sub or
Function" and when I click OK, the word "Private" is highlighted. Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at home but
want to use it on an Access 2000 mdb in work. Can you see what's wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the primary key data type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Bookmark. Record the current record's primary key (to a variable) before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key data
type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is bound to the
primary key field.

:

Hi.

I have the following code in a command buttons On Click event. The form
that
it sits on has a control sorce showing two tables in a one-to-many
relationship. When the button is clicked, the many sided record is
deleted,
which is what I want. I added the Me.Requery line to get the 'one' sided
record to reshow and this works but the records are reset to show the
first
record. Is there any way I can requery the main recordset while staying
on
the record that was being viewed when the command button was clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Also, you can remove the Me.Requery (which takes you to the first record by
default), since the Bookmark takes you to the correct record anyway.

JohnB said:
Hi again Brian.

Sorry about the mistakes earlier.

Now getting a message "Invalid Use of Null". I've used the code as last
posted by you but I changed the DimKeyCurrent line to As Long because
StudentID is a long interger. Also I have a txtbox on the form bound to
SchoolID. The code succeeds in deleting the record but still resets to the
first record. Thanks for sticking with me on this. JohnB

The code I'm now using is:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub


Brian said:
1. Change "Private KeyCurrent As Integer" to "Dim KeyCurrent As Integer" (Use
the Private... only if you put this line in the General section at the top of
your module, not in the Click sub itself). Make sure that this declaration
matches the data type of your primary key. I am assuming the primary key for
the table that is the RecordSource of this form is the StudentID field and
that there is a control on the form that is bound to this field (if there is
not, create one and hide it so that the value will be readily accessible but
will not confuse the user). If StudentID is an integer, make this "Dim
KeyCurrent As Integer"; if StudentID is a string, make this "Dim KeyCurrent
As String" instead. If StudentID is a Long Integer, use "Dim KeyCurrent As
Long". You might even get away with just "Dim KeyCurrent".

2. "PrimaryKeyName" was just a placeholder - replace it with the name of the
control on the current form that is bound to the primary key in the
underlying table; probably "StudentID".

So, it should probably look like this:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Integer
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

If StudentID is a string instead of an Integer, then the FindFirst line
should read thus:

Me.RecordsetClone.FindFirst "[StudentID] = '" & StudentID & "'"

JohnB said:
Hi Brian. I think I've added your code correctly, see below, but I get a
Compile Error when I try to use it. It says "Invalid Attribute in Sub or
Function" and when I click OK, the word "Private" is highlighted. Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at home but
want to use it on an Access 2000 mdb in work. Can you see what's wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the primary key data type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Bookmark. Record the current record's primary key (to a variable) before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key data
type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is bound to the
primary key field.

:

Hi.

I have the following code in a command buttons On Click event. The form
that
it sits on has a control sorce showing two tables in a one-to-many
relationship. When the button is clicked, the many sided record is
deleted,
which is what I want. I added the Me.Requery line to get the 'one' sided
record to reshow and this works but the records are reset to show the
first
record. Is there any way I can requery the main recordset while staying
on
the record that was being viewed when the command button was clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Brian. Sorry but same result. Code is now as follows. Thanks again for
the help. JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Brian said:
Oops. I made a typo in there.

Replace this line: Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID

with this one:

Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent

The first one tries to call up StudentID, which is currently blank. We don't
want to find the first record where StudentID is StudentID (which is blank at
the time this statement is hit), but where StudentID is the same as
KeyCurrent.

We'll get there eventually...

JohnB said:
Hi again Brian.

Sorry about the mistakes earlier.

Now getting a message "Invalid Use of Null". I've used the code as last
posted by you but I changed the DimKeyCurrent line to As Long because
StudentID is a long interger. Also I have a txtbox on the form bound to
SchoolID. The code succeeds in deleting the record but still resets to the
first record. Thanks for sticking with me on this. JohnB

The code I'm now using is:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub


Brian said:
1. Change "Private KeyCurrent As Integer" to "Dim KeyCurrent As
Click to expand...
Integer"
(Use
the Private... only if you put this line in the General section at the
Click to expand...
top
of
your module, not in the Click sub itself). Make sure that this declaration
matches the data type of your primary key. I am assuming the primary
Click to expand...
key
for
the table that is the RecordSource of this form is the StudentID field and
that there is a control on the form that is bound to this field (if
Click to expand...
there
is
not, create one and hide it so that the value will be readily
Click to expand...
accessible
but
will not confuse the user). If StudentID is an integer, make this "Dim
KeyCurrent As Integer"; if StudentID is a string, make this "Dim KeyCurrent
As String" instead. If StudentID is a Long Integer, use "Dim KeyCurrent As
Long". You might even get away with just "Dim KeyCurrent".

2. "PrimaryKeyName" was just a placeholder - replace it with the name
Click to expand...
of
the
control on the current form that is bound to the primary key in the
underlying table; probably "StudentID".

So, it should probably look like this:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Integer
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

If StudentID is a string instead of an Integer, then the FindFirst line
should read thus:

Me.RecordsetClone.FindFirst "[StudentID] = '" & StudentID & "'"

:

Hi Brian. I think I've added your code correctly, see below, but I get a
Compile Error when I try to use it. It says "Invalid Attribute in Sub or
Function" and when I click OK, the word "Private" is highlighted. Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at
Click to expand...
home
but
want to use it on an Access 2000 mdb in work. Can you see what's wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the primary key
Click to expand...
data
type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Bookmark. Record the current record's primary key (to a variable) before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key data
type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is
Click to expand...
bound to
the
primary key field.

:

Hi.

I have the following code in a command buttons On Click event.
Click to expand...
The
form
that
it sits on has a control sorce showing two tables in a one-to-many
relationship. When the button is clicked, the many sided record is
deleted,
which is what I want. I added the Me.Requery line to get the
Click to expand...
'one'
sided
record to reshow and this works but the records are reset to
Click to expand...
show
the
first
record. Is there any way I can requery the main recordset while staying
on
the record that was being viewed when the command button was clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Brian. Just replied to you but got an error response. Have just tried
your last two suggestions but still same result. My code is now as follows.
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
'Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

Brian said:
Also, you can remove the Me.Requery (which takes you to the first record by
default), since the Bookmark takes you to the correct record anyway.

JohnB said:
Hi again Brian.

Sorry about the mistakes earlier.

Now getting a message "Invalid Use of Null". I've used the code as last
posted by you but I changed the DimKeyCurrent line to As Long because
StudentID is a long interger. Also I have a txtbox on the form bound to
SchoolID. The code succeeds in deleting the record but still resets to the
first record. Thanks for sticking with me on this. JohnB

The code I'm now using is:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub


Brian said:
1. Change "Private KeyCurrent As Integer" to "Dim KeyCurrent As
Click to expand...
Integer"
(Use
the Private... only if you put this line in the General section at the
Click to expand...
top
of
your module, not in the Click sub itself). Make sure that this declaration
matches the data type of your primary key. I am assuming the primary
Click to expand...
key
for
the table that is the RecordSource of this form is the StudentID field and
that there is a control on the form that is bound to this field (if
Click to expand...
there
is
not, create one and hide it so that the value will be readily
Click to expand...
accessible
but
will not confuse the user). If StudentID is an integer, make this "Dim
KeyCurrent As Integer"; if StudentID is a string, make this "Dim KeyCurrent
As String" instead. If StudentID is a Long Integer, use "Dim KeyCurrent As
Long". You might even get away with just "Dim KeyCurrent".

2. "PrimaryKeyName" was just a placeholder - replace it with the name
Click to expand...
of
the
control on the current form that is bound to the primary key in the
underlying table; probably "StudentID".

So, it should probably look like this:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Integer
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

If StudentID is a string instead of an Integer, then the FindFirst line
should read thus:

Me.RecordsetClone.FindFirst "[StudentID] = '" & StudentID & "'"

:

Hi Brian. I think I've added your code correctly, see below, but I get a
Compile Error when I try to use it. It says "Invalid Attribute in Sub or
Function" and when I click OK, the word "Private" is highlighted. Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at
Click to expand...
home
but
want to use it on an Access 2000 mdb in work. Can you see what's wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the primary key
Click to expand...
data
type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Bookmark. Record the current record's primary key (to a variable) before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key data
type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is
Click to expand...
bound to
the
primary key field.

:

Hi.

I have the following code in a command buttons On Click event.
Click to expand...
The
form
that
it sits on has a control sorce showing two tables in a one-to-many
relationship. When the button is clicked, the many sided record is
deleted,
which is what I want. I added the Me.Requery line to get the
Click to expand...
'one'
sided
record to reshow and this works but the records are reset to
Click to expand...
show
the
first
record. Is there any way I can requery the main recordset while staying
on
the record that was being viewed when the command button was clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Close, but no cigar yet. Let's pre-empt the delet for a moment and check the
value of the StudentID field & KeyCurrent variable to see what is being
assigned. Insert these as the first two line in your sub:

MsgBox StudentID
Exit Sub

See if the message box gives you the current StudentID (before the delete).
If not, then the issue is correctly referencing the control that is bound to
StudentID.

If so, remove these two lines and insert this instead on the line after
KeyCurrent = StudentID:

MsgBox KeyCurrent
Exit Sub

See if this correctly gives you the current StudentID (as just assigned to
KeyCurrent).

Try removing the KeyCurrent = Null if these fail, and repost your results.

JohnB said:
Hi Brian. Just replied to you but got an error response. Have just tried
your last two suggestions but still same result. My code is now as follows.
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
'Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

Brian said:
Also, you can remove the Me.Requery (which takes you to the first record by
default), since the Bookmark takes you to the correct record anyway.

JohnB said:
Hi again Brian.

Sorry about the mistakes earlier.

Now getting a message "Invalid Use of Null". I've used the code as last
posted by you but I changed the DimKeyCurrent line to As Long because
StudentID is a long interger. Also I have a txtbox on the form bound to
SchoolID. The code succeeds in deleting the record but still resets to the
first record. Thanks for sticking with me on this. JohnB

The code I'm now using is:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub


1. Change "Private KeyCurrent As Integer" to "Dim KeyCurrent As Integer"
(Use
the Private... only if you put this line in the General section at the top
of
your module, not in the Click sub itself). Make sure that this declaration
matches the data type of your primary key. I am assuming the primary key
for
the table that is the RecordSource of this form is the StudentID field and
that there is a control on the form that is bound to this field (if there
is
not, create one and hide it so that the value will be readily accessible
but
will not confuse the user). If StudentID is an integer, make this "Dim
KeyCurrent As Integer"; if StudentID is a string, make this "Dim
KeyCurrent
As String" instead. If StudentID is a Long Integer, use "Dim KeyCurrent As
Long". You might even get away with just "Dim KeyCurrent".

2. "PrimaryKeyName" was just a placeholder - replace it with the name of
the
control on the current form that is bound to the primary key in the
underlying table; probably "StudentID".

So, it should probably look like this:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Integer
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

If StudentID is a string instead of an Integer, then the FindFirst line
should read thus:

Me.RecordsetClone.FindFirst "[StudentID] = '" & StudentID & "'"

:

Hi Brian. I think I've added your code correctly, see below, but I get a
Compile Error when I try to use it. It says "Invalid Attribute in Sub or
Function" and when I click OK, the word "Private" is highlighted.
Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at home
but
want to use it on an Access 2000 mdb in work. Can you see what's wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the primary key data
type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Bookmark. Record the current record's primary key (to a variable)
before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the
variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key
data
type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is bound to
the
primary key field.

:

Hi.

I have the following code in a command buttons On Click event. The
form
that
it sits on has a control sorce showing two tables in a one-to-many
relationship. When the button is clicked, the many sided record is
deleted,
which is what I want. I added the Me.Requery line to get the 'one'
sided
record to reshow and this works but the records are reset to show
the
first
record. Is there any way I can requery the main recordset while
staying
on
the record that was being viewed when the command button was
clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Brian. Sent you a message about ten minutes ago and it has not appeared
yet. Things are slowing down. In case it failed - I'll repeat. I tried your
message ideas and in both cases they showed the number 3, this was the
StudentID showing in txtStudent ID at the time. Is that good?

Anyway, I'll need to sign-off soon but justt hought I'd mention that when
the record is deleted (e.g. if I remove the failing "null" line), I'm
returned to record 1 and can't even see the third record until I exit the
form and reenter. I think this was always happening - hence my trying to
requery the forms recordset. Does that suggest anything?As I said in my
first post, this particular delete seems to remove the "many" side record
rather than the main one, even though the delete button is on the main form.
Perhaps I should take some time to investigate further and spell out exactly
what I'm doing. What do you think? Thanks again for all the help, JohnB


Brian said:
Close, but no cigar yet. Let's pre-empt the delet for a moment and check the
value of the StudentID field & KeyCurrent variable to see what is being
assigned. Insert these as the first two line in your sub:

MsgBox StudentID
Exit Sub

See if the message box gives you the current StudentID (before the delete).
If not, then the issue is correctly referencing the control that is bound to
StudentID.

If so, remove these two lines and insert this instead on the line after
KeyCurrent = StudentID:

MsgBox KeyCurrent
Exit Sub

See if this correctly gives you the current StudentID (as just assigned to
KeyCurrent).

Try removing the KeyCurrent = Null if these fail, and repost your results.

JohnB said:
Hi Brian. Just replied to you but got an error response. Have just tried
your last two suggestions but still same result. My code is now as follows.
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
'Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

Brian said:
Also, you can remove the Me.Requery (which takes you to the first
Click to expand...
record
by
default), since the Bookmark takes you to the correct record anyway.

:

Hi again Brian.

Sorry about the mistakes earlier.

Now getting a message "Invalid Use of Null". I've used the code as last
posted by you but I changed the DimKeyCurrent line to As Long because
StudentID is a long interger. Also I have a txtbox on the form bound to
SchoolID. The code succeeds in deleting the record but still resets
Click to expand...
to
the
first record. Thanks for sticking with me on this. JohnB

The code I'm now using is:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub


1. Change "Private KeyCurrent As Integer" to "Dim KeyCurrent As Integer"
(Use
the Private... only if you put this line in the General section at
Click to expand...
the
top
of
your module, not in the Click sub itself). Make sure that this declaration
matches the data type of your primary key. I am assuming the
Click to expand...
primary
key
for
the table that is the RecordSource of this form is the StudentID
Click to expand...
field
and
that there is a control on the form that is bound to this field
Click to expand...
(if
there
is
not, create one and hide it so that the value will be readily accessible
but
will not confuse the user). If StudentID is an integer, make this "Dim
KeyCurrent As Integer"; if StudentID is a string, make this "Dim
KeyCurrent
As String" instead. If StudentID is a Long Integer, use "Dim KeyCurrent As
Long". You might even get away with just "Dim KeyCurrent".

2. "PrimaryKeyName" was just a placeholder - replace it with the
Click to expand...
name
of
the
control on the current form that is bound to the primary key in the
underlying table; probably "StudentID".

So, it should probably look like this:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Integer
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

If StudentID is a string instead of an Integer, then the FindFirst line
should read thus:

Me.RecordsetClone.FindFirst "[StudentID] = '" & StudentID & "'"

:

Hi Brian. I think I've added your code correctly, see below, but
Click to expand...
I
get a
Compile Error when I try to use it. It says "Invalid Attribute
Click to expand...
in
Sub or
Function" and when I click OK, the word "Private" is highlighted.
Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at home
but
want to use it on an Access 2000 mdb in work. Can you see what's wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the primary
Click to expand...
key
data
type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Bookmark. Record the current record's primary key (to a variable)
before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the
variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key
data
type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is bound to
the
primary key field.

:

Hi.

I have the following code in a command buttons On Click
Click to expand...
event.
The
form
that
it sits on has a control sorce showing two tables in a one-to-many
relationship. When the button is clicked, the many sided
Click to expand...
record
is
deleted,
which is what I want. I added the Me.Requery line to get the 'one'
sided
record to reshow and this works but the records are reset to show
the
first
record. Is there any way I can requery the main recordset while
staying
on
the record that was being viewed when the command button was
clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Those results are good, because they indicate that those portions of code are
working correctly.

Perhaps this might be faster by e-mail. Try sending me a zipped copy of the
DB or at least a zipped copy of a stripped-down version of the db that
contains the form in question, and I will look at it.

brainy at pacifier dot com

JohnB said:
Hi Brian. Sent you a message about ten minutes ago and it has not appeared
yet. Things are slowing down. In case it failed - I'll repeat. I tried your
message ideas and in both cases they showed the number 3, this was the
StudentID showing in txtStudent ID at the time. Is that good?

Anyway, I'll need to sign-off soon but justt hought I'd mention that when
the record is deleted (e.g. if I remove the failing "null" line), I'm
returned to record 1 and can't even see the third record until I exit the
form and reenter. I think this was always happening - hence my trying to
requery the forms recordset. Does that suggest anything?As I said in my
first post, this particular delete seems to remove the "many" side record
rather than the main one, even though the delete button is on the main form.
Perhaps I should take some time to investigate further and spell out exactly
what I'm doing. What do you think? Thanks again for all the help, JohnB


Brian said:
Close, but no cigar yet. Let's pre-empt the delet for a moment and check the
value of the StudentID field & KeyCurrent variable to see what is being
assigned. Insert these as the first two line in your sub:

MsgBox StudentID
Exit Sub

See if the message box gives you the current StudentID (before the delete).
If not, then the issue is correctly referencing the control that is bound to
StudentID.

If so, remove these two lines and insert this instead on the line after
KeyCurrent = StudentID:

MsgBox KeyCurrent
Exit Sub

See if this correctly gives you the current StudentID (as just assigned to
KeyCurrent).

Try removing the KeyCurrent = Null if these fail, and repost your results.

JohnB said:
Hi Brian. Just replied to you but got an error response. Have just tried
your last two suggestions but still same result. My code is now as follows.
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
'Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

Also, you can remove the Me.Requery (which takes you to the first record
by
default), since the Bookmark takes you to the correct record anyway.

:

Hi again Brian.

Sorry about the mistakes earlier.

Now getting a message "Invalid Use of Null". I've used the code as last
posted by you but I changed the DimKeyCurrent line to As Long because
StudentID is a long interger. Also I have a txtbox on the form bound to
SchoolID. The code succeeds in deleting the record but still resets to
the
first record. Thanks for sticking with me on this. JohnB

The code I'm now using is:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub


1. Change "Private KeyCurrent As Integer" to "Dim KeyCurrent As
Integer"
(Use
the Private... only if you put this line in the General section at the
top
of
your module, not in the Click sub itself). Make sure that this
declaration
matches the data type of your primary key. I am assuming the primary
key
for
the table that is the RecordSource of this form is the StudentID field
and
that there is a control on the form that is bound to this field (if
there
is
not, create one and hide it so that the value will be readily
accessible
but
will not confuse the user). If StudentID is an integer, make this "Dim
KeyCurrent As Integer"; if StudentID is a string, make this "Dim
KeyCurrent
As String" instead. If StudentID is a Long Integer, use "Dim
KeyCurrent As
Long". You might even get away with just "Dim KeyCurrent".

2. "PrimaryKeyName" was just a placeholder - replace it with the name
of
the
control on the current form that is bound to the primary key in the
underlying table; probably "StudentID".

So, it should probably look like this:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Integer
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

If StudentID is a string instead of an Integer, then the FindFirst
line
should read thus:

Me.RecordsetClone.FindFirst "[StudentID] = '" & StudentID & "'"

:

Hi Brian. I think I've added your code correctly, see below, but I
get a
Compile Error when I try to use it. It says "Invalid Attribute in
Sub or
Function" and when I click OK, the word "Private" is highlighted.
Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at
home
but
want to use it on an Access 2000 mdb in work. Can you see what's
wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the primary key
data
type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Bookmark. Record the current record's primary key (to a variable)
before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the
variable
name/type):

Private KeyCurrent as Integer 'make the type match the primary key
data
type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is
bound to
the
primary key field.

:

Hi.

I have the following code in a command buttons On Click event.
The
form
that
it sits on has a control sorce showing two tables in a
one-to-many
relationship. When the button is clicked, the many sided record
is
deleted,
which is what I want. I added the Me.Requery line to get the
'one'
sided
record to reshow and this works but the records are reset to
show
the
first
record. Is there any way I can requery the main recordset while
staying
on
the record that was being viewed when the command button was
clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the offer Brian.

Give me a while to have a look further into how exactly this manages to
delete the 'many' sided records while leaving the 'one' side intact - that
doesn't seem right to me. Not unless the delete command button is on a
subform, which it's not. Perhaps I just managed to get something really
twisted up. If I can, I'll produce a Zip that illustrates the problem. I'll
contact you by EMail in any case. Thanks again for ll your time with this -
I really appreciate the help. JohnB


Brian said:
Those results are good, because they indicate that those portions of code are
working correctly.

Perhaps this might be faster by e-mail. Try sending me a zipped copy of the
DB or at least a zipped copy of a stripped-down version of the db that
contains the form in question, and I will look at it.

brainy at pacifier dot com

JohnB said:
Hi Brian. Sent you a message about ten minutes ago and it has not appeared
yet. Things are slowing down. In case it failed - I'll repeat. I tried your
message ideas and in both cases they showed the number 3, this was the
StudentID showing in txtStudent ID at the time. Is that good?

Anyway, I'll need to sign-off soon but justt hought I'd mention that when
the record is deleted (e.g. if I remove the failing "null" line), I'm
returned to record 1 and can't even see the third record until I exit the
form and reenter. I think this was always happening - hence my trying to
requery the forms recordset. Does that suggest anything?As I said in my
first post, this particular delete seems to remove the "many" side record
rather than the main one, even though the delete button is on the main form.
Perhaps I should take some time to investigate further and spell out exactly
what I'm doing. What do you think? Thanks again for all the help, JohnB


Brian said:
Close, but no cigar yet. Let's pre-empt the delet for a moment and
Click to expand...
check
the
value of the StudentID field & KeyCurrent variable to see what is being
assigned. Insert these as the first two line in your sub:

MsgBox StudentID
Exit Sub

See if the message box gives you the current StudentID (before the delete).
If not, then the issue is correctly referencing the control that is
Click to expand...
bound
to
StudentID.

If so, remove these two lines and insert this instead on the line after
KeyCurrent = StudentID:

MsgBox KeyCurrent
Exit Sub

See if this correctly gives you the current StudentID (as just assigned to
KeyCurrent).

Try removing the KeyCurrent = Null if these fail, and repost your results.

:

Hi Brian. Just replied to you but got an error response. Have just tried
your last two suggestions but still same result. My code is now as follows.
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
'Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

Also, you can remove the Me.Requery (which takes you to the first record
by
default), since the Bookmark takes you to the correct record anyway.

:

Hi again Brian.

Sorry about the mistakes earlier.

Now getting a message "Invalid Use of Null". I've used the code
Click to expand...
as
last
posted by you but I changed the DimKeyCurrent line to As Long because
StudentID is a long interger. Also I have a txtbox on the form
Click to expand...
bound
to
SchoolID. The code succeeds in deleting the record but still
Click to expand...
resets
to
the
first record. Thanks for sticking with me on this. JohnB

The code I'm now using is:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub


1. Change "Private KeyCurrent As Integer" to "Dim KeyCurrent As
Integer"
(Use
the Private... only if you put this line in the General
Click to expand...
section at
the
top
of
your module, not in the Click sub itself). Make sure that this
declaration
matches the data type of your primary key. I am assuming the primary
key
for
the table that is the RecordSource of this form is the
Click to expand...
StudentID
field
and
that there is a control on the form that is bound to this
Click to expand...
field
(if
there
is
not, create one and hide it so that the value will be readily
accessible
but
will not confuse the user). If StudentID is an integer, make
Click to expand...
this
"Dim
KeyCurrent As Integer"; if StudentID is a string, make this "Dim
KeyCurrent
As String" instead. If StudentID is a Long Integer, use "Dim
KeyCurrent As
Long". You might even get away with just "Dim KeyCurrent".

2. "PrimaryKeyName" was just a placeholder - replace it with
Click to expand...
the
name
of
the
control on the current form that is bound to the primary key
Click to expand...
in
the
underlying table; probably "StudentID".

So, it should probably look like this:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Integer
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

If StudentID is a string instead of an Integer, then the FindFirst
line
should read thus:

Me.RecordsetClone.FindFirst "[StudentID] = '" & StudentID & "'"

:

Hi Brian. I think I've added your code correctly, see below,
Click to expand...
but
I
get a
Compile Error when I try to use it. It says "Invalid
Click to expand...
Attribute
in
Sub or
Function" and when I click OK, the word "Private" is highlighted.
Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at
home
but
want to use it on an Access 2000 mdb in work. Can you see what's
wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the
Click to expand...
primary
key
data
type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Bookmark. Record the current record's primary key (to a variable)
before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the
variable
name/type):

Private KeyCurrent as Integer 'make the type match the
Click to expand...
primary
key
data
type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is
bound to
the
primary key field.

:

Hi.

I have the following code in a command buttons On Click event.
The
form
that
it sits on has a control sorce showing two tables in a
one-to-many
relationship. When the button is clicked, the many sided record
is
deleted,
which is what I want. I added the Me.Requery line to get the
'one'
sided
record to reshow and this works but the records are reset to
show
the
first
record. Is there any way I can requery the main
Click to expand...
recordset
while
staying
on
the record that was being viewed when the command button was
clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Click to expand...
Click to expand...
Click to expand...
 
Hi Brian

I wonder if you are still watching this thread. If I don't see a reply from
you here after a few days, I will send you an Email.

I decided to start my project again, taking more time about it and this
resulted in me not having to sort out the problem I had. In fact, the way I
had designed things, there were going to be significant other problems. In
any case, your help has given me extra knowledge that I'm sure I will apply
somewhere or other in the future. So, again, thanks for all the effort you
put into this one. I appreciate it.

Cheers, JohnB

Brian said:
Those results are good, because they indicate that those portions of code are
working correctly.

Perhaps this might be faster by e-mail. Try sending me a zipped copy of the
DB or at least a zipped copy of a stripped-down version of the db that
contains the form in question, and I will look at it.

brainy at pacifier dot com

JohnB said:
Hi Brian. Sent you a message about ten minutes ago and it has not appeared
yet. Things are slowing down. In case it failed - I'll repeat. I tried your
message ideas and in both cases they showed the number 3, this was the
StudentID showing in txtStudent ID at the time. Is that good?

Anyway, I'll need to sign-off soon but justt hought I'd mention that when
the record is deleted (e.g. if I remove the failing "null" line), I'm
returned to record 1 and can't even see the third record until I exit the
form and reenter. I think this was always happening - hence my trying to
requery the forms recordset. Does that suggest anything?As I said in my
first post, this particular delete seems to remove the "many" side record
rather than the main one, even though the delete button is on the main form.
Perhaps I should take some time to investigate further and spell out exactly
what I'm doing. What do you think? Thanks again for all the help, JohnB


Brian said:
Close, but no cigar yet. Let's pre-empt the delet for a moment and
Click to expand...
check
the
value of the StudentID field & KeyCurrent variable to see what is being
assigned. Insert these as the first two line in your sub:

MsgBox StudentID
Exit Sub

See if the message box gives you the current StudentID (before the delete).
If not, then the issue is correctly referencing the control that is
Click to expand...
bound
to
StudentID.

If so, remove these two lines and insert this instead on the line after
KeyCurrent = StudentID:

MsgBox KeyCurrent
Exit Sub

See if this correctly gives you the current StudentID (as just assigned to
KeyCurrent).

Try removing the KeyCurrent = Null if these fail, and repost your results.

:

Hi Brian. Just replied to you but got an error response. Have just tried
your last two suggestions but still same result. My code is now as follows.
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
'Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

Also, you can remove the Me.Requery (which takes you to the first record
by
default), since the Bookmark takes you to the correct record anyway.

:

Hi again Brian.

Sorry about the mistakes earlier.

Now getting a message "Invalid Use of Null". I've used the code
Click to expand...
as
last
posted by you but I changed the DimKeyCurrent line to As Long because
StudentID is a long interger. Also I have a txtbox on the form
Click to expand...
bound
to
SchoolID. The code succeeds in deleting the record but still
Click to expand...
resets
to
the
first record. Thanks for sticking with me on this. JohnB

The code I'm now using is:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Long
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub


1. Change "Private KeyCurrent As Integer" to "Dim KeyCurrent As
Integer"
(Use
the Private... only if you put this line in the General
Click to expand...
section at
the
top
of
your module, not in the Click sub itself). Make sure that this
declaration
matches the data type of your primary key. I am assuming the primary
key
for
the table that is the RecordSource of this form is the
Click to expand...
StudentID
field
and
that there is a control on the form that is bound to this
Click to expand...
field
(if
there
is
not, create one and hide it so that the value will be readily
accessible
but
will not confuse the user). If StudentID is an integer, make
Click to expand...
this
"Dim
KeyCurrent As Integer"; if StudentID is a string, make this "Dim
KeyCurrent
As String" instead. If StudentID is a Long Integer, use "Dim
KeyCurrent As
Long". You might even get away with just "Dim KeyCurrent".

2. "PrimaryKeyName" was just a placeholder - replace it with
Click to expand...
the
name
of
the
control on the current form that is bound to the primary key
Click to expand...
in
the
underlying table; probably "StudentID".

So, it should probably look like this:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim KeyCurrent As Integer
KeyCurrent = StudentID
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & StudentID
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub

If StudentID is a string instead of an Integer, then the FindFirst
line
should read thus:

Me.RecordsetClone.FindFirst "[StudentID] = '" & StudentID & "'"

:

Hi Brian. I think I've added your code correctly, see below,
Click to expand...
but
I
get a
Compile Error when I try to use it. It says "Invalid
Click to expand...
Attribute
in
Sub or
Function" and when I click OK, the word "Private" is highlighted.
Perhaps I
should say that I'm trying this out on a sample Access 97 mdb at
home
but
want to use it on an Access 2000 mdb in work. Can you see what's
wrong?
Thanks again, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Private KeyCurrent As Integer 'make the type match the
Click to expand...
primary
key
data
type
KeyCurrent = PrimaryKeyName
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Me.RecordsetClone.FindFirst "[StudentID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record


Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Bookmark. Record the current record's primary key (to a variable)
before
running your code, then return to it after the code runs.

Put this in the General portion of the form's module (sets the
variable
name/type):

Private KeyCurrent as Integer 'make the type match the
Click to expand...
primary
key
data
type

Code for the button click:

Private Button1_Click()
KeyCurrent = PrimaryKeyName
<Enter your other code here>
Me.RecordsetClone.FindFirst "[PrimaryKeyName] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark
KeyCurrent = Null 'clears the value after finding the record
End Sub

PrimaryKeyName is the name of the control on the form that is
bound to
the
primary key field.

:

Hi.

I have the following code in a command buttons On Click event.
The
form
that
it sits on has a control sorce showing two tables in a
one-to-many
relationship. When the button is clicked, the many sided record
is
deleted,
which is what I want. I added the Me.Requery line to get the
'one'
sided
record to reshow and this works but the records are reset to
show
the
first
record. Is there any way I can requery the main
Click to expand...
recordset
while
staying
on
the record that was being viewed when the command button was
clicked?

Thanks for any help, JohnB

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Me.Requery
Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Click to expand...
Click to expand...
Click to expand...
 
Back
Top