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

COPY A RECORDSET WILL NO LONGER COPY!

P: n/a
Hi!
This is a routine for copying a recordset into a new order.
It has worked fine under Win98. However, my client has changed to
Win XP, and suddenly it doesn't work anymore. I keep getting the
following error message: Object is not in collection.

So I do a very thorough check and doublecheck of fieldnames from underlying
table, tblOrderDetails. I even write them down in NotePad, and compares them...
but there is no difference. I even check the table and the forms controlsource
and the fields controlsource, names etc., almost everything else I can think of...

Could this be due to win XP? Have even tested with Win 2000, and the same
thing happens. Anybody that can tell me what I'm missing?

Here's the routine, originally suggested by A. Kallal, and it
has been going steadily under win 98:

Dim rs As DAO.Recordset
'Of course I have the reference to Microsoft DAO 3.6 checked
'and everything else as on the win98 machine...
Dim sqlNew As String
Dim lngNewOrderID As Long

If (Me!chkDeleted.Value = True) Then
MsgBox "YOU ARE NOT ALLOWED TO COPY A DELETED ORDER!"
DoCmd.CancelEvent
ElseIf (Me!chkDeleted.Value = False) Then

If MsgBox("You are about to copy this invoice." & Chr(13) _
& "All data will be copied to new invoice," & Chr(13) _
& "but is given a new invoicenumber." & Chr(13) _
& Chr(13) _
& "Sure you will continue?", vbExclamation + vbYesNo, "COPY INVOICE!") = vbYes Then

Me.Refresh

Set rs = Me.RecordsetClone
With rs
.AddNew
'But no new Customer are added, only new invoicenumber!
!CustID = Me!CustID
.Update
End With

rs.Bookmark = rs.LastModified
lngNewOrderID = rs!InvoiceID

sqlNew = "INSERT INTO tblOrderDetails( _
ItemID,Amount,Discount,UnitPrice,Fee,InvoiceID) " & _
"SELECT ItemID,Amount,Discount,UnitPrice,Fee," & lngNewOrderID & _
'Give a space to end lngNewOrderID
" FROM tblOrderDetails " & _
"WHERE InvoiceID= " & Me!InvoiceID

CurrentDb.Execute sqlNy, dbFailOnError

End If
End If

How can I write an error-trapping code to see what happens after
Me.Refresh
and after the sqlNew-thing?

Questions, questions...Ooohh...the bells, the bells, Are they ringing for me?
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
May I assume that sqlNy is a typo, and that in your code it's sqlNew?

Open your code window, and put your cursor on the line where you want code
execution to break.
Click Debug - ToggleBreakPoint to set a breakpoint.

Run the code.
It will stop at your breakpoint.
Each time you hit the F8 key, another line of code will execute.

While the code is in break mode, you can see the value of many variables
just by hovering your cursor over them in code.
If a variable doesn't respond to this treatment,
copy it to the clipboard,
open the immediate window, (ctl-G)
type ? and paste the variable name
then hit Enter.
The value of the variable should appear in the immediate window.

At the very least, by stepping through with F8, you should be able to
determine which line of code is causing the error.
Post that to this newsgroup, and your chances of getting a helpful reply
will increase greatly!

HTH
- Turtle

"Geir Baardsen" <ge***********@hotmail.com> wrote in message
news:35**************************@posting.google.c om...
Hi!
This is a routine for copying a recordset into a new order.
It has worked fine under Win98. However, my client has changed to
Win XP, and suddenly it doesn't work anymore. I keep getting the
following error message: Object is not in collection.

So I do a very thorough check and doublecheck of fieldnames from underlying table, tblOrderDetails. I even write them down in NotePad, and compares them... but there is no difference. I even check the table and the forms controlsource and the fields controlsource, names etc., almost everything else I can think of...
Could this be due to win XP? Have even tested with Win 2000, and the same
thing happens. Anybody that can tell me what I'm missing?

