Code is close but not correct 2

[melaniews]

I'm attempting to give users the opportunity to specify the file name for a text export. I've put the code on the unload event of a form. Ideally, when the user clicks the "close" button, they are promted as to whether they want to export to a text file. If they select yes, then they are prompted for a file name.

What actually happens is the filename is "strFileName" with the "date" and ".txt" added. I don't know what happens to the name that is entered in the input box.

I need a solution and guidance as to why this isn't working like I expected.

Here is my code:

Private Sub Form_Unload(Cancel As Integer)
Dim Msg, Style, Title, Response


Msg = "Do you want to export to a text file?"
Style = vbYesNoCancel
Title = "Export?"
Response = MsgBox(Msg, Style, Title)


If Response = vbYes Then
Dim strFileName As String
strFileName = InputBox("Please enter a file name." & vbCrLf & "The date will automatically be added to your filename.&quot

DoCmd.TransferText acExportDelim, , "qselInterestedIndividuals", "\\Consumer Database\Text Exports\strFileName" & " - " & Date$ & ".txt"
MsgBox "Your file has been saved to the TEXT EXPORTS subdirectory in the Consumer Database directory." & vbCrLf & " The current date has been added as part of the filename", , "Export Complete"

ElseIf Response = vbNo Then


Else
DoCmd.CancelEvent
End If
End Sub


TIA
Melanie

 

[hwkranger]

"\\Consumer Database\Text Exports\strFileName" & "

change to
"\\consumer database\text exports\" & strfilename & "-" ....

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[hwkranger]

Sorry, I posted too quickly - but - an explanation of my offered answer is that the string variable (strfilename) needs to be outside of the quotation marks. It needs to be used as a value - not as a name

Hope this helps.

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[melaniews]

A star for you and thanks very much for that explanation info.

I have another short question.

Are there properties for InputBox to be able to control what happens if the user clicks the cancel button or the close control button? I'd like to cancel the unload event in either of these cases.

TIA,
Melanie

 

[hwkranger]

yes,

if a user clicks cancel - a zero length string is returned. check for that and then do the appropriate action

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[melaniews]

Okay, so that's an IF, THEN, ELSEIF kind of thing I think. But where does it go in the code?

TIA,
Melanie

 

[hwkranger]

put this after strfilename is declared (that's after the input box)

If Len(strfilename) = 0 Then
'do whatever you want to do
End If

this takes care of both situations where the user clicks cancel - or clicks ok with nothing in the box.


Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[melaniews]

Okay, I've tried several different approaches. I tried to execute a keyboard command to stop the save. I tried an error routine to just get out. And I tried to jump forward in the code. I've have come up with a big 0 on getting anything to work. I'm sure this is due to my inexperience with VB.

Ideally, I would like to give the user a MsgBox with what is wrong (You did not enter a file name) and simply cancel the docmd.transfertext and loop back to the MsgBox and give the user another go at choosing whether they want to export to text. However, I have been unsuccessful in getting anywhere close to this result.

I don't know whether I am explaining this very well. Please let me know if I'm unclear.

All suggestions are much appreciated.

TIA
Melanie

 

[hwkranger]

Private Sub Form_Unload(Cancel As Integer)
Dim Msg, Style, Title, Response


Msg = "Do you want to export to a text file?"
Style = vbYesNoCancel
Title = "Export?"
Response = MsgBox(Msg, Style, Title)


If Response = vbYes Then
Dim strFileName As String
strFileName = InputBox("Please enter a file name." & vbCrLf & "The date will automatically be added to your filename.&quot

If Len(strfilename) = 0 Then
msgbox("Either you clicked cancel or did not enter a file name."
exit sub
End If


DoCmd.TransferText acExportDelim, , "qselInterestedIndividuals", "\\Consumer Database\Text Exports\strFileName" & " - " & Date$ & ".txt"
MsgBox "Your file has been saved to the TEXT EXPORTS subdirectory in the Consumer Database directory." & vbCrLf & " The current date has been added as part of the filename", , "Export Complete"

ElseIf Response = vbNo Then


Else
DoCmd.CancelEvent
End If
End Sub


hope this helps.

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[hwkranger]

correction:
msgbox("Either you clicked cancel or did not enter a file name."

with

MsgBox "Either you clicked cancel or did not enter a file name.", vbCritical



Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[melaniews]

Oh yes, thank you. I was missing the vbCritical part and the exit sub part and it just kept running the text export even when I clicked cancel. It works great now. I even added a docmd.cancelevent to it to make it return to the previous state.

Thanks so much.

Melanie

 

[hwkranger]

vbcritical is just an icon on the box. you don't really need it - but it's always a good idea to keep stride with the rest of the world and use their 'symbols.'

people like the familiar context.

the exit sub was the important part

Good luck.

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.