Duplicate records !!!!Help!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone,

Can someone please help me with this problem..

I am trying to duplicate values with a unique number (N_NUMBER) using a
duplicate command button in a form(the code for this is given at the end of
this post). I was able to do simple duplication of values that are entered in
the form into the table. However my manager wants it in a different way. I am
not sure if I can describe it correctly, so I am giving an example of what I
should be able to do.
For example: I want to duplicate 3650 records. I should be able to duplicate
it for a certain date/month/year. Say since I have 3650 records and when I
select a year, then I should be able to insert 10(3650/365) records under
each date in that particular year. I did work on the generic logic on how to
do it, but I am not sure how to or if I can do it in VBA or my logic is
correct or not. The logic that I though of is given below:
------------------------------------------------------------------------------------------------
No_Entires => # of records to be generated
Entries_Per_Day = No_Entries / 365
for (j=0;j<365;j++)
{
for(I=0;I<Entries_Per_Day;I++)
{
Temp = (currentdate + j) ‘current date is today’s date
Print Temp
}
}
-----------------------------------------------------------------------------------

The code that I used for duplication of records is given below:
------------------------------------------------------------------------------------------------
Private Sub Insert_Duplicates_Click()
On Error GoTo Err_Insert_Duplicates_Click

Dim COUNTER As Integer
For COUNTER = 0 To 5
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
Me!N_NUMBER_txt = Me.[N_NUMBER_txt] + 1
Next

Exit_Insert_Duplicates_Click:
Exit Sub

Err_Insert_Duplicates_Click:
MsgBox Err.Description
Resume Exit_Insert_Duplicates_Click

End Sub
 
Why don't you make this a multi-table relational database? It would solve a
lot of problems.
 
Hi Brandie,

Thanks for the reply.

Can you please explain what you mean by
Why don't you make this a multi-table relational database?
Click to expand...

Please Note:
The tables that I am using are the dimension and fact tables of a
datawarehouse built for our product. I am using the form to make the data
entry easier to users so that later once the data is entered, I will reload
it into oracle.

Thanks once again..your help is appreciated



Brandie said:
Why don't you make this a multi-table relational database? It would solve a
lot of problems.

Newbie to Access and VBA said:
Hello everyone,

Can someone please help me with this problem..

I am trying to duplicate values with a unique number (N_NUMBER) using a
duplicate command button in a form(the code for this is given at the end of
this post). I was able to do simple duplication of values that are entered in
the form into the table. However my manager wants it in a different way. I am
not sure if I can describe it correctly, so I am giving an example of what I
should be able to do.
For example: I want to duplicate 3650 records. I should be able to duplicate
it for a certain date/month/year. Say since I have 3650 records and when I
select a year, then I should be able to insert 10(3650/365) records under
each date in that particular year. I did work on the generic logic on how to
do it, but I am not sure how to or if I can do it in VBA or my logic is
correct or not. The logic that I though of is given below:
------------------------------------------------------------------------------------------------
No_Entires => # of records to be generated
Entries_Per_Day = No_Entries / 365
for (j=0;j<365;j++)
{
for(I=0;I<Entries_Per_Day;I++)
{
Temp = (currentdate + j) ‘current date is today’s date
Print Temp
}
}
-----------------------------------------------------------------------------------

The code that I used for duplication of records is given below:
------------------------------------------------------------------------------------------------
Private Sub Insert_Duplicates_Click()
On Error GoTo Err_Insert_Duplicates_Click

Dim COUNTER As Integer
For COUNTER = 0 To 5
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
Me!N_NUMBER_txt = Me.[N_NUMBER_txt] + 1
Next

Exit_Insert_Duplicates_Click:
Exit Sub

Err_Insert_Duplicates_Click:
MsgBox Err.Description
Resume Exit_Insert_Duplicates_Click

End Sub
Click to expand...
Click to expand...
 
Well, let's say you are contacting a customer every couple of days about an
order they made. Whichever table the information about the order they made
is kept in should include a customer ID field. Then you can create a second
table whith the fields: customer ID, Contact Date, Notes....etc. In this
second table, if you contacted the customer lets say 5 times then you'll have
5 records with that customer ID. So you want ot make the primary key consist
of the Customer ID and teh Contact Date. Then go to tool in the menu bar,
and click on relationships. Show the two tables that include customer ID and
link the two tables by Customer ID. Then if you go back to the original
table that has the order information...you should see a plus sign at the
beginning of every record. If you click on that, then you can view all of
the contacts made for that customer. This is a one to many relationship.
and the two tables are related, hence the name multi-table, relational
database.

Newbie to Access and VBA said:
Hi Brandie,

Thanks for the reply.

