Display static first record

[Guest]

Hi, advanced at excel (good knowledge of VBA) but not too flash at Access
(yet).

I have a customer management database, with a field for 'Primary contact' on
the front form. In another tab on the console is a full 'contacts' list which
links multiple contacts with the company.

However, on the front page, I want to display the first contact in the list
always (as a primary contact) - what do I enter to address the first record
only?

Thanks

 

[Jeff Boyce]

Access stores data in tables, but does so according to its own needs.

When you say "the first" record, you also have to say what you are using to
do the sort. For example, if each record also included a date/time value,
you could say "the first record is the one with the oldest date/time".

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Jeff, thanks, I understand that.

There is no sort attached to it - I can assigned a numerical ID to each
contact, therefore making it ID 1 that needs to be referred to - it is the
syntax i am blank with.

Thanks again, D.

 

[Jeff Boyce]

If you create a new query, you could sort by the ID, then take the "TOP 1".

By the way, if that ID is an autonumber field, there is NO guarantee that
the numbers will be sequential...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

I'd suggest using Boolean (Yes/No) PrimaryContact column in the Contacts
table rather than a numeric column. If you want to be able to edit the
primary contact record on page 1 of the tab control you can have a subform
with a RecordSource of:

SELECT *
FROM Contacts
WHERE PrimaryContact;

This subform, like that for the full contact list on the other page of the
tab control, would be linked to the parent form on CustomerID, and both could
include a check box bound to the PrimaryContact column. As it would be
necessary to uncheck the current primary contact (or delete the record)
before making another contact the primary contact, you could put some
validation code in the full contact list subform's BeforeUpdate event
procedure to prevent two contacts being made 'primary', e.g.

Const conMESSAGE = "Another contact is already designated " & _
"Primary Contact for this customer. "You must remove that " & _
" designation, or delete that contact record before designating " & _
" a new Primary Contact."

Dim strCriteria As String

strCriteria = _
"CustomerID = " & Me.CustomerID & _
" And ContactID <> " & Me.ContactID & _
" And PrimaryContact")

If Me.PrimaryContact Then
If Not IsNull(DLookup("ContactID", "Contacts", strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Warning"
Cancel = True
End If
End If

In the AfterUpdate event procedure of the full contact list subform you
would need to requery the primary contact subform so it shows the new primary
contact:

Me.Parent.sfrPrimaryContact.Requery

where sfrPrimaryContact is the name of the subform control on page 1 of the
tab (i.e. the control which houses the subform) Similarly in the primary
contact subform you would requery itself in its AfterUpdate event procedure:

Me.Requery

If you don't want to be able to edit the primary contact record on the first
page of the tab but only in the full contact list you could use an unbound
text box rather than a subform, with a ControlSource property such as:

=DLookup("Firstname & "" "" & LastName","Contacts","PrimaryContact And
CustomerID = " & [CustomerID])

The validation code in the full contact list subform's module would still be
needed, however, but obviously you would not now need to requery the first
subform.

Ken Sheridan
Stafford, England