Display static first record

Discussion in 'Microsoft Access Getting Started' started by Guest, Apr 2, 2007.

  1. Guest

    Guest 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
     
    Guest, Apr 2, 2007
    #1
    1. Advertisements

  2. Guest

    Jeff Boyce Guest

    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" <> wrote in message
    news:...
    > 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, Apr 2, 2007
    #2
    1. Advertisements

  3. Guest

    Guest 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" 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" <> wrote in message
    > news:...
    > > 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
    > >
    > >

    >
    >
     
    Guest, Apr 2, 2007
    #3
  4. Guest

    Jeff Boyce Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > > news:...
    > > > 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, Apr 2, 2007
    #4
  5. Guest

    Guest 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

    "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
    >
    >
     
    Guest, Apr 2, 2007
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jimmy

    need to delete the first 4 numbers of each record

    Jimmy, May 26, 2004, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    128
    Cheryl Fischer
    May 26, 2004
  2. Guest

    cursor in first field of new record

    Guest, Jan 13, 2005, in forum: Microsoft Access Getting Started
    Replies:
    4
    Views:
    1,128
    StCyrM
    Feb 7, 2005
  3. Guest

    How do I combine first and last name to create a unique record?

    Guest, Jun 13, 2006, in forum: Microsoft Access Getting Started
    Replies:
    4
    Views:
    753
    Ed Robichaud
    Jun 15, 2006
  4. Guest

    Record Find, First & Last

    Guest, Aug 9, 2006, in forum: Microsoft Access Getting Started
    Replies:
    4
    Views:
    257
    Guest
    Aug 9, 2006
  5. Telemann

    How to append successive record contents to first record

    Telemann, Apr 19, 2008, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    179
    Duane Hookom
    Apr 20, 2008
Loading...

Share This Page