Calling a Form from a Report

[brumshine]

I would like to have a report call a form for input then call the report without calling the form when a button is pressed. Confused yet? It's causing a recursive loop and I can't figure out a solution other than creating a loader report that contains a doCMD that opens the form that calls the report. I really don't want to do it that way... Is there a way to pause the report from being processed then resume after I've filled out the form?

Any tips or suggestions are appreciated.

thanks,

BRUMSHINE

 

[boblarson]

Why are you trying to open a form for input to a report that is already opened? Are you trying to change the parameters or something? Your post is a bit confusing (at least to me, but that could just be that it is Friday here).

 

[brumshine]

The users I'm designing this for have always clicked on daily reports to provide them with the data to complete job duties. My report calls for a start date and an end date that is passed to the query used to create that report. I was hoping I could call the form when they clicked the report.

Does this help? Friday......

 

[boblarson]

Actually shouldn't the form open first and the start and end date is entered and then the report is opened? It shouldn't happen in reverse.

 

[brumshine]

I'm trying to avoid that Bob.

 

[boblarson]

I'm trying to avoid that Bob.

I gotta ask "why?" It makes no sense. You have to open reports from somewhere and you should be launching them from a form that either opens the report the user needs (without parameters if that type of report) or with parameters for that particular report.

 

[brumshine]

The only reason I want to do this is for usability. The end users are not the most tech savy bunch, if I'm able to launch the report that calls a form then runs it would save them the extra step of trying to find the form. They already run multiple reports so hopefully this can just be a check mark on there list. Sorry if I was unclear in earlier posts, your assistance is much apreciated Bob.

Thanks,

 

[Dennisk]

I've seen this style before and it is a right pain, but essentially you can open the form in the on open event of the report. Far, far better to create a form for gathering criteria and open the report from the form.

 

[boblarson]

The only reason I want to do this is for usability. The end users are not the most tech savy bunch,

Then why not give them a form to open the report they want and add the criteria they need. That is the MOST user friendly system. They should not be opening things from the database window. USE FORMS, USE FORMS, USE FORMS!!!!

 

[brumshine]

Thanks guys, I will defenently consider your suggestions for future projects.

Bob, I am using forms. What difference would it make if they click the forms rather than reports? They are both located in the database window correct? The users can run a wide variety of reports from the MDB, many that do not require user input, they are all located under the reports tab.

I think I'm figuring out how to launch the form from the report then call the report and pass the input values to the query. I'll post my code once I get all the bugs worked out. Perhaps someone in the future will find this helpful.

 

[Rabbie]

Your users should never see the database window. In a user friendly system they should see a nice menu form that lets them select what they want to do.

This will actually have the advantages of making things simpler for you and for them.

 

[brumshine]

Does anyone know a away to pass data from the vba code in the form to the vba code in the report? I've tried and tried but can't get it figured out. I'm hoping to be able to pass a start and finish date variable.

I know I shouldn't have to be doing this but I'm hoping once I get this acomplished I can be done with this sloppy job. Thanks for any ideas.

 

[pbaldy]

I typically open a form for criteria then open the report from there, as has already been suggested. If you want to open the form from the report, open it in dialog mode from the report's open event. When the user finishes entering criteria, hide rather than close the form. Have the report look at the form for the criteria. Close the form in the report's close event.

 

[Dennisk]

to refer to an open form from the report use, set a text box recordsource to

=Forms!YourForm!YourControlName

 

[SQL_Hell]

Brumshine

Isn't the problem based around the fact the users are opening the report from the database window?

The best thing to do is create them a splash screen (switchboard, menu screen) and have command buttons that open the report and form.

Surely the users are tech savvy enough to click a button that says 'open report' for example.

Another possiblity is to create a form which looks very much like their report but with the added functionality being able to drill down and add / ammend data

 

[Brianwarnock]

Brumshine

Surely the users are tech savvy enough to click a button that says 'open report' for example.

We are all ignorant till we are educated, Too many IT people are to pompous to do the education, maybe the extra knowledge gives them a feeling of power.

Brian

 

[brumshine]

I finally got it working! Thank you for helping me complete as well as learn correct programming procedures. I figured I would post my code just incase anyone was attempting to do what I had to.

Thanks again, you guys rock.

BRUMSHINE

REPORT CODE

Option Compare Database
Private Sub Report_Open(Cancel As Integer)
formOpen = fIsLoaded("Date Range Form")
If formOpen = 0 Then
DoCmd.OpenForm "Date Range Form", , , , , acDialog
Else
DoCmd.OpenForm "Date Range Form", acNormal, "", "", acEdit, acDialog
End If
End Sub
Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
        If Forms(strFormName).CurrentView <> 0 Then
            fIsLoaded = True
        End If
    End If
End Function

FORM CODE

Option Compare Database
Private Sub butSub_Click()
If IsNull(SDate.Value) Then
MsgBox ("Please enter the start date.")
SDate.SetFocus
ElseIf IsNull(FDate.Value) Then
MsgBox ("Please enter the end date.")
FDate.SetFocus
ElseIf SDate.Value > FDate.Value Then
MsgBox ("Start Date occurs after finish date, please revise.")
Else
Me.Visible = False
End If
End Sub
Private Sub Cal_Enter()
SDate.Value = Cal.Value
End Sub
Private Sub Cal_LostFocus()
FDate.SetFocus
SDate.Value = Cal.Value
Cal.Visible = False
lblnotice.Visible = False
End Sub
Private Sub Form_Close()
DoCmd.CancelEvent
End Sub
Private Sub SDate_Click()
Cal.Visible = True
lblnotice.Visible = True
Cal.SetFocus
End Sub
Private Sub Cal2_Enter()
FDate.Value = Cal2.Value
End Sub
Private Sub Cal2_LostFocus()
butSub.SetFocus
FDate.Value = Cal2.Value
Cal2.Visible = False
lblnotice.Visible = False
End Sub
Private Sub FDate_Click()
Cal2.Visible = True
lblnotice.Visible = True
Cal2.SetFocus
End Sub
Public Function setSDate()
setSDate = SDate.Value
End Function

 

[DCrake]

If alls you are doing is prompting the user for a start and end date for the report why did you not just set parameters on the underlying report query. As others have suggested your methodology is back to front. For future reference I have attached a document explaining the concepts of using public variables to pass between forms and queries/reports. Having grasped the concept the rest is natural.

David

 

[brumshine]

Hey DCrake... BRILLIANT. That document did a really nice job of showing examples of how to pass public variables directly to the query. You're the man!

BRUMSHINE

 

[brumshine]

I'm running into one final issue with this hellish report.... When the user clicks the "X" close button on the form the report continues to process. Is there any way to stop the report from my form when the close button is clicked?

Thanks in advance.

BRUMSHINE