Multiple Records from a Form/Subform

[Schowti]

I am currently building a database with the purpose of capturing data about various employees' skills. The idea is that a supervisor can fill out a questionnaire of sorts, with this employee, and give them a rating out of 5 for each skill. I have been working on this for a few days, and I am most certainly not an Access genius, so I have come to a bit of a wall. I know where I want to get, but I don't know how or if it is even possible, to get there. I am wanting to have a form that has all the questions on it, so each time the supervisor pulls it up they can just tab through and fill out the form. I have a main form with a subform, although this isn't ideal, it is the only way I can think of to even get close to what I am looking for. The main form portion, after a bit of testing, seems to be producing the desired results I am looking for in populating the specified table, but my subform (questionnaire) is just not doing what I had hoped. I know some of my formatting will change, but I just need a way to allow the supervisors to fill out the questionnaire, hit a button that adds all of the information in both the mainform and subform to their respective table(s) (which I know will require multiple records-1 for each question), and then be able to produce reports by employee. I want various text boxes to autofill with the questionnaire info, and then have a box for the supervisor to input the ratings. I have used a Dlookup for the default property in each of the question and description text boxes in the subform, so they will reference info from a questionnaire table. Any help on this would be greatly appreciated. I attached my database, so take a look and tell me how out to lunch I am...

[June7]

I am not surprised the subform isn't working. You have 20 controls bound to the same field of the same record.

I see two options:

1. 20 unbound controls for each field and use VBA code to save a new detail record for each completed 'row' of the form.

2. Save a set of detail records and then open the form in Continuous or Datasheet view to display the already saved records for the manager to complete. Would not be able to do the grouping as you currently show on the form.

[Schowti]

1. 20 unbound controls for each field and use VBA code to save a new detail record for each completed 'row' of the form.

I thought going the unbound VBA route would be the only option given the layout of my form. My only problem is, I don't know how I would go about writing the VBA for it. Could I get some help on coding just a single record, or in this case, a single question to a table? I appreciate the help. You have already brought much needed relief as it is! Thanks!

[June7]

Google: Access VBA save record from unbound form.

Here is one example of saving a single record: http://www.blueclaw-db.com/unbound_access_forms.htm

In your case you want to save 20 records. Can have the sample code repeated 20 times, just change each instance to reference the appropriate set of controls. An alternative is loop structure which is definitely trickier. Here is an example from my project that builds a very long string from 8 sets of 5 unbound controls:

With Me
    For i = 1 To 8
        strReport = strReport & _
        Pad(i, "R", 15) & _
        Pad(Format(Forms(.Name).Controls("tbxSpgAvg" & i), "0.000"), "R", 19) & _
        Pad(Format(Forms(.Name).Controls("tbxAbsAvg" & i), "0.00"), "R", 9) & _
        Pad(Format(Forms(.Name).Controls("tbxLbfAvg" & i), "0"), "R", 38) & _
        Pad(Format(Forms(.Name).Controls("tbxNAvg" & i), "0"), "R", 11) & _
        Pad(Format(Forms(.Name).Controls("tbxFloAvg" & i), "0"), "R", 7) & _
        " " & vbCrLf
    Next
End With

Your loop would instead incorporate the code in the referenced link. An alternative to code that opens recordset and adds record to the recordset is an INSERT sql action which inserts record directly to table:

For i = 1 to 20
    CurrentDb.Execute "INSERT INTO tablename(fieldname1, fieldname2) " & _
                "VALUES('" & Forms(Me.Name).Controls("controlnameA" & i) & "', #" & Forms(Me.Name).Controls("controlnameB" & i) & "#)"
Next

I am showing apostophe delimiters for text values and # for date values. Numbers don't need delimiters.

Note that for these looping examples the sets of controls must be similarly named and have a numeric suffix.

And if you are not already aware, if names have spaces, special characters, punctuation (underscore is exception) in them or are reserved words, enclose in [].

[Schowti]

So, it is time for me to swallow my pride. I have used VBA before, but it was a while ago, and not near as advanced as this project requires. I thought I could draw on my past, but it seems to have left me hanging. Could you help me out with declaring variables and starting my code. I am also wondering if I need I will need to tie this code to my "Add Record" button? Thanks so much for the help!

