Setting a Value in a Field based on Selection in Combo Box

[sross81]

Hello,

I have a value in a table called WeekendAdmit. It is a Yes/No datatype. If the user selects from the combo box any UnavailableReason I want the Weekend Admit field to be updated to Yes.

Originally this was done in a macro, but I wanted to learn how to do it in VB. I get an error called Object Required. I tried putting Set in front of the Me.WeekendAdmit = 1 and that doesn't work either.

Any ideas?

Here is my code:
If Me.UnavailableReason Is Not Null Then
Me.WeekendAdmit = 1
End If
If Me.UnavailableReason Is Null Then
Me.WeekendAdmit = 0
End If

 

[boblarson]

It should go in the combo's After Update event. And the optimized code would be:

Me.WeekendAdmit = (Len(Me.UnavailableReason & vbNullstring) > 0)

 

[boblarson]

Oh, and just for your information, you do not use Is Null or Is Not Null when in VBA. You have to use

If IsNull(Me.SomeControl) Then

or

If Not IsNull(Me.SomeControl) Then


but I used LEN instead of null because if someone backspaces and leaves nothing, that isn't NULL but an empty string. And the Len way of doing it (checking the length of the value, while adding on an empty string at the end - vbNullString - avoids the "invalid use of null" error for the len function if it is indeed null.)

 

[sross81]

Thank you for the information on how to write proper VB code.

I do have it in the after update event and I switched my Is null to be in the front and the object required error went away when I did that and the field updates like it should now.

I would like to understand the last part in case a user does put a space. How is this actually assigning a Yes or No? Should it somehow be in this vbNullstring?

Me.WeekendAdmit = (Len(Me.UnavailableReason & vbNullstring) > 0)


If Not IsNull(Me.UnavailableReason) Then
Me.WeekendAdmit = 1 ??
End If
If IsNull(Me.UnavailableReason) Then
Me.WeekendAdmit = 0 ??
End If