Can you please explain what you mean by
Why don't you make this a multi-table relational database?
Click to expand...

Please Note:
The tables that I am using are the dimension and fact tables of a
datawarehouse built for our product. I am using the form to make the data
entry easier to users so that later once the data is entered, I will reload
it into oracle.

Thanks once again..your help is appreciated



Brandie said:
Why don't you make this a multi-table relational database? It would solve a
lot of problems.

Newbie to Access and VBA said:
Hello everyone,

Can someone please help me with this problem..

I am trying to duplicate values with a unique number (N_NUMBER) using a
duplicate command button in a form(the code for this is given at the end of
this post). I was able to do simple duplication of values that are entered in
the form into the table. However my manager wants it in a different way. I am
not sure if I can describe it correctly, so I am giving an example of what I
should be able to do.
For example: I want to duplicate 3650 records. I should be able to duplicate
it for a certain date/month/year. Say since I have 3650 records and when I
select a year, then I should be able to insert 10(3650/365) records under
each date in that particular year. I did work on the generic logic on how to
do it, but I am not sure how to or if I can do it in VBA or my logic is
correct or not. The logic that I though of is given below:
------------------------------------------------------------------------------------------------
No_Entires => # of records to be generated
Entries_Per_Day = No_Entries / 365
for (j=0;j<365;j++)
{
for(I=0;I<Entries_Per_Day;I++)
{
Temp = (currentdate + j) ‘current date is today’s date
Print Temp
}
}
-----------------------------------------------------------------------------------

The code that I used for duplication of records is given below:
------------------------------------------------------------------------------------------------
Private Sub Insert_Duplicates_Click()
On Error GoTo Err_Insert_Duplicates_Click

Dim COUNTER As Integer
For COUNTER = 0 To 5
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
Me!N_NUMBER_txt = Me.[N_NUMBER_txt] + 1
Next

Exit_Insert_Duplicates_Click:
Exit Sub

Err_Insert_Duplicates_Click:
MsgBox Err.Description
Resume Exit_Insert_Duplicates_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Brandie,

Thanks.I did understand what Multi-table relational database is.

But I did not quite understand how that relates to my question of
duplicating records per day? How can I have the design differently? And what
is the purpose that it serves for my record duplication

Please let me know.
Thanks


Brandie said:
Well, let's say you are contacting a customer every couple of days about an
order they made. Whichever table the information about the order they made
is kept in should include a customer ID field. Then you can create a second
table whith the fields: customer ID, Contact Date, Notes....etc. In this
second table, if you contacted the customer lets say 5 times then you'll have
5 records with that customer ID. So you want ot make the primary key consist
of the Customer ID and teh Contact Date. Then go to tool in the menu bar,
and click on relationships. Show the two tables that include customer ID and
link the two tables by Customer ID. Then if you go back to the original
table that has the order information...you should see a plus sign at the
beginning of every record. If you click on that, then you can view all of
the contacts made for that customer. This is a one to many relationship.
and the two tables are related, hence the name multi-table, relational
database.

Newbie to Access and VBA said:
Hi Brandie,

Thanks for the reply.

Can you please explain what you mean by
Why don't you make this a multi-table relational database?
Click to expand...

Please Note:
The tables that I am using are the dimension and fact tables of a
datawarehouse built for our product. I am using the form to make the data
entry easier to users so that later once the data is entered, I will reload
it into oracle.

Thanks once again..your help is appreciated



Brandie said:
Why don't you make this a multi-table relational database? It would solve a
lot of problems.

:

Hello everyone,

Can someone please help me with this problem..

I am trying to duplicate values with a unique number (N_NUMBER) using a
duplicate command button in a form(the code for this is given at the end of
this post). I was able to do simple duplication of values that are entered in
the form into the table. However my manager wants it in a different way. I am
not sure if I can describe it correctly, so I am giving an example of what I
should be able to do.
For example: I want to duplicate 3650 records. I should be able to duplicate
it for a certain date/month/year. Say since I have 3650 records and when I
select a year, then I should be able to insert 10(3650/365) records under
each date in that particular year. I did work on the generic logic on how to
do it, but I am not sure how to or if I can do it in VBA or my logic is
correct or not. The logic that I though of is given below:
------------------------------------------------------------------------------------------------
No_Entires => # of records to be generated
Entries_Per_Day = No_Entries / 365
for (j=0;j<365;j++)
{
for(I=0;I<Entries_Per_Day;I++)
{
Temp = (currentdate + j) ‘current date is today’s date
Print Temp
}
}
-----------------------------------------------------------------------------------

The code that I used for duplication of records is given below:
------------------------------------------------------------------------------------------------
Private Sub Insert_Duplicates_Click()
On Error GoTo Err_Insert_Duplicates_Click

Dim COUNTER As Integer
For COUNTER = 0 To 5
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
Me!N_NUMBER_txt = Me.[N_NUMBER_txt] + 1
Next

Exit_Insert_Duplicates_Click:
Exit Sub

Err_Insert_Duplicates_Click:
MsgBox Err.Description
Resume Exit_Insert_Duplicates_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hello Brandie

I solved the problem. However I would also like to know the different
approach you were suggesting. Can you please explain to me what you had in
mind...

The code that I used to do that is :
---------------------------------------------------
Private Sub Duplicates_insert_Click()
On Error GoTo Err_Duplicates_insert_Click

Dim COUNTER, temp As Integer
Dim No_Entries, Entries_Per_Day, j, i As Integer
j = 0
i = 0
No_Entries = 49
Entries_Per_Day = No_Entries / 7
Do While j < 6
Do While i < Entries_Per_Day
Me!Current_Date_txt = (Now() + j)
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
Me!N_NUMBER_txt = Me.[N_NUMBER_txt] + 1
i = i + 1
j = j + 1
Loop
Loop
If Me.Dirty Then
DoCmd.RunCommand acCmdUndo
'DoCmd.Close
'DoCmd.Close acForm, "form_name"

Else

End If


Exit_Duplicates_insert_Click:
Exit Sub

Err_Duplicates_insert_Click:
MsgBox Err.Description
Resume Exit_Duplicates_insert_Click
End Sub
----------------------------------------------------

Thanks once again for your help.

Newbie to Access and VBA said:
Hi Brandie,

Thanks.I did understand what Multi-table relational database is.

But I did not quite understand how that relates to my question of
duplicating records per day? How can I have the design differently? And what
is the purpose that it serves for my record duplication

Please let me know.
Thanks


Brandie said:
Well, let's say you are contacting a customer every couple of days about an
order they made. Whichever table the information about the order they made
is kept in should include a customer ID field. Then you can create a second
table whith the fields: customer ID, Contact Date, Notes....etc. In this
second table, if you contacted the customer lets say 5 times then you'll have
5 records with that customer ID. So you want ot make the primary key consist
of the Customer ID and teh Contact Date. Then go to tool in the menu bar,
and click on relationships. Show the two tables that include customer ID and
link the two tables by Customer ID. Then if you go back to the original
table that has the order information...you should see a plus sign at the
beginning of every record. If you click on that, then you can view all of
the contacts made for that customer. This is a one to many relationship.
and the two tables are related, hence the name multi-table, relational
database.

Newbie to Access and VBA said:
Hi Brandie,

Thanks for the reply.

Can you please explain what you mean by
Why don't you make this a multi-table relational database?

Please Note:
The tables that I am using are the dimension and fact tables of a
datawarehouse built for our product. I am using the form to make the data
entry easier to users so that later once the data is entered, I will reload
it into oracle.

Thanks once again..your help is appreciated



:

Why don't you make this a multi-table relational database? It would solve a
lot of problems.

:

Hello everyone,

Can someone please help me with this problem..

I am trying to duplicate values with a unique number (N_NUMBER) using a
duplicate command button in a form(the code for this is given at the end of
this post). I was able to do simple duplication of values that are entered in
the form into the table. However my manager wants it in a different way. I am
not sure if I can describe it correctly, so I am giving an example of what I
should be able to do.
For example: I want to duplicate 3650 records. I should be able to duplicate
it for a certain date/month/year. Say since I have 3650 records and when I
select a year, then I should be able to insert 10(3650/365) records under
each date in that particular year. I did work on the generic logic on how to
do it, but I am not sure how to or if I can do it in VBA or my logic is
correct or not. The logic that I though of is given below:
------------------------------------------------------------------------------------------------
No_Entires => # of records to be generated
Entries_Per_Day = No_Entries / 365
for (j=0;j<365;j++)
{
for(I=0;I<Entries_Per_Day;I++)
{
Temp = (currentdate + j) ‘current date is today’s date
Print Temp
}
}
-----------------------------------------------------------------------------------

The code that I used for duplication of records is given below:
------------------------------------------------------------------------------------------------
Private Sub Insert_Duplicates_Click()
On Error GoTo Err_Insert_Duplicates_Click

Dim COUNTER As Integer
For COUNTER = 0 To 5
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
Me!N_NUMBER_txt = Me.[N_NUMBER_txt] + 1
Next

Exit_Insert_Duplicates_Click:
Exit Sub

Err_Insert_Duplicates_Click:
MsgBox Err.Description
Resume Exit_Insert_Duplicates_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top