Using Check Boxes to Select Records for Exporting

[mulch17]

Hello everyone! I've got a question that is much harder than I anticipated. I was thinking this would be very easy, but I have been mistaken so far. Hopefully it's something obvious that I'm overthinking!

I'm happy to provide many more details as necessary, but I'll try to keep it brief for the first post.

I am trying to design a form, which would display records from a query. I would like to have check boxes next to each of the records returned from the query. The user would choose specific records by clicking in the check boxes, and then click a button that exports them to an Excel spreadsheet.

However, this has turned out to be trickier than I thought. The check boxes are displayed as blue squares by default, and individual records can't be selected. You can only check none or all of the records.

My understanding, from searching through old posts, is that this is because check boxes are unbound controls. The solutions I've seen all say to add a Yes/No field to the underlying table. However, my records are coming from a query, not a table. Additionally, this particular query is used in many places, so I'm hesitant to make any modifications or additions to it, for regression testing reasons. I've also seen articles that advise avoiding Yes/No fields, and using Number fields instead.

To me, it seems like overkill to add a whole new field to a table and several queries, just to have a check box interface on one form. I would want those check boxes to be cleared when the form closes, so there's really no need to waste space storing them. Is there an easier way to do this? Am I missing something obvious here?

Thanks in advance for any replies, I appreciate it!!!

 

[CJ_London]

hmm - it's a fair amount of work but investigate using ado disconnected recordsets - google to find out more - this looks like the sort of thing you are looking for

http://www.databasejournal.com/feat...p/3846361/Create-In-Memory-ADO-Recordsets.htm

 

[mulch17]

Thanks for the quick reply. This link looks like what I want, but if I'm being completely honest, it is above my current level of understanding of Access. I would need to spend a good amount of time reviewing this code and modifying it for my database, which would take a while.

Specifically, I'm still wondering how to "link" the exporting code to the checked form records. The example file at that link just has checks, but nothing more than that - they aren't used to generate any other forms/reports/queries, from what I'm seeing.

I'm envisioning a simple select statement, along these lines (pseudo-code):

SELECT * FROM [Form] WHERE [CheckBox].[Value] = TRUE

Obviously that's just pseudo-code, I'm still going to try to find the syntax for the real code and go from there.

 

[mulch17]

I'm also running into trouble with enabling the proper references/libraries that are used in that code. For some reason, I keep getting a "User-defined type not defined" error, even though I have the ActiveX objects reference checked.

The code will eventually be deployed in a classified environment, so I have no idea what version of that library they are going to have, but it's probably different than mine.

Having said all of this, I think that method is going to be quite a bit of trouble, probably much more than it's worth. I did not think it would be this challenging.

 

[CJ_London]

I did say it was a fair amount of work

you would step look through the recordset using movefirst - movenext - either to do something like export a report or collect say the id's together in a string so you can then build a query something like

 Dim SelectedIDs as string
  
 me.recordset.movefirst
 while not me.recordset.eof
     if me.recordset("Selected") then SelectedIDs=SelectedIDs & ", " & me.recordset("ID")
     me.recordset.movenext
 Wend
 SelectedIDs=mid(SelectedIDs,2)
 sqlstr="SELECT * FROM myTable WHERE ID in (" & SelectedIDs & ")

 

[JHB]

...
To me, it seems like overkill to add a whole new field to a table and several queries, just to have a check box interface on one form. I would want those check boxes to be cleared when the form closes, so there's really no need to waste space storing them. Is there an easier way to do this? Am I missing something obvious here?

Thanks in advance for any replies, I appreciate it!!!

You could create a new query, based one the query you don't want to alter, which could put the result into a table just created for the form's purpose and have the field for the check box in that table.

 

[MarlaC]

You could create a new query, based one the query you don't want to alter, which could put the result into a table just created for the form's purpose and have the field for the check box in that table.

This is exactly what I would do. Populate a temporary local table with your query results and have the additional true/false field in this table only. You can empty or destroy the table once you've completed your output or in the form's close event so that you're not storing unneeded data. It will allow individual record selection for output. You will have to allow editing on the form, but can lock all fields other than the checkbox to prevent data changing from the original.

 

[mulch17]

I did say it was a fair amount of work

you would step look through the recordset using movefirst - movenext - either to do something like export a report or collect say the id's together in a string so you can then build a query something like

 Dim SelectedIDs as string
  
 me.recordset.movefirst
 while not me.recordset.eof
     if me.recordset("Selected") then SelectedIDs=SelectedIDs & ", " & me.recordset("ID")
     me.recordset.movenext
 Wend
 SelectedIDs=mid(SelectedIDs,2)
 sqlstr="SELECT * FROM myTable WHERE ID in (" & SelectedIDs & ")

I know it's been a while, but I wanted to come back and say thank you, because I basically used your two replies to complete this. It was surprisingly non-trivial, but it was way less horrible than I expected.

It still could present a challenge, since I do not know what version of the "Microsoft Office Access Database Engine Object Library" the user has in their classified environment, and I had to manually enable that reference on my machine for this to work.

Thanks for your replies, CJ!!!

 

[CJ_London]

since I do not know what version of the "Microsoft Office Access Database Engine Object Library"

google 'late binding', however that particular library has been stable for many years