Check For " No Records" After Update

[Nikki Norris via AccessMonster.com]

I just took this code over and tried to make changes, but it's not working.

Scenario: I'm on a blank form with a company name drop down. I pick a
company name from the drop-down and the first record populates the form and
the label reflects the company name. I now decide to pick another company
name, but there are no records for this company.

How do I check that there are no records, blank out/Clear the fields from
the previous records, then do not change the label, but give the user a
popup "no records found"???

Here's my code:

Private Sub Combo170_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Companyid] = " & Str(Nz(Me![Combo170], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Dim SQL As String

SQL = "Select * from qryJOProfile Where Companyid=" & Me.Combo170.Value

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "NO RECORDS FOR THIS VENDOR."
'Cancel = True
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Else

Dim sFilter As String
sFilter = [CompanyName]
Me!Label90.Caption = sFilter

End If

End Sub

Thanks,,
Nikki

 

[Nikki Norris via AccessMonster.com]

Does anyone have a solution?

 

[John Nurick]

Can you explain a bit more clearly? See questions/comments in line.

I just took this code over and tried to make changes, but it's not working.

Scenario: I'm on a blank form with a company name drop down. I pick a
company name from the drop-down and the first record populates the form and
the label reflects the company name. I now decide to pick another company
name, but there are no records for this company.

If there is no record for the company, how can the user pick its name?
(What tables/queries are the form and the combo bound to?

How do I check that there are no records, blank out/Clear the fields from
the previous records, then do not change the label, but give the user a
popup "no records found"???

When you say "clear the fields from the previous records", do you want
to *delete* the previous records, or delete the contents of some of
their fields, or create a new blank record, or something else?

Here's my code:

Private Sub Combo170_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Companyid] = " & Str(Nz(Me![Combo170], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

The next two lines

Dim SQL As String

SQL = "Select * from qryJOProfile Where Companyid=" & Me.Combo170.Value

don't do anything and might as well be omitted.

At this point the RecordCount will (I think) only ever be zero if there
are no records in the form's record source, so the message will (I
think) not be displayed when you want).

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "NO RECORDS FOR THIS VENDOR."
'Cancel = True

If this next line does anything, it will undo any changes the user made
to the current record (i.e. the record that was being displayed when the
user selected a company for which there is no record).

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Else

Dim sFilter As String
sFilter = [CompanyName]
Me!Label90.Caption = sFilter

End If

End Sub

I suspect that what you need is something like this, starting after the
FindFirst:
If Not rs.EOF Then 'there is at least one record
'for the selected company
Me.Bookmark = rs.Bookmark 'move form to first such record
Me.Label90.Caption = Me.CompanyName 'or Me.Combo170.Column(1)
Else 'No record
Me.Label90.Caption = ""
'Maybe do other stuff such as display message
End If
End Sub

Also, check out the LimitToList property and NotInList event of the
ComboBox object. These are often needed in the sort of situation you
seem to have.