Duplicating A Record with Null values

[Ray]

Good day. I have a form with a command button that duplicates the current
record using the code below. It works if i open the form from the back end
but not if the form is opened on the frontend. On the front end i get the
message "You can't go to the specific record" and the recod is not duplicated.

Any suggestions on what i can do to correct this issue?

Dim strDeliverable1, strDeliverable2, strDeliverable3, strDeliverable4,
strDeliverable5 As String
Dim Null_Deliverable1 As String
Dim Null_Deliverable2 As String
Dim Null_Deliverable3 As String
Dim Null_Deliverable4 As String
Dim Null_Deliverable5 As String

If Not IsNull(Me![Deliverable1]) Or Me.Deliverable1 = "" Then
Null_Deliverable1 = False
Else
Null_Deliverable1 = True
End If
If Not IsNull(Me![Deliverable2]) Or Me.Deliverable2 = "" Then
Null_Deliverable2 = False
Else
Null_Deliverable2 = True
End If
If Not IsNull(Me![Deliverable3]) Or Me.Deliverable3 = "" Then
Null_Deliverable3 = False
Else
Null_Deliverable3 = True
End If
If Not IsNull(Me![Deliverable4]) Or Me.Deliverable4 = "" Then
Null_Deliverable4 = False
Else
Null_Deliverable4 = True
End If
If Not IsNull(Me![Deliverable5]) Or Me.Deliverable5 = "" Then
Null_Deliverable5 = False
Else
Null_Deliverable5 = True
End If

If Null_Deliverable1 = False Then strDeliverable1 = Me![Deliverable1]
If Null_Deliverable2 = False Then strDeliverable2 = Me![Deliverable2]
If Null_Deliverable3 = False Then strDeliverable3 = Me![Deliverable3]
If Null_Deliverable4 = False Then strDeliverable4 = Me![Deliverable4]
If Null_Deliverable5 = False Then strDeliverable5 = Me![Deliverable5]

DoCmd.GoToRecord , , acNewRec

If Null_Deliverable1 = False Then Me![Deliverable1] = strDeliverable1
If Null_Deliverable2 = False Then Me![Deliverable2] = strDeliverable2
If Null_Deliverable3 = False Then Me![Deliverable3] = strDeliverable3
If Null_Deliverable4 = False Then Me![Deliverable4] = strDeliverable4
If Null_Deliverable5 = False Then Me![Deliverable5] = strDeliverable5

strDeliverable1 = ""
strDeliverable2 = ""
strDeliverable3 = ""
strDeliverable4 = ""
strDeliverable5 = ""
Null_Deliverable1 = ""
Null_Deliverable2 = ""
Null_Deliverable3 = ""
Null_Deliverable4 = ""
Null_Deliverable5 = ""

 

[SteveS]

Good day. I have a form with a command button that duplicates the current
record using the code below. It works if i open the form from the back end
but not if the form is opened on the frontend. On the front end i get the
message "You can't go to the specific record" and the recod is not duplicated.

Any suggestions on what i can do to correct this issue?

Dim strDeliverable1, strDeliverable2, strDeliverable3, strDeliverable4,
strDeliverable5 As String

strDeliverable1 thru strDeliverable4 are declared as Varient.
Only strDeliverable5 is delcared as a String

Dim Null_Deliverable1 As String
Dim Null_Deliverable2 As String
Dim Null_Deliverable3 As String
Dim Null_Deliverable4 As String
Dim Null_Deliverable5 As String

Null_Deliverable1 thru Null_Deliverable5 are delcared as String, but in the
code below, you are trying to assign them TRUE or FALSE.

Try

Dim Null_Deliverable1 as Boolean
Dim Null_Deliverable2 as Boolean


These should be:

Null_Deliverable1 = FALSE
Null_Deliverable2 = FALSE
Null_Deliverable3 = FALSE
Null_Deliverable4 = FALSE
Null_Deliverable5 = FALSE

after you change the declarations.


HTH

 

[SteveS]

Oops,

That should have read:

And instead of

Null_Deliverable1 = ""
Null_Deliverable2 = ""
Null_Deliverable3 = ""
Null_Deliverable4 = ""
Null_Deliverable5 = ""

these should be:

