looping thru the recordset of a form

[uscitizen]

i have a table (row source) called "Lesions: Non-Target" and it's got a primary key comprised on "Patient_ID" and "Test_Number" (1,2,3,4....) where the latter can have more than one number with an unpredictable upper limit on it per patient.

the interactive form is displayed as a continuous form and displays just those records matching a single "Patient_ID" number:

[Patient_ID]=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient_ID]

in the footer section of the said form, there's a button on it which when clicked launches a report that prints what the user is essentially looking at (for archival purposes).

in addition to the data being stored in the underlying recordset, there are two more fields: Form_LockedBL (y/n field) and Form_LastPrintedBL (a date field).

what i need to happen when this button's pressed is to have the values of Form_LockedBL set to True and Form_LastPrintedBL to NOW() for every record that matches the filter above, i.e. for every record the user's looking at on the screen.



“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[vbajock]

I am a little unclear from your post on whether Form_LockedBL (y/n field) and Form_LastPrintedBL (a date field) are bound to data fields in your underlying recordset. If they are, you have to set up either an ADO or DAO recordset based on the RecordsetClone property of the form. Look at recordsetclone in the help file, there is example code that will show you how to do it. Once you have the clone set up, code a do loop to evaluate and update your records, then requery or refresh your form to show the results

 

[uscitizen]

hello, vbajock!

the short answer to your question is yes, the two fields do definitely exist in the underlying table/recordsource.

the unexpurgated answer is as under:

following's the code for the 'Print' button
============================================================

Private Sub Command39_Click()
On Error GoTo Err_Command39_Click

Dim stDocName As String
Dim Last_printedBL As Date


Me.Refresh

MsgBox "setting locked to true and evaluating ""now"""
Me!Form_LockedBL = True
Me!Last_printedBL = Now()

MsgBox "dim rs as dao.recordset statement"
Dim rs As dao.Recordset
MsgBox "set rs = ms.recordsetclone statement"
Set rs = Me.RecordsetClone
If Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
MsgBox "setting form_locked value to me!..."
rs("Form_LockedBL").Value = Me!Form_LockedBL.Value
MsgBox "setting form_printed value to now"
rs("Last_PrintedBL").Value = Me!Last_printedBL.Value
rs.Update
rs.MoveNext
Loop
End If
Set rs = Nothing



stDocName = "RECIST Disease Evaluation: Baseline"
OpenReport_FX stDocName, acNormal, "", "[Patient Number] = " & Me.[Patient Number]

Me.EnableEditsBtn.Visible = True
' MsgBox "Edits button should have appeared"
Me.EnableEditsBtn.SetFocus
Me.Command39.Visible = False
' MsgBox "Print button should have disappeared"
Me.AllowEdits = False
' MsgBox "Disallowed Edits on current form"
' now take care of the fact that disallowing edits on current form does not disable
' the calendar buttons in the detail section
Dim ctl As Control
For Each ctl In Me.Detail.Controls
Select Case ctl.ControlType
Case acCommandButton
ctl.Enabled = False
End Select
Next ctl

' MsgBox "Disallowed edits for dates"

Exit_Command39_Click:
Exit Sub

Err_Command39_Click:
MsgBox Err.description
Resume Exit_Command39_Click

End Sub

============================================================

there are two more vba codings that probably require ministering to.

the next is for a button 'EnableEditsBtn' which on getting clicked enables the form/controls for editing

============================================================

Private Sub EnableEditsBtn_Click()
Dim ctl As Control
For Each ctl In Me.Detail.Controls
Select Case ctl.ControlType
Case acCommandButton
ctl.Enabled = True
End Select
Next ctl

Me!Form_LockedBL = False
MsgBox "dim rs as dao.recordset statement"
Dim rs As dao.Recordset
MsgBox "set rs = ms.recordsetclone statement"
Set rs = Me.RecordsetClone
If Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF
rs.Edit
MsgBox "setting form_locked value to me!..."
rs("Form_LockedBL").Value = Me!Form_LockedBL.Value
rs.Update
rs.MoveNext
Loop
End If
Set rs = Nothing

