use checkboxes to enter date on continuous form

[Chris Freeman]

I have a continuous form in Access 2003 with checkboxes to indicate which
records get processed by entering a date in the date field.

The code:
Me.Void_Import_Date = Me.Text74
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Me.Check88 = True

The problems:
1. using plain checkboxes results in all checkboes getting clicked when you
click one box.
Solution:
I put the boxes in a frame, and this picks up just the associated values for
the record.

New problems:
1. Frame doesn't seem to allow unchecking the box.
2. The check boxes aren't changing from unchecked to checked status.
3. Can't seem to have the check box recognized to use a "Select All" code: I
get an error stating that I can't assign a value to the object:

Private Sub chk_SelectAll_Click()

If Me.chk_SelectAll = False Then
Me.Frame76.Controls!Check88 = True
Else
Me.Frame76.Controls!Check88 = True
End If

End Sub

Thanks for any help in advance


Is there a work around, or do I have to use

 

[Jeanette Cunningham]

Hi Chris
here are a couple of posts that explain how to get this work, I think these
explanations are easy to follow, but ask again if that's not the case for
you.

http://groups.google.com/group/micr...nk=gst&q=checkbox+continuous#ff6eb7723cabad06

http://groups.google.com/group/micr...nk=gst&q=checkbox+continuous#779eb4cc046ee633


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Chris Freeman]

Jeanette,
I followed the overall approach, and things work fine:
1. Created a new field in the original table, and bound the check box to the
field
2. Checking the box on and off assigns the date value to the field, and
removes the value from the field.

This works great for an invidual record. But in production, we could have
hudreds of records, so I also have a Select All check box. I'm using this
code:

Set currdb = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "select * from qry_Void_Return", currdb, adOpenDynamic,
adLockOptimistic
C = rst.RecordCount
RC = 0

Do While Not rst.EOF
If Me.chk_SelectAll = -1 Then
P = rst.Fields![PROPERTY_ID]
Me.chk_Void = -1
Me.Void_Import_Date = Date
Else
P = rst.Fields![PROPERTY_ID]
Me.chk_Void = 0
Me.Void_Import_Date = ""
End If
rst.MoveNext
RC = RC + 1

Loop
rst.Close

The query opens, and I'm using the P indicator to track which record the
code is looking at. The code does cycle through the records, as the P value
changes matching the ID in the query.

But when I look at the form and the table, only the first record updates.
even though all indications show the record moving, the update doesn't seem
to. I added the C=rst.RecordCount, and it returns a value of -1. Any idea on
this?

Thanks

--
Chris Freeman
IT Project Coordinator

Hi Chris
here are a couple of posts that explain how to get this work, I think these
explanations are easy to follow, but ask again if that's not the case for
you.

http://groups.google.com/group/micr...nk=gst&q=checkbox+continuous#ff6eb7723cabad06

http://groups.google.com/group/micr...nk=gst&q=checkbox+continuous#779eb4cc046ee633


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Chris Freeman]

Jeanette,
Finally figured it all out. had to create a couple of wicked work arounds
that I'm sure the pro community is turning over in thier grave at, but it
works. the forms opens with records with inidividual check boxes thurn
populate/unpopulate and a Select All button that populates all records,
unpopulates all records:

DT = Me.Void_Import_Date.Value

If Me.chk_SelectAll = -1 Then
Set currdb = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "select * from qry_Void_Return", currdb, adOpenDynamic,
adLockOptimistic
Do While Not rst.EOF
P = rst.Fields![PROPERTY_ID]
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tbl_Check_Reissue SET
tbl_Check_Reissue.[Void Import Date] = Date(), tbl_Check_Reissue.[Manual
Void] = -1 WHERE (((tbl_Check_Reissue.Property_ID)=" & P & "));"
DoCmd.SetWarnings True
rst.MoveNext
Loop
Else
Set currdb = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "select * from tbl_Check_Reissue WHERE
(((tbl_Check_Reissue.[Void Import Date])=#" & DT & "#));", currdb,
adOpenDynamic, adLockOptimistic
Do While Not rst.EOF
P = rst.Fields![PROPERTY_ID]
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tbl_Check_Reissue SET
tbl_Check_Reissue.[Void Import Date] = """", tbl_Check_Reissue.[Manual Void]
= 0 WHERE (((tbl_Check_Reissue.Property_ID)=" & P & "));"
DoCmd.SetWarnings True
rst.MoveNext
Loop
End If

rst.Close

'refresh form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70


Thanks for your input
--
Chris Freeman
IT Project Coordinator

Hi Chris
here are a couple of posts that explain how to get this work, I think these
explanations are easy to follow, but ask again if that's not the case for
you.

http://groups.google.com/group/micr...nk=gst&q=checkbox+continuous#ff6eb7723cabad06

http://groups.google.com/group/micr...nk=gst&q=checkbox+continuous#779eb4cc046ee633


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Chris, glad that you got it working.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,
Finally figured it all out. had to create a couple of wicked work arounds
that I'm sure the pro community is turning over in thier grave at, but it
works. the forms opens with records with inidividual check boxes thurn
populate/unpopulate and a Select All button that populates all records,
unpopulates all records:

DT = Me.Void_Import_Date.Value

If Me.chk_SelectAll = -1 Then
Set currdb = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "select * from qry_Void_Return", currdb, adOpenDynamic,
adLockOptimistic
Do While Not rst.EOF
P = rst.Fields![PROPERTY_ID]
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tbl_Check_Reissue SET
tbl_Check_Reissue.[Void Import Date] = Date(), tbl_Check_Reissue.[Manual
Void] = -1 WHERE (((tbl_Check_Reissue.Property_ID)=" & P & "));"
DoCmd.SetWarnings True
rst.MoveNext
Loop
Else
Set currdb = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "select * from tbl_Check_Reissue WHERE
(((tbl_Check_Reissue.[Void Import Date])=#" & DT & "#));", currdb,
adOpenDynamic, adLockOptimistic
Do While Not rst.EOF
P = rst.Fields![PROPERTY_ID]
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tbl_Check_Reissue SET
tbl_Check_Reissue.[Void Import Date] = """", tbl_Check_Reissue.[Manual
Void]
= 0 WHERE (((tbl_Check_Reissue.Property_ID)=" & P & "));"
DoCmd.SetWarnings True
rst.MoveNext
Loop
End If

rst.Close

'refresh form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70


Thanks for your input