SQL Query Question

[RobWulf]

Okay I am having issues with my query for my database

Private Sub Combo2_AfterUpdate()
DoCmd.RunSQL "INSERT INTO tmp_ticket (ticket) SELECT ticket FROM [tbl_ticket] WHERE assigned LIKE ""Me.Combo2"""
End Sub

It sort of works, it trys to append 0 rows!

What I am trying to do is get this SQL code to go to my Ticket Table and look in the Assigned field for a name that is LIKE the value in the combo box, but so far all I get is empty air.


The basic set up is this

tbl_ticket

AID - autonumber
ticket - text (the ticket numbers we use are a mix of numbers and letters)
ttitle - text
assigned - Text (this is the list of names all lined up with first and last sperated by a ; John Doe; Jane Doe) we have no control over this list

tbl_employ
AID - Autonumber
ename - individual name (First Last)
manager
department

The form simply has the drop down list of Employee Names taken from tbl_employ

with the above code in the after update section that should update the tmp_ticket table that displays on a new subform.

 

[James Dudden]

If the value is a string then it should be as follows:

DoCmd.RunSQL "INSERT INTO tmp_ticket (ticket) SELECT ticket FROM [tbl_ticket] WHERE assigned LIKE '*" & Me!Combo2 & "*'"

 

[RobWulf]

Mr. Dudden I tried that method and it still trying to append 0 records.

Is there something I am missing?

Do I need to declare assigned as a string for SQL to see it as such?

 

[James Dudden]

Is combo2 a dropdown with 2 or more columns and if so which column is the name column?

If for example in the drop-down you have the ID as column 1 but hidden and the name column is column 2 then you would use this code:

DoCmd.RunSQL "INSERT INTO tmp_ticket (ticket) SELECT ticket FROM [tbl_ticket] WHERE assigned LIKE '*" & Me!Combo2.column(1) & "*'"

NB: The first column is know as 0 and the second as 1 etc

 

[RobWulf]

Thats what I was missing, the .column(1)

Thanks, I am really new to all this VBA coding