Inserting Data from form into table

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!
0
Utf
3/18/2010 4:53:01 PM
access.forms 6864 articles. 2 followers. Follow

13 Replies
969 Views

Similar Articles

[PageSpeed] 45

> 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

0
sweet_dreams
3/18/2010 6:22:43 PM
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.


"jrp444" <jrp444@discussions.microsoft.com> wrote in message 
news:5A56F593-A1DA-4469-AAE1-5E1726452BDD@microsoft.com...
>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! 


0
Jeff
3/18/2010 7:29:39 PM
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.

"sweet_dreams" wrote:

> > 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
> 
> .
> 
0
Utf
3/18/2010 7:59:01 PM
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
-- 
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"jrp444" wrote:

> 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!
0
Utf
3/18/2010 8:03:02 PM
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. 


"Jeff Boyce" wrote:

> 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.
> 
> 
> "jrp444" <jrp444@discussions.microsoft.com> wrote in message 
> news:5A56F593-A1DA-4469-AAE1-5E1726452BDD@microsoft.com...
> >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! 
> 
> 
> .
> 
0
Utf
3/18/2010 8:32:06 PM
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" wrote:

> > 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
> 
> .
> 
0
Utf
3/18/2010 8:34:01 PM
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.

"jrp444" <jrp444@discussions.microsoft.com> wrote in message 
news:764F250A-25AE-45C1-A125-9588EDA6BCA8@microsoft.com...
>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.
>
>
> "Jeff Boyce" wrote:
>
>> 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.
>>
>>
>> "jrp444" <jrp444@discussions.microsoft.com> wrote in message
>> news:5A56F593-A1DA-4469-AAE1-5E1726452BDD@microsoft.com...
>> >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!
>>
>>
>> .
>> 


0
Jeff
3/18/2010 8:51:37 PM
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.  

"Jeff Boyce" wrote:

> 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.
> 
> "jrp444" <jrp444@discussions.microsoft.com> wrote in message 
> news:764F250A-25AE-45C1-A125-9588EDA6BCA8@microsoft.com...
> >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.
> >
> >
> > "Jeff Boyce" wrote:
> >
> >> 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.
> >>
> >>
> >> "jrp444" <jrp444@discussions.microsoft.com> wrote in message
> >> news:5A56F593-A1DA-4469-AAE1-5E1726452BDD@microsoft.com...
> >> >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!
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
3/18/2010 9:32:01 PM
No it is not.  But thanks for the suggestion.

"Steve Sanford" wrote:

> 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
> -- 
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
> 
> 
> "jrp444" wrote:
> 
> > 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!
0
Utf
3/18/2010 9:34:02 PM
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" wrote:

> 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" wrote:
> 
> > > 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
> > 
> > .
> > 
0
Utf
3/18/2010 10:55:01 PM
On Thu, 18 Mar 2010 13:32:06 -0700, jrp444 <jrp444@discussions.microsoft.com>
wrote:

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

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?
-- 

             John W. Vinson [MVP]
0
John
3/19/2010 1:55:55 AM
Will you show me how to do it in DOA?

"Steve Sanford" wrote:

> 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" wrote:
> 
> > 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" wrote:
> > 
> > > > 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
> > > 
> > > .
> > > 
0
Utf
3/19/2010 5:23:04 PM
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" wrote:

> Will you show me how to do it in DOA?
> 
> "Steve Sanford" wrote:
> 
> > 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" wrote:
> > 
> > > 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" wrote:
> > > 
> > > > > 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
> > > > 
> > > > .
> > > > 
0
Utf
3/20/2010 8:50:01 PM
Reply:

Similar Artilces:

standard form
is it possible to have excel print out a standardised form from the following format automatically? If I have data in cells A1,B1,C1,D1 (which I want to appear in a standard position on a standard form), can I use a data entry in say E1 to then trigger excel to automatically print out a standardised form using all (or some) of the data entered in A1 - E1 -- thanks in advance sammy Sammy, Sounds like a WORD mailmerge to me using your spreadsheet as the data. HTH "sammy" wrote: > is it possible to have excel print out a standardised form from the following > format a...

