Help Saving form data to a different table (1 Viewer)

[bessej43]

Can anyone assist me in saving the information that is showing on my form to a different table. The table that I want to save it to is called Leave Control Log. The system locks up on the first line (DIM DB As Database). Can any see where I messed up. Thank you for your help.

Dim DB As Database
Dim RST As Recordset
Set DB = CurrentDb
Set RST = DB.OpenRecordset (“Leave Control Log”)
RST.AddNew
RST!Leave Number = Forms!Input Data Lv Log!Leave Number
RST!Issue Date = Forms!Input Data Lv Log!Issue Date
RST!Full Name = Forms!Input Data Lv Log!Full Name
RST!Unit Desc = Forms!Input Data Lv Log!Unit Desc
RST!Grade Desc = Forms!Input Data Lv Log!Grade Desc
RST!Current Duty Phone= Forms!Input Data Lv Log!Current Duty Phone
RST!SSAN = Forms!Input Data Lv Log!SSAN
RST!Leave From = Forms!Input Data Lv Log!Leave From
RST!Leave To = Forms!Input Data Lv Log!Leave To
RST!Leave Type = Forms!Input Data Lv Log!Leave Type
Set RST = Nothing
Set DB = Nothing

 

[Pat Hartman]

You need to make sure that the DAO 3.6 or 4.0 library is installed. Then change the Dim statements so that they specificly define DAO objects so Access doesn't confuse them with similarly named ADO objects. You also need to add an .update statement. There is another error that I'll leave you to correct. VBA cannot support object names that contain embedded spaces or special characters. Therefore, you'll either need to change the offending names to something acceptable (preferred) or surround each offending name in square brackets whenever you use it.

For example:
RST![Leave Number] = Forms![Input Data Lv Log]![Leave Number]

Dim DB As DAO.Database
Dim RST As DAO.Recordset
Set DB = CurrentDb
Set RST = DB.OpenRecordset (“Leave Control Log”)
RST.AddNew
RST!Leave Number = Forms!Input Data Lv Log!Leave Number
RST!Issue Date = Forms!Input Data Lv Log!Issue Date
RST!Full Name = Forms!Input Data Lv Log!Full Name
RST!Unit Desc = Forms!Input Data Lv Log!Unit Desc
RST!Grade Desc = Forms!Input Data Lv Log!Grade Desc
RST!Current Duty Phone= Forms!Input Data Lv Log!Current Duty Phone
RST!SSAN = Forms!Input Data Lv Log!SSAN
RST!Leave From = Forms!Input Data Lv Log!Leave From
RST!Leave To = Forms!Input Data Lv Log!Leave To
RST!Leave Type = Forms!Input Data Lv Log!Leave Type
RST.Update
Set RST = Nothing
Set DB = Nothing

 

[bessej43]

Thank you Pat

Pat,

Thank you for all the help.