On Click event triggers when clicking on a column

R

Robert

I have a main form with a datasheet subform in the footer. The purpose of
the datasheet is to select records in the main form by selecting a record on
the datasheet. The code that I have currently does do this:

Private Sub Form_Click()
Dim num1 As Integer
num1 = [id]
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[id] = " & num1
If Not rs.EOF Then
Forms.frmattendanceupdate.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

The problems are that the code is executed when I click on a column to sort
the datasheet. There is no need to execute this when clicking on a column,
only when clicking on a row (record).. Also, after sorting on a column and
then clicking on a row (record), I get an error message saying that this is
an invalid bookmark.

Is there any way to avoid executing this code when clicking on a column?
And how do I get it to work after this sort has been executed?

Robert
 
J

Jeanette Cunningham

You could move the code to the double click event of each text box in the
record.
The code would only fire when a row in the record is clicked.

When using find first, use the NoMatch property of the recordset-->
If Not rs.NoMatch Then
Forms.frmattendanceupdate.Bookmark = rs.Bookmark
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
R

Robert

Thanks. I changed the if statement to

If Not rs.EOF And Not rs.NoMatch Then

and the result was the same.

Jeanette Cunningham said:
You could move the code to the double click event of each text box in the
record.
The code would only fire when a row in the record is clicked.

When using find first, use the NoMatch property of the recordset-->
If Not rs.NoMatch Then
Forms.frmattendanceupdate.Bookmark = rs.Bookmark
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Robert said:
I have a main form with a datasheet subform in the footer. The purpose of
the datasheet is to select records in the main form by selecting a record
on the datasheet. The code that I have currently does do this:

Private Sub Form_Click()
Dim num1 As Integer
num1 = [id]
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[id] = " & num1
If Not rs.EOF Then
Forms.frmattendanceupdate.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

The problems are that the code is executed when I click on a column to
sort the datasheet. There is no need to execute this when clicking on a
column, only when clicking on a row (record).. Also, after sorting on a
column and then clicking on a row (record), I get an error message saying
that this is an invalid bookmark.

Is there any way to avoid executing this code when clicking on a column?
And how do I get it to work after this sort has been executed?

Robert
 
D

Dirk Goldgar

Robert said:
I have a main form with a datasheet subform in the footer. The purpose of
the datasheet is to select records in the main form by selecting a record
on the datasheet. The code that I have currently does do this:

Private Sub Form_Click()
Dim num1 As Integer
num1 = [id]
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[id] = " & num1
If Not rs.EOF Then
Forms.frmattendanceupdate.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

The problems are that the code is executed when I click on a column to
sort the datasheet. There is no need to execute this when clicking on a
column, only when clicking on a row (record).. Also, after sorting on a
column and then clicking on a row (record), I get an error message saying
that this is an invalid bookmark.

Is there any way to avoid executing this code when clicking on a column?
And how do I get it to work after this sort has been executed?


I think I would use the Current event of the subform, rather than its Click
event. The Click event of a form is only raised when you click on certain
parts of the form, such as its record selector or, apparently, the column
header.

Aside from that, you also have a problem because you are using incompatible
bookmarks. The recordset (or clone of the recordset) of the subform is not
a clone of the recordset of the parent form, and so their bookmarks are not
compatible. I suggest you use this code instead:

'----- start of suggested code -----
Private Sub Form_Current()

If Not IsNull(Me.ID) Then
Me.Parent.Recordset.FindFirst "ID=" & Me.ID
End If

End Sub

'----- end of suggested code -----
 
J

Jeanette Cunningham

Good catch Dirk.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Dirk Goldgar said:
Robert said:
I have a main form with a datasheet subform in the footer. The purpose of
the datasheet is to select records in the main form by selecting a record
on the datasheet. The code that I have currently does do this:

Private Sub Form_Click()
Dim num1 As Integer
num1 = [id]
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[id] = " & num1
If Not rs.EOF Then
Forms.frmattendanceupdate.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

