Hello
I want to change the captions in certain fields in a query each time it is run. The criteria for the captions will be a reference number, a reference date (both held outside the database), and then totals of $ values of other columns in the query. I tried putting formulas in the caption property of the field but that didn't work and I'm not sure how to write the code in the event procedure when the query runs. Can anyone help with this please?
Thank you
Erica
Hi Erica,
What is the purpose of the query? I mean, what is it being used for? Normally, we recommend that you don't let your users work on the data directly through queries. It is more advisable to use Forms for user interface.
When you use forms, you'll have access to a lot more control and so you will be able to use VBA to change the column captions any which way you want.
Just my 2 cents...
What is the purpose of the query? I mean, what is it being used for? Normally, we recommend that you don't let your users work on the data directly through queries. It is more advisable to use Forms for user interface.
When you use forms, you'll have access to a lot more control and so you will be able to use VBA to change the column captions any which way you want.
Just my 2 cents...
The purpose of the query is to create a payfile that is set to Client specifications (column headings and data) for uploading into their system. Some of the column headings will be based on the total values in the column so will change each time a new invoice batch is run. Other column headings come from outside the database so would have to be entered each time the query was run. The query is exported into excel and sent to the client. We don't want to manipulate it in excel if possible.
Thanks for the explanation but I don't think there's enough information there for me to give you any specific advice, so I'm just going to show you a general process using VBA.
Let's pretend that we have a form with a couple of textboxes where the user will enter the column captions that they want to show up in the Excel file. Also, we are going to pretend that we have a query called "qryExportToExcel" that we are going to export. It doesn't matter what table or fields are in this query because we are going to change it everytime we run our code.
Now, on our form, let's say that we have a command button labeled "Export To Excel" that the user will click to create the Excel file after they enter the column names. Here's the possible code behind that button:
Hope that helps...
Let's pretend that we have a form with a couple of textboxes where the user will enter the column captions that they want to show up in the Excel file. Also, we are going to pretend that we have a query called "qryExportToExcel" that we are going to export. It doesn't matter what table or fields are in this query because we are going to change it everytime we run our code.
Now, on our form, let's say that we have a command button labeled "Export To Excel" that the user will click to create the Excel file after they enter the column names. Here's the possible code behind that button:
CODE
Dim qdf As QueryDef
Dim strSQL As String
Set qdf = CurrentDb.QueryDefs("qryExportToExcel")
strSQL = "SELECT Field1 As " & Me.Textbox1 & ", Field2 As " & Me.Textbox2 & " FROM TableName"
qdf.SQL = strSQL
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryExportToExcel","C:\FullPath\To\ExcelFile.xls"
Dim strSQL As String
Set qdf = CurrentDb.QueryDefs("qryExportToExcel")
strSQL = "SELECT Field1 As " & Me.Textbox1 & ", Field2 As " & Me.Textbox2 & " FROM TableName"
qdf.SQL = strSQL
Set qdf = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryExportToExcel","C:\FullPath\To\ExcelFile.xls"
Hope that helps...
Thank you. I will have a go and let you know what happens. Appreciate your help
Let us know how it goes...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.