Adding/editing problem

[Niklas Östergren]

Hi!

I´m having a minor problem of adding/editing records in a form based on a
multitable query!

The Q is based on 3 tables <tblPerson> (containing Name, address, Dob etc.),
<tblMember> (containing MemberNo, MemberType, EnlistedDate, etc.) and
<tblMemberValidation> (containing MemberEndDate, MemberFee, MemberCard
etc.).

I also have a table containing phone numbers <tblPhoneNumbers> and a lookup
table for different type of phonenumbers <tblPhoneNumbertype> (Home, Work,
Mobile)

The relationship looks like this:

<tblPerson> 1 - Many <tblMember> 1 - Many <tblMemberValidation>
PersonID----------------fkPersonID
........................................MemberID------------------fkMemberID

AND

<tblPerson> 1 - Many <tblPhoneNumbers> Many - 1 <tblPhoneNumbertype>
PersonID------------------fkPersonID
.........................................fkPhoneTypeID---------------PhoneTyp
eID

Now to my problem!

I want to disply a form showing each member (MemberNo, Name and all types of
phonenumbers (home, work etc.)), in a datasheet form, which have a valid
membership.

So I base the form on a Q qryMainMemberList whichs looks like this:

======================================================
SELECT qryMemberList.fkPersonID, qryMemberList.Name, qryMemberList.LastName,
qryPhoneNumbers_CrossQ.Home, qryPhoneNumbers_CrossQ.Work,
qryPhoneNumbers_CrossQ.Mobile, qryMemberList.MemberNo,
qryMemberList.MemberType, qryMemberList.MemberEndDate,
qryMemberList.MemberCard
FROM qryMemberList LEFT JOIN qryPhoneNumbers_CrossQ ON
qryMemberList.fkPersonID = qryPhoneNumbers_CrossQ.fkPersonID;
========================================================

I have 2 problems with this which I need help with:

1.) It´s not possible to update a record, probably due to the cross query,
or? If so how do I solve this?

2.) How do I sort out only the records with the highest <MemberEndDate>. I
don´t whant to show a member twice just because he/she have payed member fee
twice?

TIA

// Niklas

 

[John Vinson]

Hi!

I´m having a minor problem of adding/editing records in a form based on a
multitable query!

The Q is based on 3 tables <tblPerson> (containing Name, address, Dob etc.),
<tblMember> (containing MemberNo, MemberType, EnlistedDate, etc.) and
<tblMemberValidation> (containing MemberEndDate, MemberFee, MemberCard
etc.).

...
Now to my problem!

I want to disply a form showing each member (MemberNo, Name and all types of
phonenumbers (home, work etc.)), in a datasheet form, which have a valid
membership.

So I base the form on a Q qryMainMemberList whichs looks like this:

======================================================
SELECT qryMemberList.fkPersonID, qryMemberList.Name, qryMemberList.LastName,
qryPhoneNumbers_CrossQ.Home, qryPhoneNumbers_CrossQ.Work,
qryPhoneNumbers_CrossQ.Mobile, qryMemberList.MemberNo,
qryMemberList.MemberType, qryMemberList.MemberEndDate,
qryMemberList.MemberCard
FROM qryMemberList LEFT JOIN qryPhoneNumbers_CrossQ ON
qryMemberList.fkPersonID = qryPhoneNumbers_CrossQ.fkPersonID;
========================================================

I have 2 problems with this which I need help with:

1.) It´s not possible to update a record, probably due to the cross query,
or? If so how do I solve this?

Exactly. Instead use a Form (based on a query joining tblPerson to
tblMember) with a Subform based on the phones table. If as the name
suggests qryPhoneNumbers_CrossQ is a Crosstab query, neither it nor
any query containing it as a Join can ever be updateable.

2.) How do I sort out only the records with the highest <MemberEndDate>. I
don´t whant to show a member twice just because he/she have payed member fee
twice?

Use a criterion of

=DMax|("[MemberEndDate]","tblMemberValidation","[tblMemberValidation].[fkPersonID]
= " & [txtPersonID])

 

[Microsoft Diskussionsgrupp]

Hi John!

Thanks for your answer! I´ll try it out!

What I´m trying to do is to make main form with all the most common
information you need about the members. A form where you can open up each
member if you need more information then what´s displayed in the mainform.

I´ts a little difficult to explaine in text but if you have time and whant
download the demo program <Förening> from
http://www.spcs.se/butik/default.asp?kundnummer=&kundnamn=&partnerkod=&kateg
ori=&aftyp=&afnr=&afepost=&guidnr=&ninlogg=&sida=navig_ny

Select cmdButton <Hämta demoversion>. Open up the form member (small button
on top marked <M>).

That´s what I´m trying to do!

Why?
Because it´s easy to use and very often the user don´t need more info. than
just that.

But, if it´s not possible then I have to face the fact and go another way.
Like you mentioned, a sub form with all the phone numbers!

Thanks for a quick and informative answere!

// Niklas

Hi!

I´m having a minor problem of adding/editing records in a form based on a
multitable query!

The Q is based on 3 tables <tblPerson> (containing Name, address, Dob etc.),
<tblMember> (containing MemberNo, MemberType, EnlistedDate, etc.) and
<tblMemberValidation> (containing MemberEndDate, MemberFee, MemberCard
etc.).

...
Now to my problem!

I want to disply a form showing each member (MemberNo, Name and all types of
phonenumbers (home, work etc.)), in a datasheet form, which have a valid
membership.

So I base the form on a Q qryMainMemberList whichs looks like this:

======================================================
SELECT qryMemberList.fkPersonID, qryMemberList.Name, qryMemberList.LastName,
qryPhoneNumbers_CrossQ.Home, qryPhoneNumbers_CrossQ.Work,
qryPhoneNumbers_CrossQ.Mobile, qryMemberList.MemberNo,
qryMemberList.MemberType, qryMemberList.MemberEndDate,
qryMemberList.MemberCard
FROM qryMemberList LEFT JOIN qryPhoneNumbers_CrossQ ON
qryMemberList.fkPersonID = qryPhoneNumbers_CrossQ.fkPersonID;
========================================================

I have 2 problems with this which I need help with:

1.) It´s not possible to update a record, probably due to the cross query,
or? If so how do I solve this?

