Duplicating data to different tables

**PaulT** · Nov 12 '05, 07:58 PM

Re: Duplicating data to different tables

Export?
Why not just open a recordset on the "REPORTS", do addNew, and do the
same thing?

Even if it's in another database, use that other database's name
instead of CurrentDB()

-Paul T.

jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402040200.6fda 3ed5@posting.go ogle.com>...[color=blue]
> I know I should not be doing this, but I find it very useful. I have a
> database in Access which stores data for a small company. Sometimes we
> need to add similar information to different tables. Currently I am
> already doing something similar by copying certain records into the
> same table. The only thing that changes is one field.
>
> Please look at the code:
>
> Dim MyDb As DAO.Database, MyRs As DAO.Recordset
> Dim strCode As String
> Dim strFilter As String
> Set MyDb = CurrentDb
> Set MyRs = MyDb.OpenRecord set("APPLICATIO NS")
> MyRs.AddNew
> MyRs!SSN = Forms!APPLICATI ONS!SSN
> MyRs!FNAME = Forms!APPLICATI ONS!FNAME
> MyRs!LNAME = Forms!APPLICATI ONS!LNAME
> MyRs!RECEIVED = Forms!APPLICATI ONS!RECEIVED
> MyRs!CLOSED = Forms!APPLICATI ONS!CLOSED
> MyRs!INITIALS = Forms!APPLICATI ONS!INITIALS
> MyRs.Update
> strCode = DMax("[ID]", "Applicatio ns")
> strFilter = "(([ID] = " & strCode & ")) "
> DoCmd.ApplyFilt er , strFilter
> Me.Refresh
> Me.CODE.SetFocu s
> If IsNull([CODE]) Then
> MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
> Me!CODE.SetFocu s
> End If
>
> As you will notice there may be one field with no data. In that case
> the input box will come up and users can type the required info.
> Any help?
> As was now wandering if is possible to export the same information
> into a different table, let's say named REPORTS. The code should copy
> the data i have typed in the active form, and paste in into this other
> table.[/color]

**Paolo** · Nov 12 '05, 08:11 PM

Re: Duplicating data to different tables

Thanks. I have added the same code behing a new button of my form but
I have a debug problem with:
MyRs!SSN = Forms!APPLICATI ONS!SSN

I am working on a form named REPORTS with a record source to the table
named REPORTS and trying and only trying to copy the same records to a
different table, named APPLICATIONS. I believe there must be a problem
with the MyRs line.

Note that I am working on the same database.

Any idea? Thanks.

pthursby@spsu.e du (PaulT) wrote in message news:<814613e.0 402051339.1aeee 1c2@posting.goo gle.com>...[color=blue]
> Export?
> Why not just open a recordset on the "REPORTS", do addNew, and do the
> same thing?
>
> Even if it's in another database, use that other database's name
> instead of CurrentDB()
>
> -Paul T.
>
> jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402040200.6fda 3ed5@posting.go ogle.com>...[color=green]
> > I know I should not be doing this, but I find it very useful. I have a
> > database in Access which stores data for a small company. Sometimes we
> > need to add similar information to different tables. Currently I am
> > already doing something similar by copying certain records into the
> > same table. The only thing that changes is one field.
> >
> > Please look at the code:
> >
> > Dim MyDb As DAO.Database, MyRs As DAO.Recordset
> > Dim strCode As String
> > Dim strFilter As String
> > Set MyDb = CurrentDb
> > Set MyRs = MyDb.OpenRecord set("APPLICATIO NS")
> > MyRs.AddNew
> > MyRs!SSN = Forms!APPLICATI ONS!SSN
> > MyRs!FNAME = Forms!APPLICATI ONS!FNAME
> > MyRs!LNAME = Forms!APPLICATI ONS!LNAME
> > MyRs!RECEIVED = Forms!APPLICATI ONS!RECEIVED
> > MyRs!CLOSED = Forms!APPLICATI ONS!CLOSED
> > MyRs!INITIALS = Forms!APPLICATI ONS!INITIALS
> > MyRs.Update
> > strCode = DMax("[ID]", "Applicatio ns")
> > strFilter = "(([ID] = " & strCode & ")) "
> > DoCmd.ApplyFilt er , strFilter
> > Me.Refresh
> > Me.CODE.SetFocu s
> > If IsNull([CODE]) Then
> > MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
> > Me!CODE.SetFocu s
> > End If
> >
> > As you will notice there may be one field with no data. In that case
> > the input box will come up and users can type the required info.
> > Any help?
> > As was now wandering if is possible to export the same information
> > into a different table, let's say named REPORTS. The code should copy
> > the data i have typed in the active form, and paste in into this other
> > table.[/color][/color]

