VBA SQL返回语句，不带值 [英] VBA SQL Returning Statement, Not Value

问题描述

我正在使用一个列表框，当您单击一行时，它将填充工作表上的字段.因此，如果我单击工作单18，它将从表中获取部门，优先级，受让人等，并填写工作单.当我单击该行时，只需输入以下内容，我就会弹出一个带有票号的msgbox:

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

然后，当我尝试

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

我的注释框将打印SQL语句，但从不运行.我在这里做错了什么?我将需要填充许多字段，所以还有更快的方法吗?

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?

谢谢

推荐答案

在必须进行多次编辑之后，我发现DLookup不适用于多个字段和未绑定的表单.所以最后我决定:

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

然后我使用myR.FindFirst来获取我要查找的行.由于我的票务系统仍处于测试阶段，所以我只有大约100条记录，但是我希望当.FindFirst达到10000+时，它能够快速搜索.

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.

这篇关于VBA SQL返回语句，不带值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！