modifying properties of controls 'wholesale' 5

[uscitizen]

let's say there are a host of command buttons on a form and you'd like to disable them easily (w/o enumerating every last one of their names in statements like button1.enabled = false, .......,button1000.enabled = false), not that there really are 1000s of them but more than you'd want to be bothered coding explicitly. can vba handle that? what if there are a few buttons in the footer (as opposed to the details section where the many live) which you would want to leave alone?

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

 

[uscitizen]

fascinating, captain

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

 

[SUnderwood]

thats the buscuit!

Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
seanunderwood1@hotmail.com

http://www.emergencydatabasesupport.port5.com

 

[uscitizen]

despite all the progress made in implementing this enhancement thus far, i find myself confronting a cunundrum.
the gory details....

i have a form which uses a table as its datasource. this table has both a patient_id and test_number which make up its primary composite index (in this order). although there might be instances when only one record per subject might occur, the majority of patients will have more than one record.

i use this code behind a 'Print' button on the form:

============================================================
Private Sub Command40_Click()
On Error GoTo Err_Command40_Click

Dim stDocName As String
Dim Last_printedBL As Date

Me.Refresh

Me!Last_printedBL = Now()


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

Me!Form_LockedBL = True
Me.EnableEditsBtn.Visible = True
' MsgBox "Edits button should have appeared"
Me.EnableEditsBtn.SetFocus
Me.Command40.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_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.description
Resume Exit_Command40_Click

End Sub

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

i have bolded two lines because of their relevance. it is turning out that all i needed was a single reference to the state of the form for each patient and not one for each level of the composite index, so i guess my question's got to do with how to export the values of Last_PrintedBL (the last time the form was printed) and Form_Locked (a y/n field which is used to test whether or not the form was printed for each subject) to another datasource which could be used later when running the OnCurrent event VBA below?

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

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_Locked
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.Command40.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_Locked
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.Command40.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_Locked
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.Command40.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

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

this vba code's structure is based upon having been used with another form in the mdb, but one which had a datasource with a single record per patient and therefore appropriate. i sense the solution lies in exporting the values of the bolded variables somewhere else where 1) only one record per patient's kept (since a form for a patient either is or isn't printed and 2)the values of the pair of test variables can be accessed w/o requiring any intervention by the user.


i hope the problem description is making sense!


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

 

[uscitizen]

....after giving it a little more thought, a 2nd strategy came to mind: perhaps assigning the visible property to the print button only when the cursor was aligned with the record having the value 1 (one) for test_number. upon getting clicked, the code would assign the same values to the two variables to all records for this same patient_id. thus, no matter which record the pointer was on (test_number 1 or 2 or 3 or....) the rule would be satisfied.

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

 

[Blorf]

Putting aside all the possiblilities, I printed this tread, and will keep it in my top drawer for always and for ever. It has some really cool stuff.

Ascii dumb question, get a dumb Ansi

 

[RoyVidar]

This is a new question, perhaps addressing it in a new thread?

That said, it's a bit complex, and I wont be able to do anything more than some sketching.

In most of the db's I design, I keep a table containing only one record, with a lot of settings. This might be for instance company name, different preferences and settings, timestamps for different events interesting at application level (last backup/archive.). You'll need to decide at what level you need to keep your info, whether it is application level or perhaps per patient (if the latter, you could probably add it to the recordsource of the form and update thru assigning to a bound texctontrol)

Or, the way I usually do it, is dumping the new info to the table. For instance like this:

[tt]dim sSql as string
sSql = "Insert into tblMisc (BlahDate) Values (#" & format$(Now(),"yyyy-mm-dd") & "#)"
docmd.runsql sSql[/tt]

And open a recordset to retrieve it again.

[tt]dim sSql as string
dim rs as DAO.recordset
sSql = "Select BlahDate from tblMisc"
set rs = currentdb.openrecordset(sSql)
if not rs.bof and not rs.eof then
Me!txtSomeBox.Value = rs("BlahDate").Value
end if
rs.close
set rs = nothing[/tt]

If using the Main/parent patient record, the appropriate where condition would probably need to be applied.

If you have buttons in the detail section of a continuous form ("to the right of each record"), they are all considered one button. So hide one, based on some criteria, you'll "hide all". (Conditional formatting gives the possibility of enabling and disabling control based on value, but not buttons)

Adding the same values to all these records "wholesale", I'm not sure I agree. I wouldn't add this info to each record unless I needed to track something per each record. In this scenario, it seems storing the info per patient seems more appropriate.

Still - little sample on looping thru the recordset of a form, assigning a value to one field:

[tt]dim rs as dao.recordset
set rs = me.recordsetclone
if not rs.eof then
rs.movefirst
do while not rs.eof
rs.edit
rs("FieldName").Value = Me!txtSomeBox.Value
rs.update
rs.movenext
loop
end if
set rs = nothing[/tt]

- as always, typed not tested;-)