**PaulT** · Nov 12 '05, 08:13 PM

Re: Duplicating data to different tables

Does your 'RECORDS' table actually have a SSN field?

Also, make sure you don't have SSN as a number type in one place, and
text in another (like forms usually have).

-Paul T.

jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402090156.35bc 58c@posting.goo gle.com>...[color=blue]
> Thanks. I have added the same code behing a new button of my form but
> I have a debug problem with:
> MyRs!SSN = Forms!APPLICATI ONS!SSN
>
> I am working on a form named REPORTS with a record source to the table
> named REPORTS and trying and only trying to copy the same records to a
> different table, named APPLICATIONS. I believe there must be a problem
> with the MyRs line.
>
> Note that I am working on the same database.
>
> Any idea? Thanks.
>
> pthursby@spsu.e du (PaulT) wrote in message news:<814613e.0 402051339.1aeee 1c2@posting.goo gle.com>...[color=green]
> > Export?
> > Why not just open a recordset on the "REPORTS", do addNew, and do the
> > same thing?
> >
> > Even if it's in another database, use that other database's name
> > instead of CurrentDB()
> >
> > -Paul T.
> >
> > jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402040200.6fda 3ed5@posting.go ogle.com>...[color=darkred]
> > > I know I should not be doing this, but I find it very useful. I have a
> > > database in Access which stores data for a small company. Sometimes we
> > > need to add similar information to different tables. Currently I am
> > > already doing something similar by copying certain records into the
> > > same table. The only thing that changes is one field.
> > >
> > > Please look at the code:
> > >
> > > Dim MyDb As DAO.Database, MyRs As DAO.Recordset
> > > Dim strCode As String
> > > Dim strFilter As String
> > > Set MyDb = CurrentDb
> > > Set MyRs = MyDb.OpenRecord set("APPLICATIO NS")
> > > MyRs.AddNew
> > > MyRs!SSN = Forms!APPLICATI ONS!SSN
> > > MyRs!FNAME = Forms!APPLICATI ONS!FNAME
> > > MyRs!LNAME = Forms!APPLICATI ONS!LNAME
> > > MyRs!RECEIVED = Forms!APPLICATI ONS!RECEIVED
> > > MyRs!CLOSED = Forms!APPLICATI ONS!CLOSED
> > > MyRs!INITIALS = Forms!APPLICATI ONS!INITIALS
> > > MyRs.Update
> > > strCode = DMax("[ID]", "Applicatio ns")
> > > strFilter = "(([ID] = " & strCode & ")) "
> > > DoCmd.ApplyFilt er , strFilter
> > > Me.Refresh
> > > Me.CODE.SetFocu s
> > > If IsNull([CODE]) Then
> > > MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
> > > Me!CODE.SetFocu s
> > > End If
> > >
> > > As you will notice there may be one field with no data. In that case
> > > the input box will come up and users can type the required info.
> > > Any help?
> > > As was now wandering if is possible to export the same information
> > > into a different table, let's say named REPORTS. The code should copy
> > > the data i have typed in the active form, and paste in into this other
> > > table.[/color][/color][/color]