Null_Deliverable1 = FALSE
Null_Deliverable2 = FALSE
Null_Deliverable3 = FALSE
Null_Deliverable4 = FALSE
Null_Deliverable5 = FALSE

 

[SteveS]

Good day. I have a form with a command button that duplicates the current
record using the code below. It works if i open the form from the back end
but not if the form is opened on the frontend. On the front end i get the
message "You can't go to the specific record" and the recod is not duplicated.

OK, so I read your post again and I don't what you mean by "if i open the
form from the back end ". The BE should only have the tables. The FE has
everything else.

Just for fun, I played around with the code. I kinda rewrote it .... but
this works for me.....

'------beg code-----------
Dim strDeliverable1 As String
Dim strDeliverable2 As String
Dim strDeliverable3 As String
Dim strDeliverable4 As String
Dim strDeliverable5 As String

Dim blnHasEntry As Boolean

Dim blnDeliverable1 As Boolean
Dim blnDeliverable2 As Boolean
Dim blnDeliverable3 As Boolean
Dim blnDeliverable4 As Boolean
Dim blnDeliverable5 As Boolean

'initialize variables

' set to true if a control has data
blnHasEntry = False

' assume the controls have data
blnDeliverable1 = True
blnDeliverable2 = True
blnDeliverable3 = True
blnDeliverable4 = True
blnDeliverable5 = True

' now check them
blnDeliverable1 = Not Nz(Me.Deliverable1, "") = ""
blnDeliverable2 = Not Nz(Me.Deliverable2, "") = ""
blnDeliverable3 = Not Nz(Me.Deliverable3, "") = ""
blnDeliverable4 = Not Nz(Me.Deliverable4, "") = ""
blnDeliverable5 = Not Nz(Me.Deliverable5, "") = ""

' if TRUE, Store data in variables
If blnDeliverable1 Then
strDeliverable1 = Me.Deliverable1
blnHasEntry = True
End If
If blnDeliverable2 Then
strDeliverable2 = Me.Deliverable2
blnHasEntry = True
End If
If blnDeliverable3 Then
strDeliverable3 = Me.Deliverable3
blnHasEntry = True
End If
If blnDeliverable4 Then
strDeliverable4 = Me.Deliverable4
blnHasEntry = True
End If
If blnDeliverable5 Then
strDeliverable5 = Me.Deliverable5
blnHasEntry = True
End If

'check if there were entries
If blnHasEntry Then
'add new record
DoCmd.GoToRecord , , acNewRec

'add data IF there was data in any of the controls
If blnDeliverable1 Then
Me.Deliverable1 = strDeliverable1
End If
If blnDeliverable2 Then
Me.Deliverable2 = strDeliverable2
End If
If blnDeliverable3 Then
Me.Deliverable3 = strDeliverable3
End If
If blnDeliverable4 Then
Me.Deliverable4 = strDeliverable4
End If
If blnDeliverable5 Then
Me.Deliverable5 = strDeliverable5
End If
End If
'----end code----------


HTH

 

[Ray]

Thanks for responding SteveS. Sorry for my terminology. What i meant is if i
go through form directly using shift/enter instead of the switchboard. I'll
try your suggestion and see if it works. Thanks again!

 

[Ray]

SteveS

I tried your code and I got the same eror that says "You can't go to the
specific record" and then it creates a blank record. Any suggestions?

 

[SteveS]

If you open the form directly from the database window the code works
(inserts a new record with data), but if you open the form using the
switchboard, only a (bl;ank) new record is created???

In the switchboard, what code is in the button click event?

Have you stepped thru the to see what is happening? Do the variables get the
data from the controls? Are the boolean variables set correctly?

 

[Ray]

The form is a subform and i have the code below on the click event to open
the form

On Error GoTo Err_Deliverablesbt_Click
If IsNull(Me![ID]) Then
MsgBox "Enter Contract information before viewing Report Cards form."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] =
Forms![Contract Profile]![ID]"
End If

Exit_Deliverablesbt_Click:
Exit Sub

Err_Deliverablesbt_Click:
MsgBox Err.Description
Resume Exit_Deliverablesbt_Click

Once i am on the subform i have a duplicate record button and the on click
event for this command I copied and paste the code from your post