Pulling data from the previous sheet
Is it possible to pull data from the previous sheet in a formula without naming the sheet? Every sheet has the same formats but each sheet represents a day of the month. These sheet names will change every month. Example: I have the date in cell s12 and I want to add 1 day on each sheet. In cell d15 i have a number that I want to carry over to the next sheet in cell e15. -- Thanks For Your Help Ed Davis I think you'll need a custom function for this: Function PrevSheet() PrevSheet = Sheets(ActiveSheet.Index - 1).Name & "!" End Function Use as below: =INDIREC...

Finding data files
I just installed Windows XP but my Money 2001 now doesn't show any of my data. I saved everthing on my hard drive before I installed XP, but I did not back up my Money data. Isn't there a file that contains my current information? What is the name of the file Money saves its information to? Thank You!! See http://umpmfaq.info/faqdb.php?q=109. "Dale" <dalevan@iserv.net> wrote in message news:2a57301c466e7$63e929c0$a501280a@phx.gbl... > I just installed Windows XP but my Money 2001 now doesn't > show any of my data. I saved everthing on my hard d...

Form becomes disabled/text invisible
Hi, I have a class that inherits from Form. It's pretty simple; it displays text. I have multiple of these, which get constructed dynamically. Unfortunately, if I click on one of these Forms, it becomes disabled, and the text inside of it is no longer visible (the scroll bar is no longer visible, etc.). In fact, it causes the rest of the windows to become disabled as well. It's as if the application has become too busy to maintain the Form's visibility. The application is multi-threaded. What's going on? Thanks. Julie Julie skrev: > Hi, > I have a cl...

Disable standard form buttons
Hi How could I disable (=hide) standard buttons like "look up address" and "recalculate" in QuoteForm or "send mail" in AccountForm ? Thanks Sebastiano Have not seen this in the standard package...you may be able to modify the underlying source code but it would be unsupported "Sebastiano Castrini" <castrini(remove)@libero.it> wrote in message news:%239HVo7zMEHA.2976@TK2MSFTNGP10.phx.gbl... > Hi > > How could I disable (=hide) standard buttons like "look up address" and > "recalculate" in QuoteForm or "se...

EXE change in data modified
Hi Guys Have a question regarding GP. What can cause a change in the "modified date" to the dynamics.exe file? In theory, something would need to change the file. My best guess would be a service pack would change the date. -- www.fmtconsultants.com "cruesta@gmail.com" wrote: > Hi Guys > > Have a question regarding GP. What can cause a change in the "modified > date" to the dynamics.exe file? > > For GP 9.0 SP 1 the Dynamics.exe modified date is 5/16/2006 which looks to be the date the Service Pack was created (as opposed to the da...

Word Form Checkboxes won't print
I have created a form in Word with checkboxes. But when I print it, instead of the boxes, I get {FORMCHECKBOX} in each location where a box should appear. What am I doing wrong? sanlass wrote: > I have created a form in Word with checkboxes. But when I print it, instead > of the boxes, I get {FORMCHECKBOX} in each location where a box should > appear. What am I doing wrong? Press Alt-F9 to toggle field display. But note that you probably won't be able to do this while viewing a protected form, so close the form, press Alt+F9, then open the form again. -- ...

Insert file name in header without showing extension
I know how to get the filename into a header/footer. Is there any way to get the filename in the header/footer WITHOUT showing the filename extension? 1) Click on 'My Computer' > 'Tools' > 'Folder Options...' > View tab and check the option "Hide extensions for known files". OR: 2) If you don't want to change the way your computer displays file names - make use of a Macro: ------------------------------------------------------- Sub Header_FileName_No_Extension() On Error Resume Next LFN = InStrRev(ActiveWorkbook.Name, "...