' MsgBox "Form_Locked set to False"
Me.AllowEdits = True
' MsgBox "Edits are allowed again"
Me.Command39.Visible = True
' MsgBox "Print button is now visible"
Me.Command39.Enabled = True
' MsgBox "Print button is now enabled"
Me.Command39.SetFocus
' MsgBox "Print button how has focus"
Me.EnableEditsBtn.Visible = False
' MsgBox "edit button has disappeared"
End Sub

============================================================

lastly, i need to be concerned with what happens to the buttons as the user scrolls from one record to the next, so for the event property 'OnCurrent', i have this one
============================================================
Private Sub Form_Current()
If mintLASSecurityLevel <> 11 And Me!Form_LockedBL = True Then
MsgBox "sec level is " & mintLASSecurityLevel & " user is " & LAS_GetUserName & " flag is " & Me!Form_LockedBL
For Each ctl In Me.Detail.Controls
Select Case ctl.ControlType
Case acCommandButton
ctl.Enabled = False
End Select
Next ctl
Me.AllowEdits = False
Me.EnableEditsBtn.Visible = True
Me.Command39.Visible = False
Me.Form_Locked_Label.Visible = False
ElseIf mintLASSecurityLevel <> 11 And Me!Form_LockedBL = False Then
MsgBox "sec level is " & mintLASSecurityLevel & " user is " & LAS_GetUserName & " flag is " & Me!Form_LockedBL
For Each ctl In Me.Detail.Controls
Select Case ctl.ControlType
Case acCommandButton
ctl.Enabled = True
End Select
Next ctl
Me.AllowEdits = True
Me.EnableEditsBtn.Visible = False
Me.Command39.Visible = True
Me.Form_Locked_Label.Visible = False
ElseIf mintLASSecurityLevel = 11 Then
MsgBox "sec level is " & mintLASSecurityLevel & " user is " & LAS_GetUserName & " flag is " & Me!Form_LockedBL
For Each ctl In Me.Detail.Controls
Select Case ctl.ControlType
Case acCommandButton
ctl.Enabled = False
End Select
Next ctl
Me.EnableEditsBtn.Visible = False
Me.Command39.Visible = False
If Me!Form_LockedBL = True Then
Me.Form_Locked_Label.Caption = "Locked to edits"
Me.Form_Locked_Label.ForeColor = 255
Else
Me.Form_Locked_Label.Caption = "Edits Enabled"
Me.Form_Locked_Label.ForeColor = 0
End If
End If
End Sub

============================================================

to test this all out, i have a Patient_ID with three values of Test_Number in the record source table underlying the form and attempt to simulate a user's deciding to print a report(which should simultaneously lock all records of this continuous form's from editing and display a button 'Enable Edits' while also causing 'Print' button to disappear). then i attempt to 'Enable Edits' again (which should do just that for all three records that can be viewed w/ this form and hide the 'Enable Edits' button and cause the 'Print' button to reapper).

i position the pointer onto the 2nd of the three records and hit the Print button (command_39) and the code runs seemingly successfully: the three records for this Patient number have the same datetime and the check-box 'on' in the relevant fields under discussion. continuing w/ the test, i position the pointer onto the third and final record, hit the 'Enable Edits' button and the code didn't seemed to work OK - the Form_LockedBL column in the recordsource is now blank ('no') just for the third record of this Patient_ID's

can you find the achilees heel in all this?




“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[antoineh]

these answer are ok. But... it is always better to avoid looping on a recordset and use queries instead (lot of code, difficult to maintain if changes in tables etc...).
I'd rather :
- use queries for providing the data to the form and sub forms
- use that very same queries, for providing the data inside a modification query, where you tell to set the date field to Now

how to tell the query to select the records that are in the forms :
in the filter field of the query (for the Where clause), put something like :
[Forms].[name_of_the_Form].[name_of_the_control_of the id]

the less vba code, the better...
Antoine

 

[uscitizen]

vbajock,

after some further testing following my response, i found the 'problem' does not present itself when i close and re-open the form following clicking either the 'Print' or the 'Enable Edits' button. my underlying assumption earlier as i simulated a user's interaction was that the user came to the conclusion following having printed the report that further edits were required and wished to enable the edits before leaving the form. this is probably not realistic (in the sense that it could be found to frequently occur) but it would probably be good to program for in the eventuality. so, i guess i'm back where we left off earlier.

as to the response of antoineh's, i'm not sure i really follow that.

“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln