Disabling Field in a form

[spn200286]

Good Afternoon

I want to disable a field (ClientGender)in a form (called frmSales) if ClientType does not = "Private Individual" , iv done this before and remeber it to be reasonably simple but that was a while ago and im afraid iv forgotten nearly all i knew about VB (i was never that good at it to begin with)

i built a BeforeUpdate event that goes like

Private Sub ClientGender_BeforeUpdate()
If ClientType = "Private Individual" Then
    ClientGender.Enabled = True
Else
ClientGender.Enabled = False

End Sub

i read somewhere that this needs to be put in two locations? On Current event i beleve

firstly is this code correct/acceptable, secondly where would i find (or type in) the On Current event code?

thank you for any assistance

 

[JamesMcS]

OK - you've put Clientindustry in the body text, but Clienttype in the code. Also you've mentioned an on click but the code says beforeupdate().

It should be sufficient just to put this in the on current event, which is a form event - in the form properties.

 

[CBrighton]

I'd add it in the AfterUpdate of ClientIndustry / ClientType (whichever it is) too so that it fires when the field used as criteria is updated as well as when the record is loaded.

 

[spn200286]

edited sorry for not paying attention/having muddled notes

Trying that now, thanks for your reply

 

[spn200286]

Im still having issues with this.
I added the code to the on current event, and get a compiler error.
Nothing happens if i have it just on the AfterUpdate(), BeforeUpdate() or any update!

below is the code i have

Private sub ClientGender_AfterUpdate()
If ClientType = "Private Individual" Then
ClientGender.Enabled = TRUE
Else
ClientGender.Enabled = False
End Sub

----------------------------------------

Private Sub Form_Current ()
If ClientType = "Private Individual" Then
ClientGender.Enabled = TRUE
Else
ClientGender.Enabled = False
End Sub

Its the Private Sub Form_Current() that's throwing a compiler error (exception and highlighted yellow)

if anyone can help id be really grateful (and so would my laptop, which may be going through the wall soon)

 

[JamesMcS]

What line is highlighted, and what's the exact error message?

 

[JamesMcS]

And when does it happen exactly? When you move to a different record? or when you move out of clientgender?

 

[CBrighton]

Can you attach a sample database with the form and table (containing some dummy data which follows the format of the live data)?

 

[vbaInet]

You're not using ClientGender's default BeforeUpdate event. You've built your own custom function and it shouldn't be so. The BeforeUpdate event of a control has a required Cancel parameter.

Delete your event, look at the list of events in the property sheet for that control, click the elipsis button, select Code Builder and hit OK.

 

[spn200286]

im posting off a different computer at the moment, will copy the database over in a moment

moving from design to form view brings up the error

Compile error:
Invalid outside procedure

the code highlighted is Private Sub Form_Current() (the code is set out exactly as above)

 

[spn200286]

frmSales is the form in question!

i rebuilt the beforeupdate query aswell, not sure why that was like that

 

[CBrighton]

Ah, I only have Office 2k3 so can't open an accdb file.

If you can convert a copy to mdb format I'll have a look otherwise I'm sure someone else will have a look at the accdb version.

 

[vbaInet]

I just mentioned it to prompt you of the format of that event. The events you should be using are the Current event of the form and the AfterUpdate event of ClientType.

 

[JamesMcS]

OK - the error you're getting is because

CommandBars("Menu Bar").Enabled = False

isn't in a sub. When do you want this to kick off? I'm guessing when the form opens? You need to cut and paste into that event.

 

[JamesMcS]

Also you've got no End If in form_current

 

[spn200286]

03 format

sorry for being a tad useless with this!

JamesMcS thats on the frmreportmenu would that inter-fear with a different form?

 

[JamesMcS]

Didn't think to check the bound column of the combo box. You also had the code in clientgender's beforeupdate, rather than clienttype's afterupdate. I've changed it to On Change:

Private Sub ClientType_Change()
If ClientType.Value = 2 Then
    cboClientGender.Enabled = True
 
    Else
 
    cboClientGender.Enabled = False
 
End If
End Sub
Private Sub Form_Current()
If ClientType.Value = 2 Then
    cboClientGender.Enabled = True
 
    Else
 
    cboClientGender.Enabled = False
 
End If
End Sub

 

[JamesMcS]

re: commandbars line - put it wherever it's needed, but inside a sub/function - can't just be on its own like that.

 

[JamesMcS]

Also don't forget to associate this code with the event on your form

 

[CBrighton]

Didn't think to check the bound column of the combo box. You also had the code in clientgender's beforeupdate, rather than clienttype's afterupdate. I've changed it to On Change:

Private Sub ClientType_Change()
If ClientType.Value = 2 Then
    cboClientGender.Enabled = True
 
    Else
 
    cboClientGender.Enabled = False
 
End If
End Sub
Private Sub Form_Current()
If ClientType.Value = 2 Then
    cboClientGender.Enabled = True
 
    Else
 
    cboClientGender.Enabled = False
 
End If
End Sub

Yup, this is the code I ended up with.

Except I didn't change the name of ClientGender to cboClientGender.

If you reuse this code remove the cbo prefix from the VBA or add it to the ClientGender combobox name.