Code for no match found?

E

el zorro

I'm using this code on a form (SelectionForm) to open
another form (TheForm)to display a specific record:
stDocName = "TheForm"
stLinkCriteria = "[RecordNumber]=" & Me![Text9]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Pretty basic. Text9 is the box on SelectionForm were the
user enters the record number.

Works great if the user inputs a real Record Number.
However, when the user enters a number for which there is
no matching record, TheForm opens at a new, blank record.
Strange-- I thought there would be an error message of
some kind.

I would rather have the user notified that she needs to
input a correct number if there is no match. How can I do
this? As always-- y'all are great!
 
A

Allen Browne

You could either use DLookup() to see if there is such as record before
opening the form, or examine the form's RecordsetClone.RecordCount after
opening the form.

To cancel the opening of the form when it contains no records, set the
target form's Open event procedure to:
[Event Procedure]
Click the Build button (...) beside this. Access opens the code window.
Set up the event procedure like this:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "No match."
End If
End Sub


If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://members.iinet.net.au/~allenbrowne/casu-07.html
 
E

el zorro

THanks for your response! I want to try to take care of
this via the On Click event of the SelectionForm control
button that opens the form, so I took a crack at your
DLookup suggestion. Here's what I came up with:

CtrlNo = DLookup
("[RecordNumber]", "EditLstQ", "[REcordNumber] = " & Me!
[Text9])
stLinkCriteria = "[nREQ]=" & Me![Text9]
DoCmd.OpenForm stDocName, , , stLinkCriteria

THis actually seems to work. If the user selects a record
not on the list, she gets a pop-up message "Invalid Use
of Null" and the procedure to open the form stops. (I
added a little text to the error message to explain that
she probably selected a nonexistant record.) WHat do you
think?
-----Original Message-----
You could either use DLookup() to see if there is such as record before
opening the form, or examine the form's
Click to expand...
RecordsetClone.RecordCount after
opening the form.

To cancel the opening of the form when it contains no records, set the
target form's Open event procedure to:
[Event Procedure]
Click the Build button (...) beside this. Access opens the code window.
Set up the event procedure like this:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "No match."
End If
End Sub


If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://members.iinet.net.au/~allenbrowne/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm using this code on a form (SelectionForm) to open
another form (TheForm)to display a specific record:
stDocName = "TheForm"
stLinkCriteria = "[RecordNumber]=" & Me![Text9]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Pretty basic. Text9 is the box on SelectionForm were the
user enters the record number.

Works great if the user inputs a real Record Number.
However, when the user enters a number for which there is
no matching record, TheForm opens at a new, blank record.
Strange-- I thought there would be an error message of
some kind.

I would rather have the user notified that she needs to
input a correct number if there is no match. How can I do
this? As always-- y'all are great!
Click to expand...


.
Click to expand...
 
A

Allen Browne

The DLookup() returns Null if no match is found.
Only a Variant type can handle the Null.
Therefore you need:
Dim CtrlNo As Variant
CtrlNo = DLookup(...
If IsNull(CtrlNo) Then
MsgBox "no go"
Else
stLink...

End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

el zorro said:
THanks for your response! I want to try to take care of
this via the On Click event of the SelectionForm control
button that opens the form, so I took a crack at your
DLookup suggestion. Here's what I came up with:

CtrlNo = DLookup
("[RecordNumber]", "EditLstQ", "[REcordNumber] = " & Me!
[Text9])
stLinkCriteria = "[nREQ]=" & Me![Text9]
DoCmd.OpenForm stDocName, , , stLinkCriteria

THis actually seems to work. If the user selects a record
not on the list, she gets a pop-up message "Invalid Use
of Null" and the procedure to open the form stops. (I
added a little text to the error message to explain that
she probably selected a nonexistant record.) WHat do you
think?
-----Original Message-----
You could either use DLookup() to see if there is such as record before
opening the form, or examine the form's
Click to expand...
RecordsetClone.RecordCount after
opening the form.

To cancel the opening of the form when it contains no records, set the
target form's Open event procedure to:
[Event Procedure]
Click the Build button (...) beside this. Access opens the code window.
Set up the event procedure like this:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "No match."
End If
End Sub


If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://members.iinet.net.au/~allenbrowne/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm using this code on a form (SelectionForm) to open
another form (TheForm)to display a specific record:
stDocName = "TheForm"
stLinkCriteria = "[RecordNumber]=" & Me![Text9]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Pretty basic. Text9 is the box on SelectionForm were the
user enters the record number.

Works great if the user inputs a real Record Number.
However, when the user enters a number for which there is
no matching record, TheForm opens at a new, blank record.
Strange-- I thought there would be an error message of
some kind.

I would rather have the user notified that she needs to
input a correct number if there is no match. How can I do
this? As always-- y'all are great!
Click to expand...


.
Click to expand...
Click to expand...
 
E

el zorro

Works even better. Thanks!
-----Original Message-----
The DLookup() returns Null if no match is found.
Only a Variant type can handle the Null.
Therefore you need:
Dim CtrlNo As Variant
CtrlNo = DLookup(...
If IsNull(CtrlNo) Then
MsgBox "no go"
Else
stLink...

End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

THanks for your response! I want to try to take care of
this via the On Click event of the SelectionForm control
button that opens the form, so I took a crack at your
DLookup suggestion. Here's what I came up with:

CtrlNo = DLookup
("[RecordNumber]", "EditLstQ", "[REcordNumber] = " & Me!
[Text9])
stLinkCriteria = "[nREQ]=" & Me![Text9]
DoCmd.OpenForm stDocName, , , stLinkCriteria

THis actually seems to work. If the user selects a record
not on the list, she gets a pop-up message "Invalid Use
of Null" and the procedure to open the form stops. (I
added a little text to the error message to explain that
she probably selected a nonexistant record.) WHat do you
think?
-----Original Message-----
You could either use DLookup() to see if there is such as record before
opening the form, or examine the form's
Click to expand...
RecordsetClone.RecordCount after
opening the form.

To cancel the opening of the form when it contains no records, set the
target form's Open event procedure to:
[Event Procedure]
Click the Build button (...) beside this. Access opens the code window.
Set up the event procedure like this:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "No match."
End If
End Sub


If you need help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://members.iinet.net.au/~allenbrowne/casu- 07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I'm using this code on a form (SelectionForm) to open
another form (TheForm)to display a specific record:
stDocName = "TheForm"
stLinkCriteria = "[RecordNumber]=" & Me![Text9]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Pretty basic. Text9 is the box on SelectionForm were the
user enters the record number.

Works great if the user inputs a real Record Number.
However, when the user enters a number for which
Click to expand...
there
is
no matching record, TheForm opens at a new, blank record.
Strange-- I thought there would be an error message of
some kind.

I would rather have the user notified that she needs to
input a correct number if there is no match. How can
Click to expand...
I
do
this? As always-- y'all are great!


.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

msg for no record found 5
fomr opens to wrong record 5
Data Entry = No 6
cannot filter continuous form data 2
stLinkCriteria 2
Code error when opening a form 2
Remove filter 4
Sub Form Updating 3

Top