How to make one combo box required based on selection in another combo box

[Etoimos]

The DataMode argument goes after the WhereCondition:https://docs.microsoft.com/en-us/off...docmd.openform

DoCmd.OpenForm "Run Sheet", , , "Event = '" & Me.EventLookupCombo & "' AND ExecutionDate = #" & Me.DateLookupCombo & "# AND RunNumber = " & Me.RunLookupCombo & "",acFormReadOnly

But it would be much easier if the last combo would return the unique record ID instead of the run number, you would only have to reference one control.



Cheers,

Your last bit of code got it to working with the ReadOnly at the end. I'll still play around with using the ID as you suggested earlier as well. It does not hurt to know more than one way of doing something.

[Etoimos]

This next set of questions might belong in their own thread, but they pertain to a lot of the stuff we have worked out in this thread, so I'm going to start here.

Now that I have the Run Sheet working the way that it needs to, I need to build a Form (or maybe a report) that allows the employee to summarize the day of testing activities. It is called a Daily Activity Report (DAR). I think it will be easier to try and show what I want to accomplish then trying to just explain it:



All of the words/data in blue will be simply pulled from the RunResultData table that was populated by the Run Sheet form. The black words/data will be new entries that are made on this form and stored in a new DAR table. The portion I'm not sure about is the red and magenta areas.

I need those to be dynamically created based on the number of Runs that were completed in a day. That can vary from day to day from 0 to 10. I need this form to determine how many Runs were conducted on the selected day, add the Run Number # heading and then populate the blue colored data below it, and finally add a text box so that the employee can add notes about the run.

Is something like this even possible?

[Gicu]

Sorry, not quite following you, can you quickly show us an example for a day with lets say 3 runs? You want to repeat the run specific blue boxes or concatenate in them all three run results? To edit data (add the summary details/notes) you will need a form bound to the DAR table. On that form you could add a subform bound to the RunResults table filtered by the date in the main form so it only shows the runs for that day.

Cheers,

[Etoimos]

Here you go Vlad:



I would like each Run for the day to have its own entry/section. I would like for those sections to be setup in two columns to match the style of the Run Sheet form we made (if you remember how that was laid out) with sections for remarks that spans both columns.

I was thinking that I needed this new form to be bound to the new DAR table and I thought that I might need to use a subform to bring in the Run data. What I'm not sure of is if I can control the layout of the subforms and what the best way is to have employee select the date to retrieve them.

[Gicu]

But if each run has its own summary section they don't belong in the DAR table but the RunResults. Adding fields like RunSummary1, RunSummary2....RunSummary10 to tblDAR goes against relational database design. What happens if you have a day with 12 runs? You would need to add two more fields to the table and redesign a whole lot of queries/forms/reports/VBA to accommodate them.
You can add a RunRemarks field in the RunResults table and make it show like you want in the subform. Then in tblDAR you would have a field for date and a summary remarks field for the entire day (all runs).

To select a date you can use a number of controls, maybe a listbox showing the date and the number of runs for that day (you could limit the row source of the listbox by month or week if you wish) or a combo similar to what you have on the switchboard but showing summary data (RunDate, NumberOFRuns).
Cheers,
Vlad

[Etoimos]

The Run Summary will be written up after the Run has already been completed, sometimes hours after the fact. The DAR is where leadership will look for the summaries. I guess if the Run Summary was a record in the RunResultData table that was both entered and shown only on the DAR, that could work. So would it be possible to have a Daily Summary on the DAR form that gets submitted to the DAR table and multiple Run Summaries that get submitted to the RunResultData table all via the same submit button at the bottom of the DAR form?

[Gicu]

Doesn't matter when you populate the field, just don't show it in the daily run form and only show the field in the DAR form.

I guess if the Run Summary was a record in the RunResultData table

it is not a record, it is a field that is part of the daily run record. If you link the form(bound too DAR table) to the subform (based on the RunResults table) via the date you would have them both bound/live, no need for extra submit buttons, you simply populate the data in the form and it gets saved in the right table.

Cheers,

[Etoimos]

Thanks Vlad. So here is my thought process on this:

RunResultData table
Add a DARsummary field and make it Long Text and not required


DAR table
Add a Dailysummary field and make it Long Text and not required

Do I need to add anything like a date and event field to the DAR table since that info is already stored in the RunResultData table? I'm not quite sure how the two will be tired together if there is not these fields.


DAR form

Have a Daily Summary text box bound to the DAR table Dailysummary filed for the employees to enter the daily summary in.
Have a RunSummary text box bound to the RunResultData DARsummary field for the employees to enter the summary for each run.
Add all other required fields to be pulled from the RunSheet form that was filled out.
Add a submit button at the bottom.

[Gicu]

