Insert into Table with command button

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hi everyone,

I'm kind of new to programming forms and I have a question
on using a command button to insert some values into
tables.

Examples:

Company Name:______

Company Address:______

Enter

How do I program the Enter button to insert the values
from the two textboxes into my company table and how to
insert the company name into another table (call it comp)
all together at once. What I mean is that, with a click on
the Enter button, the values should go into more than one
table.

Any counseling is appreciated

James
 
Many ways to skin this cat. Below is the 'totally coded' approach. You can
also achieve this goal with queries or a combination of queries and code.
HTH.

- Assaf

'~~~~~~~~~~~~~~~~~~~~~~~

Private Sub cmdSetCompany_Click()

' cmdSetCompany is a command
' button on your form

Dim ws As Workspace
Dim rstComp1 As Recordset
Dim rstComp2 As Recordset

On Error GoTo errHandler

' Use Workspace object for transactions
Set ws = DBEngine.Workspaces(0)
Set rstComp1 = CurrentDb.OpenRecordset("Comp1Table", dbOpenDynaset)
Set rstComp2 = CurrentDb.OpenRecordset("Comp2Table", dbOpenDynaset)

On Error GoTo errTrans

ws.BeginTrans

With rstComp1
.AddNew
!CompanyName = Forms!myform!CompanyName
!CompanyAddress = Forms!myform!CompanyAddress
.Update
End With

With rstComp2
.AddNew
!CompanyName = Forms!myform!CompanyName
.Update
End With

ws.CommitTrans

MsgBox "Company added to 2 tables"

EndIt:
Set ws = Nothing
Set rstComp1 = Nothing
Set rstComp2 = Nothing

Exit Sub

errTrans:
MsgBox "Error cmdSetCompany_Click (" & Err.Number & "): " & Err.Description,
vbCritical
ws.Rollback
Resume EndIt

errHandler:
MsgBox "Error cmdSetCompany_Click (" & Err.Number & "): " & Err.Description,
vbCritical
Resume EndIt

End Sub
 
Another option without so much coding is to set the source for this form to
the table containing the data so that entering text will add to the table.
You can add a cancel button that could undo the enter so it doesn't get
saves. When this first form opens, have it open another form, in add entry
mode, linked to the second table with just the field for the company name.
Set the Visible property of this form to False so you don't see it. Then
have the button copy what was entered in the company name on the first form
to the field on the second form, which will automatically add it to the
table. Then move to the next new record. You'll need to put in checks so
it doesn't add anything if Company Name is black, but that should be simple.
You could even have the second form as a subform to the main form, where the
button would add to the subform.

Kelvin
 
Thanks everyone
-----Original Message-----
Another option without so much coding is to set the source for this form to
the table containing the data so that entering text will add to the table.
You can add a cancel button that could undo the enter so it doesn't get
saves. When this first form opens, have it open another form, in add entry
mode, linked to the second table with just the field for the company name.
Set the Visible property of this form to False so you don't see it. Then
have the button copy what was entered in the company name on the first form
to the field on the second form, which will automatically add it to the
table. Then move to the next new record. You'll need to put in checks so
it doesn't add anything if Company Name is black, but that should be simple.
You could even have the second form as a subform to the main form, where the
button would add to the subform.

Kelvin

& "): " &
Err.Description, & "): " &
Err.Description,


.
Click to expand...
 
Back
Top