renaming query field headings

[ryan.fitzpatrick3]

I have renamed query field headings with my own headings this way when
users export to excel the orginal database headings, which are
confusing, will not be displayed. but when i rename them and click the
export to excel button a input box appears, why would this be?

Here's query SQL that renames fields

SELECT tblAdagePaidDebits.gl_cmp_key AS Company,
tblAdagePaidDebits.so_brnch_key AS Branch,
tblAdagePaidDebits.en_vend_key AS [Vendor ID],
tblAdagePaidDebits.en_vend_name AS [Vendor Name],
tblAdagePaidDebits.in_type_key AS [Ingred/Pack],
tblAdagePaidDebits.in_item_key AS [Item Number],
tblAdagePaidDebits.in_desc AS [Item Desc],
tblAdagePaidDebits.po_dtl_uom AS UOM, tblAdagePaidDebits.in_comcd_key
AS Commodity, tblAdagePaidDebits.[Rec Date] AS [Year],
tblAdagePaidDebits.[SumOfActual Inv Quant] AS Quantity,
tblAdagePaidDebits.[SumOfAct Dom $ Spend] AS Spend,
tblAdagePaidDebits.en_phfmt_key AS Buyer, Date() AS [Date Downloaded]
FROM tblAdagePaidDebits;

Here is export to excel code

Private Sub Export_Click()
On Error GoTo Err_Export_Click

Dim dbCurr As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim lngOrderBy As Long
Dim strQueryName As String
Dim strSQL As String

' You only need to go to this effort if there's a filter
If Len(Me.Filter) > 0 Then
Set dbCurr = CurrentDb

' Get the SQL for the existing query
strSQL = dbCurr.QueryDefs("3QryAdageVolumeSpendsum").SQL

' Check whether there's an ORDER BY clause in the SQL.
' If there is, we need to put the WHERE clause in front of it.
lngOrderBy = InStr(strSQL, "ORDER BY")
If lngOrderBy > 0 Then
strSQL = Left(strSQL, lngOrderBy - 1) & _
" WHERE " & Me.Filter & " " & _
Mid(strSQL, lngOrderBy)

Else
' There's no ORDER BY in the SQL.
' Remove the semi-colon from the end, then append the WHERE clause
strSQL = Left(strSQL, InStr(strSQL, ";") - 1) & _
" WHERE " & Me.Filter
End If

' By using the current date and time, hopefully that means
' a query by that name won't already exist
strQueryName = "qryTemp" & Format(Now, "yyyymmddhhnnss")

' Create the temporary query
Set qdfTemp = dbCurr.CreateQueryDef(strQueryName, strSQL)

' Export the temporary query
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"C:\Documents and Settings\All Users\Desktop\Adage Downloaded
On" & Format(Now, "mm" & "-" & "dd" & "-" & "yyyy" & "@" & "hh" &
"nn") & ".xls", _
hasfieldnames:=True

' Delete the temporary query
dbCurr.QueryDefs.Delete strQueryName

Else

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"C:\Documents and Settings\All Users\Desktop\Adage Downloaded
On" & Format(Now, "mm" & "-" & "dd" & "-" & "yyyy" & "@" & "hh" &
"nn") & ".xls", _
hasfieldnames:=True

End If

Exit_Export_Click:
Set dbCurr = Nothing
Exit Sub

Err_Export_Click:
MsgBox Err.Description
Resume Exit_Export_Click

End Sub


It's weird it used to work and now I get this input box. Any
suggestions? Thanks.

Ryan

 

[ryan.fitzpatrick3]

The input box just says the field name that has been renamed. Order by
is blank.

What does the input box say?  Does it refer to a field name that has been
changed?  Right click in the query design (top half, not a field) and check
the properties to see if there is anything being referenced in the [orderby]
or filter properties.

I have renamed query field headings with my own headings this way when
users export to excel the orginal database headings, which are
confusing, will not be displayed. but when i rename them and click the
export to excel button a input box appears, why would this be?

Here's query SQL that renames fields

SELECT tblAdagePaidDebits.gl_cmp_key AS Company,
tblAdagePaidDebits.so_brnch_key AS Branch,
tblAdagePaidDebits.en_vend_key AS [Vendor ID],
tblAdagePaidDebits.en_vend_name AS [Vendor Name],
tblAdagePaidDebits.in_type_key AS [Ingred/Pack],
tblAdagePaidDebits.in_item_key AS [Item Number],
tblAdagePaidDebits.in_desc AS [Item Desc],
tblAdagePaidDebits.po_dtl_uom AS UOM, tblAdagePaidDebits.in_comcd_key
AS Commodity, tblAdagePaidDebits.[Rec Date] AS [Year],
tblAdagePaidDebits.[SumOfActual Inv Quant] AS Quantity,
tblAdagePaidDebits.[SumOfAct Dom $ Spend] AS Spend,
tblAdagePaidDebits.en_phfmt_key AS Buyer, Date() AS [Date Downloaded]
FROM tblAdagePaidDebits;