The problems are that the code is executed when I click on a column to
sort the datasheet. There is no need to execute this when clicking on a
column, only when clicking on a row (record).. Also, after sorting on a
column and then clicking on a row (record), I get an error message saying
that this is an invalid bookmark.

Is there any way to avoid executing this code when clicking on a column?
And how do I get it to work after this sort has been executed?


I think I would use the Current event of the subform, rather than its
Click event. The Click event of a form is only raised when you click on
certain parts of the form, such as its record selector or, apparently, the
column header.

Aside from that, you also have a problem because you are using
incompatible bookmarks. The recordset (or clone of the recordset) of the
subform is not a clone of the recordset of the parent form, and so their
bookmarks are not compatible. I suggest you use this code instead:

'----- start of suggested code -----
Private Sub Form_Current()

If Not IsNull(Me.ID) Then
Me.Parent.Recordset.FindFirst "ID=" & Me.ID
End If

End Sub

'----- end of suggested code -----

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

(please reply to the newsgroup)
 
L

larry glover

Robert said:
I have a main form with a datasheet subform in the footer. The purpose of
the datasheet is to select records in the main form by selecting a record
on the datasheet. The code that I have currently does do this:

Private Sub Form_Click()
Dim num1 As Integer
num1 = [id]
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[id] = " & num1
If Not rs.EOF Then
Forms.frmattendanceupdate.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

The problems are that the code is executed when I click on a column to
sort the datasheet. There is no need to execute this when clicking on a
column, only when clicking on a row (record).. Also, after sorting on a
column and then clicking on a row (record), I get an error message saying
that this is an invalid bookmark.

Is there any way to avoid executing this code when clicking on a column?
And how do I get it to work after this sort has been executed?

Robert
 
R

Robert

? ?
larry glover said:
Robert said:
I have a main form with a datasheet subform in the footer. The purpose of
the datasheet is to select records in the main form by selecting a record
on the datasheet. The code that I have currently does do this:

Private Sub Form_Click()
Dim num1 As Integer
num1 = [id]
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[id] = " & num1
If Not rs.EOF Then
Forms.frmattendanceupdate.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

The problems are that the code is executed when I click on a column to
sort the datasheet. There is no need to execute this when clicking on a
column, only when clicking on a row (record).. Also, after sorting on a
column and then clicking on a row (record), I get an error message saying
that this is an invalid bookmark.

Is there any way to avoid executing this code when clicking on a column?
And how do I get it to work after this sort has been executed?

Robert
 
R

Robert

Thanks. Will give it a try.

Dirk Goldgar said:
Robert said:
I have a main form with a datasheet subform in the footer. The purpose of
the datasheet is to select records in the main form by selecting a record
on the datasheet. The code that I have currently does do this:

Private Sub Form_Click()
Dim num1 As Integer
num1 = [id]
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[id] = " & num1
If Not rs.EOF Then
Forms.frmattendanceupdate.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

The problems are that the code is executed when I click on a column to
sort the datasheet. There is no need to execute this when clicking on a
column, only when clicking on a row (record).. Also, after sorting on a
column and then clicking on a row (record), I get an error message saying
that this is an invalid bookmark.

Is there any way to avoid executing this code when clicking on a column?
And how do I get it to work after this sort has been executed?


I think I would use the Current event of the subform, rather than its
Click event. The Click event of a form is only raised when you click on
certain parts of the form, such as its record selector or, apparently, the
column header.

Aside from that, you also have a problem because you are using
incompatible bookmarks. The recordset (or clone of the recordset) of the
subform is not a clone of the recordset of the parent form, and so their
bookmarks are not compatible. I suggest you use this code instead:

'----- start of suggested code -----
Private Sub Form_Current()

If Not IsNull(Me.ID) Then
Me.Parent.Recordset.FindFirst "ID=" & Me.ID
End If

End Sub

'----- end of suggested code -----

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

(please reply to the newsgroup)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top