Kamis, 11 Mei 2017

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

 

Khalid-


If MemberID is a number, get rid of the quotes:

UPDATE Members SET Members.PassportNo = DLookUp("PassportNo","tblPassport","MemberID = " &  [MemberID]);

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 
(Paris, France)




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



John,

Sorry for replying late,, i was looking on the tab of my yahoo mail for a new email and it didn't showed up, i manually refreshed the group and saw your reply.

Should i run these update codes one by one separately ? I tried it with the first one.

Sql:
UPDATE Members SET Members.PassportNo = DLookUp("PassportNo","tblPassport","MemberID = " & "'" & [MemberID] & "'");

query grid at bottom

Field: PassportNo
Table: Members
Update to: DLookUp("PassportNo","tblPassport","MemberID = " & "'" & [MemberID] & "'")

Got no result. Error message was:
 ....didn't update 609 field(s) due to a type conversion failure, .....

Khalid



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

Nope.  Remember, you're trying to copy the "blank" fields.

UPDATE Members SET Members.PassportNo = DLookUp("PassportNo","tblPassport","MemberID = " & "'" & [MemberID] & "'");
UPDATE Members SET PassportIssueDate = DLookUp("PassportIssueDate","tblPassport","MemberID = " & "'" & [MemberID] & "'");
UPDATE Members SET Members.PassportExpiryDate = DLookUp("PassportExpiryDate","tblPassport","MemberID = " & "'" & [MemberID] & "'");
UPDATE Members SET Members.PassportIssuePlace = DLookUp("PassportIssuePlace","tblPassport","MemberID = " & "'" & [MemberID] & "'");


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 11, 2017, at 7:40 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

I have now added following fields in Members table:
PassportNo
Passport IssueDate
PassportExpiryDate
PassportIssuePlace

Obviously these all fields are blank now. I now want them to copy them from tblPassport to Members table.

I tried it with a update query you gave me for my other database (PCTL) where it did worked and solved the issue. But i made mistake by modifying its SQL and it deleted MemberID from tblPassport having 96 records.

I retrieved tblPassport from my backup. Now could you please correct the SQL of update query.

tblPassport has following fields:
MemberID
PassportNo
Passport IssueDate
PassportExpiryDate
PassportIssuePlace

My wrong SQL is as follows in which i did not mentioned:

Passport IssueDate
PassportExpiryDate
PassportIssuePlace

These are also to be included:

UPDATE tblPassport SET tblPassport.MemberID = DLookUp("MemberID","Members","MemberID = " & "'" & [MemberID] & "'");

I think it should be:

UPDATE Members SET Members.MemberID = DLookUp("MemberID","tblPassport","MemberID = " & "'" & [MemberID] & "'");

Khalid










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

John,

I have a Form "Add New Member" (Default View--> Single). On this Form I have put "TabCtlMemberInfo"
having some pages of members information, one of them is "PagePassport" and it has Form "frmPassportSubform" -->Record Source---> tblPassport with following fields:
PassportNo
Passport IssueDate
PassportExpiryDate
cboPassportIssuePlace

Now if i put these back to Members table, should i keep "frmPassportSubform" changing its Record source to Members table ?

Also putting PassportNo and other information in Members table against each member would be a fatigue for me.

Any way i will follow your suggestion.

Khalid
 


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

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@... [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 (9)
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.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)