Here's the routine, originally suggested by A. Kallal, and it
has been going steadily under win 98:

Dim rs As DAO.Recordset
'Of course I have the reference to Microsoft DAO 3.6 checked
'and everything else as on the win98 machine...
Dim sqlNew As String
Dim lngNewOrderID As Long

If (Me!chkDeleted.Value = True) Then
MsgBox "YOU ARE NOT ALLOWED TO COPY A DELETED ORDER!"
DoCmd.CancelEvent
ElseIf (Me!chkDeleted.Value = False) Then

If MsgBox("You are about to copy this invoice." & Chr(13) _
& "All data will be copied to new invoice," & Chr(13) _
& "but is given a new invoicenumber." & Chr(13) _
& Chr(13) _
& "Sure you will continue?", vbExclamation + vbYesNo, "COPY INVOICE!") = vbYes Then
Me.Refresh

Set rs = Me.RecordsetClone
With rs
.AddNew
'But no new Customer are added, only new invoicenumber!
!CustID = Me!CustID
.Update
End With

rs.Bookmark = rs.LastModified
lngNewOrderID = rs!InvoiceID

sqlNew = "INSERT INTO tblOrderDetails( _
ItemID,Amount,Discount,UnitPrice,Fee,InvoiceID) " & _
"SELECT ItemID,Amount,Discount,UnitPrice,Fee," & lngNewOrderID & _
'Give a space to end lngNewOrderID
" FROM tblOrderDetails " & _
"WHERE InvoiceID= " & Me!InvoiceID

CurrentDb.Execute sqlNy, dbFailOnError

End If
End If

How can I write an error-trapping code to see what happens after
Me.Refresh
and after the sqlNew-thing?

Questions, questions...Ooohh...the bells, the bells, Are they ringing for

me?
Nov 13 '05 #2

P: n/a
Hi, Geir.

As Turtle points out, there's a typo in the code that you have posted. I
suspect that you copied and pasted it verbatim from your code module that is
no longer working in Windows XP. If this is the case, then a simple "Option
Explicit" and compile operation would have allowed you to immediately see
the variable where the typo occurred and to quickly fix it.

For Tom Wickerath's recommendations for setting "Option Explicit" to help
you avoid future errors due to typos, as well as other advice on settings
for your VB Editor, please see this Web page:

http://www.access.qbuilt.com/html/ge...tml#VBEOptions

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Any human can read my reply E-mail address and should alter it so that a
message will be forwarded to me. Spammers are free to use my UNALTERED
reply E-mail address. I will *never* get those messages!)
"MacDermott" <ma********@nospam.com> wrote in message
news:pE***************@newsread2.news.atl.earthlin k.net...
May I assume that sqlNy is a typo, and that in your code it's sqlNew?

Open your code window, and put your cursor on the line where you want code
execution to break.
Click Debug - ToggleBreakPoint to set a breakpoint.

Run the code.
It will stop at your breakpoint.
Each time you hit the F8 key, another line of code will execute.

While the code is in break mode, you can see the value of many variables
just by hovering your cursor over them in code.
If a variable doesn't respond to this treatment,
copy it to the clipboard,
open the immediate window, (ctl-G)
type ? and paste the variable name
then hit Enter.
The value of the variable should appear in the immediate window.

At the very least, by stepping through with F8, you should be able to
determine which line of code is causing the error.
Post that to this newsgroup, and your chances of getting a helpful reply
will increase greatly!

HTH
- Turtle

"Geir Baardsen" <ge***********@hotmail.com> wrote in message
news:35**************************@posting.google.c om...
Hi!
This is a routine for copying a recordset into a new order.
It has worked fine under Win98. However, my client has changed to
Win XP, and suddenly it doesn't work anymore. I keep getting the
following error message: Object is not in collection.

So I do a very thorough check and doublecheck of fieldnames from underlying
table, tblOrderDetails. I even write them down in NotePad, and compares

them...
but there is no difference. I even check the table and the forms

