Type Mismatch error in addrecord button ??

Z

Zorkmid

Registered User.
Local time
Today, 07:36
Joined
Mar 3, 2009
Messages
188
Hey guys,

Im trying to build a new database project from scartch and I have copied exactly my code from a similar project (I think.) I cannot find out why the type mismatch error 13 gets thrown at me when I fill out the form and try and add the record.

Code: 
Private Sub cmdAddNew_Click()
    Dim err As Integer
    Dim cnn1 As ADODB.Connection
    Dim tblCase As ADODB.Recordset
    Dim StrCnn As String
 
    '***************************************************
    'Check that all fields have been completed correctly
    '***************************************************
 
    txtName.SetFocus
    If txtName.Text = "" Then
    err = err + 1
    MsgBox "Please enter the name of the subject" & err
    End If
 
    cboInfection.SetFocus
    If cboInfection.Text = "" Then
    err = err + 1
    MsgBox "Please specify infection type." & err
    End If
 
    cboPrecaution.SetFocus
    If cboPrecaution.Text = "" Then
    err = err + 1
    MsgBox "Please specify type of precaution." & err
    End If
 
    cboUnit.SetFocus
    If cboUnit.Text = "" Then
    err = err + 1
    MsgBox "Please specify unit." & err
    End If
 
    txtRoom.SetFocus
    If txtRoom.Text = "" Then
    err = err + 1
    MsgBox "Please specify room number." & err
    End If
 
    txtStartDate.SetFocus
    If txtStartDate.Text = "" Then
    err = err + 1
    MsgBox "Please indicate the date when isolation precautions began."
    End If
 
    txtEndDate.SetFocus
    If txtEndDate.Text = "" Then
    err = err + 1
    MsgBox "Please indicate date when isolation precautions terminated, or subject was discharged, or deceased."
    End If
 
    'If no errors, insert data into table
    If err < 1 Then
 
        'Open a connection
        Set cnn1 = New ADODB.Recordset
        mydb = "G:\Medical\LaboratoryMedicine\InfectionControl\Statistics\Fiscal 2009\DB Conversion (Matt)\Infection Control Stats.mdb"
        StrCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
        cnn1.Open StrCnn
 
        'Open Case Table (tblCase)
        Set tblCase = New ADODB.Recordset
        tblCase.CursorType = adOpenKeyset
        tblCase.LockType = adLockOptimistic
        tblCase.Open "tblCase", cnn1, , , adCmdTable
 
        'Get the new record data
        tblCase.AddNew
            tblCase![Subject's Name] = txtName
            tblCase![Infection Type] = cboInfection
            tblCase![New Case?] = opNew
            tblCase![Isolation Precautions] = cboPrecaution
            tblCase![Unit] = cboUnit
            tblCase![Room Number] = txtRoom
            tblCase![Isolation Start Date] = txtStartDate
            tblCase![Isolation End Date] = txtEndDate
            tblCase![Notes] = txtNotes
            tblCase![K#] = txtKNumber
 
        'Show the newly added record for confirmation
            MsgBox "Case for: " & tblCase!txtName & " Has been successfully added to database."
 
        'Close connections to DB.
            tblCase.Close
            cnn1.Close
 
        'Clear all objects
            Me.txtName = ""
            Me.cboInfection = ""
            Me.cboPrecaution = ""
            Me.opNew = ""
            Me.cboUnit = ""
            Me.txtRoom = ""
            Me.txtStartDate = ""
            Me.txtEndDate = ""
            Me.txtNotes = ""
            Me.txtKNumber = ""
 
        'Send back to main page
 
        Else
        MsgBox "An error has occured, please verify that all fields are complete and try again.  Call Matt (x2244) for further assistance."
        End If
End Sub


If anyone has any idea, I'd be grateful.

P.S. The Vba debugger points to the line with:

Set tblCase = New ADODB.Recordset

-Thanks,
Z
 
Try changing the Dim statement to: Dim tblCase As object
 
I receive the same error.
 
I tried replacing Dim tblCase As ADODB.Recordset

with

Dim tblCase As Recordset

I get the error:

Method or Data member not found.

The yellow arrow in debugger points to the "Private Sub cmdAddNew_Click()" line. And tblCase.CursorType = adOpenKeyset is highlighted in blue (the BOLD part).

Hope this helps


-Z
 
You have Dim cnn1 As ADODB.Connection and then later on you have Set cnn1 = New ADODB.Recordset. If you use the default property of your controls which is the .Value property, it is not necessary to set the focus to the control.
 
I'm not really sure what you are getting at, Im pretty new to VBA, but I took all of the framework for this code from allen browne's page. I have identical code in another DB that seems to work fine. Can you go into a more detailed explanation of your last reply?

-Thanks,
Z
 
I dont remember where I got it, but it works like a charm, here's the other similar code.

Code: 
Private Sub rmAddIncident_Click()
   Dim err As Integer
   Dim cnn1 As ADODB.Connection
   Dim Risk_Data As ADODB.Recordset
   Dim strCnn As String
 
'Check that all fields are filled in
rmENCON.SetFocus
If rmENCON.Text = "" Then
err = err + 1
MsgBox "Please fill in the ENCON number." & err
End If
 
rmName.SetFocus
If rmName.Text = "" Then
err = err + 1
MsgBox "Please fill in the name of the person affected by the incident."
End If
 
rmDate.SetFocus
If rmDate.Text = "" Then
err = err + 1
MsgBox "Please fill in the date the incident occured."
End If
 
rmInjury.SetFocus
If rmInjury.Text = "" Then
err = err + 1
    MsgBox "Please specify the degree of injury."
End If
 
rmComments.SetFocus
If rmComments.Text = "" Then
err = err + 1
MsgBox "Please briefly describe the incident in the comments box."
End If
 
rmResolved.SetFocus
If rmResolved.Text = "" Then
err = err + 1
MsgBox "Please briefly summarize the follow-up."
End If
 
rmVictimstatus.SetFocus
If rmVictimstatus.Text = "" Then
err = err + 1
MsgBox "Please fill in the Victim status."
End If
 
rmLocation.SetFocus
If rmLocation.Text = "" Then
err = err + 1
MsgBox "Please fill in the location the incident took place."
End If
 
rmType.SetFocus
If rmType.Text = "" Then
err = err + 1
MsgBox "Please incicate the type of incident that occured."
End If
 
rmSpecificType.SetFocus
If rmSpecificType.Text = "" Then
err = err + 1
MsgBox "Please incicate the specific type of incident that occured."
End If
 
'if no errors insert data
If err < 1 Then
  ' Open a connection.
    Set cnn1 = New ADODB.Connection
    mydb = "G:\Admin\Ceo\Human Resources\RISKMGMT\Inhouse data\Risk Database\Inhouse Data.mdb"
    strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
    cnn1.Open strCnn
 
' Open Risk_Data table.
    Set Risk_Data = New ADODB.Recordset
    Risk_Data.CursorType = adOpenKeyset
    Risk_Data.LockType = adLockOptimistic
    Risk_Data.Open "Risk_Data", cnn1, , , adCmdTable
 
'get the new record data
      Risk_Data.AddNew
        Risk_Data!ENCON = rmENCON
        Risk_Data!Name = rmName
        Risk_Data!Date = rmDate
        Risk_Data!Injury = rmInjury
        Risk_Data![Incident Comments] = rmComments
        Risk_Data![Medication Risk] = Med_Risk
        Risk_Data![Medication/Equipment] = rmMedication
        Risk_Data![Victim Status] = rmVictimstatus
        Risk_Data!Location = rmLocation
        Risk_Data!Type = rmType
        Risk_Data![Specific Type] = rmSpecificType
        Risk_Data![Phys/Empl/Pt Involved] = rmPersonInvolved
        Risk_Data![Follow-up Summary] = rmResolved
        Risk_Data![Resolved or Pending] = ResolvedOrPending
        Risk_Data![Date Resolved] = rmDateResolved
      Risk_Data.Update
 
'Show the newly added data.
   MsgBox "Incident #: " & Risk_Data!ENCON & " has been successfully added"
 
'close connections to DB.
  Risk_Data.Close
    cnn1.Close
 
'clear all objects
Me.rmENCON.Value = ""
Me.rmDate.Value = ""
Me.rmName.Value = ""
Me.rmInjury.Value = ""
Me.rmComments.Value = ""
Me.Med_Risk.Visible = False
Me.rmMedication.Value = ""
Me.rmLocation.Value = ""
Me.rmVictimstatus.Value = ""
Me.rmType.Value = ""
Me.rmSpecificType.Value = ""
Me.rmPersonInvolved.Value = ""
Me.rmResolved.Value = ""
Me.ResolvedOrPending.Value = ""
Me.rmDateResolved.Value = ""
'send back to main page.
 DoCmd.OpenForm "Main_Page"
 DoCmd.Close acForm, "Enter_New_Incident"
 
Else
MsgBox "An Error has occurred, please check and try again"
End If
 
End Sub
 
Look at the difference in the line I've highlighted in RED.
Code: 
    'If no errors, insert data into table
    If err < 1 Then
 
        'Open a connection
        [COLOR="Red"][B]Set cnn1 = New ADODB.Recordset[/B][/COLOR]
        mydb = "G:\Medical\LaboratoryMedicine\InfectionControl\Statistics\Fiscal 2009\DB Conversion (Matt)\Infection Control Stats.mdb"
        StrCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
        cnn1.Open StrCnn
 
Thanks rural. I fixed that line and I no longer get that error. Now it is throwing me:


Run-time error '-2147217887 (80040e21):'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

The debugger points to this line:

tblCase![New Case?] = opNew

I've checked my registry for OLEDB_Services and it is present, so I guess there is still something wrong with my ADO connection string


-Z
 
I have not used ADO yet, only DAO so I'm very limited on what I know about ADO. I do know that CASE is a reserved word which may be causing you problems.
 
I really have no idea the differences between ADO and DAO. Should I switch? Is the other one easier?

Z
 
Could the error simply come from the way I am trying to get a new record added, in particular on the opNew (option box) line. I haven't used that type of control before.
 
If you comment out that one line, does the rest compile and run?
 
Yes, had to comment out the [K#] Field out too, AND the MSGBOX saying it was succesfully added, and the close function. BUt it seems to work well after that :P I wish I was smart.

-Z
 
Actually, no it doesnt seem to be populatiing my table at all.
 
You must log in or register to reply here.

Similar threads

D
Solved New detail records being added despite disabled button in master form
2
Replies
27
Views
1,556
GPGeorge
GPGeorge
H
When Update Query update No rows in a field (Because Condition not met) Error Message not Appear
Replies
12
Views
1,526
Isaac
Isaac
T
Attache to email from attachment Field
Replies
2
Views
718
theinviter
T
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
3,105
The_Doc_Man
The_Doc_Man
W
The problem has been resolved and the program can now be executed. Thank you very much.
Replies
6
Views
1,287
Ken Sheridan
Ken Sheridan

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom