Export data fields based on checkboxes on Form (1 Viewer)

phinix · Dec 3, 2019

I have a form with all table fields listed and check boxes next to them.
What I'm trying to achieve is to export all data fields that user selects to txt file.

Problem is it would need to check which check-box is picked, then export only these fields.

How can I do that in VB?

Could I write something like: For all checked check-boxes, export data...

I could build it on conditions, but there are 23 fields...

theDBguy · Dec 3, 2019

Hi. One way is to construct a SQL statement as you go through each checkbox and then save it into a query object, which you can export to Excel. I usually have a query called qryExcel that I use for this purpose. The SQL statement gets updated before exporting the query to Excel.

Micron · Dec 3, 2019

Listed how?
You will need a code solution. Probably one that loops through the fields of the specified table and adds their names to a make table query sql statement that is built on the fly.Whether or not to include a given field would be governed by the checkbox being selected. An easy way of relating the check to the table field name would make this easier. Perhaps
- checkbox label caption or name is the same as field
- checkbox name is the same or contains the name (e.g. chkCustomer)
- checkbox Tag property is the field name. I think this option is best.
EDIT - forgot to add that a make table query isn't what I intended to post but I'll leave it in as an option. Probably Select would do fine.

NearImpossible · Dec 4, 2019

You can try something along the lines of

Code:

Dim rst As DAO.Recordset

Open "PATH OF TEXT FILE" For Output As #1

Set rst = CurrentDb.OpenRecordset("Select [DATA FIELD] from [TABLE] Where [CHECK BOX] = True", dbOpenSnapshot)

Do While Not rst.EOF
    Print #1, rst![DATA FIELD]
    rst.MoveNext
Loop

rst.Close

Set rst = Nothing

Close #1

Update "PATH OF TEXT FILE" to the path where the text file will be located
Update [DATA FIELD] to the column name that contains the data
Update

to the table name that contains the data
Update [CHECK BOX] to the name of your check box

Micron · Dec 4, 2019

I've had this code open all day long in a form module. It's getting late now and I'm going to close the db. Might as well post it now rather than have to find it again later. It's untested.

Code:

Sub TextExport()
Dim ctl As Control
Dim sql As String

For Each ctl In Me.Controls
  If ctl.ControlType = acCheckBox And ctl = -1 And ctl.Tag <> "" Then
    sql = sql & ctl.Tag & ", "
  End If
Next

If sql <> "" Then
  Dim fld As Field
  Dim rs As DAO.Recordset, db As DAO.Database
  sql = Left(sql, Len(sql) - 2)
  sql = "SELECT " & sql & " FROM tableNameGoesHere"
  Set db = CurrentDb
  Set rs = db.OpenRecordset(sql)
   If Not (rs.BOF And rs.EOF) Then
     rs.MoveFirst
     Open "path to text file here" For Output As #1
     Do While Not rs.EOF
        For Each fld In rs.Fields
           Print #1, rs.Fields(fld) & "|"
        Next
      'enable next if line wrap required at end of each record; not tested; may need to be Chr(13) & Chr(10) and not vbCrLf
       'Print #1, vbCrLf
        rs.MoveNext
     Loop
   Close #1
   rs.Close
   Set rs = Nothing
   Set db = Nothing
  End If
 End If

AFAIK, the text file (which has to already exist) will be over-written each time when using this approach. Also, there's no error handling since I don't know if this is close to what's wanted so I've stopped here.

As per post 3 the checkbox tag property should be the name of the field it represents - no spaces or anything else that would require the use of brackets. I'm also assuming you're not using the tag property on any other checkboxes for any other purpose.

isladogs · Dec 4, 2019

To avoid overwriting the text file each time, you can include the current date in the file name. For example
"c:\ExportData" & Format(Date, "yyyymmdd") & ".txt"
or if likely to be repeated the same day, use date and time...
"c:\ExportData" & Format(Now, "yyyymmddhhnnss") & ".txt"

phinix · Dec 4, 2019

Thanks guys, it took me a while to use your suggestion, but its working great!

Thank you all!

I decided to create a table first with this data, then export it.

I have new problem, wanted to simply add autonumber first in this new table and for some reason when running SELECT newID = IDENTITY(1, 1), field1, field2,... it gives me "undefined function" error.

theDBguy · Dec 4, 2019

phinix said:
Thanks guys, it took me a while to use your suggestion, but its working great!

Thank you all!

I decided to create a table first with this data, then export it.

