Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pass Variables between Databases

Status
Not open for further replies.

Wes1961

Technical User
Aug 21, 2001
72
US
I have an add-in to my main database that houses some code and functions that I have developed


Example: I have a report that has the following code in the OnNoData. If the user can runs this report manually and there is nodata then I want to display a message box. However, I also want to call this from VBA code to create a "SnapShot" report during an unmanned night procedure. My problem is that the message box displays and waits for input during the VBA code. I want to be able to pass from the main db the variable "blnNight = true" (which works.)




Report : rptUnmatched ... which is in main database
Private Sub Report_NoData(Cancel As Integer)

If blnNight <> True Then
Msg = "The report has no data." & vbLf & "Printing the report is canceled. " _
Title = "No Data"
Style = vbOKOnly + vbCritical
Msgbox(Msg, Title, Style)
End If
Cancel = True
blnNoData = True

End Sub



VBA code to create snapshot in library database;

Function MakeReports(blnNight As Boolean)
On Error GoTo Rpt_NoData
Dim dbs As Database, rst As Recordset

Do While Not rst.EOF
' Output to a "Snapshot" file the report listed
stDocName = rst("rptName")
rst.Edit
rst("RptFlag") = True
stFile = stLocation & stRptName & ".snp"
Wipe (stFile)
Debug.Print blnNight
DoCmd.OutputTo acOutputReport, stDocName, "Snapshot Format", stFile
rst.Update
DoEvents
rst.MoveNext
Loop

Exit_Rpt:
Exit Function

Rpt_NoData:
If Err.Number = 2501 Then
rst("RptFlag") = False
Resume Next
Else
MsgBox Err.Description
End If
Resume Exit_Rpt

End Function


When the above code calls the report in the "DoCmd.OutputTo" line I don't know how to pass the "blnNight" variable. I have confirmed that it is "True" right before the call. Then in the OnNoData section it always defaults back to false.

Main Db (VBA code) calls Function MakeReports in Library which calls the report rptUnMatched in the main DB.

How can I pass variables back and forth from my main db to the library db. Can I do this?
 
why not test for night in the database with the report using the reports On Open Event .. or On Load Event

If Format(Now(), "hh:nn:ss") > CDate("05:00:00 PM") Then
blnNight = True
Else
blnNight = False
End If

PaulF
 
Thanks Paul... Many things occur in the night process, sometimes it errors out and needs to be run manually the next morning.

I would like a solution that works whenever I envoke the macro "night" macro. If I can't get one then I'll use the time.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top