Hi there,
I'm not sure I follow you 100%, so if I miss the mark, I apologize. If you want to write a new record to an Access table just before you save your workbook, you could try modifying the following to suit your needs:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Macro purpose: To add record to Marina Access database using ADO and SQL
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDB As String
' Set the string to the path of your database
strDB = "D:\My Documents\Access Files\Marina.mdb"
' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
' Open recordset based on Moorages table
rst.Open "INSERT INTO Moorages (CustID,Type,DatePaid,DateStart,DateEnd,Amount)" _
& " VALUES ('zz-01','Prorate',#1/15/03#,#1/15/04#,#2/28/05#,500)", cnt
' Close ADO objects
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
This code would go into the ThisWorkbook module. The hardest part would be modifying the SQL statement to get it to match your table structure, so a breakdown follows:
"INSERT INTO Moorages (CustID,Type,DatePaid,DateStart,DateEnd,Amount)" _
& " VALUES ('zz-01','Prorate',#1/15/03#,#1/15/04#,#2/28/05#,500)"
-Replace "Moorages" with your table's name
-Replace "CustID,Type,DatePaid, etc..." with the column headings that you want to fill on your table. I believe that you should only need to put in the columns that you actually want to fill with data, providing that any you don't care about can accept NULL values or have default values
-Change my info in the values section to match what you want. Make sure that you separate each field with , surround text with ', dates with #, and values need no markings
This can be coded to accept values directly from your worksheet cells as well. More information would be needed to accomplish that though.
Hope this helps,
Ken