By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,222 Members | 1,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,222 IT Pros & Developers. It's quick & easy.

Duplicating data to different tables

P: n/a
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.OpenRecordset("APPLICATIONS")
MyRs.AddNew
MyRs!SSN = Forms!APPLICATIONS!SSN
MyRs!FNAME = Forms!APPLICATIONS!FNAME
MyRs!LNAME = Forms!APPLICATIONS!LNAME
MyRs!RECEIVED = Forms!APPLICATIONS!RECEIVED
MyRs!CLOSED = Forms!APPLICATIONS!CLOSED
MyRs!INITIALS = Forms!APPLICATIONS!INITIALS
MyRs.Update
strCode = DMax("[ID]", "Applications")
strFilter = "(([ID] = " & strCode & ")) "
DoCmd.ApplyFilter , strFilter
Me.Refresh
Me.CODE.SetFocus
If IsNull([code]) Then
MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
Me!CODE.SetFocus
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.
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.

jp***@tin.it (Paolo) wrote in message news:<9f**************************@posting.google. 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.OpenRecordset("APPLICATIONS")
MyRs.AddNew
MyRs!SSN = Forms!APPLICATIONS!SSN
MyRs!FNAME = Forms!APPLICATIONS!FNAME
MyRs!LNAME = Forms!APPLICATIONS!LNAME
MyRs!RECEIVED = Forms!APPLICATIONS!RECEIVED
MyRs!CLOSED = Forms!APPLICATIONS!CLOSED
MyRs!INITIALS = Forms!APPLICATIONS!INITIALS
MyRs.Update
strCode = DMax("[ID]", "Applications")
strFilter = "(([ID] = " & strCode & ")) "
DoCmd.ApplyFilter , strFilter
Me.Refresh
Me.CODE.SetFocus
If IsNull([code]) Then
MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
Me!CODE.SetFocus
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.

Nov 12 '05 #2

P: n/a
Thanks. I have added the same code behing a new button of my form but
I have a debug problem with:
MyRs!SSN = Forms!APPLICATIONS!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.

pt******@spsu.edu (PaulT) wrote in message news:<81*************************@posting.google.c om>...
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.

jp***@tin.it (Paolo) wrote in message news:<9f**************************@posting.google. 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.OpenRecordset("APPLICATIONS")
MyRs.AddNew
MyRs!SSN = Forms!APPLICATIONS!SSN
MyRs!FNAME = Forms!APPLICATIONS!FNAME
MyRs!LNAME = Forms!APPLICATIONS!LNAME
MyRs!RECEIVED = Forms!APPLICATIONS!RECEIVED
MyRs!CLOSED = Forms!APPLICATIONS!CLOSED
MyRs!INITIALS = Forms!APPLICATIONS!INITIALS
MyRs.Update
strCode = DMax("[ID]", "Applications")
strFilter = "(([ID] = " & strCode & ")) "
DoCmd.ApplyFilter , strFilter
Me.Refresh
Me.CODE.SetFocus
If IsNull([code]) Then
MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
Me!CODE.SetFocus
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.

Nov 12 '05 #3

P: n/a
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.
jp***@tin.it (Paolo) wrote in message news:<9f*************************@posting.google.c om>...
Thanks. I have added the same code behing a new button of my form but
I have a debug problem with:
MyRs!SSN = Forms!APPLICATIONS!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.

pt******@spsu.edu (PaulT) wrote in message news:<81*************************@posting.google.c om>...
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.

jp***@tin.it (Paolo) wrote in message news:<9f**************************@posting.google. 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.OpenRecordset("APPLICATIONS")
MyRs.AddNew
MyRs!SSN = Forms!APPLICATIONS!SSN
MyRs!FNAME = Forms!APPLICATIONS!FNAME
MyRs!LNAME = Forms!APPLICATIONS!LNAME
MyRs!RECEIVED = Forms!APPLICATIONS!RECEIVED
MyRs!CLOSED = Forms!APPLICATIONS!CLOSED
MyRs!INITIALS = Forms!APPLICATIONS!INITIALS
MyRs.Update
strCode = DMax("[ID]", "Applications")
strFilter = "(([ID] = " & strCode & ")) "
DoCmd.ApplyFilter , strFilter
Me.Refresh
Me.CODE.SetFocus
If IsNull([code]) Then
MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
Me!CODE.SetFocus
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.

Nov 12 '05 #4

P: n/a
Yes, both tables have exactly the same fields. I still don't undertand
where is the problem.
pt******@spsu.edu (PaulT) wrote in message news:<81************************@posting.google.co m>...
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.
jp***@tin.it (Paolo) wrote in message news:<9f*************************@posting.google.c om>...
Thanks. I have added the same code behing a new button of my form but
I have a debug problem with:
MyRs!SSN = Forms!APPLICATIONS!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.

pt******@spsu.edu (PaulT) wrote in message news:<81*************************@posting.google.c om>...
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.

jp***@tin.it (Paolo) wrote in message news:<9f**************************@posting.google. 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.OpenRecordset("APPLICATIONS")
> MyRs.AddNew
> MyRs!SSN = Forms!APPLICATIONS!SSN
> MyRs!FNAME = Forms!APPLICATIONS!FNAME
> MyRs!LNAME = Forms!APPLICATIONS!LNAME
> MyRs!RECEIVED = Forms!APPLICATIONS!RECEIVED
> MyRs!CLOSED = Forms!APPLICATIONS!CLOSED
> MyRs!INITIALS = Forms!APPLICATIONS!INITIALS
> MyRs.Update
> strCode = DMax("[ID]", "Applications")
> strFilter = "(([ID] = " & strCode & ")) "
> DoCmd.ApplyFilter , strFilter
> Me.Refresh
> Me.CODE.SetFocus
> If IsNull([code]) Then
> MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
> Me!CODE.SetFocus
> 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.

