Variable not working

[moishier]

I am new at Access programming, and am having some trouble with some VBA code that I think there probably is a simple solution.

I am trying to set a property on a function that uses a variable from a query.

Here is what I have so far:

Private Sub Command2_Click()

Dim strFilter As String
Dim strInputFileName As String

Dim rst As Recordset
Dim product As String
Dim db As Database
 
Set db = CurrentDb
 
Set rst = db.OpenRecordset("SELECT issue_date from [Next issue date]")
 
With rst
   .MoveFirst
   product = .Fields(0)
End With

Set rst = Nothing
Set db = Nothing

MsgBox product

'Ask for SaveFileName
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
strSaveFileName = ahtCommonFileOpenSave( _
                                    OpenFile:=False, _
                                    Filter:=strFilter, _
                                    DefaultExt:=".TXT", _
                                    FileName:="product", _
                                Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

' the issue is right here: it does not translate "product" as the query value
                    
MsgBox strSaveFileName
 
DoCmd.TransferText acExportFixed, "Complaints", "Complaints to export", strSaveFileName

MsgBox "Saved!"

End Sub

The issue is that it does not translate "product" as the query value.

I am using the VBA from here in a module to handle the save file dialog box:
access.mvps.org/access/api/api0001.htm

Any help is appreciated!

 

[pr2-eugin]

Few things..

* You do not need the variable to be surrounded by double Quotes.. Just

FileName:=product,

Should suffice..

* By the looks of it you are taking the issue date as the File Name.. Windows does not allow you to store files with / in them.. So you need to wrap the String with a Replace function..

FileName:=Replace(product, "/", "-"),

 

[moishier]

Thank you! That was very helpful and enabled me to complete the project.

 

[pr2-eugin]

Glad to be of help..