data type mismatch in criteria expression 2

[Acet1485]

Before I begin, I would like to thank any and all even willing to take a gander at my dilemma. I am pretty new at using Access, and was given the task of creating a new database for my office. The project has run smoothly up until now.

I have a form (frmTicketView)that retrieves and displays specific records from the table (tblTicket_Requests). The form allows the user to update 2 fields in particular, (Ticket_Status) and (Date_Closed) by clicking an update button on the bottom of the form that opens up a new form with 2 unbound fields that represent these two controls. This code has been used in the same manner to update data in another of my tables. The only difference is that table had an AutoID with the Autonumber field type. This table however, uses a text field (Ticket_No. My aplogies for the wall of text, but here is the code.

Private Sub Command8_Click()

On Error GoTo Err_Command8_Click

Dim db As DAO.Database

Dim rst As DAO.Recordset



strMessage = "Update this Ticket?"

intOptions = vbQuestion + vbYesNo

bytchoice = MsgBox(strMessage, intOptions)

If bytchoice = vbYes Then

Set db = CurrentDb()

Set rst = db.OpenRecordset("SELECT * FROM (tblTicket_Requests) Where (Ticket_No) =" & Forms!frmTicketView.Ticket_No, dbOpenDynaset, dbSeeChanges)



With rst

.Edit

!Date_Completed = Forms!frmTicketUpdate![Text0]

!Ticket_Status = Forms!frmTicketUpdate![Combo5]

.Update

.Close

End With

MsgBox ("Ticket Updated!")



Exit_Command8_Click:

Exit Sub



Err_Command8_Click:

MsgBox Err.Description

End If

End Sub


I tried for 2 full days now to figure out why my SELECT statement fails in VBA but works in query design. Any help would be greatly appreciated.

 

[PHV]

Set rst = db.OpenRecordset("SELECT * FROM tblTicket_Requests Where Ticket_No=[!]'[/!]" & Forms!frmTicketView.Ticket_No[!] & "'"[/!], dbOpenDynaset, dbSeeChanges)



Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Acet1485]

Wow, I guess staring at the same line of text all day and you just can't see the problem even as it smacks you in the face lol. Thanks so much, will adjust this baby first thing in the morning. Have a great day!!!

 

[Andrzejek]

You can easily help yourself by:

Dim [blue]strSQL[/blue] as String
...

[blue]strSQL[/blue] = "SELECT * FROM tblTicket_Requests Where Ticket_No = '" & Forms!frmTicketView.Ticket_No & "'"
[green]
'Debug.Print strSQL
[/green]
Set rst = db.OpenRecordset([blue]strSQL[/blue], dbOpenDynaset, dbSeeChanges)

This way you can un-comment Debug statement and *see* in Immediate Window your sql, which is a lot easier to see what's going on.

Also, just a hint.
Your message box can be easily written in one statement:
[tt]
If vbYes = MsgBox("Update this Ticket?", _
vbYesNo Or vbQuestion Or vbDefaultButton2, _
"Update?") Then
....
End If
[/tt]
You can use VBA's MZTools, it has - among whole bunch of other tools - a MsgBox Assistance which is great. And the price is right (free)

Have fun.

---- Andy