Continuous Forms - Reference Individual Records

[NMiller007]

I am working with my first continuous form and am having some trouble accomplishing some things I think I should be able to do.

What I have is an approval form. It lists all unapproved records with a check box which is unchecked (I used the Yes/No field from my query as the Control Source). What I want to do is have the user check the boxes for the records they want approved. Currently, when a box is checked, that record is updated. What I need is a Cancel feature, which will uncheck all boxes.

I could have the check boxes not have the field as the control source, but then I am not sure how I would go through and update the table for each record that is checked.

Also, as a side item, I have a button for each record, which when clicked displays user comments (stored in the same table). Is it possible to only have the button visible if the comment field is not Null? I've tried using the form events to no avail. The reason I'm using a button is to get around the fact that form text boxes won't grow. The button pops up a text box, which displays the comment. Right now, if comment is Null, I just have the box say "No Comment."

Thanks!

 

[Remou]

You can create a query to update the records, or you can loop through the recordset for the form, either of these options can be run from a button in the header of your continuous form. You can use Conditional Formatting to disable the notes button, if there are no comments. You may wish to look at Shift + F2 for your notes: it zooms a field. The zoom can also be run through code.

 

[NMiller007]

I think I'd be more comfortable using a query to update the records (RecordSets have been giving me fits recently). I'm using a query to populate the form. Basically any record that is not approved shows up. When the cancel button is clicked, how can I tell which records appeared on the form? I don't want to unapproved records that were approved on previous sessions.

Will the query I used to create the form still be active, so I could just modify all records in the query to be unnapproved (Could you give me an example of a simple query that would set all Approved field to 0)?

I like the zoom idea. I had done something similar on another project, but since I couldn't zoom with a right click on the field I dismissed that as an option. I have to remember Shift+F2. Maybe I'll just have the field zoom On Focus.

Thanks!

 

[PHV]

A starting point for your Cancel button Click event procedure:
With Me.Recordset
.MoveFirst
While Not .EOF
Me![check box] = False
.MoveNext
WEnd
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

Hm, I spoke too soon. If you do not have an approved date, we are stuck with the recordset. It will be easy. Here is a start:

'This line requires a reference to the DAO 3.x Object Library
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.MoveFirst
Do While Not rs.EOF
  rs.Edit
  rs!Approved=False
  rs.Update
Loop

Set rs = Nothing

 

[NMiller007]

Thanks to both of you! The RecordSet method doesn't look very complicated. I think when I first tried to learn about RecordSets, I tried to start at level 10. Maybe I just need to go back and start working with some simple examples.

I do have an approved date. I didn't even think about going through and setting anything with an approved date after this session started as unapproved.

Well, I added that code to the button and it worked perfectly.

Thanks again!

 

[missinglinq]

Another idea on the zoom would be to drop the button and use:

Private Sub Text14_GotFocus()
  [b]If Not IsNull(Text14) Then[/b]
     DoCmd.RunCommand acCmdZoomBox
  [b]End If[/b]
End Sub

The zoom effect will only work if the textbox isn't empty. If you find it more effective to not have to enter notes into the small textbox, just drop the bold code lines and the box will zoom for data entry!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!