Update form

duke_44 · Post by **duke_44** » 08 Mar 2017, 20:06

I would be willing to change the design if you think its feasible and easier to work with. what would the difference be in terms of coding?

Post by **HansV** » 08 Mar 2017, 21:20

In the attached version (zipped), I have added two tables: tblFindingTypes and tblFindings. The first one contains the types of findings: Veh1, File1, etc.
The second contains the actual findings for the records in tblVehRec.

I have left the corresponding fields in tblVehRec for the moment, but they aren't used anymore.

I have created a VBA module basFillFindings with the code that I used to populate tblFindings. Don't run the code again unless you clear tblFindings first.

The findings are now shown in a subform / subreport. The subreport only displays unresolved findings; this is now very easy - see the query qryUnresolved.

DB for Hans.zip

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

I like what you've done! Thanks, looks as though that'll make my reporting easier!

Just for my knowledge, if I leave the design the way I designed it, would my report require a number of IF statements to show only unresolved issues or what would some coding look like?

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

You mention not to run the code again unless I clear tblFindings. This would be constantly updated with different findings year over year, would this pose any issues with it function? Or will it just continue to update tblFindings? Thanks again

Post by **HansV** » 09 Mar 2017, 15:08

duke_44 wrote:You mention not to run the code again unless I clear tblFindings. This would be constantly updated with different findings year over year, would this pose any issues with it function? Or will it just continue to update tblFindings? Thanks again

From now on, new findings entered on the form (actually its subform) will go directly into tblFindings, so there is no need to run the code any more. I merely left it in the database so that you could see how I had transferred the findings from tblVehRec to tblFindings.

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

duke_44 wrote:Just for my knowledge, if I leave the design the way I designed it, would my report require a number of IF statements to show only unresolved issues or what would some coding look like?

If you leave the original design, reporting only unresolved issues would be complicated.

duke_44 · Post by **duke_44** » 09 Mar 2017, 16:13

Good to know! Thanks

So I am creating the Finding subform and I can't seem to get my cbo to display VEH1, Veh2...etc all it displays is 1,2,3...etc. I have gone through the properties a number of times and compared side by side yours. Any thoughts?

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

I set the following properties:
Row Source: tblFindingTypes
Column Count: 2 (this makes the description part of the row source)
Column Widths: 0 (this hides the first column with the numeric ID)

duke_44 · Post by **duke_44** » 10 Mar 2017, 12:56

I have made the change suggested and like the way the DB works, however when I attempt to add data into the form especially in the sub form that we just added to store the findings, I can't adjust the VEH!, VEH2,....File!, File2....etc.

Post by **HansV** » 10 Mar 2017, 16:06

My apologies - I mistakenly made FindingID an AutoNumber field in tblFindings; it should be a number field. (It is correctly an AutoNumber field in tblFindingTypes)

See the modified version.

DB for Hans.zip

duke_44 · Post by **duke_44** » 13 Mar 2017, 16:01

I'm almost certain I made all the changes as you've suggested but I can't seem to have the report correctly display unresolved issues. I have included a new copy of the DB any advice/guidance

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

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

The combo box in the subreport is bound to a field FindType that is not part of the record source. The control source of the combo box should be FindID, and to make it display the corresponding description, Column Count should be set to 2 and Column Counts to 0 (see my reply 3 posts up in this thread).

The text box in the subreport is unbound. Its control source should be Finding.

Finally, the subreport should be linked to the main report by setting its Link Master Fields and Link Child Fields properties to FileNum, but before you can do that, you have to add FileNum to the record source of the main report (as in the database I attached earlier on).

Note: I compacted the database (Database Tools > Compact and Repair Database); this decreased its size from 85 MB to less than 1.5 MB!

See DB for Hans Mar 13.zip on DropBox.

duke_44 · Post by **duke_44** » 14 Mar 2017, 13:37

Thank you!

Is there an easy way to allow my user to export the report to PDF via a button?

Post by **HansV** » 14 Mar 2017, 13:44

Create the following On Click event procedure for the PDF command button on the report:

Code: Select all

Private Sub Command18_Click()
    DoCmd.OutputTo ObjectType:=acOutputReport, OutputFormat:=acFormatPDF
End Sub

The code will prompt the user to specify a location and filename (default name is rptOperator.pdf, but that can be edited)

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

and what if my user cancels during the save process? When I click cancel I get a runtime error 2501.

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

sorry but I have forgot to ask. Is there a way to calculate in my subform how many unresolved issues there are. I have placed a findings field in the subform sfrmASR.

Post by **HansV** » 14 Mar 2017, 14:47

The main form uses subform sfrmFinding, not sfrmASR.
You can place a text box with control source

=Abs(Sum([Resolved]=False))

in the form footer of sfrmFinding.

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

Can I mimic the coding for the Filter by Date button to also filter by a partial VIN and by CACC? Would there be any changes to the VBA other than change field names?

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

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

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

duke_44 wrote:Can I mimic the coding for the Filter by Date button to also filter by a partial VIN and by CACC? Would there be any changes to the VBA other than change field names?

In the line

Code: Select all

            .Filter = "[DateField] = #" & Format(Me.txtDate, "mm/dd/yyyy") & "#"

apart from the names of the field and text box, the # characters and the format are specific to a date field. For a number field, you'd use something like

Code: Select all

            .Filter = "[NumberField] = " & Me.txtNumber

and for a text field, something like

Code: Select all

            .Filter = "[TextField] = " & Chr(34) & Me.txtText & Chr(34)

Chr(34) is the double quote character ".

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