Exactly. Instead use a Form (based on a query joining tblPerson to
tblMember) with a Subform based on the phones table. If as the name
suggests qryPhoneNumbers_CrossQ is a Crosstab query, neither it nor
any query containing it as a Join can ever be updateable.

2.) How do I sort out only the records with the highest <MemberEndDate>. I
don´t whant to show a member twice just because he/she have payed member fee
twice?

Use a criterion of

=DMax|("[MemberEndDate]","tblMemberValidation","[tblMemberValidation].[fkPer
sonID]
= " & [txtPersonID])

 

[John Vinson]

Hi John!

Thanks for your answer! I´ll try it out!

What I´m trying to do is to make main form with all the most common
information you need about the members. A form where you can open up each
member if you need more information then what´s displayed in the mainform.

A Tab control with additional fields or a subform on it can be a very
easy way to do this.

I´ts a little difficult to explaine in text but if you have time and whant
download the demo program <Förening>

Sorry... rather too busy right now to do so!

 

[Niklas Östergren]

Hi again!

Sorry, but my brain wasn´t quit working in the beginning of the week! What
says that I need a updatable mainform, where I list all the common
information about our members, NOTHING!

I only whant to display the information, if the user whant´s to update any
data about a member he/she only have to open up a new form with just that
member on, right! ;-) Stupid me!

Thank´s anyway for taking time!

// Niklas

 

[Niklas Östergren]

Hmm, I´ts a long time ago I worked with developing in Acces, and my skills
are just bearable!

Please forgive me for asking but where shall the criteria be?

=DMax("[MemberEndDate]","tblMemberValidation","[tblMemberValidation].[fkPers
onID]

= " & [txtPersonID])

Can I use it as a filter on the form or do I have to open the form, from a
module, where I use this as a criteria in VBA when I open the form up? Or am
I totally out of line here? :-(

// Niklas

Hi!

I´m having a minor problem of adding/editing records in a form based on a
multitable query!

The Q is based on 3 tables <tblPerson> (containing Name, address, Dob etc.),
<tblMember> (containing MemberNo, MemberType, EnlistedDate, etc.) and
<tblMemberValidation> (containing MemberEndDate, MemberFee, MemberCard
etc.).

...
Now to my problem!

I want to disply a form showing each member (MemberNo, Name and all types of
phonenumbers (home, work etc.)), in a datasheet form, which have a valid
membership.

So I base the form on a Q qryMainMemberList whichs looks like this:

======================================================
SELECT qryMemberList.fkPersonID, qryMemberList.Name, qryMemberList.LastName,
qryPhoneNumbers_CrossQ.Home, qryPhoneNumbers_CrossQ.Work,
qryPhoneNumbers_CrossQ.Mobile, qryMemberList.MemberNo,
qryMemberList.MemberType, qryMemberList.MemberEndDate,
qryMemberList.MemberCard
FROM qryMemberList LEFT JOIN qryPhoneNumbers_CrossQ ON
qryMemberList.fkPersonID = qryPhoneNumbers_CrossQ.fkPersonID;
========================================================

I have 2 problems with this which I need help with:

1.) It´s not possible to update a record, probably due to the cross query,
or? If so how do I solve this?

Exactly. Instead use a Form (based on a query joining tblPerson to
tblMember) with a Subform based on the phones table. If as the name
suggests qryPhoneNumbers_CrossQ is a Crosstab query, neither it nor
any query containing it as a Join can ever be updateable.

2.) How do I sort out only the records with the highest <MemberEndDate>. I
don´t whant to show a member twice just because he/she have payed member fee
twice?

Use a criterion of

=DMax|("[MemberEndDate]","tblMemberValidation","[tblMemberValidation].[fkPer
sonID]
= " & [txtPersonID])

 

[John Vinson]

Can I use it as a filter on the form or do I have to open the form, from a
module, where I use this as a criteria in VBA when I open the form up? Or am
I totally out of line here? :-(

You can use it as either, or you can use it as a criterion on the
field in the Query.

 

[MSAccess]

OK!

Now it works! I hade to change some fieldnames (from fkPersonID to
fkMemberID) since I was refering to table tblMemberValidation and since
ther´s no field name fkPersonID there!

Thanks a lot for your help!

// Niklas