Export data fields based on checkboxes on Form

[Deleted member 147267]

At first I thought I might have been full of baloney because it didn't err as I expected - but I didn't notice that you set On Error Resume Next right off the bat. Never do that. In a code block that attempts to do something to something that might not exist, then OK. Examples might be getting an application instance that might not be open, setting a db property that might not exist, etc. but never (well almost) as a default. Also, you are not declaring multi-line variables properly.
Dim strSQL, strSQL2, strSQL3, strSQL4 As String
Only strSQL4 is a String, the rest are variants. Not sure why you're using a mix of embedded macros as well as code for pretty much the same things.

I'm at the point where strSQL is generated but I don't see the point of all that Replace stuff beforehand. It makes no changes and none seem necessary. I get SELECT [ID], SourceID, CompanyID, Company, ContactID INTO [Data Export Temp] FROM Data right off the bat.

There are no errors now, but I have no idea what's supposed to be going on. I made a couple of different checkbox selections and looked at the tables each time but saw no variations. A lot of this code could be cleaned up/removed I think, but without knowing what the difference is supposed to be from one time to another, I hesitate to do so.

 

[phinix]

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.

Thanks for looking into it, I appreciate it.

Right, so if I split that condition, will check for checkboxes, got it.
Then if it has a tag - so you are saying all will have tags so I shouldn't be checking this?

At first I thought I might have been full of baloney because it didn't err as I expected - but I didn't notice that you set On Error Resume Next right off the bat. Never do that. In a code block that attempts to do something to something that might not exist, then OK. Examples might be getting an application instance that might not be open, setting a db property that might not exist, etc. but never (well almost) as a default. Also, you are not declaring multi-line variables properly.
Dim strSQL, strSQL2, strSQL3, strSQL4 As String
Only strSQL4 is a String, the rest are variants. Not sure why you're using a mix of embedded macros as well as code for pretty much the same things.

I'm at the point where strSQL is generated but I don't see the point of all that Replace stuff beforehand. It makes no changes and none seem necessary. I get SELECT [ID], SourceID, CompanyID, Company, ContactID INTO [Data Export Temp] FROM Data right off the bat.

There are no errors now, but I have no idea what's supposed to be going on. I made a couple of different checkbox selections and looked at the tables each time but saw no variations. A lot of this code could be cleaned up/removed I think, but without knowing what the difference is supposed to be from one time to another, I hesitate to do so.

I think I understand what you mean about not using On Error Resume Next. Thanks.

Next - variables - What do you mean by variants? So I should declare them separately? OK, will do.

Macros are for "single runs" in case I need to run some of those actions separately by clicking

Replace are replacing empty strings in case there was no field - if I don't do that sql string will look like SELECT ID, , , FROM... so won't work.

Basically, table Data Export will change, when you change fields on the form.
It creates different table for export process, by letting user choose what fields he needs to export.
Pick different fields on form and click the button - Data Export table will change.

Sorry, error handling is commented out - did you un-comment it? Then it will fail.


EDIT:

Cool, so splitting these conditions fixed it

For Each ctl In Forms!Form_Export.Controls
  If ctl.ControlType = acCheckBox Then
    If Nz(ctl, 0) = True And Nz(ctl.Tag, "") <> "" Then
    strSQL = strSQL & ctl.Tag & ", "
    End If
  End If
Next

 

[Deleted member 147267]

My comments aren't meant to be harsh, just advisory. We all have to start somewhere. When I started, I didn't know how to use the message box function!

Then if it has a tag - so you are saying all will have tags so I shouldn't be checking this?

No, I'm saying the next check is also paired with something, but in this case it shouldn't matter because all controls now have a tag property AFAIK. The key word here is "property". Don't confuse that with you using it or not.

What do you mean by variants? So I should declare them separately?

Multiple declarations on one line such as Dim sql As String, lngNumber As Long, dteBegin As Date, etc. If you just write Dim sql, sql is a variant because it's not explicitly data-typed. Doing it as you did, only 1 variable is typed.

Replace are replacing empty strings in case there was no field

Maybe I don't understand what "in case there was no field" means because I'm not seeing the issue. Doesn't seem to matter what combination of checks I pick. Here's one
SELECT [ID], SourceID, ContactID INTO [Data Export Temp] FROM Data
There are no extra characters. If this has to do with strSQL4 or some other variable, it's likely that you approach is wrong. For example, you have
strSQL4 = strSQL4 & ", " & fld.Name then you inject a comma just before it here SELECT [WOWID], forcing you to use the Mid function to remove it. If you're going to concatenate things, put comma at the end, build the string and use Left(myStringHere, Len(myStringHere)-1) to drop the trailing comma.

BTW, you are Executing a SELECT query and doing nothing with it. Not only does that not make sense, I expected it to raise an error but it doesn't even though I disabled the Resume Next statement. According to M$:

The Execute method is valid only for action queries. If you use Execute with another type of query, an error occurs

If you have macros to run something without opening a form, then why use embedded macros in a form? That's more or less what I meant by having a mix. As for the command button, you might as well just run the code in its click event rather than calling public functions. Unless you plan to call these functions from other places, you're just breaking things into parcels of code for no good reason.

Last, drop the Nz's as in

  If ctl.ControlType = acCheckBox Then
    If ctl = True And ctl.Tag <> "" Then strSQL = strSQL & ctl.Tag & ", "
  End If

The tag is either "" (zero length string) or not. You check if it's "". As you have it, you're saying "if it's "" then nz it to ""
The check for the control is that it is True (checked). You don't care if it's anything else. Unless you have its Triple State property set to True, it cannot be anything else but False, but you don't care anyway. It's either True or not. If it's not, it gets ignored.

Last but not least, if the goal is to output a volatile set of fields there are easier ways. One might be to just build your sql by looping as you are doing, create a recordset from it and do TransferText method. At this point I can't recall what the goal was and don't want to risk looking back and losing my answer here.