**Paolo** · Nov 12 '05, 08:17 PM

Re: Duplicating data to different tables

Yes, both tables have exactly the same fields. I still don't undertand
where is the problem.

pthursby@spsu.e du (PaulT) wrote in message news:<814613e.0 402091851.9757f c3@posting.goog le.com>...[color=blue]
> Does your 'RECORDS' table actually have a SSN field?
>
> Also, make sure you don't have SSN as a number type in one place, and
> text in another (like forms usually have).
>
> -Paul T.
>
>
> jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402090156.35bc 58c@posting.goo gle.com>...[color=green]
> > Thanks. I have added the same code behing a new button of my form but
> > I have a debug problem with:
> > MyRs!SSN = Forms!APPLICATI ONS!SSN
> >
> > I am working on a form named REPORTS with a record source to the table
> > named REPORTS and trying and only trying to copy the same records to a
> > different table, named APPLICATIONS. I believe there must be a problem
> > with the MyRs line.
> >
> > Note that I am working on the same database.
> >
> > Any idea? Thanks.
> >
> > pthursby@spsu.e du (PaulT) wrote in message news:<814613e.0 402051339.1aeee 1c2@posting.goo gle.com>...[color=darkred]
> > > Export?
> > > Why not just open a recordset on the "REPORTS", do addNew, and do the
> > > same thing?
> > >
> > > Even if it's in another database, use that other database's name
> > > instead of CurrentDB()
> > >
> > > -Paul T.
> > >
> > > jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402040200.6fda 3ed5@posting.go ogle.com>...
> > > > I know I should not be doing this, but I find it very useful. I have a
> > > > database in Access which stores data for a small company. Sometimes we
> > > > need to add similar information to different tables. Currently I am
> > > > already doing something similar by copying certain records into the
> > > > same table. The only thing that changes is one field.
> > > >
> > > > Please look at the code:
> > > >
> > > > Dim MyDb As DAO.Database, MyRs As DAO.Recordset
> > > > Dim strCode As String
> > > > Dim strFilter As String
> > > > Set MyDb = CurrentDb
> > > > Set MyRs = MyDb.OpenRecord set("APPLICATIO NS")
> > > > MyRs.AddNew
> > > > MyRs!SSN = Forms!APPLICATI ONS!SSN
> > > > MyRs!FNAME = Forms!APPLICATI ONS!FNAME
> > > > MyRs!LNAME = Forms!APPLICATI ONS!LNAME
> > > > MyRs!RECEIVED = Forms!APPLICATI ONS!RECEIVED
> > > > MyRs!CLOSED = Forms!APPLICATI ONS!CLOSED
> > > > MyRs!INITIALS = Forms!APPLICATI ONS!INITIALS
> > > > MyRs.Update
> > > > strCode = DMax("[ID]", "Applicatio ns")
> > > > strFilter = "(([ID] = " & strCode & ")) "
> > > > DoCmd.ApplyFilt er , strFilter
> > > > Me.Refresh
> > > > Me.CODE.SetFocu s
> > > > If IsNull([CODE]) Then
> > > > MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
> > > > Me!CODE.SetFocu s
> > > > End If
> > > >
> > > > As you will notice there may be one field with no data. In that case
> > > > the input box will come up and users can type the required info.
> > > > Any help?
> > > > As was now wandering if is possible to export the same information
> > > > into a different table, let's say named REPORTS. The code should copy
> > > > the data i have typed in the active form, and paste in into this other
> > > > table.[/color][/color][/color]

**PaulT** · Nov 12 '05, 08:22 PM

Re: Duplicating data to different tables

Are you certain the datatypes are the same??

-Paul

jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402110507.60dc 7f7e@posting.go ogle.com>...[color=blue]
> Yes, both tables have exactly the same fields. I still don't undertand
> where is the problem.
>
>
> pthursby@spsu.e du (PaulT) wrote in message news:<814613e.0 402091851.9757f c3@posting.goog le.com>...[color=green]
> > Does your 'RECORDS' table actually have a SSN field?
> >
> > Also, make sure you don't have SSN as a number type in one place, and
> > text in another (like forms usually have).
> >
> > -Paul T.
> >
> >
> > jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402090156.35bc 58c@posting.goo gle.com>...[color=darkred]
> > > Thanks. I have added the same code behing a new button of my form but
> > > I have a debug problem with:
> > > MyRs!SSN = Forms!APPLICATI ONS!SSN
> > >
> > > I am working on a form named REPORTS with a record source to the table
> > > named REPORTS and trying and only trying to copy the same records to a
> > > different table, named APPLICATIONS. I believe there must be a problem
> > > with the MyRs line.
> > >
> > > Note that I am working on the same database.
> > >
> > > Any idea? Thanks.
> > >
> > > pthursby@spsu.e du (PaulT) wrote in message news:<814613e.0 402051339.1aeee 1c2@posting.goo gle.com>...
> > > > Export?
> > > > Why not just open a recordset on the "REPORTS", do addNew, and do the
> > > > same thing?
> > > >
> > > > Even if it's in another database, use that other database's name
> > > > instead of CurrentDB()
> > > >
> > > > -Paul T.
> > > >
> > > > jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402040200.6fda 3ed5@posting.go ogle.com>...
> > > > > I know I should not be doing this, but I find it very useful. I have a
> > > > > database in Access which stores data for a small company. Sometimes we
> > > > > need to add similar information to different tables. Currently I am
> > > > > already doing something similar by copying certain records into the
> > > > > same table. The only thing that changes is one field.
> > > > >
> > > > > Please look at the code:
> > > > >
> > > > > Dim MyDb As DAO.Database, MyRs As DAO.Recordset
> > > > > Dim strCode As String
> > > > > Dim strFilter As String
> > > > > Set MyDb = CurrentDb
> > > > > Set MyRs = MyDb.OpenRecord set("APPLICATIO NS")
> > > > > MyRs.AddNew
> > > > > MyRs!SSN = Forms!APPLICATI ONS!SSN
> > > > > MyRs!FNAME = Forms!APPLICATI ONS!FNAME
> > > > > MyRs!LNAME = Forms!APPLICATI ONS!LNAME
> > > > > MyRs!RECEIVED = Forms!APPLICATI ONS!RECEIVED
> > > > > MyRs!CLOSED = Forms!APPLICATI ONS!CLOSED
> > > > > MyRs!INITIALS = Forms!APPLICATI ONS!INITIALS
> > > > > MyRs.Update
> > > > > strCode = DMax("[ID]", "Applicatio ns")
> > > > > strFilter = "(([ID] = " & strCode & ")) "
> > > > > DoCmd.ApplyFilt er , strFilter
> > > > > Me.Refresh
> > > > > Me.CODE.SetFocu s
> > > > > If IsNull([CODE]) Then
> > > > > MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
> > > > > Me!CODE.SetFocu s
> > > > > End If
> > > > >
> > > > > As you will notice there may be one field with no data. In that case
> > > > > the input box will come up and users can type the required info.
> > > > > Any help?
> > > > > As was now wandering if is possible to export the same information
> > > > > into a different table, let's say named REPORTS. The code should copy
> > > > > the data i have typed in the active form, and paste in into this other
> > > > > table.[/color][/color][/color]

**PaulT** · Nov 12 '05, 08:22 PM

Re: Duplicating data to different tables

Also, what is the error message when debug 'hits' that line?

-Paul

jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402110507.60dc 7f7e@posting.go ogle.com>...[color=blue]
> Yes, both tables have exactly the same fields. I still don't undertand
> where is the problem.
>
>
> pthursby@spsu.e du (PaulT) wrote in message news:<814613e.0 402091851.9757f c3@posting.goog le.com>...[color=green]
> > Does your 'RECORDS' table actually have a SSN field?
> >
> > Also, make sure you don't have SSN as a number type in one place, and
> > text in another (like forms usually have).
> >
> > -Paul T.
> >
> >
> > jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402090156.35bc 58c@posting.goo gle.com>...[color=darkred]
> > > Thanks. I have added the same code behing a new button of my form but
> > > I have a debug problem with:
> > > MyRs!SSN = Forms!APPLICATI ONS!SSN
> > >
> > > I am working on a form named REPORTS with a record source to the table
> > > named REPORTS and trying and only trying to copy the same records to a
> > > different table, named APPLICATIONS. I believe there must be a problem
> > > with the MyRs line.
> > >
> > > Note that I am working on the same database.
> > >
> > > Any idea? Thanks.
> > >
> > > pthursby@spsu.e du (PaulT) wrote in message news:<814613e.0 402051339.1aeee 1c2@posting.goo gle.com>...
> > > > Export?
> > > > Why not just open a recordset on the "REPORTS", do addNew, and do the
> > > > same thing?
> > > >
> > > > Even if it's in another database, use that other database's name
> > > > instead of CurrentDB()
> > > >
> > > > -Paul T.
> > > >
> > > > jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402040200.6fda 3ed5@posting.go ogle.com>...
> > > > > I know I should not be doing this, but I find it very useful. I have a
> > > > > database in Access which stores data for a small company. Sometimes we
> > > > > need to add similar information to different tables. Currently I am
> > > > > already doing something similar by copying certain records into the
> > > > > same table. The only thing that changes is one field.
> > > > >
> > > > > Please look at the code:
> > > > >
> > > > > Dim MyDb As DAO.Database, MyRs As DAO.Recordset
> > > > > Dim strCode As String
> > > > > Dim strFilter As String
> > > > > Set MyDb = CurrentDb
> > > > > Set MyRs = MyDb.OpenRecord set("APPLICATIO NS")
> > > > > MyRs.AddNew
> > > > > MyRs!SSN = Forms!APPLICATI ONS!SSN
> > > > > MyRs!FNAME = Forms!APPLICATI ONS!FNAME
> > > > > MyRs!LNAME = Forms!APPLICATI ONS!LNAME
> > > > > MyRs!RECEIVED = Forms!APPLICATI ONS!RECEIVED
> > > > > MyRs!CLOSED = Forms!APPLICATI ONS!CLOSED
> > > > > MyRs!INITIALS = Forms!APPLICATI ONS!INITIALS
> > > > > MyRs.Update
> > > > > strCode = DMax("[ID]", "Applicatio ns")
> > > > > strFilter = "(([ID] = " & strCode & ")) "
> > > > > DoCmd.ApplyFilt er , strFilter
> > > > > Me.Refresh
> > > > > Me.CODE.SetFocu s
> > > > > If IsNull([CODE]) Then
> > > > > MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
> > > > > Me!CODE.SetFocu s
> > > > > End If
> > > > >
> > > > > As you will notice there may be one field with no data. In that case
> > > > > the input box will come up and users can type the required info.
> > > > > Any help?
> > > > > As was now wandering if is possible to export the same information
> > > > > into a different table, let's say named REPORTS. The code should copy
> > > > > the data i have typed in the active form, and paste in into this other
> > > > > table.[/color][/color][/color]

**Paolo** · Nov 12 '05, 08:24 PM

Re: Duplicating data to different tables

yes all fields and properties are the same in both tables.
Is it because I am trying to copy the current records on the opened form?

