Solved Is there a way to prepopulate a continuous subform? (1 Viewer)

[Bloodlvst]

I'm not sure if this is even possible, but hopefully you all can help.

I've attached my relationships.

So I have a form for ScorecardSummary. There is a subform within this form for ScorecardItems, which is a continuous form.

What I'd like, is a way that when you open the scorecard summary form, that the subform is already populated with 11 items for that interaction ID, and each is populated with each of the 11 score types. Is there a way to store the values input in a continuous form and only save them to the database if the user clicks a save button of some sort? (I already have logic on my single forms where the data isn't saved until the user explicitly submits, just not sure if this can be done).

Or, would it just be easier to have all 11 "scoretypes" as a column on the "tbl_scorecarditems" since every type is mandatory on every scorecard anyway?

 

[theDBguy]

Hi. Whatever you do, don't add the score types as columns in your table. Almost anything is possible in Access; but whether it's a good idea or not, is another matter.

 

[Bloodlvst]

Almost forgot to attach my relationships

 

[pbaldy]

You can run an append query that gets the items from an "items" table plus the interaction ID from the form.

 

[Bloodlvst]

You can run an append query that gets the items from an "items" table plus the interaction ID from the form.

I think I understand what you mean...Let me see if I have it right. So I could:

1) Have logic to check if there's already an entry matching InteractionID when I open the ScorecardSummary form. If there is already entries, just display those as normal.
2) If not, change the form source to the append query until saved

Or am I way off here?

 

[theDBguy]

I suspect you might end up using a temp table here, since I thought you said you didn't want to save the records to your table unless there's a score (or something). Just my 2 cents...

 

[Bloodlvst]

I suspect you might end up using a temp table here, since I thought you said you didn't want to save the records to your table unless there's a score (or something). Just my 2 cents...

Is a temp table just a table with the same columns, and I use it as a sort of cache until I commit the record (and then copy the rows into the "real" table)?

Sorry, not the most well versed in Access unfortunately.

 

[theDBguy]

Is a temp table just a table with the same columns, and I use it as a sort of cache until I commit the record (and then copy the rows into the "real" table)?

Sorry, not the most well versed in Access unfortunately.

Hi. Yes, that's correct. Either way you go, it sounds like you'll be creating placeholder records, which I would usually not recommend. Good luck!

 

[pbaldy]

I think I understand what you mean...Let me see if I have it right. So I could:

1) Have logic to check if there's already an entry matching InteractionID when I open the ScorecardSummary form. If there is already entries, just display those as normal.
2) If not, change the form source to the append query until saved

Or am I way off here?

You wouldn't change the source in #2, you'd run the append query and then maybe requery the form depending on where you put the process.

I do this type of thing to help data entry users. It's easier for them to just hit enter on items they don't need and fill in quantity on items they do as they go down a continuous form. They'd string me up if I made them select each desired item in a continuous form. When they exit the form I delete any they didn't enter a quantity for.

 

[Pat Hartman]

There's a difference between having optional items and required items. If the user ALWAYS needs to answer 11 questions regarding an interaction, then it makes sense to use an append query to append the rows with the typeID already filled in at the start of the process so the user can just go down the list and enter his answers.

If you were creating an inspection system and there were a hundred possible inspections some required and some optional, the append query should append the required items and the user would manually insert the optional ones by picking their typeID's from a combo.

 

[Galaxiom]

None of those clumsy solutions like Temp Tables, Append Queries or Placeholder Records are required for this task. And only trivial code.

Have a look at how it is done in the grades.mdb sample database in Post # 3 of this thread.

The outer join creates virtual records that appear in the Form's recordset for data to be entered. Only those rows where data is entered are ever written to any table.

A Mandatory flag can probably be added to the table that manifests the virtual records to use in a test Before Update.

 

[pbaldy]

@Pat: in my case there are 10-12 rates, a driver could have charged one or more of them during their shift. I'm dealing with "heads-down" data entry people keying in data off the driver's sheet. They would scream if they had to stop, look up, take their hands off the keyboard to select a rate, etc. They enter data for several hundred drivers a day. This process allows them to simply key in a quantity or hit enter to skip a rate. They can hit tab to jump out at any point.

@Galaxiom: That is a slick solution, I'll keep it in mind if the need arises for this type of thing.

 

[Pat Hartman]

Heads down data entry is a whole different ball of wax. I've only built one application that actually relied on data entry operators since I started working with Access. In all the other cases, the users did their own data entry, frequently with the customer on the phone so the process is different. Whenever there are hand written or printed documents though, it is important that the form fields follow along with the data source document so the user doesn't have to jump all around looking for stuff.

 

[pbaldy]

it is important that the form fields follow along with the data source document so the user doesn't have to jump all around looking for stuff.

I agree. Over the years they've tweaked the printed forms a few times and I've tweaked the data entry forms to match.

 

[Galaxiom]

Whenever there are hand written or printed documents though, it is important that the form fields follow along with the data source document so the user doesn't have to jump all around looking for stuff.

I once built a form that precisely replicated the paper form. I used an image of the paper form as the background and built the controls on top of the boxes.

 

[Bloodlvst]

Hi all, sorry to raise this thread from the dead! Some personal life events happened which forced me to give this thread an ol' Irish goodbye. Things are well now, so hopefully you folks are still willing to help me out on this.

@Galaxiom - Thanks for the example, but unfortunately it wasn't quite what I was looking for in this case. Unless I've misunderstood something in it (totally possible lol)

In further tinkering with this, I've manage to get the subform on the tbl_ScorecardItems table to behave in this way:

Private Sub Form_Load()
    Dim counter
    i = 1
    While i <= 11
        DoCmd.GoToRecord , , acNewRec
        Me.ScoreTypeID = counter
        Me.ScoreMetID = 3
        i = i + 1
    Wend
End Sub

So when it opens, it creates 11 new records, one for each of the 11 types we have. So far this is doing exactly what I wanted, if the form is opened, I have all the 11 required scoretypes already populated on the form for the evaluator to simply add their notes to and score. What I can't figure out, and hopefully one of you experts can, is that:

1) I only need to even bother creating all of these if when the parent form is loaded, there are no entries that match its "InteractionID" on the tbl_Scorecarditems table. Ideally I'd want to check if all 11 exist, but since this code should only ever run on a new instance, as long as one entry exists I can assume they all do and not run the loop.
2) I already have some some code for most of my forms that doesn't save data on a form if a user clicks a cancel button, rather than a save button. If the user cancels the parent form, I need to also delete these 11 entries. (This one is a "nice to have" but in no way do I need it )

 

[Pat Hartman]

I would still use an append query. When a new record is added to the main form, use the FORM's AfterInsert event to run the append query (or your code) to insert the rows in the child table.

 

[Bloodlvst]

I would still use an append query. When a new record is added to the main form, use the FORM's AfterInsert event to run the append query (or your code) to insert the rows in the child table.

I just took a look at append queries. I don't fully get it, but I'll give it a go later this evening and see if I can get it to do what I'm looking for!

 

[Pat Hartman]

To use the append query, you need a table of the items you want to append. The append query inserts the appropriate value for the foreign key to link the appended records to the parent.

 

[Bloodlvst]

To use the append query, you need a table of the items you want to append. The append query inserts the appropriate value for the foreign key to link the appended records to the parent.

So essentially, an "orphan" table that does nothing but temporarily hold records until I use the append query to write them to the "real" table?