Inserting Data from form into table

  • Thread starter Thread starter jrp444
  • Start date Start date
J

jrp444

I am trying to populate a table from a form that I am pulling information in
from other tables. I am trying to get it when I enter the date that it will
insert the information into the table. I am getting an errror (There was an
error adding the record.3001, Arguments are of the wrong tyype, are out of
acceptable range, or are in conflict with one another). I have check the
spell and the names in my module and every thing seems to be ok. Could
somebody please help me firgure out why I am getting this error. Below is my
Module:
Private Sub dated_AfterUpdate()

Dim sql As String
Dim rsAdd As New ADODB.Recordset

On Error GoTo DbError
'Assign updatable cursor and lock type properties.

rsAdd.CursorType = adOpenDynamic
rsAdd.LockType = adLockOptimistic
'Opening recordset object

rsAdd.Open "tbl_Issued", remoteConnection, , , adCmdTable

With rsAdd
..AddNew

!ID = Me.ID
!Commodity = Me.txt_comm
!Trailer_no = Me.txt_Trl_no
!county = Me.txt_county
!driver = Me.txt_driver
!Web_EOC = Me.txt_eoc
!D_Date = Me.dated



..Update
..Close
End With
MsgBox "Record Added.", vbInformation

'‘Close the form-level Recordset object and refresh
'‘it to include the newly updated row.
'rsAdjustors.Close
'SetRecordset

Exit Sub

DbError:

MsgBox "There was an error adding the record." _
& Err.Number & ", " & Err.Description
End Sub


Thanks!
 
Could somebody please help me firgure out why I am getting this error.

I think that problem is that all control values are stored as variants
and when you have tabel where all fields have defined data type,
before inserting value you have to convert it to proper datatype. Try
to debug your your code and see where the error occours, which value
causes error. Then try to convert this value to proper datatype.

Regards,
Sebastian
 
From your description, it sounds like you are trying to:
* gather information from some tables, and
* duplicate that information in another table

If so, why?! You might do something like that if you were using Excel...
but Access is NOT a spreadsheet.

If you'll provide a more specific description of what data you have and what
business need you are trying to solve, folks here may be able to offer more
specific suggestions.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
You are probably right except I do not know how to make the debug mode work
when I am in the form. It will not run the module in module mode. Also, how
to change the data type in the module.
 
Which line does the error occur??

By any chance is "ID" an autonumber? If it is, comment out that line and try
the code again.

HTH
 
I have a form that indiviuals will fill in that uses combo boxes and other
data that is needed and I want to take this information an put it into a
table that I can use to run other macros and reports.
 
I found that the following line gives me this error:

rsAdd.Open "[Issued]", remoteConnection, , , adCmdTable

The Issued is the table that I want to put the information into.
 
Bear in mind that you don't need to have a table with data laid out for your
report. One of the many nice features of a relational database like Access
is that you can use a query to join related tables, collecting the data you
need, then basing a report on that query, rather than on a table.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
I do know that but I am trying to do this from a form so others can fill it
in and see what they have done.
 
No it is not. But thanks for the suggestion.

Steve Sanford said:
Which line does the error occur??

By any chance is "ID" an autonumber? If it is, comment out that line and try
the code again.

HTH
Click to expand...
 
I don't see where you have defined the connection string "remoteConnection" .

The Syntax for opening a recordset is :

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

where ActiveConnection is either a Variant that evaluates to a valid
Connection object variable name, or a String that contains ConnectionString
parameters


Here is an snippet of code from Help:

Dim Cnxn As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnxn As String
Dim strSQLEmployees As String
Dim varDate As Variant

' Open connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

' Open employee table
Set rstEmployees = New ADODB.Recordset
strSQLEmployees = "employee"
rstEmployees.Open strSQLEmployees, Cnxn, adOpenKeyset, adLockOptimistic,
adCmdTable
-----<snip>-----


FWIW, I think it would be much easier (IMO) to do this using DAO.


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


jrp444 said:
I found that the following line gives me this error:

rsAdd.Open "[Issued]", remoteConnection, , , adCmdTable

The Issued is the table that I want to put the information into.

sweet_dreams said:
I think that problem is that all control values are stored as variants
and when you have tabel where all fields have defined data type,
before inserting value you have to convert it to proper datatype. Try
to debug your your code and see where the error occours, which value
causes error. Then try to convert this value to proper datatype.

