Jumat, 12 Mei 2017

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

 

John,


Thank you very much for this effort for me. All of the 3 queries ran without error, and "blanks" in Members table were filled at their appropriate places.

This means that last
....= " & [MemberID]); 

should have been without quotes, am i getting it right ?

Thanks once again John

Khalid
 


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

Ooops.  Try this:

UPDATE Members SET [Passport IssueDate] = DLookUp("[Passport IssueDate]","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 
(Paris, France)




On May 12, 2017, at 6:16 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

Yes there is space in "Passport IssuePlace"

While putting in sql:
UPDATE Members SET [Passport IssueDate] = DLookUp("[Passport IssueDate]","tblPassport","MemberID = " [MemberID]);

I get error message:
Syntax error (missing operator) in query expression 'DLookup("[Passport IssueDate]","tblPassport,"MemberID="[MemberID]);

I am running these queries one by one separately.

Khalid



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

Khalid-

Did you run these three queries?

UPDATE Members SET [Passport IssueDate] = DLookUp("[Passport IssueDate]","tblPassport","MemberID = " [MemberID]);
UPDATE Members SET Members.PassportExpiryDate = DLookUp("PassportExpiryDate","tblPassport","MemberID = " [MemberID]);
UPDATE Members SET Members.PassportIssuePlace = DLookUp("PassportIssuePlace","tblPassport","MemberID = " [MemberID]);

It looks like you have a space in the name of Passport IssueDate, so I added that.

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



John,

I still cannot fill blanks in Members table for the following: 

Passport IssueDate
PassportExpiryDate

above are Date type

PassportIssuePlace --> Text



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

John,

Yes i replied you after checking that it updated field PassportNo in Members table.

Khalid


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

Yes, after you get rid of the quotes.  Did you check the Members table to see if it updated correctly?

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On May 11, 2017, at 22:13, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Yes, now it did worked for PassportNo

Should i run the other three Sql's one by one ?

Passport IssueDate
PassportExpiryDate

above are Date type

PassportIssuePlace --> Text

Khalid



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

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



(Message over 64 KB, truncated)

__._,_.___

Posted by: khalidtanweerburrah@yahoo.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (17)

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.


.

__,_._,___

Tidak ada komentar:

Posting Komentar