Nov 12 '05 #5

P: n/a
Are you certain the datatypes are the same??

-Paul

jp***@tin.it (Paolo) wrote in message news:<9f**************************@posting.google. com>...
Yes, both tables have exactly the same fields. I still don't undertand
where is the problem.
pt******@spsu.edu (PaulT) wrote in message news:<81************************@posting.google.co m>...
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.
jp***@tin.it (Paolo) wrote in message news:<9f*************************@posting.google.c om>...
Thanks. I have added the same code behing a new button of my form but
I have a debug problem with:
MyRs!SSN = Forms!APPLICATIONS!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.

pt******@spsu.edu (PaulT) wrote in message news:<81*************************@posting.google.c om>...
> 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.
>
> jp***@tin.it (Paolo) wrote in message news:<9f**************************@posting.google. 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.OpenRecordset("APPLICATIONS")
> > MyRs.AddNew
> > MyRs!SSN = Forms!APPLICATIONS!SSN
> > MyRs!FNAME = Forms!APPLICATIONS!FNAME
> > MyRs!LNAME = Forms!APPLICATIONS!LNAME
> > MyRs!RECEIVED = Forms!APPLICATIONS!RECEIVED
> > MyRs!CLOSED = Forms!APPLICATIONS!CLOSED
> > MyRs!INITIALS = Forms!APPLICATIONS!INITIALS
> > MyRs.Update
> > strCode = DMax("[ID]", "Applications")
> > strFilter = "(([ID] = " & strCode & ")) "
> > DoCmd.ApplyFilter , strFilter
> > Me.Refresh
> > Me.CODE.SetFocus
> > If IsNull([code]) Then
> > MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
> > Me!CODE.SetFocus
> > 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.

Nov 12 '05 #6

P: n/a
Also, what is the error message when debug 'hits' that line?

-Paul

jp***@tin.it (Paolo) wrote in message news:<9f**************************@posting.google. com>...
Yes, both tables have exactly the same fields. I still don't undertand
where is the problem.
pt******@spsu.edu (PaulT) wrote in message news:<81************************@posting.google.co m>...
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.
jp***@tin.it (Paolo) wrote in message news:<9f*************************@posting.google.c om>...
Thanks. I have added the same code behing a new button of my form but
I have a debug problem with:
MyRs!SSN = Forms!APPLICATIONS!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.

pt******@spsu.edu (PaulT) wrote in message news:<81*************************@posting.google.c om>...
> 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.
>
> jp***@tin.it (Paolo) wrote in message news:<9f**************************@posting.google. 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.OpenRecordset("APPLICATIONS")
> > MyRs.AddNew
> > MyRs!SSN = Forms!APPLICATIONS!SSN
> > MyRs!FNAME = Forms!APPLICATIONS!FNAME
> > MyRs!LNAME = Forms!APPLICATIONS!LNAME
> > MyRs!RECEIVED = Forms!APPLICATIONS!RECEIVED
> > MyRs!CLOSED = Forms!APPLICATIONS!CLOSED
> > MyRs!INITIALS = Forms!APPLICATIONS!INITIALS
> > MyRs.Update
> > strCode = DMax("[ID]", "Applications")
> > strFilter = "(([ID] = " & strCode & ")) "
> > DoCmd.ApplyFilter , strFilter
> > Me.Refresh
> > Me.CODE.SetFocus
> > If IsNull([code]) Then
> > MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
> > Me!CODE.SetFocus
> > 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.

Nov 12 '05 #7

P: n/a
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?

pt******@spsu.edu (PaulT) wrote in message news:<81*************************@posting.google.c om>...
Also, what is the error message when debug 'hits' that line?

-Paul

jp***@tin.it (Paolo) wrote in message news:<9f**************************@posting.google. com>...
Yes, both tables have exactly the same fields. I still don't undertand
where is the problem.
pt******@spsu.edu (PaulT) wrote in message news:<81************************@posting.google.co m>...
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.
jp***@tin.it (Paolo) wrote in message news:<9f*************************@posting.google.c om>...
> Thanks. I have added the same code behing a new button of my form but
> I have a debug problem with:
> MyRs!SSN = Forms!APPLICATIONS!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.
>
> pt******@spsu.edu (PaulT) wrote in message news:<81*************************@posting.google.c om>...
> > 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.
> >
> > jp***@tin.it (Paolo) wrote in message news:<9f**************************@posting.google. 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.OpenRecordset("APPLICATIONS")
> > > MyRs.AddNew
> > > MyRs!SSN = Forms!APPLICATIONS!SSN
> > > MyRs!FNAME = Forms!APPLICATIONS!FNAME
> > > MyRs!LNAME = Forms!APPLICATIONS!LNAME
> > > MyRs!RECEIVED = Forms!APPLICATIONS!RECEIVED
> > > MyRs!CLOSED = Forms!APPLICATIONS!CLOSED
> > > MyRs!INITIALS = Forms!APPLICATIONS!INITIALS
> > > MyRs.Update
> > > strCode = DMax("[ID]", "Applications")
> > > strFilter = "(([ID] = " & strCode & ")) "
> > > DoCmd.ApplyFilter , strFilter
> > > Me.Refresh
> > > Me.CODE.SetFocus
> > > If IsNull([code]) Then
> > > MsgBox "Please enter the Claim TYPE!. ", vbOKOnly
> > > Me!CODE.SetFocus
> > > 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.

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.