Setting Reference to Excel Library

[Montez659]

If I set a reference in my Access db to Excel 12.0, will this reference automatically carry over to a different machine if I distribute the db?

What if they are working with an earlier version of Excel by some chance - will it have to be manually set then or will it default to the most current library?

 

[boblarson]

I would HIGHLY suggest NOT setting a reference to Excel and INSTEAD use LATE BINDING. Then it will work flawlessly between versions but you are right, if they have an earlier version then a reference error WILL occur and they would have to open the database while holding shift, go to the VBA window, to TOOLS > REFERENCES and uncheck the version 12 reference.

For an example of code which works WITHOUT an Excel reference set - see my code here:

http://www.btabdevelopment.com/ts/default.aspx?PageId=48

 

[Montez659]

Hah! Genius! Not that I understood most of your code but I see that instead of having to reference the library when declaring, you just declare as an object. Works great, thanks Bob!

 

[Montez659]

Bob, quick question on the link that you provided. I am trying to reference a query when calling the function, and I keep getting an error that says "Too few parameters. Expected 1." I traced this back to my query which references a combobox on the same form that I have the commandbutton to trigger the function. Essentially, the user chooses from a combobox the group that they would like to export, and the query limits the records by that group code. What am I doing wrong that it doesn't like the expression? This is what I have:

[Forms]![frmExportToSheet]![cmbGroupID]

If I remove that expression altogether, the code works great, but of course returns all records. Appreciate the help!

 

[xenou]

What is the query SQL?

 

[Montez659]

SELECT tblGroups.groupID AS tblGroups_groupID, tblContactInfo.firstName, tblContactInfo.lastName, JOINEDtblAttendance.attendanceID, JOINEDtblAttendance.contactID, JOINEDtblAttendance.groupID AS JOINEDtblAttendance_groupID, JOINEDtblAttendance.wk1, JOINEDtblAttendance.wk2, JOINEDtblAttendance.dateCreated, JOINEDtblAttendance.needChildcare
FROM tblContactInfo INNER JOIN (tblGroups INNER JOIN JOINEDtblAttendance ON tblGroups.groupID = JOINEDtblAttendance.groupID) ON tblContactInfo.contactID = JOINEDtblAttendance.contactID
WHERE (((tblGroups.groupID)=[Forms]![frmExportToSheet]![cmbGroupID]));

 

[Montez659]

Additional question regarding the code in the link, or exporting in general. Is there any way to convert fields that are Boolean into something other than TRUE/FALSE before they hit the spreadsheet? Or is there a good way to convert them in the spreadsheet (without running VBA within the spreadsheet itself, although that might be an option)?

 

[xenou]

Are you sure you have the correct name? That the form is open? That the value for the combobox is a compatible data type? This is usually not a problem - might be something "obvious".

You can convert true false to other values - there are various means depending on what you want to show. You could convert it to number, for instance. Or the most flexible would be:
=IIF([MyField],"ValueForTrue","ValueForFalse")

Where MyField is your boolean value and "ValueForTrue" and "ValueForFalse" are whatever you want for true and false (don't use quotes if you want numbers). I'm not sure actually if you format the field in the access query how it comes out. It may transfer as formatted (probably would).

 

[Montez659]

Xenou, checked all that you mentioned and even deleted the control and redid it to no avail. I am thinking that the problem is somehow related to how the query is interacting with the VBA because I can click on the query from my navigation pane and it runs fine, even with the criteria (as long as my form is open). It is just when it is triggered via the code that the error comes about.

I guess another option would be to enter the SQL into the VBA and create a query that way. I may test that and see if it works.

As far as the IIF, where would this go - in the VBA or within the query itself? And if in the query, would it just go in the Field or criteria?

 

[xenou]

As far as the IIF, where would this go - in the VBA or within the query itself? And if in the query, would it just go in the Field or criteria?

In the query:

SELECT IIF([Field1],"SomeValue1","SomeValue2") As MyField 
FROM Table1;

It's not possible to say much about how the vba is interacting without more knowledge. It's rare to be able to not run a query that works fine otherwise. There's still probably something obvious that we are missing here. You may want to provide more context on how this all works.

Actually - you are running this from Excel, no? I don't think the query can see the form from Excel. If this "Form" is an excel userform, you should build the query string with the parameter values in place, and send it to access that way.