Dim strDeliverable1 As String
Dim strDeliverable2 As String
Dim strDeliverable3 As String
Dim strDeliverable4 As String
Dim strDeliverable5 As String

'initialize variables

' set to true if a control has data
blnHasEntry = False

' assume the controls have data
blnDeliverable1 = True
blnDeliverable2 = True
blnDeliverable3 = True
blnDeliverable4 = True
blnDeliverable5 = True

' now check them
blnDeliverable1 = Not Nz(Me.Deliverable1, "") = ""
blnDeliverable2 = Not Nz(Me.Deliverable2, "") = ""
blnDeliverable3 = Not Nz(Me.Deliverable3, "") = ""
blnDeliverable4 = Not Nz(Me.Deliverable4, "") = ""
blnDeliverable5 = Not Nz(Me.Deliverable5, "") = ""

' if TRUE, Store data in variables
If blnDeliverable1 Then
strDeliverable1 = Me.Deliverable1
blnHasEntry = True
End If
If blnDeliverable2 Then
strDeliverable2 = Me.Deliverable2
blnHasEntry = True
End If
If blnDeliverable3 Then
strDeliverable3 = Me.Deliverable3
blnHasEntry = True
End If
If blnDeliverable4 Then
strDeliverable4 = Me.Deliverable4
blnHasEntry = True
End If
If blnDeliverable5 Then
strDeliverable5 = Me.Deliverable5
blnHasEntry = True
End If

'check if there were entries
If blnHasEntry Then
'add new record
DoCmd.GoToRecord , , acNewRec

'add data IF there was data in any of the controls
If blnDeliverable1 Then
Me.Deliverable1 = strDeliverable1
End If
If blnDeliverable2 Then
Me.Deliverable2 = strDeliverable2
End If
If blnDeliverable3 Then
Me.Deliverable3 = strDeliverable3
End If
If blnDeliverable4 Then
Me.Deliverable4 = strDeliverable4
End If
If blnDeliverable5 Then
Me.Deliverable5 = strDeliverable5
End If
End If

When i step through i do not get an error message. Its only when i am going
through the normal route to access the form and duplicate the record that i
get the error.

 

[SteveS]

Let me see if I understand this right:


You have a switchboard.
You press a button and a form, "Contract Profile", opens
You select a record (profile) and press another button,

which opens another form, "Report Cards" filtered by [ID]

You select a record on form "Report Cards" to duplicate
and press the duplicate button

Is this right?

One other question, do you want to copy the entire record or specific
fields? (like 3 out of 5)

 

[Ray]

SteveS, i really appreaciate your help...

Yes, that is exactly right. I'm only copying the deliverables fields but the
issue is all five fields may or may not have values and that's okay. I want
the fields that do not have values to be ignored when i duplicate the record.

Let me see if I understand this right:


You have a switchboard.
You press a button and a form, "Contract Profile", opens
You select a record (profile) and press another button,

which opens another form, "Report Cards" filtered by [ID]

You select a record on form "Report Cards" to duplicate
and press the duplicate button

Is this right?

One other question, do you want to copy the entire record or specific
fields? (like 3 out of 5)


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

The form is a subform and i have the code below on the click event to open
the form

On Error GoTo Err_Deliverablesbt_Click
If IsNull(Me![ID]) Then
MsgBox "Enter Contract information before viewing Report Cards form."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] =
Forms![Contract Profile]![ID]"
End If

Exit_Deliverablesbt_Click:
Exit Sub

Err_Deliverablesbt_Click:
MsgBox Err.Description
Resume Exit_Deliverablesbt_Click

Once i am on the subform i have a duplicate record button and the on click
event for this command I copied and paste the code from your post

'----snip--------
'----snip--------

When i step through i do not get an error message. Its only when i am going
through the normal route to access the form and duplicate the record that i
get the error.

 

[SteveS]

Ray,

OK, so far you don't have a form/subform. Just one form opening another form.
A subform is a form embedded in a form using the subform control.


Question: When you open the form "Report Cards", are there any records in
the form?

In looking at the code for the button on the form "Contract Profile" that
opens the form "Report Cards", you should change the line

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = Forms![Contract
Profile]![ID]"

to

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = " &
Forms![Contract Profile]![ID]"

