IF Then Else VBA statement

[ui7598h]

Hello, I have a form where user enters a case number into a text box and on the after update property i need one of two labels to become visible based on if the case number is on the table tblQualityAudit. the case number is numeric, and the field property is set to double on the table as well as the text box property. below is the code that I have.


Private Sub txtLookup_AfterUpdate()
Dim dblCase As Double
dblCase = txtLookup
If strCase = DLookup("case", "tblQualityAudit") Then
Me.lblReject.Visible = True
Else
Me.lblGood.Visible = True
End If
End Sub




when testing, the lblGood appears for everything. even if the case number is in the tblQualityAudit. not sure if there is an issue with the DLookup or maybe I am missing something. any help would be appreciated.

Also if you can give insight on how to make the label go back to not visible when the user goes to enter a new value that would be awesome too


Thanks in advance
Steve

[pbaldy]

Well for starters, you set dblCase and then test strCase.

http://www.baldyweb.com/OptionExplicit.htm

[ui7598h]

Private Sub txtLookup_AfterUpdate()
Dim dblCase As Double
dblCase = txtLookup
If dblCase = DLookup("case", "tblQualityAudit") Then
Me.lblReject.Visible = True
Else
Me.lblGood.Visible = True
End If
End Sub

I originally declared variable as string, and pasted the code. i thought after starting the post that it may be because it is a numeric value it needed to be declared as Double and i just changed what I had pasted. I missed changing the strCase to dblCase. I fixed and pasted above.

[June7]

Is this in a Datasheet or Continuous form? Programmatically setting control properties will be reflected by ALL instances of the control - after all, there is actually only one control. Textbox and combobox have Conditional Formatting to accomplish dynamically setting font and background colors and enabled properties. Visibility is not available with Conditional Formatting. Can use an UNBOUND textbox with Conditional Formatting to serve as the 'label' - change colors or Enabled property.

[John_G]

Two things to note:

First, you see the labels on each record because they are unbound controls, and stay visible/hidden until you change them. In your after update procedure, when make one label visible, you have to hide the other one - so each part of the condition will set one label to true and the other label to false.

Secondly,you have to have the same code in the On Current event procedure, so that the labels are displayed correctly when you move from one existing record to another.
That will work only if you have a field in your table where you keep the value you selected in the lookup combo.

[pbaldy]

Sounds like your DLookup() needs a criteria. As it is, it's only looking at the first record.

DLookup Usage Samples

[ui7598h]

pbaldy,

Thank you. it was in need of criteria. changed code to

Private Sub txtLookup_AfterUpdate()Dim dblCase As Double
dblCase = txtLookup
If dblCase = DLookup("case", "tblQualityAudit", "case = '" & dblCase & "'") Then
Me.lblReject.Visible = True
Else
Me.lblGood.Visible = True
End If
End Sub

it now works flawlessly.

Thanks to all who replied

[pbaldy]

Happy to help.