Ok, so close, just need a little push to get form inputs into repo

[Guest]

My continuing saga...Access 2003, Win XP professional

Background: A) Need to have users input parameters via form, and get report
to display and print data based on user selected parameters; B) Have perused
this site, read multiple posts, and Microsoft Access help, and always need a
little more clarity. I am stuck now but am so so close C) Have created form,
query, and report. D) User can open form, input parameter #1 of eventually 3
required parameters, user clicks on preview report command button, nothing
happens. E) User can run query and results are displayed. F) User can open
report, and form opens.

I am hoping someone can help me finish this task. I just want the user to
select a start date, end date, and OPR, and have a report display and print
based on those user input parameters. In addition to helping me get over this
hump,
a) how can I get the user to select the start and end
dates from the same calendar
b) can I use the same macro for closing and cancelling,
since my exisiting 2 separate macros for these actions have the same
arguments
c) what could I have done to the ocxCalendar control so
that it now will not display the current date upon opening even though it
used to, and if I create a new calendar it does display the current date upon
opening?

THANK YOU in advance for your help. I am hoping to put this task to bed
today. I have tried to give every single bit of info I could. I have more
if you need it.

Andy

1) Tables:
a) 2 tables involved:
1) tblOPR: 2 fields: OPR_ID, OPR_Name, primary key is OPR_ID,
only 3 OPRs at this time
2) tblWorkRequest: 20 fields, primary key is PROJ_ID, 10 fields
need to be on report: PROJ_ID, BLDG, WO_NO, TITLE, ACT_COST, OPR, CONTRACTOR,
PROJ_STATUS, PROJ_START_DATE, PROJ_END_DATE

2) Queries:
a) only 1 query involved: name is qryCostTotalbyOPR
b) total of 10 fields in query due to 10 pieces of data desired to be
shown on report
c) of the 10 fields, 3 are to be parameters: OPR, PROJ_START_DATE,
PROJ_END_DATE
d) query ran fine with all 3 parameters
e) have changed the query so currently only parameter now is OPR
f) OPR field criteria is [forms]![frmCalendar]![cmdOPR]

3) Reports:
a) Only 1 report involved: name is repCostTotalbyOPR
b) 11 fields of data
c) report to display data based on who OPR is and when project ends
d) properties are:
1) record source: qryCostTotalbyOPR
2) filter on: no
3) order by on: yes
4) caption: qryCostTotalbyOPR
5) record locks: no locks
6) auto resize: yes
7) auto center: no
8) page header: all pages
9) page footer: all pages
10) date grouping: use systems settings
11) grp keep together: per column
12) pop up: no
13) modal: no
14) border style: sizable
15) control box: yes
16) min max buttons: both enabled
17) close button: yes
18) width: 10.4583"
19) picture: (none)
20) picture type: embedded
21) picture size mode: clip
22) picture alignment: center
23) picture tiling: no
24) picture pages: all pages
25) grid x: 24
26) grid y: 24
27) layout for print: yes
28) fast laser printing: yes
29) help context id: 0
30) palette source: (default)
31) On Open: mcrOpenCalendarForm
32) On Close: mcrCloseDialog
33) has module: no
34) orientation: left-to-right
35) moveable: yes

4) Forms:
a) only 1 form involved: name is frmCalendar and is based on
ActiveX calendar control, ocxCalendar
b) form is slightly broken: does not open to current date. it used
to but I did something and now it doesn't open to current date. but, it does
everything else.
c) form has 9 command buttons operated by event procedure visual
basic code: next day, previous day, next month, previous month, next week,
previous month, next year, previous year, preview report
1) visual basic code:
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
'Purpose: Set the Date on Opening
ocxCalendar.Object.Value = Date
Call ocxCalendar_AfterUpdate
End Sub

Private Sub ocxCalendar_AfterUpdate()
'Purpose: Update the text boxes
lblDate.Caption = Format(ocxCalendar.Object.Value, "mm/dd/yy")
txtDate.Value = Format(ocxCalendar.Object.Value, "dddddd")
End Sub

Sub cmdNextDay_Click()
ocxCalendar.Object.NextDay
End Sub

Sub cmdPreviousDay_Click()
ocxCalendar.Object.PreviousDay
End Sub