controlsource
and the fields controlsource, names etc., almost everything else I can

think of...

Could this be due to win XP? Have even tested with Win 2000, and the same thing happens. Anybody that can tell me what I'm missing?

Here's the routine, originally suggested by A. Kallal, and it
has been going steadily under win 98:

Dim rs As DAO.Recordset
'Of course I have the reference to Microsoft DAO 3.6 checked
'and everything else as on the win98 machine...
Dim sqlNew As String
Dim lngNewOrderID As Long

If (Me!chkDeleted.Value = True) Then
MsgBox "YOU ARE NOT ALLOWED TO COPY A DELETED ORDER!"
DoCmd.CancelEvent
ElseIf (Me!chkDeleted.Value = False) Then

If MsgBox("You are about to copy this invoice." & Chr(13) _
& "All data will be copied to new invoice," & Chr(13) _
& "but is given a new invoicenumber." & Chr(13) _
& Chr(13) _
& "Sure you will continue?", vbExclamation + vbYesNo, "COPY INVOICE!") =

vbYes Then

Me.Refresh

Set rs = Me.RecordsetClone
With rs
.AddNew
'But no new Customer are added, only new invoicenumber!
!CustID = Me!CustID
.Update
End With

rs.Bookmark = rs.LastModified
lngNewOrderID = rs!InvoiceID

sqlNew = "INSERT INTO tblOrderDetails( _
ItemID,Amount,Discount,UnitPrice,Fee,InvoiceID) " & _
"SELECT ItemID,Amount,Discount,UnitPrice,Fee," & lngNewOrderID & _
'Give a space to end lngNewOrderID
" FROM tblOrderDetails " & _
"WHERE InvoiceID= " & Me!InvoiceID

CurrentDb.Execute sqlNy, dbFailOnError

End If
End If

How can I write an error-trapping code to see what happens after
Me.Refresh
and after the sqlNew-thing?

Questions, questions...Ooohh...the bells, the bells, Are they ringing

for me?

Nov 13 '05 #3

P: n/a
Hi!
Thank you for good advices. I did it all. I checked the typo. There's
no typo, just me writing too fast when copying to the group. The code
is as I got it from the suggestion of A.Kallal, so it should work as
under win98. However, I did as McDermot explained , pasted and run in
the immediate window, and here is the result, commented at codelines:
Dim rs As DAO.Recordset
Dim sqlNew As String
>>>'No typo Dim lngNewOrderID As Long
>'Taking away the checking if this order is deleted, for sake of >>>>>>>'simplicity while checking code If MsgBox("You are about to copy this invoice." & Chr(13) _
& "All data will be copied to new invoice," & Chr(13) _
& "but is given a new invoicenumber." & Chr(13) _
& Chr(13) _
& "Sure you will continue?", vbExclamation + vbYesNo, "COPY INVOICE!") = vbYes Then
Me.Refresh
>>'And here I get: ?Me.Refresh "Expected function or variable"
Set rs = Me.RecordsetClone
With rs
.AddNew
'But no new Customer are added, only new invoicenumber!
!CustID = Me!CustID
.Update
End With

rs.Bookmark = rs.LastModified
lngNewOrderID = rs!InvoiceID

sqlNew = "INSERT INTO tblOrderDetails( _
ItemID,Amount,Discount,UnitPrice,Fee,InvoiceID) " & _
"SELECT ItemID,Amount,Discount,UnitPrice,Fee," & lngNewOrderID & _
'Give a space to end lngNewOrderID
" FROM tblOrderDetails " & _
"WHERE InvoiceID= " & Me!InvoiceID
>>>'And here I get: "False"
CurrentDb.Execute sqlNy, dbFailOnError
>>'And here I get: ? CurrentDb.Execute sqlNy, dbFailOnError "Argument not >>>>>>>'optional"
End If


And when setting a breakpoint, the code breaks on the line: Me.Refresh

