I cannot find the reason for type mismatch error 13 can someone help?

R

Repent

Registered User.
Local time
, 17:59
Joined
Apr 10, 2008
Messages
108
I've been trying to put the final touches on some code that a user has helped me with but for the life of me I cannot find out why I'm getting a data type mismatch error 13.

I have a user form that workers fill out. They fill out this record every two hours. At times, they need to create duplicate records. The only difference being that the time of the record would be different from the time of the last record. Basically a user has six times during their shift that they have to complete records, 630am, 830am, 1030am, 1230pm, 230pm, end-days.

There are times when a worker would create a record at 630am and at the same time have to duplicate the record but only change the time to 830, 1030, and so on.

Instead of the worker having to fill out the entire form three times, with the same info except for the record time, I had in mind to have additional checkboxes on the form to represent the additional time records needed. The user would fill out the form once, select the checkboxes for the additional records, press a command button, and the records would be created, each a duplicate of the other but with the time field being different.

Below is the code that a user graciously helped me with. I am getting a data type mismatch error 13 on the "Call addentry(varTime)" entry. I've tried changing the data types to match each other as well as changing them all together.

Somewhere I'm missing something really basic (probably to most) and I'm hoping to get that final help to move past this development piece.

Here is the code I'm working with: I had commented out some of the code for troubleshooting purposes. I've also attached a stripped down version of my DB, created in Access 2010. I've tried to save the db in a lower version but I'm getting an error that states I have elements in this db that cannot be saved in a lower version.

Code: 
Option Compare Database
Option Explicit

Sub AddRecords_Click()

Dim varTime As String

If cb430AM.Value = True Then
    varTime = "End-Nights"
    Call addentry(varTime)
End If

If cb630AM.Value = True Then
    varTime = "6:30 AM"
    Call addentry(varTime)
End If

'If cb830AM.Value = True Then
 '   varTime = "8:30 AM"
  '  Call addentry(varTime)
'End If

'If cb1030AM.Value = True Then
 '   varTime = "10:30 AM"
  '  Call addentry(varTime)
   ' End If
    
'If cb1230PM.Value = True Then
 '   varTime = "12:30 PM"
  '  Call addentry(varTime)
'End If

'If cb230PM.Value = True Then
 '   varTime = "2:30 PM"
  '  Call addentry(varTime)
'End If

'If cb430PM.Value = True Then
 '   varTime = "4:30 PM"
  '  Call addentry(varTime)
'End If

'If cb630PM.Value = True Then
 '   varTime = "6:30 PM"
  '  Call addentry(varTime)
'End If

'If cb830PM.Value = True Then
 '   varTime = "8:30 PM"
  '  Call addentry(varTime)
'End If

'If cb1030PM.Value = True Then
 '   varTime = "10:30 PM"
  ' Call addentry(varTime)
'End If

'If cb1230AM.Value = True Then
 '   varTime = "12:30 AM"
  '  Call addentry(varTime)
'End If

'If cb230AM.Value = True Then
 '   varTime = "2:30 AM"
  '  Call addentry(varTime)
'End If
End Sub
 
Private Function addentry()

Dim db As Database
Dim rs As Recordset
Dim varTime As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblProductionNumbers", dbOpenDynaset)

rs.AddNew
rs("ManHoursID") = Me.ManHoursID.Value
rs("ProductionDate") = Me.ProductionDate.Value
rs("TimeID") = varTime
rs("LineID") = LineID.Value
rs("ProductID") = Me.ProductID.Value
rs("OperatorID") = Me.OperatorID.Value
rs("TailOffID") = Me.TailOffID.Value
rs("LF Run") = Me.[LF Run].Value
rs("LF Produced") = Me.[LF Produced].Value
rs("Comments") = Me.Comments.Value
rs.Update
rs.Close
db.Close

cb430AM.Value = False
cb630AM.Value = False
cb830AM.Value = False
cb1030AM.Value = False
cb1230PM.Value = False
cb230PM.Value = False
cb430PM.Value = False
cb630PM.Value = False
cb830PM.Value = False
cb1030PM.Value = False
cb1230AM.Value = False
cb230AM.Value = False


End Function
 

Attachments

Your problem is:

1. You are using lookups at table level for the TimeID field. Lookups at table level are NOT good to use and you should not use them. Remove them. You can use lookups at FORM level but should not at table level. See here for more on why not: www.mvps.org/access/lookupfields.htm

2. Your field TimeID is a number field but you are attempting to add it in as text. That is the problem that lookups give is that you think you are using one type when in fact you are using another. So your code should be entering the NUMBER 13 instead of END-Nights, etc.

Oh, and your naming leaves a bit to be desired. You use varTime but you aren't using the Variant datatype you are using String and in this case you should really be using one of the INTEGER datatypes.
 
Also, something is missing…
Code: 
Sub AddRecords_Click()
    Dim varTime As String

    Call addentry(varTime)

End Sub


Private Function addentry()

End Function

Chris.
 
boblarson said:
Your problem is:

1. You are using lookups at table level for the TimeID field. Lookups at table level are NOT good to use and you should not use them. Remove them. You can use lookups at FORM level but should not at table level. See here for more on why not: www.mvps.org/access/lookupfields.htm

2. Your field TimeID is a number field but you are attempting to add it in as text. That is the problem that lookups give is that you think you are using one type when in fact you are using another. So your code should be entering the NUMBER 13 instead of END-Nights, etc.

Oh, and your naming leaves a bit to be desired. You use varTime but you aren't using the Variant datatype you are using String and in this case you should really be using one of the INTEGER datatypes.
Click to expand...


Bob;

