Re: [MS_AccessPros] Add Edit of a form is locked

 

Khalid-

If you're saving only one passport, then put it in the Members table.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On May 10, 2017, at 9:55 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

There will be single entry for each member who is participating in CST (Cultural Study Tour) and we saving only one Passport details of  each member:

So please guide further.

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Your query is not updatable because of the relationships.

CST Transaction M <- 1 Members 1 -> M tblPassport

That creates a Cartesian product between transactions an passports for each member.  For example, if there are 9 transactions for a member who has two passports, you get 18 rows (9 * 2).  A multiple table join works only if the relationships keep going "down" to lower levels:  tblA -> tblB -> tblC.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On May 10, 2017, at 9:37 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I have a unbound main form "CST Participants Entry", its has only one unbound combobox "cboCstName" and its

Row Source is:

SELECT CST.CstName

FROM CST

ORDER BY CST.CstName;

Main form has one subform "Data Entry CST Account Sub Form"

Link Master Fields: cboCstName

Link Child Fields: CstName

Record Source of subform "Data Entry CST Account Sub Form" is:

SELECT DISTINCTROW [CST Transaction].MemberID, [CST Transaction].MembershipType, Members.SurName, Members.GivenName, [CST Transaction].GenderStatus, tblPassport.PassportNo, [CST Transaction].CstName, [CST Transaction].OutwardDate, [CST Transaction].OutwardDeparturePort, [CST Transaction].OutwardArrivalPort, [CST Transaction].InwardDate, [CST Transaction].InwardDeparturePort, [CST Transaction].InwardArrivalPort

FROM (Members INNER JOIN [CST Transaction] ON Members.MemberID = [CST Transaction].MemberID) INNER JOIN tblPassport ON Members.MemberID = tblPassport.MemberID

ORDER BY [CST Transaction].MemberID;

I am stuck in Data entry/Edit mode and entering new record.

Before this form was working properly. The change in design of this form is that before field "PassportNo"

was in tbl "Members", now i created a new table "tblPassport" which contains field "PassportNo"

Is my relationship wrong? or query design wrong, should i have to make one or two queries?

Please help.

Khalid

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___