(Note the " & in the above line.)



----
Also, instead of all the code to copy the record, you can use just three
lines:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend


If there is a button named "btncopy", this is what the code, with error
handling, would look like:

Private Sub btncopy_Click()
On Error GoTo Err_btncopy_Click

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Exit_btncopy_Click:
Exit Sub

Err_btncopy_Click:
MsgBox Err.Description
Resume Exit_btncopy_Click

End Sub


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

SteveS, i really appreaciate your help...

Yes, that is exactly right. I'm only copying the deliverables fields but the
issue is all five fields may or may not have values and that's okay. I want
the fields that do not have values to be ignored when i duplicate the record.

Let me see if I understand this right:


You have a switchboard.
You press a button and a form, "Contract Profile", opens
You select a record (profile) and press another button,

which opens another form, "Report Cards" filtered by [ID]

You select a record on form "Report Cards" to duplicate
and press the duplicate button

Is this right?

One other question, do you want to copy the entire record or specific
fields? (like 3 out of 5)


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

The form is a subform and i have the code below on the click event to open
the form

On Error GoTo Err_Deliverablesbt_Click
If IsNull(Me![ID]) Then
MsgBox "Enter Contract information before viewing Report Cards form."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] =
Forms![Contract Profile]![ID]"
End If

Exit_Deliverablesbt_Click:
Exit Sub

Err_Deliverablesbt_Click:
MsgBox Err.Description
Resume Exit_Deliverablesbt_Click

Once i am on the subform i have a duplicate record button and the on click
event for this command I copied and paste the code from your post

'----snip--------
'----snip--------

When i step through i do not get an error message. Its only when i am going
through the normal route to access the form and duplicate the record that i
get the error.

 

[Ray]

Question: When you open the form "Report Cards", are there any records in

the form?

Yes, the Report Card form will open up and show all of the report cards
related to that Contract Profile and they are connected through a one-to-many
relaitionship by the ID.

I'll try you rsuggestions and see what i get. Thanks...

Ray,

OK, so far you don't have a form/subform. Just one form opening another form.
A subform is a form embedded in a form using the subform control.


Question: When you open the form "Report Cards", are there any records in
the form?

In looking at the code for the button on the form "Contract Profile" that
opens the form "Report Cards", you should change the line

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = Forms![Contract
Profile]![ID]"

to

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = " &
Forms![Contract Profile]![ID]"

(Note the " & in the above line.)



----
Also, instead of all the code to copy the record, you can use just three
lines:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend


If there is a button named "btncopy", this is what the code, with error
handling, would look like:

Private Sub btncopy_Click()
On Error GoTo Err_btncopy_Click

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Exit_btncopy_Click:
Exit Sub

Err_btncopy_Click:
MsgBox Err.Description
Resume Exit_btncopy_Click

End Sub


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

SteveS, i really appreaciate your help...

Yes, that is exactly right. I'm only copying the deliverables fields but the
issue is all five fields may or may not have values and that's okay. I want
the fields that do not have values to be ignored when i duplicate the record.

Let me see if I understand this right:


You have a switchboard.
You press a button and a form, "Contract Profile", opens
You select a record (profile) and press another button,

which opens another form, "Report Cards" filtered by [ID]

You select a record on form "Report Cards" to duplicate
and press the duplicate button

Is this right?

One other question, do you want to copy the entire record or specific
fields? (like 3 out of 5)


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

The form is a subform and i have the code below on the click event to open
the form

On Error GoTo Err_Deliverablesbt_Click
If IsNull(Me![ID]) Then
MsgBox "Enter Contract information before viewing Report Cards form."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] =
Forms![Contract Profile]![ID]"
End If

Exit_Deliverablesbt_Click:
Exit Sub

Err_Deliverablesbt_Click:
MsgBox Err.Description
Resume Exit_Deliverablesbt_Click

Once i am on the subform i have a duplicate record button and the on click
event for this command I copied and paste the code from your post


'----snip--------
'----snip--------

When i step through i do not get an error message. Its only when i am going
through the normal route to access the form and duplicate the record that i
get the error.

 

[Ray]

Okay i tried to input the code you gave

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = Forms![Contract
Profile]![ID]"

and i got an error message "syntax error in string in query expression
'[Report Cards]![ID] = " & Forms![Contract Profile]![ID]'

