Update form

Post by **HansV** » 14 Mar 2017, 16:02

duke_44 wrote:also just to clarify on the calculated field. Will this return the amount of unresolved issues for each record or the total in the DB? I would like the number of unresolved for each record to be displayed

When the subform is displayed on the main form, the text box will display the number of unresolved findings for the current record in the main form.

duke_44 · Post by **duke_44** » 14 Mar 2017, 16:44

Can I code it so that my user can enter only a few characters of the VIN and the sfrm filter by any VINs that match the characters entered. Eg. VINs are 17 characters long, I would like my user to be able to enter perhaps 5-8 characters instead of all 17

Post by **HansV** » 14 Mar 2017, 17:21

If you want to match on the first characters of VIN:

Code: Select all

            .Filter = "[VIN] Like " & Chr(34) & Me.txtVIN & "*" & Chr(34)

If you want to match on any subset of characters of VIN:

Code: Select all

            .Filter = "[VIN] Like " & Chr(34) & "*" & Me.txtVIN & "*" & Chr(34)

duke_44 · Post by **duke_44** » 14 Mar 2017, 17:48

is there an easy way to clear the filter(s) and return the subform to where it started?

Post by **HansV** » 14 Mar 2017, 19:04

Execute a line with

.FilterOn = False

within the appropriate With ... End With block.

duke_44 · Post by **duke_44** » 14 Mar 2017, 19:14

Something like this?

With Me.sfrmASR.Form
.FilterOn = False
Me.txtDateFilt = ""
Me.txtVINFilt = ""
Me.txtCACCFilt = ""
Me.txtOpFilt = ""
End With

Post by **HansV** » 14 Mar 2017, 19:16

Yes, indeed.

duke_44 · Post by **duke_44** » 15 Mar 2017, 14:40

https://www.dropbox.com/s/nnav7w3m9h12p ... accdb?dl=0" onclick="window.open(this.href);return false;

Hi Hans, I am now working on a report to show the performance of the PM fields (rptPMSummary with sbrPM). I have added calculated fields in (frmASRTool) as the report is based on performance against a policy. I have added a button (btnPM) which will open up frmSearchWizard_PM where my user can enter a date and retrieve those records. However the reports doesn't seem to be working. Can you please have a look and provide some advice.

The next steps:

I would like the report to be able to calculate the average of just the records/intervals shown in the report.

Post by **HansV** » 15 Mar 2017, 14:41

I'm busy with other things at the moment; I'll have a look later today.

duke_44 · Post by **duke_44** » 15 Mar 2017, 14:43

No problem, I am on your timeline, thank you

Post by **HansV** » 15 Mar 2017, 15:54

1) You have created a procedure

Private Sub btn()PMRpt_Click()

The parentheseses in the middle prevent the code from being valid; it should be

Private Sub btnPMRpt_Click()

2) The combo box cboOperator lists the FileNum field from tblVehRec; I assume that you wanted something else. If so, what?

duke_44 · Post by **duke_44** » 15 Mar 2017, 16:11

At some point I am hoping to be able to have my user search with different criteria so I may need that. The information that I would like to report on is currently stored in tblVehRec if that helps at all.

I fixed the parentheses and the report still has an issue, it asks that I enter a parameter value?

Post by **HansV** » 15 Mar 2017, 16:45

You want to filter on ServName but the combo box shows FileNum values. That clearly won't work.

Ideally, you should replace the ServName field in tblVehRec with ServID, the number field that links to the primary key ServID in tblServList, but that field is currently empty, so I'll leave it as it is for the moment.

The combo box should display ServNames, so its Row Source should be a query based on tblServList that returns the ServName field:

SELECT tblServList.ServName FROM tblServList ORDER BY tblServList.ServName;

See https://www.dropbox.com/s/udw6kjget9364 ... 5.zip?dl=1" onclick="window.open(this.href);return false;
What would you like to calculate averages of?

duke_44 · Post by **duke_44** » 15 Mar 2017, 17:00

The averages:

So when an inspection occurs the user will check their maintenance performance and they are graded based on the average of said services. So I would like to have a report the pulls in data for their maintenance performance. In short if you select a series of inspections from a specified day I would like the report to show the data captured on their maintenance performance, calculate the average and show a percentage of compliance based on the specified policy.

I am sorry if this is long winded I am hoping the more you know the easier it would be to understand.

If you need more information please let me know and thank you as always for your time and help

Post by **HansV** » 15 Mar 2017, 17:22

Which field or fields contain the grades?

duke_44 · Post by **duke_44** » 15 Mar 2017, 17:36

currently the fields are located in tblVehRec named PM1D-PM3D & PM1O-PM3O & PM1H - PM3H each has a PM1DI.....the fields with I in it refers to the interval which is a calculated field that shows the time/distance, etc since the last maintenance. I would need the average of all the I's. I would also need to have the I's judged against the PMPD, PMPO and PMPH (these fields are to indicate what the written policy is) if outside the policy I would like the I fields to be in RED (I have conditionally formatted these fields to appear red already) I would like the report to pull the data from the above fields and displayed. I have created a report rptPMSummary and sbrPM with bound fields.

Post by **HansV** » 15 Mar 2017, 20:12

You'll have to add the PM fields to the record source of the report, qryInspReport.
Right-click the page header or detail section, and click to highlight Report Header/Footer in the context menu.
Add text boxes to the report footer with control sources like

=Avg([PM1D1])

Set the Format property of the text boxes to (for example) Fixed, and Decimal Places to 1 or 2.
You can apply conditional formatting as needed.

If the report is opened with a where-condition from frmSearchWizard_PM, these text boxes will show the average for the filtered records only.

duke_44 · Post by **duke_44** » 16 Mar 2017, 14:04

I think it may be easier to allow my user to press a button and export the required data to Excel.

Does Access have the ability to extract a series of requested entries (eg. a selected inspection date) and place the data in specific cells within a specified Excel template? If so how can you assist with the coding to ensure the data is placed in the proper locations?

I have included a copy of the Excel file so you may see how I'd like the info laid out. The entries on the Excel sheet are currently limited to 12 entries, if possible can they 2 programs work together and allow for formatting to be done automatically as to shorten the amount if less than 12 of increase the amount if greater.

Post by **HansV** » 16 Mar 2017, 16:34

What should be exported to columns C. D to H and J to N?

duke_44 · Post by **duke_44** » 16 Mar 2017, 17:14

The first record would start in Row 5
A is CACC
B is Odometer
D is PM1O
F is PM2O
H is PM3O
J is PM1D
L is PM2D
N is PM3D

F19 would calculate the average of the Intervals for D F H
L19 would calculate the average of the intervals for J L N

A1 would be ServName
D3 would be PMPO
J3 would be PMPD

Eileen's Lounge

Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form

Re: Update form