Export data fields based on checkboxes on Form (1 Viewer)
- Thread starter phinix
- Start date
Cool! Thanks
I just added one line there as at the end I was getting ", FROM":
Code:
StartCheck:
X = Len(strSQL)
strSQL = Replace(strSQL, ", ,", ",")
strSQL = Replace(strSQL, ", FROM", " FROM")
Y = Len(strSQL)
If Y < X Then GoTo StartCheckIt'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
MVP / VIP
- Local time
- Today, 20:36
- Joined
- Jan 14, 2017
- Messages
- 18,219
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.
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.
Here is latest 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
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
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.
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.
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
MVP / VIP
- Local time
- Today, 20:36
- Joined
- Jan 14, 2017
- Messages
- 18,219
1. You have an unbound checkbox which can also be null. Thea black shading are null values Try this
If that still errors try
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
Code:
If ctl.ControlType = acCheckBox And ctl = True And Nz(ctl.Tag,"") <> "" ThenIf that still errors try
Code:
If ctl.ControlType = acCheckBox And Nz(ctl,0) = True And Nz(ctl.Tag,"") <> "" Then2. 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
MVP / VIP
- Local time
- Today, 20:36
- Joined
- Jan 14, 2017
- Messages
- 18,219
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
Last edited:
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?
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
MVP / VIP
- Local time
- Today, 20:36
- Joined
- Jan 14, 2017
- Messages
- 18,219
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
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
Last edited:
isladogs
MVP / VIP
- Local time
- Today, 20:36
- Joined
- Jan 14, 2017
- Messages
- 18,219
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
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.
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.
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.
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.
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.
Last edited:
Similar threads
