I would stay away from using VBA, since you can just bind your form directly
to the table. Assuming for the moment, though, that you need to add an entry
containing Employee_ID, Hire_Date, SSN, & Salary to the cbo_tbl_A table, here
is a way that ends up being much easier than using VBA. Be careful to
differentiate amongst RecordSource (of the form), ControlSource (of the
text/combo boxes) and RowSource (of the combo box) as you read:
Set the form's RECORDSOURCE to be dbo_tbl_A (just the table name, assuming
you have it linked already). In the form's detail section, make a combo box
called Employee_ID. Make its CONTROL SOURCE also Employee_ID (i.e. the
Employee_ID field from the dbo_tbl_A table).
Make three text boxes called Hire_Date, SSN, & Salary. Set their CONTROL
SOURCEs to be the same/respective field names from the table
Set the ROWSOURCE for the Employee_ID combo box to draw from the Employee_ID
list (another table probably) I would have column 1 be the Employee_ID and
column 2 be [FirstName] & " " & [LastName]. Make sure that column 1 is the
primary key and is the bound column. You can specify column widths of 0,1 to
bind to the Employee_ID but show FirstName LastName to the user.
Put this into the form's Open event:
DoCmd.GoToRecord acForm, Me.Name, acNewRec
Here is what happens:
When you open the form, it automatically goes to a new blank record for
Table A. You pick the employee and enter values for hire date, SSN, & salary.
When you close the form or navigate to a new record, the current record is
automatically saved to table A. And all that with only one line of VBA code!
bingo said:
Brian, thank you for a quick respond. am sorry that i didn't provide enough
information. the new table 'B' already existed in the database. the combo
box is a lookup field, it pulls the emp_id from table 'A'. and display the
employee first and last name so that the user knows who he/she is working on.
i'm able to accomplish this. The form has also been bounded to a new table
'A' ( i did this by go into form properties and select table A so it shows in
the form's record source. as i mentioned in the initial post, the user will
enter information into three text boxes. i would like to take the emp_id and
the information in the three text boxes and add them to table A. i thought
the user would have to click a button and there must be code behind the
button in the event procedure in order to add the record. as you can see, i'm
a newbie to Access. this is the code i have so far but it doesn't work. when
i run the form and click the add button, i receieve a compile syntax error
and the Insert Into line is highlighted. can you please tell me what is
wrong with the syntax. thank you for all respond/suggestion.
Private Sub btnAddRecord_Click()
On Error GoTo Err_btnAddRecord_Click
Dim strSQLAppend As String
strSQLAppend = "INSERT INTO dbo_tbl_A ("[Employee_ID],[Hire_Date],
[SSN], [Salary]") & _
"SELECT
[Forms]![Compensation]![cboEmpID],[Forms]![Compensation]![TxtHireDate],
[Forms]![Compensation]![TxtSSN],[Forms]![Compensation]![TxtSalary]"
DoCmd.SetWarnings (warningsoff)
DoCmd.RunSQL strSQLAppend
DoCmd.SetWarnings (warningson)
Me.cboEmpID = ""
Me.TxtHireDate = ""
Me.TxtSSN = ""
Me.TxtSalary = ""
Exit_btnAddRecord_Click:
Exit Sub
Err_btnAddRecord_Click:
MsgBox Err.Description
Resume Exit_btnAddRecord_Click
End Sub
Brian said:
When you say, "add these to a new table", are you talking about
programmatically creating the table on the SQL server via Access? Or does the
table already exist?
Assuming the latter, simply set up an ODBC link to the SQL server table in
Access (File -> Get External Data -> Link Tables -> Files of Type -> ODBC
databases; the DSN must already exist). Make the form's RecordSource this
linked table and bind each of the combo/text boxes on your form to the fields
in the table. Then, you don't have to "submit" it; it saves automatically.
You just have to control whether it goes to a new record when you open the
form (if you need to prevent users from editing existing records). To do
this, put this VBA code in the form's open event:
DoCmd.GoToRecord acForm, Me.Name, acNewRec
:
I would like to use a form to enter data into a SQL Server table. On the
form, i've a combo box that pull in the emp_id from table A and three text
boxes, which allow the user to fill in the information (hire_date, SSN,
salary). Once the user enter the data into the three text boxes, they can
click a button to submit the data. i would like to know what vba code do i
need behind the button to take the emp_id, hire_date, SSN and salary and add
these to a new table (table B). Your help/suggestion is greatly appreciated.
