VBA SQL Returning Statement, Not Value

Question

I'm working with a listbox, and when you click on a row it will populate the fields on a sheet. So if I click work ticket 18, it will grab the department, priority, assignee, etc. from a table and fill the work order. When I click on the row, I get a msgbox pop up with the ticket number by just writing:

MsgBox Me.Work_Order_List.Value

Then when I try

ticNum = Me.Work_Order_List.Value
strSQL = "SELECT Description_Of_Problem FROM Work_Orders WHERE " & ticNum & " = Ticket_Number;"
Debug.Print strSQL
Me.Notes = strSQL

My notes box will print the SQL statement but never run. What am I doing wrong here? I will need to populate many fields so is there a faster way as well?

Thanks

Grant · Accepted Answer · 2013-02-08 22:54:03Z

1

Me.Notes = DLookup("Description_Of_Problem", "Work_Orders", "Ticket_Number=" & ticNum)

That was exactly what I was looking for. I was able to delete the rest of my code and use those for each field. Thanks HansUp!

answered Feb 8, 2013 at 22:54

Grant

9031 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fionnuala Over a year ago

Do tell, why are you dlooking up a bunch of fields (slow, uneditable) rather than binding a query or table (fast, editable)? Even if you just wish to write several fields to several controls, you would be better working from a recordset. DlookUp is for one or two controls and generally from a different table.

Grant Over a year ago

I chose this because I am unfamiliar with binding a query. I tried the code you provided and tried to make it work, but I could not. I will have to do some research on recordsets because as of now I open and close a recordset at the beginning and end of each sub, and I thought it would take more time to open and close each time when a row is clicked than a DLookUp. So to answer your question: because I am still learning VBA and DLookUp worked for what I was doing.

Fionnuala · Accepted Answer · 2013-02-11 20:17:58Z

You are not running the query. You should set the recordsource of your form to the sql if you want the data to fill into fields.

 Me.RecordSource = "SELECT Description_Of_Problem, Other, Etc FROM Work_Orders"

The above will only work if you have controls bound to fields, but it is the easiest way to populate a lot of controls.

The best way to start is to create a form based on Work_Orders using the form wizard. You can then avoid any coding at all by creating a combobox to select records on your form. The user can then select a ticket and jump to that record. There is a wizard for that, too.

Dim rs As DAO.Recordset

ticNum = Me.Work_Order_List.Value
strSQL = "SELECT Description_Of_Problem FROM Work_Orders WHERE Ticket_Number =" _
       & ticNum 
Debug.Print strSQL
Set rs = CurrentDB.OpenRecordset strSQL
Me.Notes = rs!Description_Of_Problem

Binding a form

Start the form wizard based on a table or query

Form wizard

Follow through the steps to end up with a form in design view

the form in design view

There are a number of ways to navigate through the records, you seem to like the listbox. Add a list box making sure that the wizard is selected.

select listbox

Choose to find records on the current form

find record on form

And select the items to appear in the listbox

select listbox items

Select an item to find it on the form.

select item to find record

This is what makes it a bound form, the record source, which can be a table name, a query name or an SQL statement. Note that the control to be filled in automatically are bound to field names, but the listbox to find records is marked 'unbound'.

bound form

Grant · Accepted Answer · 2013-02-12 23:37:36Z

After having to make multiple edits, I found that DLookup was not as convenient with multiple fields and an unbound form. So in the end I have decided:

Dim myR As Recordset

Set myR = CurrentDb.OpenRecordset("Work_Orders", dbOpenDynaset)
myR.FindFirst ("[Ticket_Number] = " & Me.Work_Order_List.Value & "")

Me.Update_Status = myR![Current_Status]
Me.Downtime_Code = myR![Downtime_Code]
Me.Date_For_Completion = myR![Date_For_Completion]
Me.Notes = myR![Notes_From_Assignee]
Me.Description_Box = myR![Description_Of_Problem]
Set myR = Nothing

And I use myR.FindFirst to grab the row I'm looking for. Since my ticket system is still in the testing phase I only have ~100 records, but I'm hoping it will be able to search quickly when it reaches 10000+ with .FindFirst.

Collectives™ on Stack Overflow

VBA SQL Returning Statement, Not Value

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related