My Assistant
Site Message
(Message will auto close in 2 seconds)
 If statement for dependency between two option groups
|
|
 Aug 22 2006, 03:50 AM
Post
#1
|
|
|
UtterAccess Addict Posts: 176 From: USA  |
Hi UA
I have a problem making the code for an "If" statement and was hoping that you could help me. I have a table with the following relevant fields: tblPunchList PunchListID pk autonumber dtCloseContractor date/time dtCloseClient date/time StatusContractor yes/no StatusClient yes/no On my main form I have two separate option groups; "fogStatusContractor" and "fogStatusClient" referring to the above fields. Both option groups are either "Open" or "Closed" (Closed=Yes, Open=no). I like to prevent users from Closing the "fogStatusClient" option group before the "fogStatusContractor" is "closed", i.e. if "fogStatusContractor" is Open (StatusContractor field in table equal "No"), then "fogStatusClient" must be "Open" (StatusClient field in table equal "No"). I write the date into my table, when items are closed. CODE Private Sub fogStatusClient_AfterUpdate() Me.txtdtPLCloseClient = Date End Sub Private Sub fogStatusClient_BeforeUpdate(Cancel As Integer) If (Me.StatusContractor = False) Then 'MsgBox "Settting Date for Contractor Closed: " & Now() Me.StatusClient = False MsgBox "You can not close the item before the item has been closed by the Contractor" Else MsgBox "Settting Date for Client Close: " & Date Me.txtdtPLCloseClient = Date Me.cbofkCloseClientID = lngMyEmpID Me.StatusClient = True End If End Sub Ultimately, I do not want to have the message boxes to appear, but these are just used for information during development. Currently, the above code does not prevent a change in "StatusClient" even though "If" statement is not valid. Can some one help? Lastly, what is a good syntax for an option group control? I have currently used fog for Frame Option Group. |
 |
 
|
 |
Replies
(1 - 3)
|
Aug 22 2006, 07:36 PM
Post
#2
|
|
|
UtterAccess VIP Posts: 18,396 From: Oklahoma City, Oklahoma |
The BeforeUpdate has a Cancel parameter.
To stop the change you need to use: Cancel = True Not sure if this is correct, but is should be something like: CODE Private Sub fogStatusClient_BeforeUpdate(Cancel As Integer)
If (Me.StatusContractor = False) Then 'MsgBox "Settting Date for Contractor Closed: " & Now() Me.StatusClient = False MsgBox "You can not close the item before the item has been closed by the Contractor" ' Cancel the Update Cancel = True Else MsgBox "Settting Date for Client Close: " & Date Me.txtdtPLCloseClient = Date Me.cbofkCloseClientID = lngMyEmpID Me.StatusClient = True End If End Sub |
|
|
|
|
Aug 22 2006, 09:02 PM
Post
#3
|
|
|
UtterAccess Addict Posts: 176 From: USA |
The Cancel = True command seems to prevent users from changing the status in the table as desired, however the option group in the form accepts the change, i.e. the radio button shows "closed" rather than going back to "open".
The code also "locks" users at the current record, i.e. I can not go to the next or previous record using the navigation buttons. This is even if I select the "open" radio button... What do I need to look into here to make this work? |
|
|
|
|
Sep 22 2006, 03:17 AM
Post
#4
|
|
|
UtterAccess Addict Posts: 176 From: USA |
Hi UA
I finally figured out a solution that works for me. Here is the code that I ended up with... no If statement, but purely looking at the value of the option group. I also figured out a way to clear all controls, if the user for some reason re-opens an already closed item. Hope this helps someone else. CODE Private Sub fogStatusClient_AfterUpdate() If (Me.StatusClient = False) Then Me.txtdtPLCloseClient = Null 'setting closed date to Null as defect item has Open status Me.cbofkCloseClientID = Null 'setting closed by to Null as defect item has Open status Me.txtdtPLCloseClient.Locked = True 'lock control until Status Client is closed Me.cbofkCloseClientID.Locked = True 'lock control until Status Client is closed Else Me.txtdtPLCloseClient = Date 'setting date when defect item is closed Me.cbofkCloseClientID = lngMyEmpID 'used to automatically record user who closed defect item End If End Sub Private Sub fogStatusContractor_AfterUpdate() If (Me.StatusContractor = False) Then Me.txtdtPLCloseContractor = Null 'setting closed date to Null as defect item has Open status Me.cbofkCloseContractorID = Null 'setting closed by to Null as defect item has Open status fogStatusClient.Enabled = False fogStatusClient.Value = False Me.txtdtPLCloseClient = Null 'setting closed date to Null as defect item has Open status Me.cbofkCloseClientID = Null 'setting closed by to Null as defect item has Open status Else Me.txtdtPLCloseContractor = Date 'setting date when defect item is closed fogStatusClient.Enabled = True End If 'Me.cbofkCloseContractorID = lngMyEmpID 'used to automatically record user who closed defect item End Sub Private Sub Form_Current() If (Me.StatusContractor = False) Then fogStatusClient.Enabled = False Else fogStatusClient.Enabled = True End If If (Me.StatusClient = False) Then Me.txtdtPLCloseClient.Locked = True 'lock control until Status Client is closed Me.cbofkCloseClientID.Locked = True 'lock control until Status Client is closed Else Me.txtdtPLCloseClient.Locked = False 'unlock control as Status Client is Closed Me.cbofkCloseClientID.Locked = False 'unlock control as Status Client is Closed End If End Sub Let me know, if there is a smarter way of doing this. |
|
|
|
 |
 |
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 19th May 2013 - 03:44 AM |