Enabling a control based on another

[MattioMatt]

Hi Minty,

Thanks for going into further detail! I'd like the combo control to be enabled or disabled based on the value of the chkStatus (which comes from another table) whether it is a new record or existing record.

I think this is why I'm having the problem on a new record as the if statements are looking for values that do not yet exist.

Is there a way to use the On Current Event to look up a record in another table/query and use that to enable/disable the control? I think the problem with trying this is that there will be no ID to relate to as again even though it is a foreign key, because it is a new record it's not been saved.

My other thought is to disabled all but one of the controls (the first is a required field) and use the before update on that control to then enabled the other controls/do the if statement I've been trying to do as that way it will have forced it to created the record.

 

[Minty]

Yes you can do that - How are you populating the chkStatus field at the moment?

 

[MattioMatt]

It's populated from a query (or so I assume)
The form in question has a query bound to it, within that query I am pulling fields from two tables. chkStatus is coming from a field in one of the two tables, I've simplified what I am trying to do below:

Table1: (feed currently comes from excel spreadsheet)
-----------
SO_ID
SOName
ActiveStatus (chkStatus)

Table2:
--------
PT_ID
SO_ID
DateCompleted
DASStatus (cmboDASStatus)

Query (From is bound on):
----------------------------
PT_ID
SO_ID
DateCompleted
ActiveStatus
DASStatus

The form is opened by clicking a button which populates the SO_ID from the previous form in which it was opened from:

Private Sub NewTestBtn_Click()
DoCmd.OpenForm "frmTestDetail"
Forms!frmTestDetail![SO_ID] = Me![SO_ID]
End Sub

I am assuming (as I don't know for sure) that it is pulling the Status check from the SO_ID but the disabling of controls is based there being a record in table2 so whilst in theory I thought it wouldn't be a problem with the if statement because it had already pulled whether it was checked or not.

 

[Minty]

That makes sense. Does the calling form have the chkStatus field available to it?

 

[MattioMatt]

When you say calling the form - do you mean opening it without using the code?

 

[Minty]

Sorry no - the form where NewTestBtn is being pressed.

 

[MattioMatt]

Apologies Minty my fault for not understanding not yours.
It's available as a control in a tab on the previous screen yes. The NewTestBtn is on one of the tabs, and the form itself where the button is on is bound to a query that pulls the information from table1 so I would say yes it is available.

 

[MattioMatt]

Minty, I think you have just given me the push in the right direction I needed!

Quickly had a play around with the following on the NewTestbtn

Private Sub NewTestBtn_Click()
DoCmd.OpenForm "frmTestDetail"
Forms!frmTestDetail![SO_ID] = Me![SO_ID]
Forms!frmTestDetail![ActiveStatus] = Me![ActiveStatus]
If ActiveStatus = False Then
Forms!frmTestDetail![cmboDASStatus].Enabled = False
End If
End Sub

On a quick test it seems to be working exactly how I'd like it to work.

 

[Minty]

There are a myriad ways of doing this but this is simple and may help you with other things ; You can pass the value of it to the form you are opening using the openargs property. Something like

Private Sub NewTestBtn_Click()

 [COLOR="Green"]             'This will now pass the value as a string into the open args property. The number of comma's is important![/COLOR]
DoCmd.OpenForm "frmTestDetail", acNormal, , , , , [COLOR="Red"]Me.ActiveStatus[/COLOR] 

Forms!frmTestDetail![SO_ID] = Me![SO_ID]

End Sub

Then in your form frmTEstDetail you can use this to set the check box. It's value is available though the Me.OpenArgs property and will be Null,-1,0 depending on the value on the calling form. Use this to determine what you want to do with your combo box.

 

[MattioMatt]

Thanks for all your help Minty and everyone else that as contributed! Appreciate all of the help and support!