Table Command function
Can you please explain the table command function under "data" in excel, with a simple example because I don't know how one can use this function in Microsoft Excel. Thank you. Bill Here are a couple of examples http://www.dicks-blog.com/excel/2004/09/data_table_basi.html http://www.dicks-blog.com/excel/2004/10/retail_pricing.html -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Bill T." <Bill T.@discussions.microsoft.com> wrote in message news:6F1E220D-3FED-451B-85B6-8514634C3F6A@microsoft.com... > Can you please explai...

access tables and relationship
i have 3 tables with primary key of Car_registrationNumber in tblstock C_DrivingLicenseNumber in tblcustomer S_ID in tblstaff my linking table which connect all three table in one to many relationship is tblhire my problem is this] in my tblhire i have three fields which are the three primary keys at the top when i click on the C_DrivingLicenseNumber primary key it shows me the customer driving licnse number and first name and surname but when i click on the Car_registrationNumber it only shows me the Car registration Number not the car make and model why to be more clear hav...

extrace a data record from large worksheet.
I would appreciate it if someone could give a bit of direction. I have a large spreadsheet - 139 cols by 170 rows - and I need to extract data from certain columns in certain rows. I'm thinking that I need to 'lookup' each label: about seven of them, and return for each of those lookup data from all 139 columns. ie: blue, red, purple, orange as row labels; getting date from jan2, jan3, jan4, jan5 ... columns. this date would go into a data list, under the columns: blue, red etc. I can do lookups, have some experience with arrays, and have worked with simple loops. I'm ju...

Data bar conditional formatting based on another cell
I'm using Excel 2007 and have my setup as below: =20 Column A contains list of names Column K contains percentage gains for the names in column A. The percentages vary between -100% and +100% =20 I want to apply conditional formatting as below: Have a 'data bar' in column A with GREEN color if=20 the corresponding value in K is positive. Have a 'data bar' in column A with RED color if=20 the corresponding value in K is negative. =20 There is another sheet where column K contains numbers=20 instead of percentages. I would like to format column A=20 in the same way here as we...

Add two Columns to a table from another table
It has been a couple of months since I have worked with access and I can't remeber the right way to put the critera. I want to add two columns from table A to table B where the machine name is equal to each other. I know I have to use an append query I just can't figuer out how to set the critera to place the correct data in the right place. Both tables have been imported and both have a column called machine name. I want to add two columns from table A to table B where the machine names are the same. Please help me out. Thanks very much, Greg It isn't clear whether you ar...

Data Migration from CRM 1.2 database to CDF database
Hello After talking to Microsoft It was best to reinstall CRM 1.2 and do a data migration. I have reinstalled CRM 1.2 and have used the datamigration Framework to create the CDF database. All the CDF now needs is the data to be imported. 1) Is there a tool or something that I can use to import the old 1.2 data into the CDF 2)When I try using the DTS it cannot convert between datatypes. Does the CDF have to have exactly the datatype that it was created with or can I change the datatype to suit the old 1.2 data? Thanks Just and update on the situation In the CDF database the pri...

Inserting paragraphs
In 2003 I could store several paragraphs that I used regularily and the system used to reconise if I was typing a stored paragraph and offer to insert it. Can I do this in 2007 AutoText entries can still be used in Word 2007, but AutoComplete is no longer available. You need to type the first few characters of the AutoText entry name and press F3 to insert the entry. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Paul Morris" <Paul Morris@discussions.microsoft.com> wrote in message news:93CA07FB-D4...

capturing data
hi frds, am beginner to vc++,am using vc++ to develop rs232 application,in my application i want to know how to capture a data or save data in receiveing side.plz help me this. in my application i have given list box to receive a data & to send a data i have given edit box,now i want capture or save a data in receive data. thanks & regards, ananda Read some of these articles. http://www.codeproject.com/info/search.asp?cats=2&searchkw=serial+communication&Submit1=Search&author=&sd=15+Nov+1999&ed=2+Mar+2007 You have to use CreateFile to open the port. ...

