Export data fields based on checkboxes on Form

[isladogs]

So loop through with code similar to this to remove all double commas

Dim X As Integer, Y As Integer

StartCheck:
X = Len(strSQL)
strSQL = Replace(strSQL,",,",",")
Y = Len (strSQL)
If Y<X Then GoTo StartCheck

 

[phinix]

So loop through with code similar to this to remove all double commas

Dim X As Integer, Y As Integer

StartCheck:
X = Len(strSQL)
strSQL = Replace(strSQL,",,",",")
Y = Len (strSQL)
If Y<X Then GoTo StartCheck

Cool! Thanks
I just added one line there as at the end I was getting ", FROM":

StartCheck:
X = Len(strSQL)
strSQL = Replace(strSQL, ", ,", ",")
strSQL = Replace(strSQL, ",  FROM", " FROM")
Y = Len(strSQL)
If Y < X Then GoTo StartCheck

 

[isladogs]

Excellent.
You could probably move the extra line after the loop as it should only be needed once.
Or perhaps just replace any double spaces with
strSQL = Replace(strSQL," ", " ")

 

[phinix]

Excellent.
You could probably move the extra line after the loop as it should only be needed once.
Or perhaps just replace any double spaces with
strSQL = Replace(strSQL," ", " ")

It's working fine, but for some reason when I add back my error handling, it comes up with this error:

"Error updating: Object doesn't support this property or method"

Why is that?

 

[isladogs]

No idea without seeing the latest code. Most of your error handling was disabled anyway apart from the On Error Resume Next line which you shouldn't normally use anyway.

If not already done reinstate the 'proper' error handling then step through your code. If you have MZ Tools or similar you can add line numbers to help pin down the error.

 

[phinix]

Here is latest 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
Dim X As Integer, Y As Integer


On Error GoTo Proc_Err

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 [ID], " & strSQL & " FROM Data"

StartCheck:
    X = Len(strSQL)
    strSQL = Replace(strSQL, ", ,", ",")
    Y = Len(strSQL)
    If Y < X Then GoTo StartCheck

    strSQL = Replace(strSQL, ",  FROM", " FROM")
    
    
    Debug.Print strSQL
    db.Execute strSQL, dbFailOnError
    
    
    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

 

[theDBguy]

Hi. As Colin said, have you tried "stepping through" it?

 

[Deleted member 147267]

Why not just post a copy of the db? It's (mostly?) based on my code so I feel as though I ought to try to make it work in conjunction with the rest of it.

 

[phinix]

I don't get it.. error handling keeps giving me this error.
I commented out all those lines, so it works fine.
I just gonna continue working on it without. Not ideal, but at least it works.

Now I made it SELECT INTO and it creates a nice table that will be exported later on by another macro.

 

[phinix]

Hi. As Colin said, have you tried "stepping through" it?

OK, I've run debug - it stops on second loop on this line:

If ctl.ControlType = acCheckBox And ctl = -1 And ctl.Tag <> "" Then

It goes through ok first time, then loops back and stops at second run.

Does it mean that one of the objects on that form is killing it?

Second checkbox is not ticked. It has this black square in it, like a black filling.
It is not set to have 3 states, but when you open a blank form with checkbox, all of them have this filling.

EDIT: OK, it doesn't matter if second box is ticked or not, it still gives me that error on second loop...

 

[isladogs]

1. You have an unbound checkbox which can also be null. Thea black shading are null values Try this

If ctl.ControlType = acCheckBox And ctl = True And Nz(ctl.Tag,"") <> "" Then

If that still errors try

If ctl.ControlType = acCheckBox And Nz(ctl,0) = True And Nz(ctl.Tag,"") <> "" Then

2. Check the options in the VBE. Make sure error trapping is set to break on unhandled errors (not on all errors)

3. Remove the line On Error Resume Next

4. Recommend you avoid using a make table query as its unnecessary for running the export. Just create a SELECT query for the required data and use that in your export

5. Use VBA instead of macros. You will have more power and greater control

 

[phinix]

1. You have an unbound checkbox which can also be null. Thea black shading are null values Try this

If ctl.ControlType = acCheckBox And ctl = True And Nz(ctl.Tag,"") <> "" Then

If that still errors try

If ctl.ControlType = acCheckBox And Nz(ctl,0) = True And Nz(ctl.Tag,"") <> "" Then

2. Check the options in the VBE. Make sure error trapping is set to break on unhandled errors (not on all errors)

3. Remove the line On Error Resume Next

4. Recommend you avoid using a make table query as its unnecessary for running the export. Just create a SELECT query for the required data and use that in your export

5. Use VBA instead of macros. You will have more power and greater control

Did these:
1. Both solutions still give me same error.
2. Yep, set on unhandled errors
3. Removed.
4. I realised that I will need this table created anyway, so I create it then will export, no probs.
5. Righto, I used to use Macro "ImportExportText", as don't know how to write a code for exporting to txt in standard txt/csv format.

I don't understand why it works fine without error handling lines and creates table perfectly, but if I put those lines back in, it crashes...

 

[isladogs]

Did these:
4. I realised that I will need this table created anyway, so I create it then will export, no probs.
5. Righto, I used to use Macro "ImportExportText", as don't know how to write a code for exporting to txt in standard txt/csv format.

