I am interested in writing a VBA procedure that takes inputs from a form, and uses them to change the field alias names in a query. A report uses this query thus the reason I am using field alias names.

I will post what I have so far in a bit, but has anyone ever successfully done this?

I answered a similar question recently, not exactly what you want but it may provide inspiration, alternatively it may cause perspiration! (http://www.access-programmers.co.uk/forums/showthread.php?t=135817)

what if you were to change the label captions when the report opens?

I have attached a screen shot of my form. Does anyone have an example of using VBA or SQL to take the fields on the right of this form and use them to change the field alias names in a query?

Seems like it could be simple, but I am struggle with this.

I will have to assume that the query is saved.

If this is true, I would use VBA code to modify the QyeryDef.

example



Dim qdf As DAO.QueryDef

With CurrentDb
Set qdf = .QueryDefs("Qry_YourReportsQuery")

qdf.SQL = "SELECT [Field1] as " & Me.Field1AliasName & " FROM Tbl_YourTable"
qdf.Close
Set qdf = Nothing
End With

hmmm....this seems to be replacing the entire Query

I was just posting a very simple example of how to work with a QueryDef.

I was build the complete SQL on the fly each time. Which would probably be the simplest way to handle this, but more "hard coded".

There are other ways that are not so "hard coded":

1) You would easily get the current SQL and parse thought it and do your replacements is you can figure out how to find what needed to be replaces. (hard to say without seeing you SQL statement)

or

2) You will need to have a master copy of the SQL someplace so that you know what to replace. If I were doing this, I would store the "master" SQL statement in a table.

Does anyone have an example of using VBA or SQL Seems like it could be simple, but I am struggle with this.

I draw your attention to the link I posted earlier, it contains an example which you should be able to adapt to do what you want to do.