BamaColtsFan
Registered User.
- Local time
- Today, 01:06
- Joined
- Nov 8, 2006
- Messages
- 91
Hi Gang! Ok, so I know that you can't manipulate the appearance of a standard message box and that you need to create a popup form that has what you want and looks all cool. I'm okay with doing this and I have set up a form for my code to call and use. What I don't know how to do is place a value that is determined by the VBA code in the text of the message box form. Basically, in my code sample below, I need the value of strPath to show in the appropriate place of my form (frmFileExportMessage). I can do this fairly easy calling a standard message box. I have left the code for that box below so you can see how I build it now. The popup form has the same text and a command button that closes the form.
So, here is my question: How do you change the text of a form when you call it in VBA?
Any advice is greatly appreciated, as always!
THNX!
So, here is my question: How do you change the text of a form when you call it in VBA?
Code:
Public Function CopyToWorkbook()
' This is the Baseline Export Function that places all data into the workbook then calls
' ConvertToFormattedWorkbook from the FileFormatUtility Module
Dim db As DAO.Database
Dim newPath As DAO.Recordset
Dim myDept As DAO.Recordset
Dim newDept As String
Dim myCMD As DAO.Recordset
Dim newCMD As String
Dim myDep As DAO.Recordset
Dim newDep As String
Dim strPath As String
Dim SQL As String
Dim fso As FileSystemObject
Set db = CurrentDb()
Set newPath = db.OpenRecordset("Set_Path")
Set myDept = db.OpenRecordset("qryDepartmentCodes")
Set myCMD = db.OpenRecordset("qryCommandCodes")
Set myDep = db.OpenRecordset("qryDeputyCodes")
Set fso = New FileSystemObject
strPath = newPath!Out_Path & "CombinedTimecards_Crosstab.xls"
' Check to see if the file exists. If it does, delete it. Otherwise, do nothing and keep going.
If fso.FileExists(strPath) = False Then
''
Else
Kill (strPath)
End If
' LoadFileName
DoCmd.TransferSpreadsheet acExport, 8, "qryFinalCompSum", strPath, True, "Compliance Summary"
Call ConvertToFormattedWorkbook
DoCmd.OpenForm "frmFileExportMessage"
MsgBox "Report File Export Successful!" & vbNewLine & vbNewLine & _
"Your report file is located at: " & vbNewLine & vbNewLine & _
strPath & " " & vbNewLine & vbNewLine
End FunctionAny advice is greatly appreciated, as always!
THNX!
