View Query, Select Items, Append to new table (1 Viewer)

BTD

New member
Local time
Today, 09:59
Joined
Jun 26, 2021
Messages
9
Spit-balling here:

I've got a query that shows all records of a specific incident. I'm wondering if with the query living on a form, I can select multiple specific items out of that query, and then append those results to a different table for emailing. I've already developed a method to email with an HTML table for other requests, I'd just have to adapt it for my purposes.

More details: The query only shows records of associate damaged materials, I'd like to be able to click the grey box on the side of the record and then press a button on my form to only have those results show up in my email body. OR Have some kind of control item on the form itself to control which items get sent.

Any insight would be amazing seeing as Google has come up empty on my searches.



Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:59
Joined
May 7, 2009
Messages
19,175
maybe you need a "record selector".
on the demo form Click on the record selector to add/remove from selection.
 

Attachments

  • RecordSelectorClick.accdb
    540 KB · Views: 148

BTD

New member
Local time
Today, 09:59
Joined
Jun 26, 2021
Messages
9
I'll give it a shot! Forgot entirely about that feature
 

isladogs

MVP / VIP
Local time
Today, 13:59
Joined
Jan 14, 2017
Messages
18,186
Similar to both of the above, I just add a bound boolean field to the form. Check the records you want to select then click a filter button
 
Last edited:

BTD

New member
Local time
Today, 09:59
Joined
Jun 26, 2021
Messages
9
Got the multiselect from ArnelGP working! now the next step is to figure out how to email the data i've got highlighted. I'll see what I can dig up.
I'm going to try making a second query to only show records filtered on that form by the txtSelected box and see what works.

Thanks all!
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:59
Joined
May 7, 2009
Messages
19,175
txtSelected contains all ID (of selected records).
you will then need to create a Insert SQL:

dim sID as string, sSQL as string
dim db as dao.database

sID = Me!txtselected
'remove the trailing ","
if Right$(sID,1)="," then sID = Left$(sID,Len(sID)-1)

'insert record here
set db=currentdb
sSQL = "insert into TargetTable (field1, field2) select field1, field2 from SourceTable Where ID IN (" & sID & ");"
db.Execute sSQL
 

BTD

New member
Local time
Today, 09:59
Joined
Jun 26, 2021
Messages
9
txtSelected contains all ID (of selected records).
you will then need to create a Insert SQL:

dim sID as string, sSQL as string
dim db as dao.database

sID = Me!txtselected
'remove the trailing ","
if Right$(sID,1)="," then sID = Left$(sID,Len(sID)-1)

'insert record here
set db=currentdb
sSQL = "insert into TargetTable (field1, field2) select field1, field2 from SourceTable Where ID IN (" & sID & ");"
db.Execute sSQL
ok that's BEAUTIFUL. Now I can use my loop query to add all the fields into an HTML Email Table! The only error I'm getting is this: I have a Date Column that's throwing out an INSERT INTO error


Code:
Private Sub EmailResults_Click()
DoCmd.SetWarnings False

DoCmd.OpenQuery ("ClearDroppedRackTable")

Dim sID As String, sSQL As String
Dim db As dao.Database

sID = Me!txtSelected
'remove the trailing ","
If Right$(sID, 1) = "," Then sID = Left$(sID, Len(sID) - 1)

'insert record here
Set db = CurrentDb
sSQL = "insert into DroppedRackTable (ID, Date, QN, Shift, Material, QTY, Reason, AssociateDamaged, Cost) select ID, Date, QN, Shift, Material, QTY, Reason, AssociateDamaged, Cost from [STR Database] Where ID IN (" & sID & ");"
db.Execute sSQL
End Sub

1625147922978.png
1625147935631.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:59
Joined
May 7, 2009
Messages
19,175
you enclosed Date on square bracket [Date], since date is a reserved word.
 

Users who are viewing this thread

Top Bottom