Here is export to excel code

Private Sub Export_Click()
On Error GoTo Err_Export_Click

Dim dbCurr As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim lngOrderBy As Long
Dim strQueryName As String
Dim strSQL As String

' You only need to go to this effort if there's a filter
 If Len(Me.Filter) > 0 Then
   Set dbCurr = CurrentDb

' Get the SQL for the existing query
   strSQL = dbCurr.QueryDefs("3QryAdageVolumeSpendsum").SQL

' Check whether there's an ORDER BY clause in the SQL.
' If there is, we need to put the WHERE clause in front of it.
   lngOrderBy = InStr(strSQL, "ORDER BY")
   If lngOrderBy > 0 Then
     strSQL = Left(strSQL, lngOrderBy - 1) & _
       " WHERE " & Me.Filter & " " & _
       Mid(strSQL, lngOrderBy)

   Else
' There's no ORDER BY in the SQL.
' Remove the semi-colon from the end, then append the WHERE clause
 strSQL = Left(strSQL, InStr(strSQL, ";") - 1) & _
   " WHERE " & Me.Filter
End If

' By using the current date and time, hopefully that means
' a query by that name won't already exist
   strQueryName = "qryTemp" & Format(Now, "yyyymmddhhnnss")

' Create the temporary query
   Set qdfTemp = dbCurr.CreateQueryDef(strQueryName, strSQL)

' Export the temporary query
   DoCmd.TransferSpreadsheet transfertype:=acExport, _
     spreadsheettype:=acSpreadsheetTypeExcel9, _
     tableName:=strQueryName, FileName:= _
     "C:\Documents and Settings\All Users\Desktop\Adage Downloaded
On" & Format(Now, "mm" & "-" & "dd" & "-" & "yyyy" & "@" & "hh" &
"nn") & ".xls", _
     hasfieldnames:=True

' Delete the temporary query
   dbCurr.QueryDefs.Delete strQueryName

   DoCmd.TransferSpreadsheet transfertype:=acExport, _
     spreadsheettype:=acSpreadsheetTypeExcel9, _
     tableName:=strQueryName, FileName:= _
     "C:\Documents and Settings\All Users\Desktop\Adage Downloaded
On" & Format(Now, "mm" & "-" & "dd" & "-" & "yyyy" & "@" & "hh" &
"nn") & ".xls", _
     hasfieldnames:=True

 End If

Exit_Export_Click:
 Set dbCurr = Nothing
 Exit Sub

Err_Export_Click:
 MsgBox Err.Description
 Resume Exit_Export_Click

End Sub

It's weird it used to work and now I get this input box. Any
suggestions? Thanks.

Ryan

 

[ryan.fitzpatrick3]

On my form that uses runs off of a query. I have a combobox setup for
each field so a user can search for whatever field they would like.
It's setup the exact same way as Allen Brown's search example is
setup. So for example if I put in a item # in the cboxitemnumber and
run the filter the information for this item pops up. This works fine.
now since this is on a query when I export normally in the past it'll
take that item # information and dumb it into an excel. now it'll give
me an input box of [item number] like exactly how the renamed query
field is. I could go back and put all the orginal names the database
had and just have the users change them in the excel but this should
work, I don't understand why this is happening.

Which field is it asking for?

The input box just says the field name that has been renamed. Order by
is blank.

What does the input box say?  Does it refer to a field name that hasbeen
changed?  Right click in the query design (top half, not a field) and check

[quoted text clipped - 104 lines]

 

[ryan.fitzpatrick3]

It always worked, but when I copied and pasted it into a folder in
which everyone could access it and use it, then it gives the pop up.

You're right.  It should work; and it will as soon as someone figures it out.
I'll keep thinking on it in the meantime.

Does it happen EVERY time, no matter which part of your IF THEN statements
you're in?  Have you tested each of the possible conditions (order by, filter,
etc.)?  Have you put in a break point and identified the line of code that
it's on when the box comes up?

On my form that uses runs off of a query. I have a combobox setup for
each field so a user can search for whatever field they would like.
It's setup the exact same way as Allen Brown's search example is
setup. So for example if I put in a item # in the cboxitemnumber and
run the filter the information for this item pops up. This works fine.
now since this is on a query when I export normally in the past it'll
take that item # information and dumb it into an excel. now it'll give
me an input box of [item number] like exactly how the renamed query
field is. I could go back and put all the orginal names the database
had and just have the users change them in the excel but this should
work, I don't understand why this is happening.

[quoted text clipped - 12 lines]