vba 用记录集填充列表框并比较日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14089376/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Populating a listbox with recordset and comparing dates
提问by Laughy
My program has 3 fields namely title, start date and end date for the user to enter. I wish to populate my listbox which is called "filteredResults" with results that contains records with tiles equal to the one entered by the user OR that falls into the range of the date.
我的程序有 3 个字段，即供用户输入的标题、开始日期和结束日期。我希望用结果填充我的名为“filteredResults”的列表框，其中包含的记录的磁贴等于用户输入的磁贴或落在日期范围内的磁贴。
May I know:
我可否知道：
1) How can I populate the listbox with the recordset obtained from the query?
1) 如何使用从查询中获得的记录集填充列表框？
2) How can I compare the date inside the query?
2）如何比较查询中的日期？
Thanks a lot!
非常感谢！
Private Sub FilterProj_Click()
Dim title As String, startDate As Date, endDate As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Project WHERE ORDER BY [ProjectId] DESC")
filteredResults.Recordset = rs
回答by bonCodigo
For Question 1:
对于问题 1：
Populate directly from query:
直接从查询填充：
Me.mylistbox.rowsource = _
db.OpenRecordset("SELECT titles FROM Project _
WHERE title = '" & parameter & "' ORDER BY [ProjectId] DESC")
Populate using Recordset object:
使用 Recordset 对象填充：
If (rs.RecordCount <> 0) Then
Do While Not rs.EOF
mylistbox.Items.Add(rs.Fields(0).Value)
rs.MoveNext()
Loop
End IF
Another method:
另一种方法：
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
strSQL = "SELECT titles from Projects _
WHERE title = '" & parameter & "' ORDER BY [ProjectId] DESC"
Set db = CurrentDb
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
qdf.Connect = strConn
Set rs = qdf.OpenRecordset()
Set Me.mylistbox.Recordset = rs
Question 2:
问题2：
Not very clear. I assume you require a parameterizedquery. By setting a user's value into where, andclauses.
不是很清楚。我假设您需要parameterized查询。通过将用户的值设置为where, and子句。
db.OpenRecordset("SELECT titles FROM Project _
WHERE title = '" & parameter & "' ORDER BY [ProjectId] DESC")
Or you may use the following article to set the parameterin query design viewitself.
或者，你可以使用下面的文章来设置parameter的query design view本身。
- Reference from MSDN: Using paramterized queries
回答by HansUp
Set the row source property for your filteredResultslist box to a query which uses a strategy similar to this.
将filteredResults列表框的行源属性设置为使用与此类似的策略的查询。
SELECT p.ProjectId, p.title, p.date_field
FROM [Project] AS p
WHERE
p.title = Forms![YourForm]![txtTitle]
OR
(
p.date_field >= Forms![YourForm]![txtStartDate]
AND p.date_field <= Forms![YourForm]![txtEndDate]
)
ORDER BY p.ProjectId DESC
Whenever you want to update the contents of filteredResultsto reflect changes to the user-submitted title, start date, and/or end date, you can requery the list box:
每当您想要更新 的内容filteredResults以反映对用户提交的标题、开始日期和/或结束日期的更改时，您可以重新查询列表框：
Me!filteredResults.Requery
You could trigger the requery from a command button, or from the after update events of the text boxes.
您可以从命令按钮或从文本框的更新后事件触发重新查询。
Note I assumed your Projecttable includes a text field named titleand a Date/Time field named date_field. I made up my own names (txtTitle, txtStartDate, txtEndDate) for the text boxes. Set the Format property to "General Date" for txtStartDateand txtEndDate. Substitute the name of your form for YourForm.
注意我假设您的Project表包含一个名为的文本字段title和一个名为的日期/时间字段date_field。我txtTitle, txtStartDate, txtEndDate为文本框编了自己的名字 ( )。设置的格式属性设置为“常规日期”为txtStartDate和txtEndDate。将您的表单名称替换为YourForm.