Sub cmdNextWeek_Click()
ocxCalendar.Object.NextWeek
End Sub

Sub cmdPreviousWeek_Click()
ocxCalendar.Object.PreviousWeek
End Sub

Sub cmdNextMonth_Click()
ocxCalendar.Object.NextMonth
End Sub

Sub cmdPreviousMonth_Click()
ocxCalendar.Object.PreviousMonth
End Sub

Sub cmdNextYear_Click()
ocxCalendar.Object.NextYear
End Sub

Sub cmdPreviousYear_Click()
ocxCalendar.Object.PreviousYear
End Sub
Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String

stDocName = "repqryCostTotalbyOPR"
docmd.OpenReport stDocName, acPreview

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

d) form has 1 unbound combo box named cmdOPR
1) row source: SELECT tblOPR.COMPANYID_OPR,
tblOPR.COMPANYNAME_OPR FROM tblOPR ORDER BY [COMPANYNAME_OPR];
2) column count: 2
3) bound column: 1
4) column widths: 0";1"
e) form has 1 unbound text box named txtDate
f) form has 2 command buttons run by macros:
1) Cancel
2) OK

5) Macros:
a) there are 4 individual macros grouped into one macro group
named mcrCalendar
1) mcrCancel
a) action is close and properties are:
1) object type: form
2) object name: frmCalendar
3) save: no
b) no conditions
2) mcrOK
a) action is setvalue and properties are:
1) item: [visible]
2) expression: no
b) condition is [visible]=no
3) mcrCloseDialog
a) action is close and properties are:
1) object type: form
2) object name: frmCalendar
3) save: no
b) no conditions
4) mcrOpenCalendarForm
a) actions are openform and cancel event and properties
are
1) form name: frmCalendar
2) view: form
3) data mode: edit
4) window mode: dialog
b) condition is:
1) Not IsLoaded("frmCalendar")
2) condition is written on 3rd line of macro:
1stline is openform, 2nd line is cancelevent, 3rd line has condition and no
action

6) Modules:
1) There are 2 modules:
a) 1 is named Not IsLoaded ("frmCalendar") and was exported from
the Northwind database
b) visual basic code is:
Option Compare Database
Option Explicit

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If

End Function

2) 1 is named work request application and I have memory of
why I wrote this, or if it is still needed.
a) visual basic code is:
Option Compare Database


Public Function WR_Amount(vi_doc_id As Integer) As Integer
vi_return As Integer
select FUNDING_DOC Lookup.Total OBL Amount

End Function

 

[Albert D.Kallal]

a) how can I get the user to select the start and end

dates from the same calendar

I suppose you could come up with some strange design. However, much of this
problem will be to confuse the user, as how will the user know if they are
entering the start date, or the end date. I think the best solution here is
simply to put two calendars here (a start date one, and a end date one).

Here is some screen shots of what I mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

And, I also have some more here:

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html

b) can I use the same macro for closing and cancelling,
since my exisiting 2 separate macros for these actions have the same
arguments

Hum, I don't quite understand the above? I would you open up a form. User
then selects dates etc, and then presses a button to launch/view the form.
After the user is done, they close the report, and are now back to the
report screen, and are now ready/able to enter some more dates, or simply
close the form. What more needs to be done here?

c) what could I have done to the ocxCalendar control so
that it now will not display the current date upon opening even though it
used to, and if I create a new calendar it does display the current date
upon
opening?

In the forms on-load event, simply set the date of the calendar (well the
two calendars if you following my suggestion)

me.CalStartDate.Value = date
me.CalEndDate.Value = date

 

[Guest]

Hi Albert,

Thanks for the help. I am reading the examples on the links you provided.

The two macros for closing and canceling were from the Microsoft
instructions in the ""Create a form to enter report criteria" I tried to
follow.

I tried using your code to get the calendar to open to the current date but
was unsuccessful. So, I thought about your code and wrote this and it
worked.
Private Sub Form_Load()
'Purpose: Set the Date on Opening
ocxCalendar.Object.Value = Date
Call ocxCalendar_AfterUpdate
End Sub

I still do not know what I did to the original calendar to "break" it. I
created a brand new calendar with the Active calendar control and have
compared it to the original "broken" one and I have yet to see any difference.
Thanks for your help. I will let you knwo if reading your links helps me
move along this path of finishing this task.

Andy

 

[Guest]

HI Albert,

Ok, I have read through your links. I like the subform method you use and I
will begin that step once I have this first task of getting the form inputs
into the report. Again, once I know how to get the inputs into the report as
I still do not know how to code the report to use the form inputs.

My form is completed with two calendars and they work great.
My form has a combo box so the user can use a drop down arrow to select the
OPR.

But, still no report. What do I code on the report to get it to accept the
form selections?

Thanks.

Andy

 

[Albert D.Kallal]

But, still no report. What do I code on the report to get it to accept
the
form selections?

You have two approaches. I am going to suggest you try my approach.

Here is what we need. (make a copy of your database, and try the following).

We will have your cool 'prompt' form. I don't know what it is called.

This cool prompt form is NOT bound to any table, but simply has those 3
controls on it.

The ONLY code we have written so far is TWO lines of code in the forms
on-load event.

activeXCalStart.Value = date
activeXCalEnd.Value = date

You will have to change the names I used above to the actual two names
you have for your start, and end calendars.

ok. The TOTAL amount of code we need so far is thus at two lines of code.
We now will launch the form, and check to see if the dates are set to today.

ok, got that going, the next thing we will do is open up the query for the
report. In your query, you will REMOVE ALL PARAMETERS. Lets make the sql
nice and clean. Now, try running the report, you should get NO PROMPTS, and
the report will of course show ALL the data. Good, now lets close the
report.

At this point, we got a form, a query, and the report. All of the report,
query and the form has been created by drag and drop, and we did have to
write two lines of code so far! In fact, the query builder lets you make
nice query, and since we DO NOT have any parameters in that query, again
this was child's play to make the query. (every thing so far has been the
mouse,
and simple drag and drop.

The next thing we will do is bring up the cool prompt form in design mode,
and add a button to view (launch) the report. The code for this button
will also setup the conditions (restrictions) based on the two dates, and
the combo box....

The code behind this button will be:

Dim strStartDate As String
Dim strEndDate As String

Dim strWhere As String

' build data conditions.

strStartDate = "#" & Format(Me.ActiveXStart.Value, "mm/dd/yyyy") & "#"
strEndDate = "#" & Format(Me.ActiveXEndDate.Value, "mm/dd/yyyy") & "#"

strWhere = "(PROJ_START_DATE = " & strStartDate & _
" and PROJ_END_DATE = " & strEndDate & ")"

' combo box.

strWhere = strWhere & " and (OPR = " & Me.cboOPR & ")"

DoCmd.OpenReport "repCostTotalbyOPR", acViewPreview, , strWhere

The above is all we need. You will of course in the above have to change the
names of the contorls I used (activeXStart, and me.cboOPR to whatever
you used (or, simply change the name of your contorls to the above.
Further, note that your combo box you made on this
prompt form needs the first column to be the id of the operator (assuming
that your OPR field is a number field). If this assumption of mine is
incorrect, then the above code will NOT work. Of couse, the 2nd
collum of the cboOPR combo will be the actuall text name for ease
of selection here.

at his point, we have written a total of 10 lines of code. It is true we
could use some sql with all kinds of parameters etc, but then that reports
sql
would look quite ugly, and is hard to maintain. (however, that approach
would save a few lines of code, but then the sql query is VERY hard to
build. So, we are trading a few extra lines of code to get rid of some
very ugly sql query. So, now that sql query for the report for the
above is 100% clean sql, with NO parameters.

 

[Guest]

Ok, I am about ready to give up. I thought I had done everything you said to
do but it did not work.
The lines of code you said to write work until it got here:
DoCmd.OpenReport "repCostTotalbyOPR", acViewPreview, , strWhere
It failed there as being too complex.
The following is being interpreted as a parameter: PROJ_START_DATE and
PROJ_END_DATE
I tried rewriting yoru code to match my control names and I tried changing
my control names to match yours.
I am such an idiot in that I just cannot get this to work.
I emailed you my database after pretty much gutting it to nothing. Maybe you
can see something I am missing.
Andy