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

    Deleted Record, Other record info now being called

    DW, Mar 17, 2004, in forum: Microsoft Access Getting Started
    Replies:
    0
    Views:
    268
  2. Jimmy

    need to delete the first 4 numbers of each record

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

    cursor in first field of new record

    Guest, Jan 13, 2005, in forum: Microsoft Access Getting Started
    Replies:
    4
    Views:
    1,374
    StCyrM
    Feb 7, 2005
  4. 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:
    908
    Ed Robichaud
    Jun 15, 2006
  5. Guest

    Record Find, First & Last

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

    How to append successive record contents to first record

    Telemann, Apr 19, 2008, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    216
    Duane Hookom
    Apr 20, 2008
  7. Jason

    Error going from first field to last field in previous record

    Jason, Aug 23, 2009, in forum: Microsoft Access Getting Started
    Replies:
    2
    Views:
    376
    Jason
    Aug 25, 2009
  8. LORENZO

    duplicate record in my first colomn

    LORENZO, Feb 15, 2010, in forum: Microsoft Access Getting Started
    Replies:
    3
    Views:
    259
    Tammy S.
    Feb 19, 2010
Loading...