In the DAR table you need a TestDate and you use that to link the form to the subform, so for each date in the DAR table (bound to the main form) you will see in the subform all the daily runs that happened that day.

Cheers,

[Etoimos]

Okay, I've got my main forum, DSRfrm, made and I have a subform, Run Sheet subform, linked to it. I'm able to select the date on the DSRfrm and have it display the first Run for that day in the subform. What I'm not sure how to do now is get additional subforms to the other runs that were executed on that date. Right now I have the navigation controls for the subform turned off since I do not what people to have to click/scroll though the different runs. I need each run to be displayed just like the Run 1 that shows up now.

I've attached the sample db if anyone wants to have a look at it.

[pbaldy]

I don't know if it's the way I'd go, but the only way I can think of offhand to get the look you're describing is with multiple copies of the subform and code to set their sources. I mucked around and this worked:

Private Sub DSRExecutionDate_AfterUpdate()
  Dim strSQL As String

  strSQL = "SELECT * " _
    & "FROM RunResultData " _
    & "WHERE ExecutionDate = #" & Me.DSRExecutionDate & "# AND RunNumber = 1"

  Me.RunResult1.Form.RecordSource = strSQL

  strSQL = "SELECT * " _
    & "FROM RunResultData " _
    & "WHERE ExecutionDate = #" & Me.DSRExecutionDate & "# AND RunNumber = 2"

  Me.RunResult2.Form.RecordSource = strSQL

End Sub

[Gicu]

Consider the attached design. It will keep the form concise and clean (yet the users would have to scroll through records) and create a pdf file based on a report with a click of a button. Or you could open the report instead of PDF and print.

Cheers,

[Etoimos]

I don't know if it's the way I'd go, but the only way I can think of offhand to get the look you're describing is with multiple copies of the subform and code to set their sources. I mucked around and this worked:

Private Sub DSRExecutionDate_AfterUpdate()
  Dim strSQL As String

  strSQL = "SELECT * " _
    & "FROM RunResultData " _
    & "WHERE ExecutionDate = #" & Me.DSRExecutionDate & "# AND RunNumber = 1"

  Me.RunResult1.Form.RecordSource = strSQL

  strSQL = "SELECT * " _
    & "FROM RunResultData " _
    & "WHERE ExecutionDate = #" & Me.DSRExecutionDate & "# AND RunNumber = 2"

  Me.RunResult2.Form.RecordSource = strSQL

End Sub

Looking at this code it appears that they would have to know how many Runs were done each day to select them? If that is the case this is not going to work.

Consider the attached design. It will keep the form concise and clean (yet the users would have to scroll through records) and create a pdf file based on a report with a click of a button. Or you could open the report instead of PDF and print.

Cheers,

This is not ideal since they have to scroll through the records. But since it is only the operators that have to scroll through the Run records and the management types will receive the PDF (manually via email) version, we could probably make this work. A couple of things I'll have to workout/tweak:

The PDF file name
We currently use this naming convention for are DSR PDFs: Lab_EventName_EventPhase_JulianDate_DSR
Looking at the code, it looks like you pulled the execution date out of the form and appended it to the the end of the file name after a "hard coded" DSR_rpt_. That leads me to believe that if I include all of the above mentioned fields in the form, I should be able to pull those out and have the file named the way we want it.

Submit Button
I'll need to add a submit button at the bottom. Our operators will want to click a button for them to know it was all entered into the database based on their training and experience with other systems. I'd like for this submit button to also create the DSR. Or maybe just the act of them clicking on the PDF button would be enough. As I understand it, once you click out of the textbox area, what you have typed is entered into the table record. If I go this last route, I would need the PDF button to close the current DSR form.

Remove Navigation from the main form
I think this will confuse them as to which navigation control they need to use. I'll setup a Search DSR button on the Switchboard to allow them to bring up past DSRs.

Execution Date selection
In almost all cases, the operators will be filling out the form for the current date. Having to wade through all the dates in a table style format would get troublesome in a big event (100+ days of testing). I'm not sure if a Date Picker would work better or cascading combo boxes. The latter would solve the issue of two events happening on the same day.

[Etoimos]

Also, there is excess blank space at the bottom of the DSR form that I can't seem to figure out where it is coming from. I'd like for the form to stop scrolling when they get to the bottom of it (where I have moved the PDF button to). What could be causing this?

[pbaldy]

Looking at this code it appears that they would have to know how many Runs were done each day to select them? If that is the case this is not going to work.

Presuming they are sequentially numbered, it would be trivial to pull the maximum run number and loop the code that many times. You'd still have to have created the maximum number of subforms. I might put them on tabs instead of stacking them one above the other.

That said, I probably wouldn't go this direction anyway. I might have a "runs" combo next to the date textbox. It would get populated with whatever runs exist when they chose a date (like cascading combos). Changing the combo would change the run displayed below.