Copy a recordset will now copy - almost!

[Geir Baardsen]

Hi!
As you can see I have worked with my code and done some changes. Now
the code will execute, because of this, and because I took away some
code in the OnCurrent event that was probably causing a clash. It does
in fact copy the new Invoice number and the data from tblOrderDetails ,
but still missing is the CustID-field, that it refuses to copy. The
CustID-field is a Combobox on frmOrders, and is by no way locked or...

I again have double-checked the name and field-properties, so as to
avoid naming confuses and so on...All fields in forms now has the same
names as underlying tables.

Dim rs As DAO.Recordset
Dim sqlNew As String
Dim lngNewOrderID As Long
Dim MyMsg As String, MyTtl As String
Dim MyBtn As Integer, RetVal As Integer

MyMsg = "You are about to copy this invoice." & Chr(13)
MyMsg = MyMsg & "All data will be copied to new Invoice," & Chr(13)
MyMsg = MyMsg & "and have a new invoice number." & Chr(13)
MyMsg = MyMsg & Chr(13)
MyMsg = MyMsg & "Sure you will continue?"
MyBtn = 292 'A "YES/NO" button and "NO"button is default
MyTtl = "COPY INVOICE?"
RetVal = MsgBox(MyMsg, MyBtn, MyTtl)

Select Case RetVal

Case 7
DoCmd.CancelEve nt
Me!lblInfo.Capt ion = "COPY ABORTED!"

Case 6
Me.Refresh
Set rs = Me.RecordsetClo ne

With rs
.AddNew
!CustID = Me!CustID 'It will not copy this field.
!MyDate = Now 'Date is being set ok
!MyTime = Time 'Time is being set ok
.Update
End With
rs.Bookmark = rs.LastModified
lngNewOrderID = rs!InvoiceID

sqlNew = "INSERT INTO
tblOrderDetails (ItemID,Amount, Discount,Price, Fee,InvoiceID) " & _
"SELECT ItemID,Amount,D iscount,Price,F ee," & lngNewOrderID & _
" FROM tblOrderDetails " & _
"WHERE InvoiceID = " & Me!InvoiceID

'I'm happy because the sqlNew is being copied

CurrentDb.Execu te sqlNew, dbFailOnError

End Select

I'm not sure if there is a difference from win98 to win 2000/win XP,
but I remember that this calculation (under win98):

=Sum(Nz(([Price]*[Amount]*(1-[Discount])/100)*100))

had to be changed (under win 2000/win XP) to:

=Sum(Nz([Price]*[Amount]*[Discount]))

in order to work properly. I don't know why. Do You?

However, I'll keep on working! :-)

[Keith Wilby]

ge***********@h otmail.com (Geir Baardsen) wrote:

!CustID = Me!CustID 'It will not copy this field.

Just a thought, but have you tried naming your control something other than
the field name (eg "cboCustID" )? Having the names the same may be the
cause of some confusion.

!CustID = Me!cboCustID

Regards,
Keith.