Duplicating reocords from main form and subform

[rarerock]

I'm building a database with 2 tables named "trips" and "riders". Trips
contains a tripID which is the primary key and all other fields are trip
information, date, location etc. Riders has 3 fields named trip, rider, and
type. I created a relationship with tripID and trip. I have 1 query called
"trips query1" that includes both tables that i used to create the trip form
and the rider subform. I need a command button that will allow me to
duplicate records from both forms. I've tried various codes from here and
other sites to with no success. Could someone help point me in the right
direction. Thank You.

 

[Jeff Boyce]

You're describing a solution ("duplicate records") but not the problem.

If you'll describe the business need or problems, folks here may be able to
offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[rarerock]

You're describing a solution ("duplicate records") but not the problem.

If you'll describe the business need or problems, folks here may be able to
offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm building a database with 2 tables named "trips" and "riders". Trips
contains a tripID which is the primary key and all other fields are trip

[quoted text clipped - 7 lines]

other sites to with no success. Could someone help point me in the right
direction. Thank You.

I don't have a solution, i've tried several versions of Allen Browne's code
(heres one at http://allenbrowne.com/ser-57.html) to accomplish this task and
nothing works. I'm not sure if it's my design, or i'm using the wrong
method/code?

 

[tina]

i'm willing to bet that nobody here can get a clue what you're doing from
your post. you "created a relationship with tripID and trip" - a
relationship with what? you created a query that includes both tables that
you used to create a trip form and rider subform... you're using a single
query as the RecordSource of both objects? and why would you use a
multi-table query as the RecordSource of either one? what are these
duplicate records for, why are you duplicating data, and where are you
planning to store these records?

suggest you STOP working on your database, and read up/more on relational
design principles, then re-examine your tables and relationships to make
sure they're designed correctly. after you do that, move on to queries and
forms (hint: typically, a mainform is bound to a "parent" table, and its'
subform is bound to a related "child" table; not to multi-table queries.)
post back to the ngs as you have specific questions, but be clear in
describing your set-up, what you're trying to accomplish, how you're trying
to do it, and what problem you're encountering. and it never hurts to say
which version of Access you're using.

hth

 

[Jeff Boyce]

One more time ... if you don't describe what problem/business need you are
trying to solve, we're at a real disadvantage for offering ideas. What you
described ("I need a command button that will allow me to duplicate records
from both forms") IS a solution. But we don't know for what...

Regards

Jeff Boyce
Microsoft Office/Access MVP

You're describing a solution ("duplicate records") but not the problem.

If you'll describe the business need or problems, folks here may be able
to
offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm building a database with 2 tables named "trips" and "riders". Trips
contains a tripID which is the primary key and all other fields are trip

[quoted text clipped - 7 lines]

other sites to with no success. Could someone help point me in the right
direction. Thank You.

I don't have a solution, i've tried several versions of Allen Browne's
code
(heres one at http://allenbrowne.com/ser-57.html) to accomplish this task
and
nothing works. I'm not sure if it's my design, or i'm using the wrong
method/code?

 

[rarerock via AccessMonster.com]

One more time ... if you don't describe what problem/business need you are
trying to solve, we're at a real disadvantage for offering ideas. What you
described ("I need a command button that will allow me to duplicate records
from both forms") IS a solution. But we don't know for what...

Regards

Jeff Boyce
Microsoft Office/Access MVP
[quoted text clipped - 18 lines]

nothing works. I'm not sure if it's my design, or i'm using the wrong
method/code?

The database is for setting up Daily Field Trips, for something like a school.
I have everything working but there is a need to be able to duplicate the
trips. The reason is there are multiple trips per month, say every Monday,
instead of re-inputting all the information i thought i could just duplicate
it and just change the dates. And in my earlier post i gave the wrong
information, the forms are made from the tables not a query.

 

[tina]

okay, your explanation helps a lot. if you have field trip records where the
data is identical except for the dates, i'd say that your tables structure
needs to be re-examined. sounds like you need a table that lists the various
field trips - not specific instances - but the elements that are the same in
every instance of a field trip...to a particular destination, perhaps? then
a table of specific outings, with a foreign key field linking back to the
field trips table, and a date field for the date of the specific outing, and
any other data that may change from one outing to the next. when you
normalize the data, you usually see that you don't need - or want -
duplicate records.

i'll repeat my previous advice: read up/more on relational design
principles. for more information, see
http://home.att.net/~california.db/tips.html.

hth

One more time ... if you don't describe what problem/business need you are
trying to solve, we're at a real disadvantage for offering ideas. What you
described ("I need a command button that will allow me to duplicate records
from both forms") IS a solution. But we don't know for what...

Regards

Jeff Boyce
Microsoft Office/Access MVP

You're describing a solution ("duplicate records") but not the problem.

[quoted text clipped - 18 lines]

nothing works. I'm not sure if it's my design, or i'm using the wrong
method/code?

The database is for setting up Daily Field Trips, for something like a school.
I have everything working but there is a need to be able to duplicate the
trips. The reason is there are multiple trips per month, say every Monday,
instead of re-inputting all the information i thought i could just duplicate
it and just change the dates. And in my earlier post i gave the wrong
information, the forms are made from the tables not a query.

 

[Jeff Boyce]

I agree with Tina's assessment ... I suspect you have "committed spreadsheet
on Access", and tried to set up your tables as if you were using Excel.
Spend the time to learn about "normalization" if you want to get good use of
Access' relationally-oriented features/functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

One more time ... if you don't describe what problem/business need you are
trying to solve, we're at a real disadvantage for offering ideas. What
you
described ("I need a command button that will allow me to duplicate
records
from both forms") IS a solution. But we don't know for what...

Regards

Jeff Boyce
Microsoft Office/Access MVP

You're describing a solution ("duplicate records") but not the problem.

[quoted text clipped - 18 lines]

nothing works. I'm not sure if it's my design, or i'm using the wrong
method/code?

The database is for setting up Daily Field Trips, for something like a
school.
I have everything working but there is a need to be able to duplicate the
trips. The reason is there are multiple trips per month, say every Monday,
instead of re-inputting all the information i thought i could just
duplicate
it and just change the dates. And in my earlier post i gave the wrong
information, the forms are made from the tables not a query.

 

[rarerock via AccessMonster.com]

I think i'm not explaining things correctly, and sorry for wasting everyones
time.

I agree with Tina's assessment ... I suspect you have "committed spreadsheet
on Access", and tried to set up your tables as if you were using Excel.
Spend the time to learn about "normalization" if you want to get good use of
Access' relationally-oriented features/functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP
[quoted text clipped - 21 lines]

it and just change the dates. And in my earlier post i gave the wrong
information, the forms are made from the tables not a query.

 

[John W. Vinson]

I think i'm not explaining things correctly, and sorry for wasting everyones
time.

If you'ld like to start over, feel free - perhaps you could start by posting
the names of your tables, what real-life entity the table represents, and some
key fields. I believe that Jeff and Tina are giving you good advice at least
based on the database structure you have posted. If you don't consider it good
advice, maybe it's because they don't understand your actual structure (since
all we can see is what you've posted here).

 

[rarerock via AccessMonster.com]

This is the code for the Duplicate Record Button, it copies the record from
the main form with no problems, then pops up a window Forms!frmrider!TripID
from the query, i press ok an it give me an message Invalid Use Of Null? I
changed some of the names of my tables/fields also.

Private Sub btnDuplicateRecord_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

Set dbs = CurrentDb
Set Rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicateRecord_Click

Me.Tag = Me![TripID]

With Rst
AddNew
'!TYDATE = Me!TYDATE
!PCHARGE = Me!PCHARGE
!PHONE = Me!PHONE
!DCODE = Me!DCODE
'!TPDATE = Me!TPDATE
!DTRIP = Me!DTRIP
!PLOCATION = Me!PLOCATION
Update
Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark


DoCmd.SetWarnings False
DoCmd.OpenQuery "Query1"
DoCmd.SetWarnings True

Me![frmrider].Requery

Exit_btnDuplicateRecord_Click:
Exit Sub

Err_btnDuplicateRecord_Click:
MsgBox Error$
Resume Exit_btnDuplicateRecord_Click:
End Sub


Also here is the Query:


INSERT INTO Riders ( forms!frmrider.tag, NameID, TypeID, TripID )
SELECT Riders.TripID, Riders.NameID, Riders.TypeID, CLng(Forms!frmrider!
Tripid) AS NewNumber_ID
FROM Riders;

Sorry, but i have been trying to get this to work for days now, am getting a
little frustrated.
Hope this information helps?

 

[John W. Vinson]

This is the code for the Duplicate Record Button, it copies the record from
the main form with no problems, then pops up a window Forms!frmrider!TripID
from the query, i press ok an it give me an message Invalid Use Of Null? I
changed some of the names of my tables/fields also.

The line

INSERT INTO Riders ( forms!frmrider.tag, NameID, TypeID, TripID )

is almost surely the problem. The names in parens in the INSERT INTO statement
should be fieldnames in the table. I'm certain that there is no field in the
Riders table named Forms!frmrider.tag!

If you're trying to extract the name of a table field from the Tag textbox on
the form and use that in the INSERT INTO query, you'll need to actually
construct the SQL string of the INSERT INTO query in code and then execute it.

There may be other examples in your code. It would help if you put a
breakpoint in the code and stepped through it using the Debug options to
determine exactly which query is causing the problem.

 

[John W. Vinson]

INSERT INTO Riders ( forms!frmrider.tag, NameID, TypeID, TripID )
SELECT Riders.TripID, Riders.NameID, Riders.TypeID, CLng(Forms!frmrider!
Tripid) AS NewNumber_ID
FROM Riders;

Another issue: Is the form frmrider in fact open when this code runs? Does it
have a textbox or other control named Tripid? Is that textbox null (if so
CLng() will fail)?

 

[rarerock via AccessMonster.com]

The form is open on the main form, the table for the main form also as a
field TripID that is a PK, with a one to many relation with the TripID field
in the riders table that is not a PK. There is no PK in the riders table. The
tripID carries over to the subform/riders table when the NameID and TypeID
fields are filled in.
Hope i'm not confusing you?

 

[John W. Vinson]

The form is open on the main form, the table for the main form also as a
field TripID that is a PK, with a one to many relation with the TripID field
in the riders table that is not a PK. There is no PK in the riders table. The
tripID carries over to the subform/riders table when the NameID and TypeID
fields are filled in.
Hope i'm not confusing you?

Confusing me thoroughly.

That query is taking the field Riders.TripID and trying to insert it into
forms!frmrider.tag.

It seems there *is* no tripid in Reiders - so you can't

SELECT Riders.TripID

because it's not there; and you also can't use a form reference like
forms!frmrider.tag in the (Fields List) of an INSERT query.

Step back a bit. What to you *want* this query to do?
You want to insert stuff into a table. What table do you want to insert it
into? What fields in that table?

Where will the query get the stuff that it inserts?

 

[rarerock via AccessMonster.com]

Here is a link where i got the code from.
http://support.microsoft.com/kb/208824

The form is open on the main form, the table for the main form also as a
field TripID that is a PK, with a one to many relation with the TripID field

[quoted text clipped - 11 lines]
Confusing me thoroughly.

That query is taking the field Riders.TripID and trying to insert it into
forms!frmrider.tag.

It seems there *is* no tripid in Reiders - so you can't

SELECT Riders.TripID

because it's not there; and you also can't use a form reference like
forms!frmrider.tag in the (Fields List) of an INSERT query.

Step back a bit. What to you *want* this query to do?
You want to insert stuff into a table. What table do you want to insert it
into? What fields in that table?

Where will the query get the stuff that it inserts?

 

[rarerock via AccessMonster.com]

Now i'm trying this code, and still no luck?

Private Sub cmdDupe_Click()
On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If
'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.recordsetclone
.AddNew
'!TRIPN = Me.TRIPN
!FNAME = Me.FNAME
!LNAME = Me.LNAME
!PHONE = Me.PHONE
!DCODE = Me.DCODE
!TYDATE = TYDATE
!TPDATE = TPDATE
!PLOCATION = PLOCATION
!PTIME = PTIME
!ATIME = ATIME
!DLOCATION = DLOCATION
!RLOCATION = RLOCATION
!LTIME = LTIME
!RTIME = RTIME
!STAFF = Me.STAFF
!COMMENTS = Me.COMMENTS
'etc for other fields.
.Update
'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !TRIPN
'Duplicate the related records: append query.
If Me.[Riders].Form.recordsetclone.RecordCount > 0 Then
strSql = "INSERT INTO [TRiders] ( TripID, NameID, TypeID ) "
& _
"SELECT " & lngID & " As NewID, NameID, TypeID " & _
"FROM [TRiders] WHERE TripID = " & Me.TripID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If
'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub


It halts at Me.TripID & ";"

Here is a link where i got the code from.
http://support.microsoft.com/kb/208824
[quoted text clipped - 19 lines]

Where will the query get the stuff that it inserts?

 

[Jeff Boyce]

"it halts at ...." isn't particularly informative.

Do you mean you get an error message? If so, what does it say?

If not, does Access quit, or your OS lock up, or your PC burst into flames?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Now i'm trying this code, and still no luck?

Private Sub cmdDupe_Click()
On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.
'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If
'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.recordsetclone
.AddNew
'!TRIPN = Me.TRIPN
!FNAME = Me.FNAME
!LNAME = Me.LNAME
!PHONE = Me.PHONE
!DCODE = Me.DCODE
!TYDATE = TYDATE
!TPDATE = TPDATE
!PLOCATION = PLOCATION
!PTIME = PTIME
!ATIME = ATIME
!DLOCATION = DLOCATION
!RLOCATION = RLOCATION
!LTIME = LTIME
!RTIME = RTIME
!STAFF = Me.STAFF
!COMMENTS = Me.COMMENTS
'etc for other fields.
.Update
'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !TRIPN
'Duplicate the related records: append query.
If Me.[Riders].Form.recordsetclone.RecordCount > 0 Then
strSql = "INSERT INTO [TRiders] ( TripID, NameID, TypeID )
"
& _
"SELECT " & lngID & " As NewID, NameID, TypeID " & _
"FROM [TRiders] WHERE TripID = " & Me.TripID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If
'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub


It halts at Me.TripID & ";"

Here is a link where i got the code from.
http://support.microsoft.com/kb/208824

The form is open on the main form, the table for the main form also as a
field TripID that is a PK, with a one to many relation with the TripID
field

[quoted text clipped - 19 lines]

Where will the query get the stuff that it inserts?

 

[rarerock via AccessMonster.com]

In the statement - WHERE TripID = " & Me.TripID & ";"
Method or Data Member Not Found

"it halts at ...." isn't particularly informative.

Do you mean you get an error message? If so, what does it say?

If not, does Access quit, or your OS lock up, or your PC burst into flames?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Now i'm trying this code, and still no luck?

[quoted text clipped - 72 lines]

 

[John W. Vinson]

In the statement - WHERE TripID = " & Me.TripID & ";"
Method or Data Member Not Found

That suggests that there is in fact nothing on the current Form (Me.) named
TripID.