Unable to Select Record

  • Thread starter Thread starter Anne
  • Start date Start date
A

Anne

Using:

Forms!sbfrmItemInventory.SetFocus
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

To select a record and copy it. I get the error
message "the command or action PasteAppend is not
available now".

Can anyone tell me what I'm doing wrong? I've used this
before with no problems.

Thanks in advance for your help...
 
Anne said:
Using:

Forms!sbfrmItemInventory.SetFocus
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

To select a record and copy it. I get the error
message "the command or action PasteAppend is not
available now".

Can anyone tell me what I'm doing wrong? I've used this
before with no problems.

Thanks in advance for your help...
Click to expand...

Does the name "sbfrmItemInventory" reflect the fact that
sbfrmItemInventory is a subform? If so, then unless it's opened in a
standalone window as a main form, it is not a member of the Forms
collection and you can't set focus to it in quite the way you're
attempting.

Where is this code executing? Is it behind a command button on
sbfrmItemInventory? On the parent form on which sbfrmItemInventory is a
subform? Somewhere else?
 
No, sbfrmItemInventory is a pop-up form that is associated
with the main form, so the reference is accurate.

Yes, the code is on sbfrmItemInventory, on a command
button.

The entire code is:

Private Sub cmdSeparate_Click()
Me!txtNoItems = Forms!sbfrmItem!Qty
Me!txtNoItems.SetFocus
CreateRecords (Me!txtNoItems)
End Sub

Private Function CreateRecords(noRecsString As String)
On Error GoTo main_Err
Dim A, noRecs As Integer

noRecs = Val(noRecsString) - 1

If noRecs = 1 Then
Exit Function
End If

If noRecs < 1 Then
MsgBox "Error - you must enter a quantity under Item
Entry."
Exit Function
End If

If noRecs > 50 Then
MsgBox "A safety mechanism prevents the creation of
this many records. Try a smaller number."
Exit Function
End If

DoCmd.RunCommand acCmdSelectRecord

For A = 1 To noRecs
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPasteAppend

Next A
Exit Function
main_Err:
MsgBox Error$
End Function
 
No, sbfrmItemInventory is a pop-up form that is associated
with the main form, so the reference is accurate.

Yes, the code is on sbfrmItemInventory, on a command
button.

The entire code is:

Private Sub cmdSeparate_Click()
Me!txtNoItems = Forms!sbfrmItem!Qty
Me!txtNoItems.SetFocus
CreateRecords (Me!txtNoItems)
End Sub

Private Function CreateRecords(noRecsString As String)
On Error GoTo main_Err
Dim A, noRecs As Integer

noRecs = Val(noRecsString) - 1

If noRecs = 1 Then
Exit Function
End If

If noRecs < 1 Then
MsgBox "Error - you must enter a quantity under Item
Entry."
Exit Function
End If

If noRecs > 50 Then
MsgBox "A safety mechanism prevents the creation of
this many records. Try a smaller number."
Exit Function
End If

DoCmd.RunCommand acCmdSelectRecord

For A = 1 To noRecs
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPasteAppend

Next A
Exit Function
main_Err:
MsgBox Error$
End Function
Click to expand...

I was confused by your prefix "sbfrm", which most people use to
designate a subform. But if you're using that for a main form, popup or
no, that's not the source of your problem.

However, the code snippet you originally posted as having the problem is
not present in the code you have now posted. My guess is that was your
attempt to simplify the problem, but it was misleading. I'm going to
assume that the code you posted more recently is the true code. I do
see a few problems in this code, so I'll post -- untested -- a modified
version of the code that I'd like you to try:

'----- start of suggested code -----
Private Sub cmdSeparate_Click()

If Me.NewRecord then
If Me.Dirty Then
RunCommand acCmdSaveRecord
Else
MsgBox "Can't duplicate an empty record!"
Exit Sub
End If
End If

Me!txtNoItems = Forms!sbfrmItem!Qty
Me!txtNoItems.SetFocus '** DG Note -- don't see why you need this.
CreateRecords (Me!txtNoItems)

End Sub

Private Function CreateRecords(noRecsString As String)

On Error GoTo main_Err

Dim A As Integer
Dim noRecs As Integer

noRecs = Val(noRecsString) - 1

If noRecs = 1 Then
Exit Function
End If

If noRecs < 1 Then
MsgBox "Error - you must enter a quantity under Item Entry."
Exit Function
End If

If noRecs > 50 Then
MsgBox "A safety mechanism prevents the creation of " & _
"this many records. Try a smaller number."
Exit Function
End If

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy

For A = 1 To noRecs
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPasteAppend
Next A

main_Exit:
Exit Function

main_Err:
MsgBox Error$
Resume main_Exit

End Function
'----- end of suggested code -----
 
Thanks for revising this.

Now, the error message "Microsoft Access can't find the
field forms referred to in your expression."

occurs at this line:

DoCmd.RunCommand acCmdSelectRecord

As you know, nowhere is "forms" referred to here. What do
you think? Demonic possession?
 
Anne said:
Thanks for revising this.

Now, the error message "Microsoft Access can't find the
field forms referred to in your expression."

occurs at this line:

DoCmd.RunCommand acCmdSelectRecord

As you know, nowhere is "forms" referred to here. What do
you think? Demonic possession?
Click to expand...

That would be my bet, yes. :-)

Hmm, that's very odd. Does the form's recordsource query have a
"Forms!" reference in it? Does the form have calculated controls that
use a reference to the Forms collection?
 
Back
Top