I have form linked to table 1. The form has a few control buttons that change certain fields. I am trying to make one of these controls also append a new record to table 2. Table 2 only contains three fields. One of them will have the ID number of the main record, the second field will have the date at the time the button was clicked, and the third will have the contents of a text field copied to it. I understand this is going to possibly create duplicates, but that's exactly what I want in this case.

My problem is; I don't have a dang clue what I'm doing!

I've tried the INSERT INTO stuff, and I tried to make an append query etc. but I just don't know what I'm doing because this is the first time i've ever had to do this.

Why don't you post what you've tried that fails and we'll fix it? You could do it with a saved query or dynamically.

Yeah, sorry...

Dim dbsrpdb As Database
Dim rstEmployees As Recordset
Dim getID As String
Dim getChangeDate As String
Dim getUpdatedTo As String

Set dbsrpdb = OpenDatabase("\\Csc-fs\CSC-CMS\RP-DB\Back-End\Replacement Parts.mdb")
Set rstEmployees = _
dbsrpdb.OpenRecordset("tblChangeHistory", dbOpenDynaset)

' Get data from the record
getID = Me![ID].Value
getChangeDate = Date()
getUpdatedTo = Me![Status].Value

' Call the function that adds the record.
AddName "tblChangeHistory", getID, getChangeDate, getUpdatedTo

rstEmployees.Close
dbsrpdb.Close

That is calling a function called AddName to do the actual append (I assume). What does that function look like? Also, you're not using the recordset. I don't know if you meant to add the record with it, but if not you might as well not open it.

Sorry about that, I forgot about the function...

Function AddName(rstTemp As Recordset, _
getID, getChangeDate, getUpdatedTo As String)

' Adds a new record to a Recordset using the data passed
' by the calling procedure. The new record is then made
' the current record.
With rstTemp
.AddNew
!strID = getID
!strChangeDate = getChangeDate
!strUpdatedTo = getUpdatedTo
.Update
.Bookmark = .LastModified
End With

End Function

Right now, it breaks at
AddName "tblChangeHistory", getID, getChangeDate, getUpdatedTo

It highlights "tblChangeHistory" and says "type mismatch"

Unfortunately, since I've never seen a working version, I don't understand how it is supposed to work :(

Yes, it's looking for the recordset, not the table name. Try this

AddName rstEmployees, getID, getChangeDate, getUpdatedTo

Yesss!!!!

Thank You!!

Would I be able to put;
Dim dbsrpdb As Database
Dim rstEmployees As Recordset
Dim getID As String
Dim getChangeDate As String
Dim getUpdatedTo As String

Set dbsrpdb = OpenDatabase("\\Csc-fs\CSC-CMS\RP-DB\Back-End\Replacement Parts.mdb")
Set rstEmployees = _
dbsrpdb.OpenRecordset("tblChangeHistory", dbOpenDynaset)

' Get data from the record
getID = Me![ID].Value
getChangeDate = Date
getUpdatedTo = Me![Status].Value

' Call the function that adds the record.
AddName rstEmployees, getID, getChangeDate, getUpdatedTo

rstEmployees.Close
dbsrpdb.Close

into a function or a public module or something and just call the function on the click event of the control?

Excellent, glad it worked for you. Personally I would probably not pass the recordset, rather I'd have the recordset opened in the function. That would keep it all together IMO. Probably just a matter of style though.

I don't quite follow. I need to learn more about how functions pass information to and from the private sub.

Could you please explain a little more?

I suppose my ultimate goal is to have the bulk of the code in a module, so that I can call it from a number of different controls without having redundant code in the form.

I would probably call the function with just the values:

AddName getID, getChangeDate, getUpdatedTo

and open/use/close the recordset within the function. The way you have it would only make sense if you were going to pass different recordsets (ie different tables) to the function. I don't get the sense that that's the case here. I'm not saying it's wrong, it's just not how I would do it. I'd keep all the recordset functionality together, rather than opening and closing it in one place but using it in another.

Sorry, posting at the same time. Your goal is a worthy one, and actually proves my point. In your current method, you'll be repeating the recordset code everywhere, instead of just having it in the function.

Okay, so far this is what I have. I don't know if there's an even better way though. The Sub code still seems a little bulky and redundant...

Private Sub Button_Click()
'************************************************* *************************************
'******* Update tblChangeHistrory ************************************************** ***

Dim rstEmployees As Recordset
Dim getID, getChangeDate, getUpdatedTo, getUser As String

' Get data from the record
getID = Me![ID].Value
getChangeDate = Date
getUpdatedTo = Me![Status].Value
getUser = Environ("username")

' Call the function that adds the record.
AddName rstEmployees, getID, getChangeDate, getUpdatedTo, getUser

'******* End Update of tblChangeHistory ***********************************************
'************************************************* *************************************


Function AddName()
Function AddName(rstEmployees As Recordset, _
getID, getChangeDate, getUpdatedTo, getUser As String)

Dim dbsrpdb As Database

Set dbsrpdb = OpenDatabase("\\Csc-fs\CSC-CMS\RP-DB\Back-End\Replacement Parts.mdb")
Set rstEmployees = dbsrpdb.OpenRecordset("tblChangeHistory", dbOpenDynaset)

' Adds a new record to a Recordset using the data passed
' by the calling procedure. The new record is then made
' the current record.
With rstEmployees
.AddNew
!strID = getID
!strChangeDate = getChangeDate
!strUpdatedTo = getUpdatedTo
!strUser = getUser
.Update
.Bookmark = .LastModified
End With

rstEmployees.Close
dbsrpdb.Close

Is there still a cleaner way?

'************************************************* *************************************
'******* Update tblChangeHistrory ************************************************** ***

' Call the function that adds the record.
AddName Me![ID], Date(), Me![Status], Environ("username")

'******* End Update of tblChangeHistory ***********************************************
'************************************************* *************************************

And get rid of the recordset in the function header:

Function AddName(getID, getChangeDate, getUpdatedTo, getUser As String)

And move the Dim line for the recordset to the function. You should also explicitly declare each of the input parameters, not just the last one. You'll notice I got rid of the variables in the procedure calling the function. My personal philosophy is that if I'm only going to use a value once, I don't bother with a variable. There's nothing wrong with having them there, but hey, you asked for "clean". :p