RE: adding record via form

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Brian, thanks again for your respond. i figure it out. i miss a couple
quotation mark in the code.

"Brian" wrote:

> 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" 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
> > '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" 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.
.

Quantcast