I have new problem, wanted to simply add autonumber first in this new table and for some reason when running SELECT newID = IDENTITY(1, 1), field1, field2,... it gives me "undefined function" error.

Hi. Glad to hear you got it sorted out. I responded to your other thread before I saw this one. I'll have to amend my reply to address the need for adding an autonumber field. Cheers!

phinix · Dec 4, 2019

Micron said:
I've had this code open all day long in a form module. It's getting late now and I'm going to close the db. Might as well post it now rather than have to find it again later. It's untested.

Code:

Sub TextExport() Dim ctl As Control Dim sql As String For Each ctl In Me.Controls If ctl.ControlType = acCheckBox And ctl = -1 And ctl.Tag <> "" Then sql = sql & ctl.Tag & ", " End If Next If sql <> "" Then Dim fld As Field Dim rs As DAO.Recordset, db As DAO.Database sql = Left(sql, Len(sql) - 2) sql = "SELECT " & sql & " FROM tableNameGoesHere" Set db = CurrentDb Set rs = db.OpenRecordset(sql) If Not (rs.BOF And rs.EOF) Then rs.MoveFirst Open "path to text file here" For Output As #1 Do While Not rs.EOF For Each fld In rs.Fields Print #1, rs.Fields(fld) & "|" Next 'enable next if line wrap required at end of each record; not tested; may need to be Chr(13) & Chr(10) and not vbCrLf 'Print #1, vbCrLf rs.MoveNext Loop Close #1 rs.Close Set rs = Nothing Set db = Nothing End If End If

AFAIK, the text file (which has to already exist) will be over-written each time when using this approach. Also, there's no error handling since I don't know if this is close to what's wanted so I've stopped here.

As per post 3 the checkbox tag property should be the name of the field it represents - no spaces or anything else that would require the use of brackets. I'm also assuming you're not using the tag property on any other checkboxes for any other purpose.

Looks like the way I did it doesn't work for random picked fields, so started to try your code.

Here is what I have just now, simply trying to run simple SELECT before I start creating tables and exporting it etc

I'm getting error saying: "Error updating: Object doesn't support this property or method"

Here is the code:

Code:

Public Function CreateExportTable() As Boolean
On Error Resume Next
  
Dim ws As Workspace
Dim db As DAO.Database
Dim strSQL As String
Dim table1 As String
Dim frm As String
Dim Form_Export As Form
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)

frm = "Form_Export"

Dim ctl As Control
Dim sql As String

On Error GoTo Proc_Err
'start a transaction to ensure all updates are run or rolled back
ws.BeginTrans
    
If TableExists("Data Export") = True Then
CurrentDb.Execute "DROP TABLE [Data Export]"
End If


For Each ctl In Forms!Form_Export.Controls
  If ctl.ControlType = acCheckBox And ctl = -1 And ctl.Tag <> "" Then
    strSQL = strSQL & ctl.Tag & ", "
  End If
Next

If strSQL <> "" Then
  strSQL = Left(sql, Len(sql) - 2)
  strSQL = "SELECT " & sql & " FROM Data"
  
Debug.Print strSQL
db.Execute strSQL, dbFailOnError


Application.RefreshDatabaseWindow

Else
MsgBox "Dupa zbita!"

End If

ws.CommitTrans

Proc_Exit:
  Set ws = Nothing
  Set db = Nothing
  Exit Function

Proc_Err:
  ws.Rollback
  MsgBox "Error updating: " & Err.Description
  Resume Proc_Exit
  
End Function

What does this error mean and how can I fix it?

theDBguy · Dec 4, 2019

phinix said:
I'm getting error saying: "Error updating: Object doesn't support this property or method"

On which line are you getting this error?

Micron · Dec 4, 2019

strSQL = "SELECT " & sql & " FROM tableNameGoesHere"
You have a table named tableNameGoesHere ??
Always indicate which line causes the error otherwise I'm searching for a needle in a haystack - in the dark. Maybe you should start with just getting what I wrote to work according to your initial post, which was to collect the field names according to checked controls for use in a sql statement. You've added a layer of complexity with transactions and such, to something that wasn't yet proven to work. I'd suggest one step at a time, unless something obvious is revealed by identifying which line raises the error. You can always post a db copy as well, but maybe start with identifying the line.

phinix · Dec 4, 2019

theDBguy said:
On which line are you getting this error?

It doesn't higllights any line, just error windows pops up.

theDBguy · Dec 4, 2019

phinix said:
It doesn't higllights any line, just error windows pops up.

And there's no "Debug" option on the error message?

PS. Ah, I think I see why. Try disabling your error handler for a minute, while you're still troubleshooting this problem.

