Code for Record Not Found 1

[kentwoodjean]

I am looking for some help on writing a code. I have a form that is made based on a membership query. Upon entering a contract number in the first field, all other fields pertenent to the customer will populate (addr, city, state etc will appear. To accomplish this I am using an 'unbound' text box with the following code:

Private Sub ContractNum_AfterUpdate()
Dim szSQL As String

szSQL = "select * from qryMembership where Contract_Num = """ & Trim(Me.ContractNum) & """;"

Me.RecordSource = szSQL
End Sub


What I would like to do is have some type of error message that will aappear saying "Record not Found. I am learning Visual Basic and really don't now how to do this. Any help is appreciated. I checked the posts and couldn't find one for Access. thanks.

 

[SkipVought]

Hi, Tics not quotes...

    szSQL = "select * from qryMembership where Contract_Num = '" & Trim(Me.ContractNum) & "';"

Skip,
[sub]
[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE! [/sub]

 

[kentwoodjean]

Thanks for the response. I changed my szSQL statement as yours and puposely put in an incorrect contract number but did not get a message to say I had to wrong number or it was not found. Can a message of this type be built into this?

 

[SkipVought]

A criteria that returns no data is NOT an error.

However you can wite code that would advide the user that the recordset is empty.

Skip,
[sub]
[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE! [/sub]

 

[kentwoodjean]

That is exactly what I am looking for but apparently didn't know how to phase my question. I am at somewhat of a loss as to how to do this.

 

[SkipVought]

Well you have a recordset object. If the EOF and BOF properties are both TRUE after you use the MoveFirst method, then you have no rows returned.

Skip,
[sub]
[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE! [/sub]

 

[kentwoodjean]

I had to look up EOF and BOF defnitions to understand these properties. What I am curious about is the "on open" property for the form. It shows:

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

This would tell me that if there are no records then a msgBox should appear with the above message. Is that correct?

This form is used to provide additional data on an ongoing basis based on several thousand members loaded in the membership file. If when I put in a contract number that does not exist, why does the above message not show up in a message box? Or does this message have to be alined with the unbound text box?

 

[SkipVought]

You're probably going to be better off in one of the Access forums is this is an Access form.

Skip,
[sub]
[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE! [/sub]

 

[kentwoodjean]

thanks, and I have now posted in the "Forms" forum in hopes to understand how to get the result that I am looking for.