Add selected record from form to database

[Diane]

I have created a form which prints out all new records from two tables that
were compared. The new records print out on the form. I have added a
command button beside each record that displays that particular information.
When they select the button, it opens a form that displays all of the
information for that record. I would like the user to be able to click a
button that says "add to database" and it adds all of the current displayed
information without having to type it all via a blank form.

Below is the code thus far. From all that I have been reading, I am
thinking that I am going to have to use code dealing with "recordset".

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Add New Record"

stLinkCriteria = "[ACCOUNT NUMBER]=" & "'" & Me![ACCOUNT NUMBER] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Jim Burke in Novi]

In the OnClick event of the command button that is used to add the record to
the database, you need something like this:

Dim rst as ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
.Open "TableName", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
.AddNew
![TableField1] = FormField1 ' a statement like this for each field on
the form and the corresponding field from the table
.Update
.close
End With

I always use ADO - you can also do it with DAO. If you use ADO I think you
need to add a reference (through Tools, References) to the ADO library - it
should be called Microsoft ActiveX Data Objects 2.1 Library (the version
could be different depending on what version of Access you're running). I
think this is all you'll need.

 

[Diane]

This is the code as you suggested but it continues to come up with an error:
Switchboard can't find the field "|" referred to in your expression". I feel
the reason this doesn't work is because the table that I am attempting to
save this record to is different from the table that the form is getting it's
data from.

I have a query that looks at 2 tables, (one that is run later than the
other), and extracts the new records from the later table. I want to be
click on this command button for whichever record that I want to add and add
it to table number one.

Dim rst As New ADODB.Recordset

Set rst = New ADODB.Recordset

'stDocName = "Add New Record"

'stLinkCriteria = "[Account Number]=" & "'" & Me![Account Number] & "'"
'DoCmd.OpenForm stDocName, , , stLinkCriteria
With rst
.Open "Account_Geography", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
.AddNew
![Account_Geography.ACCOUNT NUMBER] = [New Billing Accts.Account Number]
![Account_Geography.MOBILE NUMBER] = [New Billing Accts.Mobile Number]
![Account_Geography.Current Plan Desc] = [New Billing Accts.Current Plan
Desc]
![Account_Geography.Current Plan Code] = [New Billing Accts.Current Plan
Code]
.Update
.Close

In the OnClick event of the command button that is used to add the record to
the database, you need something like this:

Dim rst as ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
.Open "TableName", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
.AddNew
![TableField1] = FormField1 ' a statement like this for each field on
the form and the corresponding field from the table
.Update
.close
End With

I always use ADO - you can also do it with DAO. If you use ADO I think you
need to add a reference (through Tools, References) to the ADO library - it
should be called Microsoft ActiveX Data Objects 2.1 Library (the version
could be different depending on what version of Access you're running). I
think this is all you'll need.

I have created a form which prints out all new records from two tables that
were compared. The new records print out on the form. I have added a
command button beside each record that displays that particular information.
When they select the button, it opens a form that displays all of the
information for that record. I would like the user to be able to click a
button that says "add to database" and it adds all of the current displayed
information without having to type it all via a blank form.

Below is the code thus far. From all that I have been reading, I am
thinking that I am going to have to use code dealing with "recordset".

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Add New Record"

stLinkCriteria = "[ACCOUNT NUMBER]=" & "'" & Me![ACCOUNT NUMBER] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria