display a "record not found" message before a form appears

[tbatftc]

Just took over a database and I am very new to Access. I have a table of
Account Numbers, Names, and cycle group #s. Users want to update the cycle
groups by entering the account number. I implemented a switchboard that calls
a form that uses a query (criteria [Enter account #] to bring up the form.
It works fine, except when they type in an invalid message, a blank form
appears. I would like a message to appear "Account not found, please try
again" and return to the switchboard.

 

[fredg]

Just took over a database and I am very new to Access. I have a table of
Account Numbers, Names, and cycle group #s. Users want to update the cycle
groups by entering the account number. I implemented a switchboard that calls
a form that uses a query (criteria [Enter account #] to bring up the form.
It works fine, except when they type in an invalid message, a blank form
appears. I would like a message to appear "Account not found, please try
again" and return to the switchboard.

Code the form's Open event:
If Me.RecordsetClone.RecordCount = 0 then
MsgBox "No records found"
Cancel = True
End If

 

[Philip Herlihy]

Just took over a database and I am very new to Access. I have a table of
Account Numbers, Names, and cycle group #s. Users want to update the cycle
groups by entering the account number. I implemented a switchboard that calls
a form that uses a query (criteria [Enter account #] to bring up the form.
It works fine, except when they type in an invalid message, a blank form
appears. I would like a message to appear "Account not found, please try
again" and return to the switchboard.

Your form displays whatever the query returns, and if the query returns
no results, that's what your form will display. To "validate" the
entered account number, I'd create another form just for that purpose.
Depending on your situation, you could have the user select an account
number from a combo box (drop-down) with "Limit to list" set to True.
If the user types into the box, the choices offered will shrink. Once
the selection is made, you could use the "After Update" event of the
combo to fire up the results form, although I think it may be better to
allow the user a chance to rethink by adding a Command Button to run the
results form instead. This intermediate form could be refined into a
search form, displaying name or other data to help confirm the user has
the right record before clicking the "display" button.

Phil, London

 

[tbatftc]

Thank you. I was stuck trying IsNull and IIF and other things. THis is much
simpler.
--
tmb

Just took over a database and I am very new to Access. I have a table of
Account Numbers, Names, and cycle group #s. Users want to update the cycle
groups by entering the account number. I implemented a switchboard that calls
a form that uses a query (criteria [Enter account #] to bring up the form.
It works fine, except when they type in an invalid message, a blank form
appears. I would like a message to appear "Account not found, please try
again" and return to the switchboard.

Code the form's Open event:
If Me.RecordsetClone.RecordCount = 0 then
MsgBox "No records found"
Cancel = True
End If

 

[Philip Herlihy]

Just took over a database and I am very new to Access. I have a table of
Account Numbers, Names, and cycle group #s. Users want to update the cycle
groups by entering the account number. I implemented a switchboard that calls
a form that uses a query (criteria [Enter account #] to bring up the form.
It works fine, except when they type in an invalid message, a blank form
appears. I would like a message to appear "Account not found, please try
again" and return to the switchboard.

Code the form's Open event:
If Me.RecordsetClone.RecordCount = 0 then
MsgBox "No records found"
Cancel = True
End If

Sometimes when you post a reply you just know it'll be trumped by a
better one! Nice one, Fred...



Phil