So loop through with code similar to this to remove all double commas
Code:Dim X As Integer, Y As Integer StartCheck: X = Len(strSQL) strSQL = Replace(strSQL,",,",",") Y = Len (strSQL) If Y<X Then GoTo StartCheck
StartCheck:
X = Len(strSQL)
strSQL = Replace(strSQL, ", ,", ",")
strSQL = Replace(strSQL, ", FROM", " FROM")
Y = Len(strSQL)
If Y < X Then GoTo StartCheck
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," ", " ")
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
Hi. As Colin said, have you tried "stepping through" it?
If ctl.ControlType = acCheckBox And ctl = True And Nz(ctl.Tag,"") <> "" Then
If ctl.ControlType = acCheckBox And Nz(ctl,0) = True And Nz(ctl.Tag,"") <> "" Then
1. You have an unbound checkbox which can also be null. Thea black shading are null values Try this
Code:If ctl.ControlType = acCheckBox And ctl = True And Nz(ctl.Tag,"") <> "" Then
If that still errors try
Code: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:
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...
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
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