Splitting Records in Continuous Form

[William McKinley]

All,



I've been given a great solution to a previous problem in this thread but a new requirement has been added to the form. My supervisors want the ability to record multiple types of production hours for each employee by day on one form. Thanks to this forum, I achieve the multiple records on one form. Unfortunately splitting the hours has left me quite stumped.

At first I thought I would create unbound text boxes to key in each type of hours worked for each employee's daily record. That did now work as the data updated for all rows of the unbound text box in the form. My next thought was to create Dummy fields to associate with the records, which worked, but also causes a normalization issue. My thought to fix the normalization issue is to have some kind of DoCmd.RunSQL function create two new records based on the textbox control it's associated with: textbox1(ProdHours) would create a record for Production Hours, textbox2 (CleanHours) would create a record for Cleaning Hours. \

The SQL function RespCode (responsibility code) associated based on what they were doing. That would fix the normalization issue, but I can't figure out how to do it.

Here's what everything looks like:



The first three records are what the form outputs currently. The last six records are what I would want the SQL function to spit out. I would then run a delete record query if the RespCode field is null.

Any thoughts on how to achieve the SQL part of this?

Thanks!

[June7]

The image is not showing for me (nor do the ones in the other thread).

So you want two records for each employee listed in the combobox? Simplest approach at this point is to have two For loops:

intCount = Combo17.ListCount

For i = 0 to intCount - 1
DoCmd.GoToRecord acForm, "TEST", acNewRec
Me.Combo17 = Combo17.Column(0, i)
Me.RespCode = {code for Production}
Next

For i = 0 to intCount - 1
DoCmd.GoToRecord acForm, "TEST", acNewRec
Me.Combo17 = Combo17.Column(0, i)
Me.RespCode = {code for Cleaning}
Next

Run DELETE query to remove records where Hours field is null.

[William McKinley]

Does the image show up now?

They want to maintain a "matrix" style form if all possible. It "makes it easier for them to key in data"

I tried modifying your code by doing:

Dim sql As String
sql = "INSERT INTO tblHours(Hours, RespCode, EmployeeID, ProdDate) Values ([Forms]![TEST]!ProdHours,1,[Forms]![TEST]!Combo17,[Forms]![TEST]!Combo17) "

intCount = Combo17.ListCount
 For i = 0 To intCount - 1
 DoCmd.RunSQL sql
 Me.Combo17 = Combo17.Column(0, i)
 Next

but it only updates everything with the current selected record's data

[June7]

Yes, it shows now but I am viewing at home so don't know if that makes a difference if you did something.

Well, that data entry form does not reflect the normalized structure of the six records. It might be easier for the users but programming for it is a pain.

Either do normalized or non-normalized, don't try to mix them. If you really have to use 'matrix' style, probably will have to be with an unbound form and lots of code.

[June7]

Okay, here is the only way I can envision your 'matrix' form to work with normalized table (aside from your original arrangement with finite set of controls).

Put in form header section combo or list box of employees, unbound textboxes for hours, and a command button. Form can be bound to the table with bound controls in Detail section.

Select employee from listbox, enter hours in textboxes, click button to save records. Repeat for each employee in the list. Code behind the button:

DoCmd.GoToRecord acForm, "TEST", acNewRec
Me.Combo17 = Combo17
Me.RespCode = {code for Production}
DoCmd.GoToRecord acForm, "TEST", acNewRec
Me.Combo17 = Combo17
Me.RespCode = {code for Cleaning}