Run-time error '13' Type mismatch (1 Viewer)

[paul young]

I created a Duplicate command/macro on a form and, when it is clicked on, a message appeared 'Run-time error '13' Type mismatch'. Debugging the command showed the problem was this code line:

MsgBox Err.Number, Err.Description

in the bDuplicateCurrentRecord section (shown below).

Below is the whole Duplicate coding. The coding was copied from another Duplicate template db as I am no expert on coding. It worked on 97 version but not on 2000 version.

Private Sub bDuplicateCurrentRecord_Click()
On Error GoTo Err_bDuplicateCurrentRecord_Click

Beep

Dim sBookingID As String
Dim sSerialNo As String
Dim sNameOfHost As String
Dim sTrustOrNonTrust As String
Dim sDepartment As String
Dim sContactTelephone As String
Dim sContactMobile As String
Dim sContactFax As String
Dim sDateOfRequest As String
Dim sDateOfFunction As String
Dim sTimeRequiredStart As String
Dim sTimeRequiredEnd As String
Dim sVenue As String
Dim sReasonForBooking As String
Dim sNoOfGuests As String
Dim sStandingOrder As String
Dim sComments As String
Dim sBookingReceivedBy As String
Dim sNonStandardCharge As String
Dim sTrustTotalCost As String
Dim sNonTrustTotalCost As String
Dim sWhenPaymentMade As String
Dim sPaymentMethod As String

sBookingID = BookingID
sSerialNo = SerialNo
sNameOfHost = NameOfHost
sTrustOrNonTrust = TrustOrNonTrust
sDepartment = Department
sContactTelephone = ContactTelephone
sContactMobile = ContactMobile
sContactFax = ContactFax
sDateOfRequest = DateOfRequest
sDateOfFunction = DateOfFunction
sTimeRequiredStart = TimeRequiredStart
sTimeRequiredEnd = TimeRequiredEnd
sVenue = Venue
sReasonForBooking = ReasonForBooking
sNoOfGuests = NoOfGuests
sStandingOrder = StandingOrder
sComments = Comments
sBookingReceivedBy = BookingReceivedBy
sNonStandardCharge = NonStandardCharge
sTrustTotalCost = TrustTotalCost
sNonTrustTotalCost = NonTrustTotalCost
sWhenPaymentMade = WhenPaymentMade
sPaymentMethod = PaymentMethod

DoCmd.GoToRecord , , acNewRec

BookingID = sBookingID
SerialNo = sSerialNo
NameOfHost = sNameOfHost
TrustOrNonTrust = sTrustOrNonTrust
Department = sDepartment
ContactTelephone = sContactTelephone
ContactMobile = sContactMobile
ContactFax = sContactFax
DateOfRequest = sDateOfRequest
DateOfFunction = sDateOfFunction
TimeRequiredStart = sTimeRequiredStart
TimeRequiredEnd = sTimeRequiredEnd
Venue = sVenue
ReasonForBooking = sReasonForBooking
NoOfGuests = sNoOfGuests
StandingOrder = sStandingOrder
Comments = sComments
BookingReceivedBy = sBookingReceivedBy
NonStandardCharge = sNonStandardCharge
TrustTotalCost = sTrustTotalCost
NonTrustTotalCost = sNonTrustTotalCost
WhenPaymentMade = sWhenPaymentMade
PaymentMethod = sPaymentMethod

sBookingID = ""
sSerialNo = ""
sNameOfHost = ""
sTrustOrNonTrust = ""
sDepartment = ""
sContactTelephone = ""
sContactMobile = ""
sContactFax = ""
sDateOfRequest = ""
sDateOfFunction = ""
sTimeRequiredStart = ""
sTimeRequiredEnd = ""
sVenue = ""
sReasonForBooking = ""
sNoOfGuests = ""
sStandingOrder = ""
sComments = ""
sBookingReceivedBy = ""
sNonStandardCharge = ""
sTrustTotalCost = ""
sNonTrustTotalCost = ""
sWhenPaymentMade = ""
sPaymentMethod = ""

Exit_bDuplicateCurrentRecord_Click:
Exit Sub

Err_bDuplicateCurrentRecord_Click:
If Err = 94 Then 'Invalid use of Null'
Beep
MsgBox "A box is incomplete. Please ensure that all boxes are completed before attempting to duplicate the current booking.", vbInformation, "Invalid Duplication"
Exit Sub
ElseIf Err = 2113 Then 'The data you entered is not valid for this box.
Exit Sub
Else
MsgBox Err.Number, Err.Description
Resume Exit_bDuplicateCurrentRecord_Click
End If

End Sub


Private Sub bDeleteCurrentRecord_Click()
On Error GoToErr_bDeleteCurrentRecord_Click

