Using a Form to save data from one Table to another

[pmcorriveau]

Okay, I'm definitely an Access rookie, but I have kind of a unique problem I've been unable to solve. There is a public master database with a bunch of tables and data in it being maintained by another group. My boss wants to skim some information from this, add some of his own information to it, and save it in a completely separate .mdb file on our server.



I've used Access to link to the public database, built a custom table just for us, and built a form. The form uses bound controls on the left side to pull in data from the public database, and unbound controls on the right side for user entry of data. I coded a VBA save button that should save all controls (bound/imported as well as unbound/data entry) to our local table.

The unbound controls save just fine, but the bound controls are missing from the table. A new row is created with no problems, I get no error messages, but half the fields in the table are just blank.

DoCmd.GoToRecord , , acNewRec
    Dim Rs As Recordset
    'Dim SDB As Recordset
    'Dim strSQL As String
        
    Set Rs = CurrentDb.OpenRecordset("Supervisor Table", dbOpenDynaset)
        
    Rs.AddNew
    Rs![TestNo] = Me.TestNo
    Rs![TestTitle] = Me.TestTitle
    Rs![RevNo] = Me.RevNo
    Rs![Comment] = Me.Comment
    Rs![Added_By] = Me.Added_By
    Rs![Comment_Date] = Me.Comment_Date
    Rs![ResponsibleEngineer] = Me.ResponsibleEngineer
    Rs![TestWriter] = Me.TestWriter
    Rs![CP_Required] = Me.CP_Required
    Rs![Priority] = Me.Priority
    Rs![Due_Date] = Me.Due_Date
    Rs![Additional_Comments] = Me.Additional_Comments
    Rs.Update
    Rs.Close
    Set Rs = Nothing

Any idea what the problem is here? Do I need different VBA commands? Any help would be greatly appreciated, because this is the last show-stopping bug I've got. Thanks!

[June7]

Saving the values to fields of recordset should not matter if the controls are bound or unbound.

Why do you have the GoToRecord at the beginning of the procedure? You are moving to a new record of the form's RecordSource before saving record to Rs so of course the controls will all be blank.

[pmcorriveau]

June7, really appreciate the reply - seems like lots of problems get solved here.

I tried commenting out the GoToRecord line, and the debugger jumps in with an error. To answer your question, that line has been in all the code samples I've found online, as well as being generated by the wizard when I create a new command button. When I go to the "Record Operations" category, and select the "Add New Record" action, that line of code is already in the VBA template.

Just to be clear, the flow of events is like this:
1) User just opens the form, bound fields properly populated, unbound fields properly blank.
2) User fills in unbound forms.
3) Form now looks correct, user pushes button to save all fields of all controls into a brand new record in a local table.
4) Seeing no errors, user opens table to verify successful saving, and there is a new record, but only half the fields (columns) are populated, the other half (corresponding only to the bound controls from the form) are blank.

Hope that clears up any confusion from my initial post. Still don't know where I could be going wrong.

[pmcorriveau]

Some further information - when I delete the link to the public table, and instead just do a one-time import, everything works just fine. Is there something about VBA that doesn't play well with linked data? I didn't think this mattered, but maybe it does?

[June7]

What is the exact error message?

Link table should not be issue. Don't understand why behavior is different with imported data.

The GoToRecord is not appropriate in this case because you are not adding record to the table the form is bound to.

Something else is going on. Is there more code in that procedure?

Want to provide project for analysis? Follow instructions at bottom of my post.

[pmcorriveau]

June, don't know what changed, but when I came in this morning with your line still commented out, everything worked fine. Plus, once I figured out why that command was wrong, I used the acNext command further in the code to automatically move to the next record once the save button is pressed. Appreciate all the help!