[June7]

Yes, code needs to be triggered by an event, such as a button click.

Review
http://office.microsoft.com/en-us/ac...010341717.aspx
http://office.microsoft.com/en-us/ac...080755458.aspx

[Schowti]

Here is one example of saving a single record: http://www.blueclaw-db.com/unbound_access_forms.htm

So I thought this example would be the easiest for me to perform, but for the life of me I cannot get any code I try to work. When he sets the database and the recordset, what is he doing and what are the associated objects or controls? Did he name his database "Currentdb?" Is that recordset a form or table or something else? Also, when I declare my variables, are the various unbound text boxes on my form variables and are the columns I am populating from them variables as well? Just wanted to check. Thanks!

[June7]

Show the code you have attempted.

CurrentDb is an intrinsic VBA constant.

A recordset exists in memory only but depending on how it is opened it can have a 'link' back to the source table/query object. Entries to the fields of the recordset can update the table.

Reference to controls on form is like referencing a memory variable and would be concatenated in the same manner.

[Schowti]

Show the code you have attempted.

CurrentDb is an intrinsic VBA constant.

A recordset exists in memory only but depending on how it is opened it can have a 'link' back to the source table/query object. Entries to the fields of the recordset can update the table.

Reference to controls on form is like referencing a memory variable and would be concatenated in the same manner.

I am in way over my head. Here is the code I am using and I am sure that I am waaaaaaaaaaaay wrong. I am just trying to see if one of the fields will post to the PayrollInventoryDetails_tbl.

Private Sub AddNewSkills_btn_Click()
Set db = CurrentDb
Set rst = db.OpenRecordset("PayrollInventoryDetail_tbl")
With rst
.AddNew
!InventorySkillGroup = Me.InventorySkillGroup1_txt
.Update
End With
rst.Close
End Sub

*InventorySkillGroup1_txt is the name of one of the text boxes on the first row of my question form.

I am so in the dark and frustrated right now, so any help you can give would be amazing. I have been reading up as much as possible on how I might carry out the process I need for this. Thanks so much for the support!!

[June7]

That certainly looks good. Time to test and debug. Refer to link at bottom of my post.

Then will need loop structure that will save each 'row' of the form.

[Schowti]

That certainly looks good. Time to test and debug. Refer to link at bottom of my post.

Then will need loop structure that will save each 'row' of the form.

Well, I am happy to hear that it looks alright, but before I get into debugging I am wondering how big of an affect declaring variables would have on my statements. I haven't declared my variables yet in the code, mainly because I don't know how I would truly go about doing that. Also, the error I get when I try running my code is "Compile Error: Object Required." If there is anything else I may need to know before I start debugging, especially in relation to declaring variables, please pass it on.

[June7]

Oh, sorry, I missed the lack of declarations.

Dim db As DAO.Database
Dim rst As DAO.Recordset

Review http://www.cpearson.com/excel/declaringvariables.aspx

[Schowti]

Oh, sorry, I missed the lack of declarations.

Dim db As DAO.Database
Dim rst As DAO.Recordset

Review http://www.cpearson.com/excel/declaringvariables.aspx

Now it is saying I have a "type mismatch."

[June7]

On which line? Want to provide latest version of project?

Another tutorial http://allenbrowne.com/ser-29.html

It recommends to specify the recordset type, so:
Set rst = db.OpenRecordset("PayrollInventoryDetail_tbl", dbOpenDynaset)

It also doesn't use the DAO. prefix in the declarations but that should not be an issue as I have used that syntax.

[Schowti]

Here is my most recent version. I haven't done a whole lot, but on the Inventory form/subform I have a place a button to which I have attached my code. Now, I am wondering if it is going to be difficult to have the controls on my mainform added to their related table (PayrollInventory_tbl), as well as the controls in the subform to their related table (PayrollInventoryDetail_tbl). They do not have redundant control sources, unlike the subform controls. So all in all, I want to be able to hit Add Record and have both tables simultaneously populated with the information in the forms. I am also planning on adding more fields, that I will make visible depending on the choice from the "Role_cmbobox." If that ends up being completely out to lunch then I will most likely just make a duplicate form with more controls and run the same code on it. You have been a big help. Thanks.