Procedure for Message Box if value entered in text box has not been assigned 1

[THWatson]

A form, which has a subform, to edit donations from church donors.

In a form, the user can enter a number in a text box. If the number is valid, the subform is populated with data.

What I want to do is have a procedure so that if the number entered by the user is not valid, a message box comes up.

Below is the entire code.
If the number the user enters in the text box is valid, the subform populates with no problem.
If the number entered in the text box does not exist in sql2, see the DLookup line, the result is Error code 94, invalid use of Null.

Private Sub txtEnvNbr_AfterUpdate()
On Error GoTo Err_txtEnvNbr_AfterUpdate_Error

Dim sql1 As String
Dim sql2 As String

sql1 = "SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund " _
& " FROM tblNewGivings " _
& " WHERE EnvNbr = " & Forms!frmEditNewGivings!txtEnvNbr _
& " ORDER BY EnvNbr, [Date Given]"

sql2 = DLookup("EnvNbr", "qryNewGivings", "EnvNbr = Forms!frmEditNewGivings!txtEnvNbr")

If sql2 <> Me.txtEnvNbr Then
Call MsgBox("Envelope # " & Forms!frmEditNewGivings!txtEnvNbr & " is not assigned." _
            & vbCrLf & "Please re-enter another number." _
            , vbExclamation)
Exit Sub
Me.txtEnvNbr.SetFocus
Else

Me.fsubNewGivings.Visible = True
Me.fsubNewGivings.Form.RecordSource = sql1

End If

On Error GoTo 0
   Exit Sub

Err_txtEnvNbr_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure txtEnvNbr_AfterUpdate of VBA Document Form_frmEditNewGivings"

End Sub

Can you suggest a correction to the code? Or a better method?

Thanks.
Tom

 

[dhookom]

Why not just use a combo box limited to the envelope numbers?

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
That's an idea.

Right below the text box is a List Box with all the assigned numbers. I made the text box so the user didn't have to scroll down through all that list box. And both work fine and populate the subform. My attempt now was to add a message box when the user entered an unassigned number in the text box.

But, if I convert the list box to a combo box I could then get rid of the text box.

I guess I never thought of that because I built this quite a few years ago.

I'll give it a whirl.
Tom

 

[THWatson]

Works well, Duane.

Thanks!

 

[Andrzejek]

So you had a text box "so the user didn't have to scroll down through all that list box". Now you have a combo box "so the user [] have to scroll down through all that [combo] box"

Instead of:
[tt]sql2 = DLookup...[/tt]
You may just take a value from your text box and see if this value is in the list box. If it is, you are done, if it is not - give a user a message box.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[THWatson]

Andy
There's a difference. As soon as the user begins to type in an envelope number, the combo box begins to shift accordingly.

For example, say the user wants to check Envelope # 719, as soon as she types 71 you're in the right numerical sequence.

That works...unless I have gotten myself far enough in the forest that I can't see the trees again...and I am horribly directionally challenged (turn me around in a room three times and I can't find my way out).

??
Tom