how to split data into columns and arrange the resulting data
I have data in cells A2, A3 and A4 as below: 1, 2, 4, 5, 7, 8 1, 2, 3, 4 2, 3, 4 I want to split into columns (which I did using Data > Text to Columns option). But I also want the data to "automatically" fall under appropriate heading (1, 2, 3, 4, 5, 6, 7, 8) which are in respecive cells A1:H1. Thank you in advance How about an alternative: With your data in A2:Axx, put this formula in B2: =","&SUBSTITUTE(A2," ","")&"," Then B2 will look like: ,1,2,4,5,7,8, Then put this in C2 and drag across as far as you need. =IF...

Local Data Queries Stored? Client or Server?
I have 3 complex queries I need to deploy to 35 users. After creating the 5th one from scratch, I figured the data's gotta be stored somewhere! I looked around in the registry and couldn't find any clues. Where are the local data queries stored? I'm thinking I could copy the queries (update for any unique GUIDs, of course). Thanks. It is stored on server -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "Avogadro" wrote: > I have 3 complex queries I need to ...

Form Load efficiency
When an SSTab control resides on the form is it more efficient to: 1) Load all controls during Form Load or 2) Load only the controls for the tab showing on the SSTab and then dynamically load any other SSTab when selected. David wrote: > When an SSTab control resides on the form is it more efficient to: > > 1) Load all controls during Form Load > > or > > 2) Load only the controls for the tab showing on the SSTab and > then dynamically load any other SSTab when selected. You will have to try it and see. You can make thought-experi...

Timer form event
I would like to trigger this event once a day at the same time. Any suggestions. Thanks, Stanley ...

Data Import Utility
Hello All, I need RMS data import utility.Can any1 guide me OR give me the utility.i have thousands of Lookup codes in Excel & notepad format and i need to import all of them in RMS database.I can use DTS in Sql Server but that is not feasible. Thank & Regards Junaid- This is a multi-part message in MIME format. ------=_NextPart_000_0382_01C6A431.25D77DB0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Junaid, I gave up on the SO Import Utility. Just didn't have the control we = needed. We use EMS SQL Import; http://www.sql...

order enty form
help here is my problem i have a db that tracks orders and i need a form to enter the order number and part number and quantirty. i will need to enter allot of orders at one time so i want to use a comtinuous form to enter the info into the orders table what i want to do is enter the job # and the part number and auto fill the discription and size. i have a part numbert tabel with fields part number, discription size there are too many to use a combo box. i have an order table with fileds job number, part number , discription , qantity, start time end tine, efficentcy, total time , down time, ...

How do I automatically plot different ranges of data in different.
I need to chart parameters that are a function of three variables in the 2002 version of Excel. I have used the x and y axis to represent 2 variables on an 'xy chart', but require an automatic method to represent the value of the third variable. I have tried the 'bubble-plot', but cannot get sufficient resolution between the data. Is their any way that I may plot the third variable in different colours/shades, that automatically changes for different ranges of the the third variable? THis might help you: http://www.peltiertech.com/Excel/Charts/format.html#CondChart &q...

Copying and Pasting Excel Tables into Word
We do our financials in Word, and copy and paste special as enhanced metafiles the tables that we do in Excel. This works perfectly in Word 2003. In 2007 however, when we past the table and print it, the table looks different (letter spacing funny, not smooth). I have tried printing to five different printers and it still happens. I'd suggest a slightly different approach. If you insist on using enhanced metafiles, try the following: 1. Select the range that you want to copy. 2. Instead of Copy, select the dropdown under Paste>As Picture>Copy as Picture. 3...

Trouble deserializing XML data from UPS.com
I am trying to use the UPS.com online tools to develop a class libary that uses serialization to communicate with their XML APIs. When I use the class libary to generate a TimeInTransitRequest object, the XML looks like this (I substituted the user credentials): <?xml version="1.0" encoding="utf-16"?> <AccessRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xml:lang="en-US"> <AccessLicenseNumber>LIC_NUM</AccessLicenseNumber> <UserId>UserName</User...