On Click event triggers when clicking on a column

[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

 

[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

 

[Robert]

Thanks. I changed the if statement to

If Not rs.EOF And Not rs.NoMatch Then

and the result was the same.

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

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

 

[Dirk Goldgar]

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 -----

 

[Jeanette Cunningham]

Good catch Dirk.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

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)

 

[larry glover]

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

 

[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

 

[Robert]

Thanks. Will give it a try.

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)

 

[Robert]

Thanks. It is working now.

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)