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

Copy a recordset will now copy - almost!

P: n/a
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.CancelEvent
Me!lblInfo.Caption = "COPY ABORTED!"

Case 6
Me.Refresh
Set rs = Me.RecordsetClone

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,I nvoiceID) " & _
"SELECT ItemID,Amount,Discount,Price,Fee," & lngNewOrderID & _
" FROM tblOrderDetails " & _
"WHERE InvoiceID = " & Me!InvoiceID

'I'm happy because the sqlNew is being copied

CurrentDb.Execute 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! :-)
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
ge***********@hotmail.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.
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.