Hi All
Please would you be so kind as to tell me where im going wrong?
I have a form in excel that writes to an excel sheet, in the VBA(Below), i have requested the cells update a access database.
however i get an error when trying to run it (Try it...)
Sub Paymentinput()
' exports data from the active worksheet to a table in an Access database
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(ActiveWorkbook.Path & "\DataStore.mdb")
' open the database
Set rs = db.OpenRecordset("1", dbOpenTable)
' get all records in a table
Sheets("Database").Select
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ID") = range("A2").Value
.Fields("Manager") = range("B2").Value
.Fields("Date") = range("C2").Value
.Fields("Session") = range("D2").Value
.Fields("Lead Number") = range("E2").Value
.Fields("Surname") = range("F2").Value
.Fields("Consultant") = range("G2").Value
.Fields("Prize") = range("H2").Value
.Fields("Chosen Number") = range("I2").Value
.Fields("Actual Number") = range("J2").Value
.Fields("Start Time") = range("K2").Value
.Fields("End Time") = range("L2").Value
.Fields("Difference") = range("M2").Value
' add more fields if necessary...
.update 'stores the new record
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
range("A2").Select
ActiveCell.FormulaR1C1 = ""
range("B2").Select
ActiveCell.FormulaR1C1 = ""
range("C2").Select
ActiveCell.FormulaR1C1 = ""
range("D2").Select
ActiveCell.FormulaR1C1 = ""
range("E2").Select
ActiveCell.FormulaR1C1 = ""
range("F2").Select
ActiveCell.FormulaR1C1 = ""
range("G2").Select
ActiveCell.FormulaR1C1 = ""
range("H2").Select
ActiveCell.FormulaR1C1 = ""
range("I2").Select
ActiveCell.FormulaR1C1 = ""
range("J2").Select
ActiveCell.FormulaR1C1 = ""
range("K2").Select
ActiveCell.FormulaR1C1 = ""
range("L2").Select
ActiveCell.FormulaR1C1 = ""
range("M2").Select
ActiveCell.FormulaR1C1 = ""
range("A2").Select
End Sub
Please see if you can help....!!!!
Many thanks