So instead of using lookups at the table level should I just use a value list created and defined at the table? What Integer data type would I use to handle numbers and text since some of the values are text, i.e; "End-Days" or "6:30 AM"?
 
ChrisO said:
Also, something is missing…
Code: 
Sub AddRecords_Click()
    Dim varTime As String

    Call addentry(varTime)

End Sub


Private Function addentry()

End Function

Chris.
Click to expand...
Hi

Bob referred to your naming and suggested using an Integer type. For naming, it's good to try and use a familiar type-

V or Var = Variant
S or Str = String
I or Int = Integer
D or Dbl = Double
Sng = Single

And so fourth. In our case, using the VarTime would would cause omeone to think its a Variant when it's not.


Cheers


Nidge
 
I understand now thanks Nidge. I'll fix that. I got this working now by getting rid of the lookups and using value lists but with the code it is only creating one record even though I've selected for it to create several records at once? Do I need to use a looping statement? If so, what would that code look like?

This is the current code that is working, but only one record created at a time.

Code: 
Sub AddRecords_Click()
Dim varTime As String

If cb430AM.Value = True Then
    varTime = "4:30 AM"
    Call addentry(varTime)
End If

If cb630AM.Value = True Then
    varTime = "6:30 AM"
    Call addentry(varTime)
End If

If cb830AM.Value = True Then
    varTime = "8:30 AM"
    Call addentry(varTime)
End If

If cb1030AM.Value = True Then
    varTime = "10:30 AM"
    Call addentry(varTime)
    End If
    
If cb1230PM.Value = True Then
    varTime = "12:30 PM"
    Call addentry(varTime)
    End If

If cb230PM.Value = True Then
    varTime = "2:30 PM"
    Call addentry(varTime)
End If

If cb430PM.Value = True Then
    varTime = "4:30 PM"
    Call addentry(varTime)
End If

If cb630PM.Value = True Then
    varTime = "6:30 PM"
    Call addentry(varTime)
End If

If cb830PM.Value = True Then
    varTime = "8:30 PM"
    Call addentry(varTime)
End If

If cb1030PM.Value = True Then
    varTime = "10:30 PM"
    Call addentry(varTime)
End If

If cb1230AM.Value = True Then
    varTime = "12:30 AM"
    Call addentry(varTime)
End If

If cb230AM.Value = True Then
    varTime = "2:30 AM"
    Call addentry(varTime)
    
    

End If
End Sub
 
Private Function addentry(varTime As String)

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblProductionNumbers", dbOpenDynaset)

rs.AddNew
rs("ManHoursID") = Me.ManHoursID.Value
rs("ProductionDate") = Me.ProductionDate.Value
rs("Time") = varTime
rs("Line#ID") = LineID.Value
rs("ProductID") = Me.ProductID.Value
rs("OperatorID") = Me.OperatorID.Value
rs("TailOffID") = Me.TailOffID.Value
rs("LF Run") = Me.[LF Run].Value
rs("LF Produced") = Me.[LF Produced].Value
rs("Comments") = Me.Comments.Value
rs.Update
rs.Close
db.Close

cb430AM.Value = False
cb630AM.Value = False
cb830AM.Value = False
cb1030AM.Value = False
cb1230PM.Value = False
cb230PM.Value = False
cb430PM.Value = False
cb630PM.Value = False
cb830PM.Value = False
cb1030PM.Value = False
cb1230AM.Value = False
cb230AM.Value = False


End Function
 
Repent.

Each time you call the Function addentry you reset all the Controls.

cb430AM.Value = False
cb630AM.Value = False
cb830AM.Value = False
cb1030AM.Value = False
cb1230PM.Value = False
cb230PM.Value = False
cb430PM.Value = False
cb630PM.Value = False
cb830PM.Value = False
cb1030PM.Value = False
cb1230AM.Value = False
cb230AM.Value = False

Chris.
 
ChrisO said:
Repent.

Each time you call the Function addentry you reset all the Controls.

cb430AM.Value = False
cb630AM.Value = False
cb830AM.Value = False
cb1030AM.Value = False
cb1230PM.Value = False
cb230PM.Value = False
cb430PM.Value = False
cb630PM.Value = False
cb830PM.Value = False
cb1030PM.Value = False
cb1230AM.Value = False
cb230AM.Value = False

Chris.
Click to expand...


You're right on that one Chris. I just commented out that code and can create multiple records. How would I best go about only having the checkboxes cleared after all the required records are created? I can see where even though I selected multiple boxes to create those records I basically unchecked the boxes through the code before the record could then be created.

thank you;
Chris
 
I have a set of VCR controls on the form that allows a user to step backwards and forwards through the records. I'm thinking instead of driving my addentry code through a command box on the form of creating a snippet of code that only contains the code to clear the checkboxes and calling that code through the marcos assigned to the VCR controls. The marcos would also drive the addentry code so basically all of the applicible VCR controls on the form would first run the code to addentry and after that run the code to clear the checkboxes.

Does this sound feasible? Would you do it a different way?

Chris
 
Looks like this will work with using the macros on the VCR controls.

Thanks for all your help on this Bob & Chris.

Chris
 
Last edited:
You must log in or register to reply here.

Similar threads

N
Solved Cleaning Up the recordset after use in MS Access
Replies
7
Views
655
gemma-the-husky
gemma-the-husky
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
367
CJ_London
C
M
Multiple attachments and a report in the same email
Replies
15
Views
1,294
Marshall Brooks
M
F
Runtime Error 3001 HELP I Am loosing my mind
Replies
14
Views
949
ebs17
E
K
Error 13 | Audit Log
Replies
4
Views
803
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

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