I replace that with my old code to get back into the form and the code that
i placed on the button that duplicates the report card gave me an error
saying "Cannot

"The command or action "PasteAppend' ins't available now"

Any suggestions?

Ray,

OK, so far you don't have a form/subform. Just one form opening another form.
A subform is a form embedded in a form using the subform control.


Question: When you open the form "Report Cards", are there any records in
the form?

In looking at the code for the button on the form "Contract Profile" that
opens the form "Report Cards", you should change the line

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = Forms![Contract
Profile]![ID]"

to

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = " &
Forms![Contract Profile]![ID]"

(Note the " & in the above line.)



----
Also, instead of all the code to copy the record, you can use just three
lines:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend


If there is a button named "btncopy", this is what the code, with error
handling, would look like:

Private Sub btncopy_Click()
On Error GoTo Err_btncopy_Click

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Exit_btncopy_Click:
Exit Sub

Err_btncopy_Click:
MsgBox Err.Description
Resume Exit_btncopy_Click

End Sub


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

SteveS, i really appreaciate your help...

Yes, that is exactly right. I'm only copying the deliverables fields but the
issue is all five fields may or may not have values and that's okay. I want
the fields that do not have values to be ignored when i duplicate the record.

Let me see if I understand this right:


You have a switchboard.
You press a button and a form, "Contract Profile", opens
You select a record (profile) and press another button,

which opens another form, "Report Cards" filtered by [ID]

You select a record on form "Report Cards" to duplicate
and press the duplicate button

Is this right?

One other question, do you want to copy the entire record or specific
fields? (like 3 out of 5)


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

The form is a subform and i have the code below on the click event to open
the form

On Error GoTo Err_Deliverablesbt_Click
If IsNull(Me![ID]) Then
MsgBox "Enter Contract information before viewing Report Cards form."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] =
Forms![Contract Profile]![ID]"
End If

Exit_Deliverablesbt_Click:
Exit Sub

Err_Deliverablesbt_Click:
MsgBox Err.Description
Resume Exit_Deliverablesbt_Click

Once i am on the subform i have a duplicate record button and the on click
event for this command I copied and paste the code from your post


'----snip--------
'----snip--------

When i step through i do not get an error message. Its only when i am going
through the normal route to access the form and duplicate the record that i
get the error.

 

[SteveS]

Ray,

In the IDE, do a DEBUG / COMPILE.

Then in the main database window, do a COMPACT and REPAIR.

Other than that, I am at a loss.

I made two tables and two forms. In the form "Report Cards", I have two
buttons to run both versions of the code. Both versions duplicate records
with no problems.


If you want, make a copy of the mdb, strip out the reports and unnecessary
forms, queries, and tables. Compact and repair, then WinZip it and email it
to me. Also , change any senstitve info. Maybe I will be able to see a
problem.


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Okay i tried to input the code you gave

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = Forms![Contract
Profile]![ID]"

and i got an error message "syntax error in string in query expression
'[Report Cards]![ID] = " & Forms![Contract Profile]![ID]'

I replace that with my old code to get back into the form and the code that
i placed on the button that duplicates the report card gave me an error
saying "Cannot

"The command or action "PasteAppend' ins't available now"

Any suggestions?

Ray,

OK, so far you don't have a form/subform. Just one form opening another form.
A subform is a form embedded in a form using the subform control.


Question: When you open the form "Report Cards", are there any records in
the form?

In looking at the code for the button on the form "Contract Profile" that
opens the form "Report Cards", you should change the line

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = Forms![Contract
Profile]![ID]"

to

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = " &
Forms![Contract Profile]![ID]"

(Note the " & in the above line.)



----
Also, instead of all the code to copy the record, you can use just three
lines:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend


If there is a button named "btncopy", this is what the code, with error
handling, would look like:

Private Sub btncopy_Click()
On Error GoTo Err_btncopy_Click

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Exit_btncopy_Click:
Exit Sub

Err_btncopy_Click:
MsgBox Err.Description
Resume Exit_btncopy_Click

End Sub


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

SteveS, i really appreaciate your help...

Yes, that is exactly right. I'm only copying the deliverables fields but the
issue is all five fields may or may not have values and that's okay. I want
the fields that do not have values to be ignored when i duplicate the record.

:

Let me see if I understand this right:


You have a switchboard.
You press a button and a form, "Contract Profile", opens
You select a record (profile) and press another button,

which opens another form, "Report Cards" filtered by [ID]

You select a record on form "Report Cards" to duplicate
and press the duplicate button

Is this right?

One other question, do you want to copy the entire record or specific
fields? (like 3 out of 5)


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

The form is a subform and i have the code below on the click event to open
the form

On Error GoTo Err_Deliverablesbt_Click
If IsNull(Me![ID]) Then
MsgBox "Enter Contract information before viewing Report Cards form."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] =
Forms![Contract Profile]![ID]"
End If

