Updating fields from multi-select listbox based on a GROUP BY query

[NBRJ]

I have a form, with a multiselect listbox that is displaying data from a GROUP BY query, which itself pulls data from more than one (related) tables. What I want to be able to do, is for selected listbox entries find the matching records in one of the tables used in the query and run an update to fill some (currently empty) fields within that table from the form.

Example info below, that mimics the structure I have (which is not changing, this is not a design question, but a how do I do this question):

tParent

IDParent	Year	Type	[other fields...]	

P1		2016	A	
P2		2016	A 	
P3		2016	B
P4		2015	A
P5		2015	B

tChild

IDChild	FIDP	Title	Version		Location	

C1	P1	Title1	VersionA	1.01		
C2	P1	Title1	VersionA	1.02				
C3	P1	Title1	VersionB	1.03		
C4	P2	Title2	VersionZ	2.21		
C5	P2	Title3	VersionX	3.00		
C6	P3	Title1	VersionA	7.99	
C7	P4	Title1	VersionA	4.10		
C8	P4	Title1	VersionA	6.11	
C9	P4	Title2	VersionY	4.15	
C10	P5	Title3	VersionX_1	6.20
C11	P2	Title2	VersionY	9.80	
C12	P2	Title2	VersionZ	7.03	
C13	P2	Title3	VersionX	10.1
C14	P1	Title1	VersionA	8.99
C15	P2	Title4	VersionG	5.55

[...other fields...]	
FieldAddData1 (currently empty)
FieldAddData2 (currently empty) 
FieldAddData3 (currently empty)

FORM fModify (unbound)
LIST: lstModifySelections using:
GROUPED BY QUERY: qModifytChildFields:

• Year (group) - from tParent
• Type (group) - from tParent
• Title (group) - from tChild (BOUND)
• Version (group) - from tChild
• Location (count) - from tChild

Using data from example tables the listbox entries would look like, with a filter to 2016 entries only:

Year	Type	Title		Version		#Locations (count)

2016	A	Title 1		Version A	3 <<<
2016	A	Title 1		Version B	1 <<<
2016	A	Title 2		Version Z	2
2016	A	Title 2		Version Y	1
2016	A	Title 3		Version X	2	
2016	B	Title 1		Version A	1 <<<
2016	A	Title 4		Version G	1

The numbers I have in the real database are much greater, of course.

So a user would multi-select the list items they want and then select some fields on the form such as:
cboFieldAddData1 (to update FieldAddData1)
cboFieldAddData2 (to update FieldAddData2)
cboFieldAddData3 (to update FieldAddData3)

So for example, if they selected the first 2 rows and the last but one row, select the cbo values, press button to UPDATE (in this case a total of 5 records) and those 5 rows now has values from the cbos.

I know how to cycle through a selected listbox. I know how to UPDATE records. But what I don’t know is how to do it based on a grouped query in the scenario above.

I’ve created a second query that has the IDChild in (so shows each record ID, and can no longer be a GROUPED QUERY)… thinking that might be something to help me, but not sure my way forward now… or if I’m barking up the wrong tree. I've been going round in circles for this for days just logically working out how to get there and I'm stuck why is this is such a mental block, I don't know.

2nd query: qModifytChildFieldsWithID

• Year - from tParent
• Type - from tParent
• IDChild - from tChild
• Title - from tChild
• Version - from tChild
• Location - from tChild

Any suggestions would be gratefully received.

 

[pbaldy]

I thought we addressed that here:

http://www.access-programmers.co.uk/forums/showthread.php?t=286344

 

[NBRJ]

That query is based on one table - it's since become necessary to filter it by it's parent too

 

[sneuberg]

You can use the column property https://msdn.microsoft.com/en-us/library/office/ff834793.aspx to access the values in the listbox. Each time through the loop of the listbox box develop a SQL Update string. Let's say you start off with:

Dim strSQL as String
strSQL = "UPDATE "

with some if-then logic add the SET blah = blah e.g.

If Len(cboFieldAddData1 & vbNullString) > 0 Then
    strSQL = strSQL & " SET FieldAddData1 = '" & cboFieldAddData1 & "' "
End If

if you do all three at once putting in the commas between these will take some thought.

Then add the WHERE clause something like

strSQL = strSQL & " WHERE Year = '" & ListBoxName.Columm(0, i) & "' AND Type = '" & ListBoxName.Columm(1, i) & _
    "' AND Title = '" & ListBoxName.Columm(2, i) & "' AND Version = '" & ListBoxName.Columm(3, i) & "'"

Where ListBoxName would be the name of your listbox and i would be the index of your loop.

Then just

CurrentDb.Execute strSQL, dbFailOnError

Note: Text fields have been assumed in my overview. If this is not the case then take out the single quotes. Also for text fields single quotes in the the values from the combos boxes and listbox should be escaped just in case a title for example was something like "O'Leary's Cow" This is most easily done by creating a function like:

Private Function ESQ(str As String) As String

ESQ = Replace(str, "'", "''")

End Function

Then applied to the WHERE clause for example you would have:

strSQL = strSQL & " WHERE Year = '" & ESQ(ListBoxName.Columm(0, i)) & "' AND Type = '" & ESQ(ListBoxName.Columm(1, i)) & _
    "' AND Title = '" & ESQ(ListBoxName.Columm(2, i)) & "' AND Version = '" & ESQ(ListBoxName.Columm(3, i)) & "'"

 

[NBRJ]

Thank you! I've been playing with this while you posted this and got this almost fixed... thank you so much for both your assitance.

Clearly trying to this after long working days late at night all this week is my main problem. Clear head after 10 hours sleep and I did it in under an hour. I actually enjoy this when I'm not tired and overthinking things.