Bound checkbox in datasheet subform

  • Thread starter Thread starter Rich_in_NZ
  • Start date Start date
R

Rich_in_NZ

-Access 2003 (database in 2000 format) front end, MySQL server backend

Hi,

The issue I'm having seems to be a common one, but sadly my Google searches
have yet to find the remedy. I have a subform in datasheet view with a
'SELECT DISTINCT' query on a MySQL server table as it's Record Source. The
subform has a bound checkbox, which I want to be able to check to select a
specific record then run an update query.

When the checkbox is bound, the Click() event for the checkbox doesn't seem
to execute. I click the checkbox, no tick appears, and nothing happens. I put
a msgbox in to display the unique ID of the record I select but it never
appears.

I then tried using an unbound checkbox. This almost works as the Click()
event is detected and the update occurs for the selected record, but each
checkbox changes regardless of which checkbox I click.

The most annoying thing is that I have a copy of another database which
utilises checkboxes in a subform but it works fine. I copied the VBA code
exactly and it won't work. The only difference is that the record source of
the working subform is based on a local table, not a linked server table.

Any help would be most appreciated,
Rich

P.S: Here is the Click() event code.....

Private Sub chkReviewed_Click()
Dim dtNow
dtNow = Now()
MsgBox "QCP# = " & Me.QCP_, vbOKOnly
If Me.chkReviewed = True Then
Me.ReviewDate = dtNow
CurrentDb.Execute "UPDATE tblQCP_Server_Null SET ReviewDate='" &
Format(dtNow, "yyyy-mm-dd hh:nn:ss") & "' WHERE [QCP#]=" & Me.QCP_
Else
Me.ReviewDate = Null
CurrentDb.Execute "UPDATE tblQCP_Server_NotNull SET ReviewDate=NULL
WHERE [QCP#]=" & Me.QCP_
End If
[Forms]![frmReviewQCP].RefreshSubform5
End Sub
 
On Sun, 22 Feb 2009 19:33:01 -0800, Rich_in_NZ

This is by design. If you have an UNBOUND checkbox, the value changes
for ALL rows. And if you think about it a bit more, you'd have to
agree that's the right approach.
If you want a checkbox per row, you may need another table, with a
YesNo field and a Long Integer (assuming the PK of the primary table
is a long integer). You populate that table, and have the user select
rows.

-Tom.
Microsoft Access MVP
 
Hi Tom,

Thanks for the reply....
I added the table as you suggested (at least how I interpreted it to mean),
which is populated when the form is loaded (with an append query) and the
subform is made up of data from the original table with an inner join on the
new table. Unfortunately, the results are the same. With the checkbox bound
to the field in the new table it still doesn't seem to detect the Click()
event, no checkboxes ticked and no update sql query run. Have I created the
table correctly as you suggested? It has a Yes/No field and the PK is a long
int named the same as the PK in the orignal table.

Tom van Stiphout said:
On Sun, 22 Feb 2009 19:33:01 -0800, Rich_in_NZ

This is by design. If you have an UNBOUND checkbox, the value changes
for ALL rows. And if you think about it a bit more, you'd have to
agree that's the right approach.
If you want a checkbox per row, you may need another table, with a
YesNo field and a Long Integer (assuming the PK of the primary table
is a long integer). You populate that table, and have the user select
rows.

-Tom.
Microsoft Access MVP

-Access 2003 (database in 2000 format) front end, MySQL server backend

Hi,

The issue I'm having seems to be a common one, but sadly my Google searches
have yet to find the remedy. I have a subform in datasheet view with a
'SELECT DISTINCT' query on a MySQL server table as it's Record Source. The
subform has a bound checkbox, which I want to be able to check to select a
specific record then run an update query.

When the checkbox is bound, the Click() event for the checkbox doesn't seem
to execute. I click the checkbox, no tick appears, and nothing happens. I put
a msgbox in to display the unique ID of the record I select but it never
appears.

I then tried using an unbound checkbox. This almost works as the Click()
event is detected and the update occurs for the selected record, but each
checkbox changes regardless of which checkbox I click.

The most annoying thing is that I have a copy of another database which
utilises checkboxes in a subform but it works fine. I copied the VBA code
exactly and it won't work. The only difference is that the record source of
the working subform is based on a local table, not a linked server table.

Any help would be most appreciated,
Rich

P.S: Here is the Click() event code.....

Private Sub chkReviewed_Click()
Dim dtNow
dtNow = Now()
MsgBox "QCP# = " & Me.QCP_, vbOKOnly
If Me.chkReviewed = True Then
Me.ReviewDate = dtNow
CurrentDb.Execute "UPDATE tblQCP_Server_Null SET ReviewDate='" &
Format(dtNow, "yyyy-mm-dd hh:nn:ss") & "' WHERE [QCP#]=" & Me.QCP_
Else
Me.ReviewDate = Null
CurrentDb.Execute "UPDATE tblQCP_Server_NotNull SET ReviewDate=NULL
WHERE [QCP#]=" & Me.QCP_
End If
[Forms]![frmReviewQCP].RefreshSubform5
End Sub
Click to expand...
Click to expand...
 
Back
Top