pthursby@spsu.e du (PaulT) wrote in message news:<814613e.0 402130608.6b2ec 65a@posting.goo gle.com>...[color=blue]
> Also, what is the error message when debug 'hits' that line?
>
> -Paul
>
> jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402110507.60dc 7f7e@posting.go ogle.com>...[color=green]
> > Yes, both tables have exactly the same fields. I still don't undertand
> > where is the problem.
> >
> >
> > pthursby@spsu.e du (PaulT) wrote in message news:<814613e.0 402091851.9757f c3@posting.goog le.com>...[color=darkred]
> > > Does your 'RECORDS' table actually have a SSN field?
> > >
> > > Also, make sure you don't have SSN as a number type in one place, and
> > > text in another (like forms usually have).
> > >
> > > -Paul T.
> > >
> > >
> > > jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402090156.35bc 58c@posting.goo gle.com>...
> > > > Thanks. I have added the same code behing a new button of my form but
> > > > I have a debug problem with:
> > > > MyRs!SSN = Forms!APPLICATI ONS!SSN
> > > >
> > > > I am working on a form named REPORTS with a record source to the table
> > > > named REPORTS and trying and only trying to copy the same records to a
> > > > different table, named APPLICATIONS. I believe there must be a problem
> > > > with the MyRs line.
> > > >
> > > > Note that I am working on the same database.
> > > >
> > > > Any idea? Thanks.
> > > >
> > > > pthursby@spsu.e du (PaulT) wrote in message news:<814613e.0 402051339.1aeee 1c2@posting.goo gle.com>...
> > > > > Export?
> > > > > Why not just open a recordset on the "REPORTS", do addNew, and do the
> > > > > same thing?
> > > > >
> > > > > Even if it's in another database, use that other database's name
> > > > > instead of CurrentDB()
> > > > >
> > > > > -Paul T.
> > > > >
> > > > > jprma@tin.it (Paolo) wrote in message news:<9f41a860. 0402040200.6fda 3ed5@posting.go ogle.com>...
> > > > > > I know I should not be doing this, but I find it very useful. I have a
> > > > > > database in Access which stores data for a small company. Sometimes we
> > > > > > need to add similar information to different tables. Currently I am
> > > > > > already doing something similar by copying certain records into the
> > > > > > same table. The only thing that changes is one field.
> > > > > >
> > > > > > Please look at the code:
> > > > > >
> > > > > > Dim MyDb As DAO.Database, MyRs As DAO.Recordset
> > > > > > Dim strCode As String
> > > > > > Dim strFilter As String
> > > > > > Set MyDb = CurrentDb
> > > > > > Set MyRs = MyDb.OpenRecord set("APPLICATIO NS")
> > > > > > MyRs.AddNew
> > > > > > MyRs!SSN = Forms!APPLICATI ONS!SSN
> > > > > > MyRs!FNAME = Forms!APPLICATI ONS!FNAME
> > > > > > MyRs!LNAME = Forms!APPLICATI ONS!LNAME
> > > > > > MyRs!RECEIVED = Forms!APPLICATI ONS!RECEIVED
> > > > > > MyRs!CLOSED = Forms!APPLICATI ONS!CLOSED
> > > > > > MyRs!INITIALS = Forms!APPLICATI ONS!INITIALS
> > > > > > MyRs.Update
> > > > > > strCode = DMax("[ID]", "Applicatio ns")
> > > > > > strFilter = "(([ID] = " & strCode & ")) "
> > > > > > DoCmd.ApplyFilt er , strFilter
> > > > > > Me.Refresh
> > > > > > Me.CODE.SetFocu s
> > > > > > If IsNull([CODE]) Then
> > > > > > MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
> > > > > > Me!CODE.SetFocu s
> > > > > > End If
> > > > > >
> > > > > > As you will notice there may be one field with no data. In that case
> > > > > > the input box will come up and users can type the required info.
> > > > > > Any help?
> > > > > > As was now wandering if is possible to export the same information
> > > > > > into a different table, let's say named REPORTS. The code should copy
> > > > > > the data i have typed in the active form, and paste in into this other
> > > > > > table.[/color][/color][/color]

Duplicating data to different tables

Duplicating data to different tables

Comment

Comment

Comment

Comment

Comment

Comment

Comment