I don't understand why it works fine without error handling lines and creates table perfectly, but if I put those lines back in, it crashes...

1. Try setting a default value for your checkbox =false and set triple state =false
4. In that case, don't keep making a fresh copy of it. Instead empty the table then append new data
5 To start with, use the ribbon command to convert your macro to vba. You can improve the code later.

As already suggested, upload a cut down version of your app for someone to look at

 

[phinix]

While working on that error handling issue, I have another question.

I have a table and want to run query to view this table's fields in specific order - taking last field to be first.
I spent hours on reading about OrdinalPosition, re-creating the table, selecting * except one field and then re-querying it with this field first then rest...
For a love of god I can't get it sorted...

There must be a simple solution to run select query on table and changing the order of it. I just need to move last field to front. How can I do that?

 

[isladogs]

As this is a separate question it ought to be in a separate thread.
However, unless I'm missing the point, this is so trivial to do that you will kick yourself!

In your query, just add the fields in the order you want them displayed.
OR when you view the query results just drag the fields to where you want them to appear. Access will remember the order for the next time

 

[phinix]

As this is a separate question it ought to be in a separate thread.
However, unless I'm missing the point, this is so trivial to do that you will kick yourself!

In your query, just add the fields in the order you want them displayed.
OR when you view the query results just drag the fields to where you want them to appear. Access will remember the order for the next time

From what I've read, it is not that trivial - I forgot to mention - fields are random, not always the same, so I cannot simply list them. It needs to be reformatted from *,[that field] to [that field],*

I need to either re-write all ordinal positions of fields, or somehow move last field to be first one.

EDIT: unless I find out how to reset Autonumber (for existing rows to start from 1).
That field that I need to move is an Autonumber - I create it and move to the front. However that table already has an autonumber but after deleting some records, it's not continuous. If I could reset old autonumber I wouldn't need to have that extra field.

OR
If I could run a query to view like this: SELECT [that field], * FROM table
but excluding [that field] in *, like SELECT [that field],*-[that field]
so it wouldn't be displayed twice

 

[isladogs]

You do seem to be making work for yourself unnecessarily...
Several issues here
1. If you use SELECT .* you get all fields so what you suggest will cause a field to be repeated. If you want all fields except one, you need to specify them individually.

2. Autonumber fields are designed to ensure records are unique. They are not intended to have any meaning, will not necessarily be sequential or continuous. You shouldn't use them to indicate a record order.
Also you can only have one autonumber field in a table

If you need to have a sequential number field, you can do this with a number field that is calculated using DMax(FieldName)+1 each time a new record is added.
Or you can do it in a query by using a 'rank order. See http://www.mendipdatasystems.co.uk/rank-order-queries/4594424063 for one method of doing this

 

[phinix]

You do seem to be making work for yourself unnecessarily...
Several issues here
1. If you use SELECT .* you get all fields so what you suggest will cause a field to be repeated. If you want all fields except one, you need to specify them individually.

2. Autonumber fields are designed to ensure records are unique. They are not intended to have any meaning, will not necessarily be sequential or continuous. You shouldn't use them to indicate a record order.
Also you can only have one autonumber field in a table

If you need to have a sequential number field, you can do this with a number field that is calculated using DMax(FieldName)+1 each time a new record is added.
Or you can do it in a query by using a 'rank order. See http://www.mendipdatasystems.co.uk/rank-order-queries/4594424063 for one method of doing this

OK, I got it working now.
Using TableDef I loop through all fields and just skip that one particular field, then run SELECT and put that field first, then whole string with looped fields.

Thanks for explaining that I could use DMax to get a simple ascending Order ID field - this is very useful, thanks.


OK, so now when all works fine, I will prepare this db and upload it here to get this error handling sorted.
If someone could please take a look and let me know why this error handling doesn't work, I would be very grateful.

Let me prepare it first, then will upload this afternoon.

 

[phinix]

Hi guys, sorry for not getting back with this db, I was extremely busy last month.

So please find attached my db - it has this CreateExportTable function, where I commented out error handling that was giving me error.
It works fine when commented out, but crashes when I bring that back in

Can you please take a look and see why is this not working?
Obviously I would like to keep error handling in.

Please use Form_Export to pick fields and run the function.

 

[Deleted member 147267]

EDITED.
I will take a look tonight and see where this is at. Had to stop reviewing at post 32
However, you cannot do this
If ctl.ControlType = acCheckBox And ctl = True And Nz(ctl.Tag,"") <> "" Then
on one line because EACH test will be validated for EACH control in the loop, and some controls have no such properties that you might be testing for (e.g. a command button has no value property). That will generate the error you describe. You only want to perform the subsequent checks IF the control is of the required type.
If ctl.ControlType = acCheckBox Then
If ctl = True And Nz(ctl.Tag,"") <> "" Then
....
Even that should fail if the second line performs a test on a control that the test cannot apply to. Try as I might, I've never been able to find a control that didn't have a tag property lately but I can say that way back when I generated this error because one class of form control didn't have it. That was a long time ago and that's how I recall running into this problem. Don't anyone ask me what version that was because I can't look back.