search form

[sparrow]

hi,

I have a form set up to open a page on a specific record. the user can enter the index and if the detail if correct can open the record, I would like to have a message box appear if the record is not found. Can you advise?

Many Thanks

 

[John Big Booty]

Try something like this;

If Me.Recordset.RecordCount = 0 Then
        MsgBox "Record not Found, please try another value", vbOKOnly, "Record Not Found"
End If

 

[sparrow]

Thanks for the reply John,

This is my simple bit of code so far, tried entering your suggestion but had some errors. When I enter an if statement when the record is not found I only get the record not found message. If you could advise any improvements that would be great. Thank you


Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String
Dim stLinkCriteria As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![Text0]) Or (Me![Text0]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![Text0].SetFocus



End If

stDocName = "test"

stLinkCriteria = "[Reg_No]=" & "'" & Me![Text0] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[John Big Booty]

Thanks for the reply John,

This is my simple bit of code so far, tried entering your suggestion but had some errors. When I enter an if statement when the record is not found I only get the record not found message. If you could advise any improvements that would be great. Thank you

........

I thought that's what you wanted to happen

.....I would like to have a message box appear if the record is not found. Can you advise?.......

........

What is it exactly that you are trying to achieve?

 

[sparrow]

Hi John,

Sorry I wrote that sentence wrong.

When I had the if clause in, at all times the message record not found appeared

What I would like to happen is when the user searches the database, If the record is not found a message appears saying so.

 

[John Big Booty]

What event are you using to trigger the code?

 

[sparrow]

Hi John,

User will enter a Registration number in an unbound textbox,click on a search command button which is setup to open the record that matched the detail entered in the text button. The first bit of code will ask returns a msg when no data is entered. The next will retrieve the record.

Thanks for your reply

 

[accessNator]

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String
Dim stLinkCriteria As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![Text0]) Or (Me![Text0]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![Text0].SetFocus



End If

stDocName = "test"

stLinkCriteria = "[Reg_No]=" & "'" & Me![Text0] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

By looking at this code, after you execute the check for a null value, it will continue to execute. Try redoing your if then clause like this.

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String
Dim stLinkCriteria As String

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![Text0]) Or (Me![Text0]) = "" Then
' No entry in text0 then give message
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![Text0].SetFocus
        
[COLOR=Red][B]ELSE[/B][/COLOR]
' If Text0 has an entry continue
stDocName = "test"
     
stLinkCriteria = "[Reg_No]=" & "'" & Me![Text0] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria[/quote]

[B][COLOR=Red]End If[/COLOR][/B]

End Sub

 

[sparrow]

Thank you for the reply.

I also need a msg box when a record is entered but not found on the database. I have tried elseif statements, however my code dosent work very efficiently. What would the best method be?