COPY A RECORDSET WILL NO LONGER COPY!

[Geir Baardsen]

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.CancelEve nt
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.RecordsetClo ne
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,D iscount,UnitPri ce,Fee,InvoiceI D) " & _
"SELECT ItemID,Amount,D iscount,UnitPri ce,Fee," & lngNewOrderID & _
'Give a space to end lngNewOrderID
" FROM tblOrderDetails " & _
"WHERE InvoiceID= " & Me!InvoiceID

CurrentDb.Execu te 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...Ooo hh...the bells, the bells, Are they ringing for me?

[MacDermott]

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 - ToggleBreakPoin t 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.goo gle.com...

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.CancelEve nt
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.RecordsetClo ne
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,D iscount,UnitPri ce,Fee,InvoiceI D) " & _
"SELECT ItemID,Amount,D iscount,UnitPri ce,Fee," & lngNewOrderID & _
'Give a space to end lngNewOrderID
" FROM tblOrderDetails " & _
"WHERE InvoiceID= " & Me!InvoiceID

CurrentDb.Execu te 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...Ooo hh...the bells, the bells, Are they ringing for

me?

['69 Camaro]

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********@nos pam.com> wrote in message
news:pE******** *******@newsrea d2.news.atl.ear thlink.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 - ToggleBreakPoin t 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.goo gle.com...

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.CancelEve nt
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.RecordsetClo ne
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,D iscount,UnitPri ce,Fee,InvoiceI D) " & _
"SELECT ItemID,Amount,D iscount,UnitPri ce,Fee," & lngNewOrderID & _
'Give a space to end lngNewOrderID
" FROM tblOrderDetails " & _
"WHERE InvoiceID= " & Me!InvoiceID

CurrentDb.Execu te 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...Ooo hh...the bells, the bells, Are they ringing

for me?

[Geir Baardsen]

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 >>>>>>>'simplic ity 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.RecordsetClo ne
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,D iscount,UnitPri ce,Fee,InvoiceI D) " & _
"SELECT ItemID,Amount,D iscount,UnitPri ce,Fee," & lngNewOrderID & _
'Give a space to end lngNewOrderID
" FROM tblOrderDetails " & _
"WHERE InvoiceID= " & Me!InvoiceID
>>>'And here I get: "False"
CurrentDb.Execu te sqlNy, dbFailOnError
>>'And here I get: ? CurrentDb.Execu te sqlNy, dbFailOnError "Argument not >>>>>>>'optiona l"
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. :-)

[david epsom dot com dot au]

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.goog le.com...

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
>>>'simplic ity 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.RecordsetClo ne
> 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,D iscount,UnitPri ce,Fee,InvoiceI D) " & _
> "SELECT ItemID,Amount,D iscount,UnitPri ce,Fee," & lngNewOrderID & _
> 'Give a space to end lngNewOrderID
> " FROM tblOrderDetails " & _
> "WHERE InvoiceID= " & Me!InvoiceID
>>>>'And here I get: "False"
> CurrentDb.Execu te sqlNy, dbFailOnError
>>>'And here I get: ? CurrentDb.Execu te sqlNy, dbFailOnError "Argument

not >>>>>>>'optiona l" > 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. :-)

[MacDermott]

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.goog le.com...

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
>>>'simplic ity 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.RecordsetClo ne
> 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,D iscount,UnitPri ce,Fee,InvoiceI D) " & _
> "SELECT ItemID,Amount,D iscount,UnitPri ce,Fee," & lngNewOrderID & _
> 'Give a space to end lngNewOrderID
> " FROM tblOrderDetails " & _
> "WHERE InvoiceID= " & Me!InvoiceID
>>>>'And here I get: "False"
> CurrentDb.Execu te sqlNy, dbFailOnError
>>>'And here I get: ? CurrentDb.Execu te sqlNy, dbFailOnError "Argument

not >>>>>>>'optiona l" > 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. :-)

[Geir Baardsen]

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********@nos pam.com> wrote in message news:<Z2******* *********@newsr ead2.news.atl.e arthlink.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.goog le.com...

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>>>>'simplic ity 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.RecordsetClo ne
> > 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,D iscount,UnitPri ce,Fee,InvoiceI D) " & _
> > "SELECT ItemID,Amount,D iscount,UnitPri ce,Fee," & lngNewOrderID & _
> > 'Give a space to end lngNewOrderID
> > " FROM tblOrderDetails " & _
> > "WHERE InvoiceID= " & Me!InvoiceID
> >>>>'And here I get: "False" CurrentDb.Execu te sqlNy, dbFailOnError > >>>'And here I get: ? CurrentDb.Execu te sqlNy, dbFailOnError "Argument not >>>>>>>'optiona l" > > End If
> >