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

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
http://mvp.support.microsoft.com/

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

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


"D Zandveld" <(E-Mail Removed)> wrote in message
news:37356056-C305-4110-AEE9-(E-Mail Removed)...
> 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, 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" wrote:

> 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
> http://mvp.support.microsoft.com/
>
> Microsoft IT Academy Program Mentor
> http://microsoftitacademy.com/
>
> Microsoft Registered Partner
> https://partner.microsoft.com/
>
>
> "D Zandveld" <(E-Mail Removed)> wrote in message
> news:37356056-C305-4110-AEE9-(E-Mail Removed)...
> > 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
> >
> >
>
>

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
http://mvp.support.microsoft.com/

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

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

"D Zandveld" <(E-Mail Removed)> wrote in message
news:F7101F05-DDAF-4E58-8597-(E-Mail Removed)...
> 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" wrote:
>
> > 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
> > http://mvp.support.microsoft.com/
> >
> > Microsoft IT Academy Program Mentor
> > http://microsoftitacademy.com/
> >
> > Microsoft Registered Partner
> > https://partner.microsoft.com/
> >
> >
> > "D Zandveld" <(E-Mail Removed)> wrote in message
> > news:37356056-C305-4110-AEE9-(E-Mail Removed)...
> > > 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
> > >
> > >
> >
> >

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

"D Zandveld" wrote:

> 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
>
>