Thank you for patience. If you think you can help me, please do.
Anyway..I learned to work with the code-editor in a way I never have
done before. :-)
Nov 13 '05 #4

P: n/a
Me.refresh won't work in the immediate window.

"Me" is a reference to a form, and that line will only
work inside the code module of a form.

(david)

"Geir Baardsen" <ge***********@hotmail.com> wrote in message
news:35*************************@posting.google.co m...
Hi!
Thank you for good advices. I did it all. I checked the typo. There's
no typo, just me writing too fast when copying to the group. The code
is as I got it from the suggestion of A.Kallal, so it should work as
under win98. However, I did as McDermot explained , pasted and run in
the immediate window, and here is the result, commented at codelines:
> Dim rs As DAO.Recordset
> Dim sqlNew As String
>>>>'No typo Dim lngNewOrderID As Long
>>'Taking away the checking if this order is deleted, for sake of
>>>'simplicity while checking code If MsgBox("You are about to copy this invoice." & Chr(13) _
> & "All data will be copied to new invoice," & Chr(13) _
> & "but is given a new invoicenumber." & Chr(13) _
> & Chr(13) _
> & "Sure you will continue?", vbExclamation + vbYesNo, "COPY INVOICE!") =
vbYes Then
>
> Me.Refresh
>>>'And here I get: ?Me.Refresh "Expected function or variable"
> Set rs = Me.RecordsetClone
> With rs
> .AddNew
> 'But no new Customer are added, only new invoicenumber!
> !CustID = Me!CustID
> .Update
> End With
>
> rs.Bookmark = rs.LastModified
> lngNewOrderID = rs!InvoiceID
>
> sqlNew = "INSERT INTO tblOrderDetails( _
> ItemID,Amount,Discount,UnitPrice,Fee,InvoiceID) " & _
> "SELECT ItemID,Amount,Discount,UnitPrice,Fee," & lngNewOrderID & _
> 'Give a space to end lngNewOrderID
> " FROM tblOrderDetails " & _
> "WHERE InvoiceID= " & Me!InvoiceID
>>>>'And here I get: "False"
> CurrentDb.Execute sqlNy, dbFailOnError
>>>'And here I get: ? CurrentDb.Execute sqlNy, dbFailOnError "Argument

not >>>>>>>'optional" > End If
>


And when setting a breakpoint, the code breaks on the line: Me.Refresh

Thank you for patience. If you think you can help me, please do.
Anyway..I learned to work with the code-editor in a way I never have
done before. :-)

Nov 13 '05 #5

P: n/a
Just a hunch:

Try substituting vbCrLf for Chr(13) throughout the MsgBox call immediately
preceding Me.Refresh.

HTH
- Turtle

"Geir Baardsen" <ge***********@hotmail.com> wrote in message
news:35*************************@posting.google.co m...
Hi!
Thank you for good advices. I did it all. I checked the typo. There's
no typo, just me writing too fast when copying to the group. The code
is as I got it from the suggestion of A.Kallal, so it should work as
under win98. However, I did as McDermot explained , pasted and run in
the immediate window, and here is the result, commented at codelines:
> Dim rs As DAO.Recordset
> Dim sqlNew As String
>>>>'No typo Dim lngNewOrderID As Long
>>'Taking away the checking if this order is deleted, for sake of
>>>'simplicity while checking code If MsgBox("You are about to copy this invoice." & Chr(13) _
> & "All data will be copied to new invoice," & Chr(13) _
> & "but is given a new invoicenumber." & Chr(13) _
> & Chr(13) _
> & "Sure you will continue?", vbExclamation + vbYesNo, "COPY INVOICE!") =
vbYes Then
>
> Me.Refresh
>>>'And here I get: ?Me.Refresh "Expected function or variable"
> Set rs = Me.RecordsetClone
> With rs
> .AddNew
> 'But no new Customer are added, only new invoicenumber!
> !CustID = Me!CustID
> .Update
> End With
>
> rs.Bookmark = rs.LastModified
> lngNewOrderID = rs!InvoiceID
>
> sqlNew = "INSERT INTO tblOrderDetails( _
> ItemID,Amount,Discount,UnitPrice,Fee,InvoiceID) " & _
> "SELECT ItemID,Amount,Discount,UnitPrice,Fee," & lngNewOrderID & _
> 'Give a space to end lngNewOrderID
> " FROM tblOrderDetails " & _
> "WHERE InvoiceID= " & Me!InvoiceID
>>>>'And here I get: "False"
> CurrentDb.Execute sqlNy, dbFailOnError
>>>'And here I get: ? CurrentDb.Execute sqlNy, dbFailOnError "Argument