Exit_Deliverablesbt_Click:
Exit Sub

Err_Deliverablesbt_Click:
MsgBox Err.Description
Resume Exit_Deliverablesbt_Click

Once i am on the subform i have a duplicate record button and the on click
event for this command I copied and paste the code from your post


'----snip--------
'----snip--------

When i step through i do not get an error message. Its only when i am going
through the normal route to access the form and duplicate the record that i
get the error.

 

[Ray]

Found the problem, i had a hidden form that was causing the issue. Once i
corected that the code worked like a charm.

Thanks again for your help and patience!

Ray

Ray,

In the IDE, do a DEBUG / COMPILE.

Then in the main database window, do a COMPACT and REPAIR.

Other than that, I am at a loss.

I made two tables and two forms. In the form "Report Cards", I have two
buttons to run both versions of the code. Both versions duplicate records
with no problems.


If you want, make a copy of the mdb, strip out the reports and unnecessary
forms, queries, and tables. Compact and repair, then WinZip it and email it
to me. Also , change any senstitve info. Maybe I will be able to see a
problem.


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Okay i tried to input the code you gave

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = Forms![Contract
Profile]![ID]"

and i got an error message "syntax error in string in query expression
'[Report Cards]![ID] = " & Forms![Contract Profile]![ID]'

I replace that with my old code to get back into the form and the code that
i placed on the button that duplicates the report card gave me an error
saying "Cannot

"The command or action "PasteAppend' ins't available now"

Any suggestions?

Ray,

OK, so far you don't have a form/subform. Just one form opening another form.
A subform is a form embedded in a form using the subform control.


Question: When you open the form "Report Cards", are there any records in
the form?

In looking at the code for the button on the form "Contract Profile" that
opens the form "Report Cards", you should change the line

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = Forms![Contract
Profile]![ID]"

to

DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] = " &
Forms![Contract Profile]![ID]"

(Note the " & in the above line.)



----
Also, instead of all the code to copy the record, you can use just three
lines:

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend


If there is a button named "btncopy", this is what the code, with error
handling, would look like:

Private Sub btncopy_Click()
On Error GoTo Err_btncopy_Click

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Exit_btncopy_Click:
Exit Sub

Err_btncopy_Click:
MsgBox Err.Description
Resume Exit_btncopy_Click

End Sub


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

SteveS, i really appreaciate your help...

Yes, that is exactly right. I'm only copying the deliverables fields but the
issue is all five fields may or may not have values and that's okay. I want
the fields that do not have values to be ignored when i duplicate the record.

:

Let me see if I understand this right:


You have a switchboard.
You press a button and a form, "Contract Profile", opens
You select a record (profile) and press another button,

which opens another form, "Report Cards" filtered by [ID]

You select a record on form "Report Cards" to duplicate
and press the duplicate button

Is this right?

One other question, do you want to copy the entire record or specific
fields? (like 3 out of 5)


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

The form is a subform and i have the code below on the click event to open
the form

On Error GoTo Err_Deliverablesbt_Click
If IsNull(Me![ID]) Then
MsgBox "Enter Contract information before viewing Report Cards form."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Report Cards", , , "[Report Cards]![ID] =
Forms![Contract Profile]![ID]"
End If

Exit_Deliverablesbt_Click:
Exit Sub

Err_Deliverablesbt_Click:
MsgBox Err.Description
Resume Exit_Deliverablesbt_Click

Once i am on the subform i have a duplicate record button and the on click
event for this command I copied and paste the code from your post


'----snip--------
'----snip--------

When i step through i do not get an error message. Its only when i am going
through the normal route to access the form and duplicate the record that i
get the error.