Regards,
Sebastian

.
Click to expand...
Click to expand...
 
I have a form that indiviuals will fill in that uses combo boxes and other
data that is needed and I want to take this information an put it into a
table that I can use to run other macros and reports.
Click to expand...

Do you have some specific reason not to use a form *bound to the table*, so
Access will do all the work for you and not need any code at all?
 
Will you show me how to do it in DOA?

Steve Sanford said:
I don't see where you have defined the connection string "remoteConnection" .

The Syntax for opening a recordset is :

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

where ActiveConnection is either a Variant that evaluates to a valid
Connection object variable name, or a String that contains ConnectionString
parameters


Here is an snippet of code from Help:

Dim Cnxn As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnxn As String
Dim strSQLEmployees As String
Dim varDate As Variant

' Open connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

' Open employee table
Set rstEmployees = New ADODB.Recordset
strSQLEmployees = "employee"
rstEmployees.Open strSQLEmployees, Cnxn, adOpenKeyset, adLockOptimistic,
adCmdTable
-----<snip>-----


FWIW, I think it would be much easier (IMO) to do this using DAO.


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


jrp444 said:
I found that the following line gives me this error:

rsAdd.Open "[Issued]", remoteConnection, , , adCmdTable

The Issued is the table that I want to put the information into.

sweet_dreams said:
Could somebody please help me firgure out why I am getting this error.

I think that problem is that all control values are stored as variants
and when you have tabel where all fields have defined data type,
before inserting value you have to convert it to proper datatype. Try
to debug your your code and see where the error occours, which value
causes error. Then try to convert this value to proper datatype.

Regards,
Sebastian

.
Click to expand...
Click to expand...
Click to expand...
 
I have the same question as John Vinson:

Do you have some specific reason not to use a form *bound to the table*, so
Access will do all the work for you and not need any code at all?


But here is the DAO code: (untested)

'The next two lines should be at the top
' of every code page
'-----------------------------------------
Option Compare Database
Option Explicit
'--------------Begin Code---------------------------
Private Sub dated_AfterUpdate()
Dim db As DAO.Database
Dim rsAdd As DAO.Recordset

On Error GoTo Err_dated_AfterUpdate

Set db = CurrentDb

'Open recordset
Set rsAdd = db.OpenRecordset("tbl_Issued")

With rsAdd
.AddNew
!id = Me.id
!Commodity = Me.txt_comm
!Trailer_no = Me.txt_Trl_no
!county = Me.txt_county
!driver = Me.txt_driver
!Web_EOC = Me.txt_eoc
!D_Date = Me.dated
.Update
End With

MsgBox "Record Added.", vbInformation

Exit_dated_AfterUpdate:
rsAdd.Close
Set rsAdd = Nothing
Set db = Nothing
Exit Sub

Err_dated_AfterUpdate:
MsgBox Err.Description, vbExclamation, " Error number: " & Err.Number
Resume Exit_dated_AfterUpdate

End Sub
'--------------End Code---------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


jrp444 said:
Will you show me how to do it in DOA?

Steve Sanford said:
I don't see where you have defined the connection string "remoteConnection" .

The Syntax for opening a recordset is :

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

where ActiveConnection is either a Variant that evaluates to a valid
Connection object variable name, or a String that contains ConnectionString
parameters


Here is an snippet of code from Help:

Dim Cnxn As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnxn As String
Dim strSQLEmployees As String
Dim varDate As Variant

' Open connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

' Open employee table
Set rstEmployees = New ADODB.Recordset
strSQLEmployees = "employee"
rstEmployees.Open strSQLEmployees, Cnxn, adOpenKeyset, adLockOptimistic,
adCmdTable
-----<snip>-----


FWIW, I think it would be much easier (IMO) to do this using DAO.


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


jrp444 said:
I found that the following line gives me this error:

rsAdd.Open "[Issued]", remoteConnection, , , adCmdTable

The Issued is the table that I want to put the information into.

:

Could somebody please help me firgure out why I am getting this error.

I think that problem is that all control values are stored as variants
and when you have tabel where all fields have defined data type,
before inserting value you have to convert it to proper datatype. Try
to debug your your code and see where the error occours, which value
causes error. Then try to convert this value to proper datatype.

Regards,
Sebastian

.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top