Using IIF as a control

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike DFR - 10 Mar 2006 13:17 GMT

Subform in datasheet view.
"If field 1 contains no data, use field 2, else use field 1"
Field 1 is formatted for date, ie "dd-mmm-yy"
Field 2 is formatted for text

The basic program runs as follows;

Sub Combo69_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Machine] = '" & Me![Combo69] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.List6.RowSource = "SELECT DISTINCT [Masterlist].[Customer] FROM
[Masterlist] WHERE [Masterlist].[Machine] = '" & Combo69.Text & "';"
'Clear entry
Me![Combo69] = ""
End Sub

Reply to this Message

Steve Schapel - 10 Mar 2006 16:01 GMT

Mike,

There are a couple of oddities in your code. For one, I wouldn't use
the Text property of Combo69 in the SQL expression, I think it should be...
Me.List6.RowSource = "SELECT DISTINCT [Customer] FROM [Masterlist]
WHERE [Machine] = '" & Me.Combo69 & "'"

Secondly, setting the value of Combo69 to "" does not "clear" it. I
would use...
Me.Combo69 = Null

But apart from that, I'm afraid I missed the meaning of your main
question. Can you explain a bit more about what you want to do?

Signature

Steve Schapel, Microsoft Access MVP

> Subform in datasheet view.
> "If field 1 contains no data, use field 2, else use field 1"
[quoted text clipped - 12 lines]
> Me![Combo69] = ""
> End Sub

Reply to this Message