We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Using Checkbox to Update Access DB with VB 2005

weight01
weight01 asked
on
Medium Priority
317 Views
Last Modified: 2010-04-23
Hi

I have an Access DB with a field called Office2003 - Data Type Yes\No.

I have a form in VB 2005 with a Checkbox called CBOffice2003.

When this is checked on the form I would like the DB to be updated.  I have tried as you can see below but I recieve the error:

Data type mismatch in criteria expression.
from
command.ExecuteNonQuery()

Private Sub SaveRecord()

        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\ITAssets.mdb")

        Dim sql As String = String.Empty
        Dim sql1 As String = String.Empty

        If _assetID = 0 Then

            sql = "INSERT INTO tblAssets (EFCRef,Type,Manufacturer,[User],Department,[Model],SerialNo)" & "VALUES('" & cboEFCRef.Text & "', '" & cboType.Text & "','" & cboManufacturer.Text & "','" & cboUser.Text & "','" & cboDepartment.Text & "','" & cboModel.Text & "','" & txtSerialNo.Text & "')"
            sql1 = "UPDATE tblEFCTAGS SET IsUsed = True WHERE EFCTAG='" & cboEFCRef.Text & "'"
        Else

            sql = "UPDATE tblAssets SET EFCRef ='" & cboEFCRef.Text & "'," & "Type='" & cboType.Text & "', Manufacturer='" & cboManufacturer.Text & "',[User]='" & cboUser.Text & "',Department='" & cboDepartment.Text & "',[Model]='" & cboModel.Text & "',SerialNo='" & txtSerialNo.Text & "',Office2003='" & CBOffice2003.Checked & "' WHERE assetID=" & _assetID
            sql1 = "UPDATE tblEFCTAGS SET IsUsed = True WHERE EFCTAG='" & cboEFCRef.Text & "'"

        End If

        conn.Open()

        Dim command As New OleDbCommand(sql, conn)
        Dim command1 As New OleDbCommand(sql1, conn)
        command.ExecuteNonQuery()
        command1.ExecuteNonQuery()


        conn.Close()

        Me.Close()

    End Sub

Thanks
Comment
Watch Question

Commented:
"Type" is a reserved word in Access.  Try sticking it in square brackets.

Roger

Author

Commented:
No did not work

Everything worked fine until I added the checkbox - CBOffice2003.

If I remove - ,Office2003='" & CBOffice2003.Checked & "' from the UPDATE string it works fine.
Commented:
Sorry, missed that.  CBOffice2003.Checked returns a Boolean, not a string, so it does not need single quotes around it.

Roger

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
That worked fine Roger.

Your help is appreciated.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.