Code for Record Set is Empty 1

[kentwoodjean]

I would like to write code that would advide the user that the recordset is empty. The following code appears when the user opens my form that is built upon a member ship table (over 1000 members):

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

My user will input a number in the field 'Contract Number" which contains an unbound text box and after update property and as a result will populate all other fields on the form pertenent to that particular member. The event property for this is:

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 want to happen is to have a message come up indicating there is no record when the user enters a Contract number that is not on the list. I though the form "On Open" would take care of this but does not.

When I first posed this question in another forum, it was suggested that I come to the "Forms" forum.

Thanks.

 

[TheAceMan1]

How are ya kentwoodjean . . .

Have a look at the [blue]DLookUp[/blue] aggregate function!

See Ya! . . . . . .

 

[kentwoodjean]

Looked at the DlookUp function on line as was mentioned ever so briefly in my Access books. Doesn't this do what my "After Update" event is already doing, unless I am doing something wrong.

I still can't seem to get that message indicating that the contract number number entered does not have a record. This is one of those nice finishing touches I wanted to include in this database, but I guess if I can't get to it, it will be OK.

 

[TheAceMan1]

kentwoodjean . . .
[ol][li]Move your code from the [blue]OnOpen[/blue] event to the [blue]OnLoad[/blue] event. This code doesn't check for matches, it tells you if there are any records at all. So the message should be something like:

[blue]   If Me.RecordsetClone.RecordCount = 0 Then
      MsgBox "No Records Available!"
   End If[/blue]

[/li]
[li]As for ContractNum, add the following to the [blue]BeforeUpdate[/blue] event of ContractNum (keep the code you have in the [blue]AfterUpdate[/blue] event):

[blue]   Dim Criteria As String
   
   Criteria = "[Contract_Num] = '" & Trim(Me.ContractNum) & "'"
   
   If IsNull(DLookup("[Contract_Num]", "qryMembership", Criteria)) Then
      MsgBox "No matches. Try again."
      Cancel = True
   End If[/blue]

[/li][/ol]

See Ya! . . . . . .

 

[kentwoodjean]

Exactly what I was looking for. Seemed like such a simple thing but I just could not get there.

Thanks for your help.