INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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.

CODE

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

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

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

Duane
Hook'D on Access
MS Access MVP

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

(OP)
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

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

(OP)
Works well, Duane.

Thanks!

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

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" smile

Instead of:
sql2 = DLookup...
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.

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close