phinix · Dec 4, 2019

theDBguy said:
And there's no "Debug" option on the error message?

Nope.

OK, I cleaned up the code to keep necessary lines:

Code:

Public Function CreateExportTable() As Boolean
On Error Resume Next
  
Dim ws As Workspace
Dim db As DAO.Database
Dim strSQL As String
Dim table1 As String
Dim frm As String
Dim Form_Export As Form
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)


Dim ctl As Control
Dim sql As String

For Each ctl In Forms!Form_Export.Controls
  If ctl.ControlType = acCheckBox And ctl = -1 And ctl.Tag <> "" Then
    strSQL = strSQL & ctl.Tag & ", "
  End If
Next

If strSQL <> "" Then
  strSQL = Left(sql, Len(sql) - 2)
  strSQL = "SELECT " & sql & " FROM Data"
  
Debug.Print strSQL
db.Execute strSQL, dbFailOnError


End If
  
End Function

So now it runs and debug print shows this :

SELECT FROM Data

Looks like it is not picking any check box names, right?

theDBguy · Dec 4, 2019

Hi. Did you see the PS (post script) I added to my earlier post?

isladogs · Dec 4, 2019

You should scrap the Resume Next line at least for now.

Shouldn't that be

Code:

If strSQL <> "" Then
  strSQL = Left(strSQL, Len(strSQL) - 2)
  strSQL = "SELECT " & strSQL & " FROM Data"

Micron · Dec 4, 2019

isladogs said:
Shouldn't that be

Code:

If strSQL <> "" Then strSQL = Left(strSQL, Len(strSQL) - 2) strSQL = "SELECT " & strSQL & " FROM Data"

That would indicate the lack of using OPTION EXPLICIT

phinix · Dec 5, 2019

theDBguy said:
Hi. Did you see the PS (post script) I added to my earlier post?

Hi, sorry, yes I saw it - I got rid of most extra lines to make it short and simple, including error handling.

phinix · Dec 5, 2019

Micron said:
That would indicate the lack of using OPTION EXPLICIT

Sorry, I fixed the code, it looks now like this:

Code:

Public Function CreateExportTable() As Boolean
On Error Resume Next
  
Dim ws As Workspace
Dim db As DAO.Database
Dim strSQL As String
Dim table1 As String
Dim frm As String
Dim Form_Export As Form
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)

'frm = "Form_Export"

Dim ctl As Control
Dim sql As String


'
'On Error GoTo Proc_Err
''start a transaction to ensure all updates are run or rolled back
'ws.BeginTrans
'
'If TableExists("Data Export") = True Then
'CurrentDb.Execute "DROP TABLE [Data Export]"
'End If


For Each ctl In Forms!Form_Export.Controls
  If ctl.ControlType = acCheckBox And ctl = -1 And ctl.Tag <> "" Then
    strSQL = strSQL & ctl.Tag & ", "
  End If
Next

If strSQL <> "" Then
  strSQL = Left(strSQL, Len(strSQL) - 2)
  strSQL = "SELECT " & strSQL & " FROM Data"
  
Debug.Print strSQL
db.Execute strSQL, dbFailOnError


'Application.RefreshDatabaseWindow
'
'Else
'MsgBox "Dupa zbita!"
'
End If

'ws.CommitTrans
'
'Proc_Exit:
'  Set ws = Nothing
'  Set db = Nothing
'  Exit Function
'
'Proc_Err:
'  ws.Rollback
'  MsgBox "Error updating: " & Err.Description
'  Resume Proc_Exit
  
End Function

Debug showing this:

SELECT , , , , , , , , , , , , , , , , , , , , , , , , , , FROM Data

Doesn't matter how many check boxes I tick.

EDIT: wait wait! My bad, I put Names for each checkbox, not Tag :banghead:

Let me fix this and get back to you guys

phinix · Dec 5, 2019

OK, now when I fixed all tags, all work fine when I tick all of them (25 check boxes), however I get double comas.

Also, when I tick random ones, I get this:

SELECT SourceID, , , , , , , , Surname, , , , Address1, , Address2, , , , , , , Postcode, , , , , , , , , , FROM Data

Export data fields based on checkboxes on Form (1 Viewer)

Registered User.

I’m here to help

AWF VIP

Registered User.

AWF VIP

MVP / VIP

Registered User.

I’m here to help

Registered User.

I’m here to help

AWF VIP

Registered User.

I’m here to help

Registered User.

I’m here to help

MVP / VIP

AWF VIP

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread