Error Message in Label Caption

[jsmith1611]

I am trying to set a label caption to an error message when searching for a record in a form. Basically I am setting a bookmark on the form based on a text field. Here is my vb code:

If DCount("[last]", "query_letters") = 0 Then
lblMember.Caption = "Member not found."
Else
lblMember.Caption = ""
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "acct = " & member
Me.Bookmark = rst.Bookmark

End If

query_letters is the record source for my form.

The code works as far as finding a good record, but if the record does not exist, the error message is not being displayed in the caption. I have used this method successfully for other queries when I am opening a new form, but in this case I am opening the record in the same form. I suspect my problem has something to do with that. Any help will be greatly appreciated.

Thanks,

Josh

 

[HiTechCoach]

I am trying to set a label caption to an error message when searching for a record in a form. Basically I am setting a bookmark on the form based on a text field. Here is my vb code:

If DCount("[last]", "query_letters") = 0 Then
lblMember.Caption = "Member not found."
Else
lblMember.Caption = ""
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "acct = " & member
Me.Bookmark = rst.Bookmark

End If

query_letters is the record source for my form.

The code works as far as finding a good record, but if the record does not exist, the error message is not being displayed in the caption. I have used this method successfully for other queries when I am opening a new form, but in this case I am opening the record in the same form. I suspect my problem has something to do with that. Any help will be greatly appreciated.

Thanks,

Josh

You probable do not need the Dcount() part.

Should be able to do it with just this code:

Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "acct = " & member
If Not rs.EOF Then 
   lblMember.Caption = ""
   Me.Bookmark = rst.Bookmark
Else
   lblMember.Caption = "Member not found."
End if

 

[jsmith1611]

Thanks for your reply HiTech Coach,

Your code works just as well as mine...it takes you to an existing record but no error message if the record isn't found. All it does is return the form to the first record.

Any ideas?

Thanks,
Josh

 

[HiTechCoach]

The original code I posted was based on code generated by Access's find record combo box wizard.

I was able to take a look at one of my apps and I did it in a different way.

Try:

Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "acct = " & member
If Not rst.NoMatch Then 
   lblMember.Caption = ""
   Me.Bookmark = rst.Bookmark
Else
   lblMember.Caption = "Member not found."
End if

 

[jsmith1611]

Thank you very much HiTech Coach! That code worked perfectly....I have really been pulling my hair out over this one...I knew it had to be a simple explanation.

 

[HiTechCoach]

Thank you very much HiTech Coach! That code worked perfectly....I have really been pulling my hair out over this one...I knew it had to be a simple explanation.

You're welcome!

Glad I could assist.