PC Review


Reply
Thread Tools Rate Thread

Display static first record

 
 
=?Utf-8?B?RCBaYW5kdmVsZA==?=
Guest
Posts: n/a
 
      2nd Apr 2007
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


 
Reply With Quote
 
 
 
 
Jeff Boyce
Guest
Posts: n/a
 
      2nd Apr 2007
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:(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
>
>


 
Reply With Quote
 
 
 
 
=?Utf-8?B?RCBaYW5kdmVsZA==?=
Guest
Posts: n/a
 
      2nd Apr 2007
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:(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
> >
> >

>
>

 
Reply With Quote
 
Jeff Boyce
Guest
Posts: n/a
 
      2nd Apr 2007
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:(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:(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
> > >
> > >

> >
> >


 
Reply With Quote
 
=?Utf-8?B?S2VuIFNoZXJpZGFu?=
Guest
Posts: n/a
 
      2nd Apr 2007
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
>
>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: I'm lazy: how do I make the first databound record not display/chop off the first element from SqlDataSource Eliyahu Goldin Microsoft ASP .NET 6 28th Jun 2006 11:24 AM
security first or BE/FE first or EXE first? =?Utf-8?B?Vml2aQ==?= Microsoft Access Security 0 7th Feb 2006 07:10 PM
Update record - returns to the first record in the record set Kim Microsoft Access Forms 0 22nd Apr 2005 04:20 PM
Convert cell from last name first to first name first =?Utf-8?B?RGlhbmU=?= Microsoft Excel Worksheet Functions 4 21st Oct 2004 04:59 PM
Converting from first name first to last name first Will Microsoft Excel Worksheet Functions 3 28th Mar 2004 01:11 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:08 AM.