Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-13-2018, 02:43 AM   #1
sydc
Newly Registered User
 
Join Date: May 2010
Location: United Kingdom
Posts: 10
Thanks: 8
Thanked 0 Times in 0 Posts
sydc is on a distinguished road
help with accessing datasheet in VBA

Hi,
I hope someone can help me solve this problem with my database. I have tried many approaches without success.
I have a database with a main form called “frmMainDB” and on this form is a tabbed control called “TabControl1” with four tabs associated with this control. The problem is on the first tab called “BMFA Achievements”, which has a sub form associated with this tab which is set to display details in “Datasheet” view. The page name for this tab is “BMFAAchievementsSubForm” and the page number for this control is “0”. The sub form name for this tab is “BMFAAchievementSubForm” the link master property is set to the primary key “MainDBID” (auto numbered) on the main form table “tblMainDB”, the link child property is set to a numbered key “BMFAAchievementDBID” on the sub form table “tblBMFAAchievementsSubForm”. The BMFA Achievements sub form datasheet has four columns with headers from left to right as follows; “BMFA Achievement”, “Date Achieved”, “Examiner” and “Comments”. The datasheet can have as many as twenty rows for each member and when the focus is set to any of the locations under the header “Examiner” this triggers an event to load a form called “frmExaminerSelection”, this form is populated with the following controls; a Multi-Select Listbox and a form close button. When the selection from the multi-select listbox has been made the “Close” button is selected and the selection in the listbox is added to a string called “strList” where the data is stored as a “comer delimited concatenated text string”. It is this data stored in the string that I require loading into the location that has (had) focus under the header “Examiner”. This would need to be completed on the “Close Button” event on the “frmExaminerSelection” before the form closes. Would this be possible?
The full database is available for testing if it is required as this is a test database and carries no valid personal member details therefore does not contravene the Data Protection and Privacy regulations.
Regards,
SydC.

sydc is offline   Reply With Quote
Old 07-13-2018, 03:54 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,364
Thanks: 49
Thanked 1,844 Times in 1,794 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: help with accessing datasheet in VBA

SydC,

That's a lot of verbiage re How you are trying to achieve something. It is difficult (for me at least) to decipher the underlying issue. Can you describe what you are trying to do in plain English business terms. Once readers have some clarity with what you are trying to achieve, I'm sure some focused responses will follow.

You can post your database if you do a compact and repair, then post as a zip file.
Advise readers of steps to get to the troubled area; what to do; what to expect; and what you need.
Good luck.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
sydc (Yesterday)
Old 07-13-2018, 05:23 AM   #3
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,545
Thanks: 9
Thanked 3,778 Times in 3,721 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: help with accessing datasheet in VBA

Both posts were moderated, now approved.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
sydc (Yesterday)
Old 07-13-2018, 05:29 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,802
Thanks: 52
Thanked 1,058 Times in 967 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: help with accessing datasheet in VBA

It is very difficult to wade through that wall of letters to extract your problem statement.

It LOOKS like you are saying you have one row highlighted in datasheet view on a sub-form and have something related to a combo or list box that gets selected. When you click the Close button, you want the data selected from the combo/list box to be used to update a selected record in the datasheet.

Is that what you were asking about?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.

Last edited by The_Doc_Man; 07-13-2018 at 12:26 PM.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
sydc (Yesterday)
Old 07-13-2018, 05:38 AM   #5
sydc
Newly Registered User
 
Join Date: May 2010
Location: United Kingdom
Posts: 10
Thanks: 8
Thanked 0 Times in 0 Posts
sydc is on a distinguished road
Re: help with accessing datasheet in VBA

Yes, The_Doc_Man I am trying to update a selected record in a datasheet from another form when the close button is clicked.
Sorry about all the garbage in the first post tried too hard to get all the facts together.
sydc is offline   Reply With Quote
Old 07-13-2018, 12:38 PM   #6
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,802
Thanks: 52
Thanked 1,058 Times in 967 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: help with accessing datasheet in VBA

My thought is that it might (and I repeat, MIGHT) be easier if you were to build a really quick & dirty UPDATE query and just do a DoCmd.RunSQL on the result.

If you know something about the selected row on the sub-form datasheet so that you know WHICH record was selected, and if you know the value selected from the combo/listbox, you would build the string kind of like this:

Code:
sSQLUPD = "UPDATE involved-table SET selected-field = " & me.value-from-list/combo & " WHERE " & some-sort-of-selection-criteria-goes-here ;
DoCmd.RunSQL sSQLUPD
You have to find the value from the list/combo and some unique way of identifying the selected datasheet row and put those into the string, probably via string concatenation. The syntax I used for the SET field = value portion presumed a numeric field.

