Member Avatar for Cosa

Hi,
Im new to VB. Im just trying to do a simple if statement that will open a specific form based on user input. The database requires me to scan in a barcode from a card. I then want to check if that barcode number is already in the database. to do this i have tried the following code.

Private Sub Barcode_AfterUpdate()
Dim curDatabase As Object
Dim tblStore_Barcode As Object
Dim tblPersonal As Object


Set curDatabase = CurrentDb
Set tblStore_Barcode = curDatabase.TableDefs("Store_Barcode")
Set tblPersonal = curDatabase.TableDefs("Personal")


If [Store_Barcode]![Barcode] = [Personal]![Barcode] Then
DoCmd.OpenForm (Personal_2)
Else
DoCmd.OpenForm (Personal)
End If
End Sub

I recieve the Run time error 13 - type mismatch

If [Store_Barcode]![Barcode] = [Personal]![Barcode] Then

This is the line that is highlighted. Any ideas why it is doing this?

  • 4 Contributors
  • 5 Replies
  • 385 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by jireh

Dani AI

Generated

The runtime 13 is almost always a type/shape problem — and in this case the bigger issue is what your code is comparing. the lines that call CurrentDb.TableDefs return schema objects (TableDef/FieldDef), not table rows or control values. That will produce a type mismatch even if both tables use the same field datatype. and were right to flag datatypes and Nulls, but the primary fix is to read a field value (record) — not a TableDef — and to explicitly handle Nulls and quoting.

A simple, robust approach is to test for existence with DLookup (handle text vs numeric and escape quotes):

' text barcode example
Dim found As Variant
found = DLookup("Barcode", "Store_Barcode", "Barcode = '" & Replace(Me!Barcode, "'", "''") & "'")
If Not IsNull(found) Then
  DoCmd.OpenForm "Personal_2"
Else
  DoCmd.OpenForm "Personal"
End If

If Barcode is numeric, remove the single quotes and optionally convert with CLng/Val. For larger checks or repeated use, use a DAO.Recordset:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT 1 FROM Store_Barcode WHERE Barcode = '" & Replace(Me!Barcode, "'", "''") & "'")
If Not rs.EOF Then
  DoCmd.OpenForm "Personal_2"
Else
  DoCmd.OpenForm "Personal"
End If
rs.Close: Set rs = Nothing

Quick checklist: use Me!Barcode to get the control value, avoid TableDefs for row data, explicitly guard against Null (IsNull or Nz), match quoting to the table field type, and pass form names as strings to DoCmd.OpenForm. Add Debug.Print or MsgBox to inspect Me!Barcode and VarType during debugging to confirm what you're actually comparing.

Recommended Answers

All 5 Replies

Member Avatar for Cruize_Invades

Please check the data type of the fields 'Barcode' in both table, the Personal and the Store_Barcode. The data type of the two must be the same, example the Barcode in Personal is in Integer data type and the other is in "Text/Char" data type, then it would really return an Error.

Member Avatar for Cosa

The datatype is the same for both. I made sure of that before i attempted writing the code, and i have since checked it several times to be sure.

I believe the problem is with the actual syntax. However i dont know why it is doing this.

Member Avatar for Cosa

Any ideas guys?

Member Avatar for vb5prgrmr

Unless the two tables are exact (TableDefs) then they... Is this VBA?

Member Avatar for jireh

try to remove the bracket "[]"

If [Store_Barcode]![Barcode] = [Personal]![Barcode] Then

or try to look the value if it is DBNull. dbnull is not a string type in which if you will try to use it in comparison of strings it would throw an exception error...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.