Pesky Error

[Don Rountree]

I have a form with an unbound control on it. It is used
to open another form to edit the account whose account
number I have typed in the unbound control on the first
form. If I make a mistake and type in an account number
on the first form that is not in the accounts table, I
get a debug error. Can someone help me with some code to
avoid the error, such as delete the contents of the
unbound control and prevent the second form from
opening. Thanks...

Don Rountree

 

[Bruce M. Thompson]

I have a form with an unbound control on it. It is used

to open another form to edit the account whose account
number I have typed in the unbound control on the first
form. If I make a mistake and type in an account number
on the first form that is not in the accounts table, I
get a debug error. Can someone help me with some code to
avoid the error, such as delete the contents of the
unbound control and prevent the second form from
opening. Thanks...

Simply check to see if the account number exists in the table before opening the
form:

'********EXAMPLE START
If DCount("*","MyTableName","AcctNum =" _
& Me.txtAcctNum) = 0 Then
'The account number doesn't exist
MsgBox "The number you have entered doesn't exist."
Else
'The account number exists, so open the form
DoCmd.OpenForm ...
End If
'********EXAMPLE END

Of course, you would need to replace "MyTableName" with the name of the table
behind the second form, "AcctNum" with the name of the field in that table and
"txtAcctNum" with the name of the unbound control on your form.

 

[Don Rountree]

Thank you for your response. I can't get this to work.
A couple of things. First I referenced the table behind
the second form, but the control source is a query with
just that Table in it. Secondly, this is the way I used
your code. What did I do wrong?

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEDIT_CUSTOMER"

If DCount("*", "tblCUSTOMER", "CUST_NO =" _
& Me.txtEDIT) = 0 Then
'The account number doesn't exist
MsgBox "The number you have entered doesn't exist."
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmCUSTOMER_SEARCH", acSaveNo
End If

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

 

[John Vinson]

I have a form with an unbound control on it. It is used
to open another form to edit the account whose account
number I have typed in the unbound control on the first
form. If I make a mistake and type in an account number
on the first form that is not in the accounts table, I
get a debug error. Can someone help me with some code to
avoid the error, such as delete the contents of the
unbound control and prevent the second form from
opening. Thanks...

Suggestion: make it a Combo Box instead of a textbox, based on a query
showing all account numbers; it's easier on the user to let them pick
from a list rather than slapping their hands if they type wrong.

 

[Bruce M. Thompson]

Don:

I presume that the "account number" you referred to in your earlier post is
interchangeable with "CUST_NO"?

If DCount("*", "tblCUSTOMER", "CUST_NO =" _
& Me.txtEDIT) = 0 Then

I can't tell offhand where your problem is, but you might want to try enclosing
"CUST_NO" with brackets:

If DCount("*", "tblCUSTOMER", "[CUST_NO] =" _
& Me.txtEDIT) = 0 Then

Also, if the customer number contains non-numeric characters, you will need to
embed quotes around the value passed by reference:

If DCount("*", "tblCUSTOMER", "[CUST_NO] =""" _
& Me.txtEDIT & """") = 0 Then