msgbox if txtbox >1

[knawtn via AccessMonster.com]

Hello group.

I have a form that shows records from a table based on a customer name. If
the customer has more than one record, I'd like a msgbox to appear telling
the veiwer to use the navigation buttons to view additional records for the
chosen customer.

I placed a txtbox named [txt51] on the form using =Count("*") as the record
source. This worksfine. I threw in an if statement on the form open event: If
Me.txt51>1 Then msgbox "bla bla bla". I've tried several scenerios to no
avail.

Any thoughts?

 

[Guest]

First, you need to consider redesigning your database. If you have mutiple
records in a table based on your customer, it is not correctly normalized.
Those data that cause the need for multiple records should be in a child
table and you should be using a form/subform construct.

But, to answer your question.

You text51 Control Source is incomplete (Has to be the Control Source, there
is no Record Source for a text box). You have to tell DCount what to count
and were to count it.

=DCount("*","FormRecordSource","[CustID] = " &
[Forms]![YourFormName]![txtCustomer])

FormRecordSource has to be replaced with the name of your form's record
source.
CustID needs to be the field in the Record Source that identifies the
customer.
YourFormName needs to be replaced with the name of your form.
txtCustomer needs to be replaced with the name of the control where your
customer ID is carried. The code above assumes CustID is a numeric field.
If it is a text field, then you need it this way
=DCount("*","FormRecordSource","[CustID] = '" &
[Forms]![YourFormName]![txtCustomer]) & "'"

Now, to get your message, use the form's Current event:
If Not Me.NewRecord And Me.text51 > 1 then
MsgBox "Use Navigation Buttons to View More Records for this Customer"
End If