The fly in this ointment is that, based on your description, the datasheet is technically not on the same form as the command buttons. So when you go to click the Close button, your focus won't be on the sub-form and therefore, neither will your selection be there. You might do better to look into clicking or double-clicking on the datasheet to make the critical selection "stick" and to trigger your desired action at the same time.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
sydc (Yesterday)
Old 07-14-2018, 05:21 AM   #7
sydc
Newly Registered User
 
Join Date: May 2010
Location: United Kingdom
Posts: 10
Thanks: 8
Thanked 0 Times in 0 Posts
sydc is on a distinguished road
Re: help with accessing datasheet in VBA

Hi The_Doc_Man, thanks for the reply.
I use the "On Got Focus" event of the datasheet text box txbExaminer to trigger the loading of the Multi-Select form. When the form closes the focus is returned to the txbExaminer text box on the datasheet.
I am now looking at methods for referencing the record for the selected text box on the datasheet as you suggested.

sydc is offline   Reply With Quote
Old Yesterday, 05:45 AM   #8
sydc
Newly Registered User
 
Join Date: May 2010
Location: United Kingdom
Posts: 10
Thanks: 8
Thanked 0 Times in 0 Posts
sydc is on a distinguished road
Re: help with accessing datasheet in VBA

I have created a SQL statement to update a table but I receive an error "Runtime error '3144' Syntax error in UPDATE statement" in the code.

I am not sure if the statement is correct, have I missed something?.

The SQL statement is;

sqlExaminerUPD = "UPDATE tblBMFAAchievementsSubPage SET Examiner = " & strList & _
" WHERE BMFAAchievementDBID = " & intMemberID & " AND BMFAID = " & intRowID & " ;"

DoCmd.RunSQL sqlExaminerUPD

The table to update is; tblBMFAAchievementsSubPage
The column to update is; Examiner
The value to update is contained in; strList
The unique member reference is; intMemberID
The unique row reference is; intRowID

Any help would be appreciated.
Thank you.
sydc is offline   Reply With Quote
Old Yesterday, 06:55 AM   #9
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,545
Thanks: 9
Thanked 3,778 Times in 3,721 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: help with accessing datasheet in VBA

This should help:

http://www.baldyweb.com/ImmediateWindow.htm
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
sydc (Yesterday)
Old Yesterday, 07:11 AM   #10
sydc
Newly Registered User
 
Join Date: May 2010
Location: United Kingdom
Posts: 10
Thanks: 8
Thanked 0 Times in 0 Posts
sydc is on a distinguished road
Re: help with accessing datasheet in VBA

Thanks Paul,

I have done that previously and this is the result:

UPDATE tblBMFAAchievementsSubPage SET Examiner = BCRO, JEXT WHERE BMFAAchievementDBID = 4 AND BMFAID = 3 ;

I am not sure if there is something missing before the 'WHERE' statement. The BCRO and JEXT should be the value that will be written to the 'Examiner' column in the table. BCRO and JEXT are contained in the string strList.

Syd.
sydc is offline   Reply With Quote
Old Yesterday, 07:17 AM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,545
Thanks: 9
Thanked 3,778 Times in 3,721 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: help with accessing datasheet in VBA

Examiner is text so the value must be wrapped in delimiters, generally single quotes.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
sydc (Yesterday)
Old Yesterday, 08:12 AM   #12
sydc
Newly Registered User
 
Join Date: May 2010
Location: United Kingdom
Posts: 10
Thanks: 8
Thanked 0 Times in 0 Posts
sydc is on a distinguished road
Re: help with accessing datasheet in VBA

Thanks Paul.

Thought it might be something like that, I am in my early 70's and cannot think as quick and logically as I used to do.

Added the single quotes around the string strList and all now works fine. Fantastic forum.

Kind regards,
Syd.
sydc is offline   Reply With Quote
Old Yesterday, 10:08 AM   #13
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,545
Thanks: 9
Thanked 3,778 Times in 3,721 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: help with accessing datasheet in VBA

No problem Syd.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
sydc (Yesterday)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problems with accessing datasheet subform on a tab control yahoo Forms 0 07-01-2013 11:03 AM
referencing Datasheet records to copy to new datasheet newuser47 Modules & VBA 0 06-27-2011 10:05 PM
Accessing the fields of a datasheet. Sofo Forms 8 10-17-2007 11:27 AM
Populate a datasheet and extraction of data from datasheet Savita Forms 9 09-02-2007 12:24 PM
Populate a datasheet and extraction of data from datasheet Savita General 2 09-01-2007 11:38 AM




All times are GMT -8. The time now is 04:02 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World