Beep
If MsgBox("Are you sure you want to delete the current record?", vbQuestion + vbYesNo, "Delete Current Record?") = vbYes Then
DoCmd.RunCommand ac CmdDeleteRecord
End If

Exit_bDuplicateCurrentRecord_Click:
Exit Sub

Err_bDeleteCurrentRecord_Click:
If Err=2501 The 'The RunCommand action was cancelled
Exit Sub
Else
MsgBox Err.Number, Err.Description
Resume Exit_bDeleteCurrentRecord_Click
End If

End Sub

Can anyone help?

Thanks.

Paul

 

[RoyVidar]

The second arguement of the msgbox method, is the VbMsgBoxStyle (buttons, type...). Here you probably have an error in the code somewhere, then you trigger a new error within the errorhandling. To get the errormessage, try concatenating the number and message:

msgbox err.number & " - " & err.description

To find out where the real error occurs, drop the errorhandling while debugging (comment the line On Error GoTo Err_bDuplicateCurrentRecord_Click), cause I'm a bit to lazy to go through the code looking for possible culprits, though Null errors might be one

 

[paul young]

Run-time error '13'

Thanks for your reply.

As I am no expert in coding but I amended the line to:

MsgBox Err.Number & "-" & Err.Description

A new message appeared:

-2147352567-You can't assign a value to this object

This suggests that a field data type is not a number type. Correct?

Paul

 

[RoyVidar]

Comment the errorhandling, as described, then tell which line bombs. Then tell what kind of object it is.

Are all those thingies form controls? Is any of them bound to a AutoNumber field? You're treating all of them as strings, could any of them be numeric or date?

 

[paul young]

Error

The BookingID is an autonumber field. The database in question is a function booking. For repeated bookings, I created a duplicate command to copy a record and create a new one with a new next autonumber.

Some fields are text or date, so maybe the string coding procedure would not work properly.

Paul

 

[RoyVidar]

WHICH LINE DOES IT BOMB ON?

You can't assign a value to an autonumber, but on my setup, that gives the 2448 error.

 

[Mile-O]

What do you have AllowNullValues for each field set to?

Also, why are you doing this?

 

[paul young]

As I said I am no expert on coding. I found this duplicate sample db on the internet and changed the fields, etc to accomodate with the fields of my database.

It worked on 97 version but I hit problems in 2000 version.

Using the simple duplicate wizard does not provide the solution I am looking for.

Paul

 

[Mile-O]

Okay, rewind, what is the solution you are looking for?

 

[paul young]

Error - explanation

I am creating a Function Booking database with a form showing function details such as venue, date, menu selected. Each booking record (BookingID) is an Autonumber field, purely to keep each record unique. A function can be repeated on a weekly basis. So without having to type the same function booking details again, I just duplicate the booking and change the date only (and maybe other small details). The duplicated record would generate a new autonumber automatically. That is my aim.

Hope this is clear.

Thanks.

Paul

 

[Mile-O]

Public Function DuplicateRecord(ByVal lngBooking As Long) As Boolean

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("SELECT * FROM MyTable WHERE BookingID = " & lngBooking & ";")
    Set rs2 = db.OpenRecordset("MyTable")

    With rs2
        .AddNew
        .Fields("DateField") = DateAdd("d", 7, rs1.Fields("DateField"))
        .Update
        .Close
    End With
    rs1.Close

    db.Close  

    DuplicateRecord = True

Exit_DuplicateRecord:
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
    Exit Function

Err_DuplicateRecord:
    DuplicateRecord = False
    Resume Exit_DuplicateRecord

End Function

 

[paul young]

Duplicate record

I debugged it and an error message appeared for the first line, Public Function DuplicateRecord.....

Compile error:
Member already exists in an object module from which this object module derives

Paul

 

[Mile-O]

Where did you put the code? It must go in a standalone module - not that of a form, report, or a standalone Class module.

Also, DuplicateRecord may be a reserved word - can't think just now. Might be worth it to change all instances to DupRecord or something.

 

[paul young]

Do you mean attach the code to a macro, instead of attaching to a 'On Click' or [Event Procedure] of a command button? The code would be treated as a standalone, rather attached to a form?

Paul

 

[Mile-O]

No, in the database window, switch to the Modules tab, create a new module, and paste the code into this.

On your form, you can call the function by passing the booking ID you want to duplicate:

ie..

If DuplicateRecord(Me.BookingID) Then
    MsgBox "Record duplicated.", vbInformation
Else
    MsgBox "Duplication Failed.", vbExclamation
End If

 

[paul young]

Can you help to finish it off / run the code? Click on the Duplicate button on the Booking form.

Thanks.

Paul