And, again, if this doesn't give the answers you need, consider posting in a new thread.

Roy-Vidar

 

[uscitizen]

Patient_Id Test_number Last_Printed Form_Lock Blah1 Blah2..

A 1 .. ..
A 2 .. ..
A 3 .. ..

let's say that when we clicked on the print button, the values of Blah1, Blah2 and Patient_ID and Test_number were complete in the underlying table. currently there are two more fields in the form's data source, Last_Printed and Form_lock as there were in the registration form which consumed my interest in the earlier posting. only unlike that form, this form has multiple records per form, so when we try to cobble in the code you originally suggested what we get is the fact that the date and form_locked columns will take on values only when the record pointer is sitting on the record when the button's clicked resulting in a multiplicity of form 'Last_Printed' values even though all the records for this patient were included on the report printed. this suggests that we really needed was just one value per patient. that said, i don't claim to understand your code snippets nor the reference to the where clause, unless....err, well actually there is no unless. i am confused at a higher level but still unclear where this is going.

what do the following mean?

dim sSql as string
sSql = "Insert into tblMisc (BlahDate) Values (#" & format$(Now(),"yyyy-mm-dd") & "#)"
docmd.runsql sSql

And open a recordset to retrieve it again.

dim sSql as string
dim rs as DAO.recordset
sSql = "Select BlahDate from tblMisc"
set rs = currentdb.openrecordset(sSql)
if not rs.bof and not rs.eof then
Me!txtSomeBox.Value = rs("BlahDate").Value
end if
rs.close
set rs = nothing

If using the Main/parent patient record, the appropriate where condition would probably need to be applied.

If you have buttons in the detail section of a continuous form ("to the right of each record"), they are all considered one button. So hide one, based on some criteria, you'll "hide all". (Conditional formatting gives the possibility of enabling and disabling control based on value, but not buttons)

i guess i mean, how's this supposed to help? what's it do that's going to control the display of the buttons while viewing the continuous form?


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

 

[uscitizen]

i took rov-vidar's recomm and started new thread
thread705-793953

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

 

[Tazcat]

hey there. not to beat a dead horse, but this thread is right on target with an issue I am having... I've read through this thread and tried several ways of getting this function to run, but my problem is I am playing with one property not mentioned above. I would appreciate any feedback at all for why I would keep getting the error "object doesn't support this property or method." One further note... I've done similar to this in a2k and it worked fine. This bit is in a2k2 on XP however, and I don't know if that's changed the game any.

Thoughts?

Private Sub btnEdit_Click()
Dim strEditPass As String
Dim ctrl As Control

strEditPass = InputBox("Please enter password:", "Must have password to edit records")

If strEditPass = "mypassword" Then
For Each ctrl In Me.Controls
If ctrl.Locked = True Then
ctrl.Locked = False
End If
Next ctrl
End If

End Sub

 

[RoyVidar]

Hi!

Just guessing, but if you perform this loop, then it performs on all controls on the form. Perhaps not all controls support the .locked property? I think for instance comand buttons do not support it.

Take the advice from further up, to only perform the loop on the types of controls you need it to perform on, for instance:

[tt]For Each ctrl In Me.Controls
Select case ctrl.controltype
case acTextbox, accombobox
If ctrl.Locked = True Then
ctrl.Locked = False
End If
end select
Next ctrl[/tt]

To toggle the locked property:

[tt]ctrl.Locked = not ctrl.Locked[/tt]

Roy-Vidar

 

[Golom]

Tazcat
The best way (in my experience) to handle this is with error trapping
[blue][tt]
Private Sub btnEdit_Click()
Dim strEditPass As String
Dim ctrl As Control

strEditPass = InputBox("Please enter password:", "Must have password to edit records")
On Error Resume Next
If strEditPass = "mypassword" Then
For Each ctrl In Me.Controls
If ctrl.Locked = True Then
ctrl.Locked = False
End If
Next ctrl
End If

End Sub
[/tt][/blue]
If the control doesn't have a "Locked" property then we don't really care ... we just throw the error away.

 

[Tazcat]

bless you both... solved my problem!