not >>>>>>>'optional" > End If
>


And when setting a breakpoint, the code breaks on the line: Me.Refresh

Thank you for patience. If you think you can help me, please do.
Anyway..I learned to work with the code-editor in a way I never have
done before. :-)

Nov 13 '05 #6

P: n/a
Hi, there all!
When putting your head in a bucket of water, sometimes it says 'pling'!

So, here is how I seems to have solved the riddle:

I declared: Dim lngCustID as Long
lngCustID = Me!CustID

and instead of:

With rs
> > .AddNew
> > 'But no new Customer are added, only new invoicenumber!
> > !CustID = Me!CustID
> > .Update
> > End With
I wrote:

With rs > > .AddNew
> > 'Now customer is added!
> > !CustID = lngCustID
> > .Update
> > End With

and now it works. Can U understand it???????????:-)

Thanks for all suggestions!

"MacDermott" <ma********@nospam.com> wrote in message news:<Z2****************@newsread2.news.atl.earthl ink.net>...
Just a hunch:

Try substituting vbCrLf for Chr(13) throughout the MsgBox call immediately
preceding Me.Refresh.

HTH
- Turtle

"Geir Baardsen" <ge***********@hotmail.com> wrote in message
news:35*************************@posting.google.co m...
Hi!
Thank you for good advices. I did it all. I checked the typo. There's
no typo, just me writing too fast when copying to the group. The code
is as I got it from the suggestion of A.Kallal, so it should work as
under win98. However, I did as McDermot explained , pasted and run in
the immediate window, and here is the result, commented at codelines:
> > Dim rs As DAO.Recordset
> > Dim sqlNew As String
>>>>>>'No typo


Dim lngNewOrderID As Long
'Taking away the checking if this order is deleted, for sake of>>>>'simplicity while checking code > If MsgBox("You are about to copy this invoice." & Chr(13) _
> > & "All data will be copied to new invoice," & Chr(13) _
> > & "but is given a new invoicenumber." & Chr(13) _
> > & Chr(13) _
> > & "Sure you will continue?", vbExclamation + vbYesNo, "COPY INVOICE!") =
vbYes Then > >
> > Me.Refresh
> >>>'And here I get: ?Me.Refresh "Expected function or variable"
> > Set rs = Me.RecordsetClone
> > With rs
> > .AddNew
> > 'But no new Customer are added, only new invoicenumber!
> > !CustID = Me!CustID
> > .Update
> > End With
> >
> > rs.Bookmark = rs.LastModified
> > lngNewOrderID = rs!InvoiceID
> >
> > sqlNew = "INSERT INTO tblOrderDetails( _
> > ItemID,Amount,Discount,UnitPrice,Fee,InvoiceID) " & _
> > "SELECT ItemID,Amount,Discount,UnitPrice,Fee," & lngNewOrderID & _
> > 'Give a space to end lngNewOrderID
> > " FROM tblOrderDetails " & _
> > "WHERE InvoiceID= " & Me!InvoiceID
> >>>>'And here I get: "False" CurrentDb.Execute sqlNy, dbFailOnError > >>>'And here I get: ? CurrentDb.Execute sqlNy, dbFailOnError "Argument not >>>>>>>'optional" > > End If
> >

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.