Combo Box not showing the correct Field

[Atlascycle]

I will make that change when I get in in the morning. Thanks again for the help and advice.



Jason

[Atlascycle]

When you are Building a Query is it poor practice to add several Tables that are linked and have the Query return the Text Values or is it better to have the Query return the Foreign key Values and then change the row source in your forms and reports?

[jzwp11]

It is not so much as a good or bad practice, but more related to what you are doing.

If you are doing a data entry form, then it is best to base the form on a single table and use combo boxes to populate related single values such as you have already done.

For reports, you generally want the text to show up since the key values typically do not mean anything to the person looking at the report. So you would base your report on a query that joins the various tables by the key fields but you would only include the text values in the SELECT clause because that is what you want in the report.

In some cases, you may want both the key value as well as the text value.

[Atlascycle]

As of now rptEnergySurvey is the beginning of how i would like to format the report. I am not sure how to alter the record source to look at the Query qrysurvey instead of the LOTO table.

frmSurveyParm is the unbound form that I created to filter the report, cmbMachineID has the Equipment Table as its Row Source and it displays the Text value for MachineID

Also I am not sure how to get the unbound form to filter the report, I am assuming that i need to set the On Click Event but do not know how to tell Access the filter based on the Value in cmbMachineID

Unfortunately I do not have a clue when it comes to using VB code.

[jzwp11]

As of now rptEnergySurvey is the beginning of how i would like to format the report. I am not sure how to alter the record source to look at the Query qrysurvey instead of the LOTO table.

Open the report in design view, go to the property sheet of the report-->Data tab, change the Record Source to qrySurvey. I looked in the database you posted and it is already changed to qrySurvey.

As to the code, you would need Visual basic for Application (VBA) code in the on click event of the button. This needs to be an event procedure not an expression. The code would look like this:


DoCmd.OpenReport "rptSurvey", acViewPreview, , "[machineID]=" & Me.cmbMachineID


I could not test the query (qrySurvey) or the report since you did not provide the backend database. The code above assumes that the bound field of the combo box is the machineID such that it matches the machineID field in the query.

[Atlascycle]

Ok Attached is the current and the back-end databases, whe i choose the machine and then click the button it is asking for a Parameter Value and has the machine that i picked listed above the test box on the parameter value window.

If i open the form in Design View the Row Source is qrySurvey, the coulum count is set to 1 with a 1" Column width.

MachineID is the First Column in the Query.

[jzwp11]

My apologies, I forgot that the machineID field is text, so the docmd syntax is slightly different for a text value versus a numeric value (it has to be enclosed by single quotes). I have made the modification in the attached and show below (Single quotes shown in red have to be part of a literal which are, in turn, enclosed in double quotes.)

DoCmd.OpenReport "rptEnergySurvey", acViewPreview, , "[machineID]='" & Me.cmbMachineID & "'"

[Atlascycle]

Thanks again for all of your Help, No need to apologize.

what would your suggestion be for adding the information that is in the junction tables? ie. energy sources, Operator controls, LOTO Devices Required, etc.

[jzwp11]

You mean adding them to the report? If so you have two options. First, include them in the query and then use the report's grouping levels to organize them (might get a little messy and may be difficult to organize effectively). Second would be to add subreports to the main report in much the same manner as subforms are to forms.

[Atlascycle]

Yes I meant to the Report, sorry for the vague statement there. I will give the subreport a try.

Thanks Again.

[jzwp11]

You're welcome.

[Atlascycle]

Subreports are Coming along Nicely, thanks again for all of the advice,

Will be working on the Procedure Subreports in the Morning.

[Atlascycle]

As of Now I have the report laid out and working. Now I need to work the photos back in. When i originally started posting the project for review i was deleting records to get the size of the database down to get it to upload, the problem was because i had photos as attachments to the LOTO table. I then copied the original database and deleted the attachment fields in the version that i was posting. I have been thinking about leaving the attachments in a Seperate database and linking the table to the LOTO Database. What are your thoughts on this. Should I add the attachments back into the LOTO database or should i keep the attachments in a separate database and Link the tables.

[Atlascycle]

I tried to follow the normalization rules and put all of the photos into one table and assigned a category to each one. My problem now is how to pull the attachment from the query and put it in the appropriate area of the report. If I add the photo field and the photo category field to the query that the report is based on and add the criteria to the category I can get the correct photo for one of the three but not sure how to get that to work three separate times.

I tried to copy the SQL statement from my qryOpConPhoto query into the record source of the field in the report but that would not work. Could you give me a nudge(ok a shove maybe) in the right direction.

[jzwp11]

In the older versions of Access, embedding pictures caused bloating issues, but with the attachment field those issues were reduced from what I have heard. I have not worked with pictures in either Access 2007 or 2010. In older versions, I just put the path to the picture in a field and used some code to open it.
Since I have not really done much with attachments in 2007/2010, I may not be the best person to answer your report question and how to place the pictures. It might be best to post that question in a new thread now that you have normalized your structure.