RE: adding record via form

correction: table 'B' is the new table. the form is bounded to table 'B'. i
would like to add new record to table 'B'. both table 'A' and 'B' are
existing tables in the database. Thanks

"bingo" wrote:

> 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
> 'B' ( i did this by go into form properties and select table 'B' 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 'B'. 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, enter data into the text boxes 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" wrote:
>
> > 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
> >
> > "bingo" wrote:
> >
> > > 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.
.