help with accessing datasheet in VBA (1 Viewer)

sydc

Registered User.
Local time
Today, 11:39
Joined
May 28, 2010
Messages
10
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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:39
Joined
Jan 23, 2006
Messages
15,361
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:39
Joined
Aug 30, 2003
Messages
36,118
Both posts were moderated, now approved.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:39
Joined
Feb 28, 2001
Messages
26,996
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?
 
Last edited:

sydc

Registered User.
Local time
Today, 11:39
Joined
May 28, 2010
Messages
10
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:39
Joined
Feb 28, 2001
Messages
26,996
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 [I]involved-table[/I] SET [I]selected-field[/I] = " & [I]me.value-from-list/combo[/I] & " WHERE " & [I]some-sort-of-selection-criteria-goes-here[/I] ;
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.
 

sydc

Registered User.
Local time
Today, 11:39
Joined
May 28, 2010
Messages
10
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

Registered User.
Local time
Today, 11:39
Joined
May 28, 2010
Messages
10
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

Registered User.
Local time
Today, 11:39
Joined
May 28, 2010
Messages
10
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:39
Joined
Aug 30, 2003
Messages
36,118
Examiner is text so the value must be wrapped in delimiters, generally single quotes.
 

sydc

Registered User.
Local time
Today, 11:39
Joined
May 28, 2010
Messages
10
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. :):)
 

Users who are viewing this thread

Top Bottom