Pesky Error Message

[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

 

[Steve Schapel]

Don,

Using VBA code, you can open a recordset and use the RecordCount
property to check whether the record exists, or alternatively the
DCount function...

If DCount("*","accounts","[AcctNo]=" & Me.Textbox) >0 Then
<your code to open form>
Else
MsgBox "Invalid number"
End If

or...
If CurrentDb.OpenRecordset("SELECT * FROM Accounts WHERE _
AcctNo=" & Me.Textbox).RecordCount >0 Then
<your code to open form>
Else
MsgBox "Invalid number"
End If

- Steve Schapel, Microsoft Access MVP

 

[PC Datasheet]

Don,

the simple solution to the problem is to replace the unbound control with a
combobox or listbox that draws its rowsource from the accounts table. The
combobox or listbox will then only display an account that is in the accounts
table so you will never again get the pesky error message!


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel