首页 文章

更改参数并使用VBA运行SELECT访问查询

提问于
浏览 次
-1

我有几（3）个SELECT查询链接到excel文件，需要定期更新（每周） .

我正在尝试使用VBA创建一个访问模块，它可以做两件事：

  • 更新一些要选择的参数，我将其定义为可以手动更新的变量：CurrentWeek，RetailWeek，CurrentYear和Current Date

  • 更新后，它将运行查询，以便我可以刷新excel文件中的数据 .

我尝试了几件事，但到目前为止还没有成功，我认为最接近的是：

私有子DevicePerformanceDashboard（）

Dim CurrentWeek, RetailWeek, CurrentYear As Long
Dim CurrentDate As Date
Dim qRP, qPP, qMV As DAO.QueryDef
Dim dbs As DAO.Database

Set dbs = CurrentDb()

CurrentYear = 2018
CurrentWeek = 31
RetailWeek = CurrentWeek - 8
CurrentDate = TimeValue("2018-06-01 00:00:00")


Set qRP = CurrentDb.QueryDefs("DPDashboardRetail")
Set qPP = CurrentDb.QueryDefs("DPDashboardPricePrediction")
Set qMV = CurrentDb.QueryDefs("DPDashboardVolume")

qRP.Parameters("YearNb").Value = CurrentYear
qRP.Parameters("WeekNb").Value = ">" & RetailWeek

qPP.Parameters("WeekNb").Value = ">" & CurrentWeek
qPP.Parameters("PreWeekNb").Value = CurrentWeek
qPP.Parameters("PreYearNb").Value = CurrentYear

qMV.Parameters("VolumeAndValueDate").Value = CurrentDate

DoCmd.OpenQuery "DPDashboardRetail"
DoCmd.OpenQuery "DPDashboardPricePrediction"
DoCmd.OpenQuery "DPDashboardVolume"

结束子

目前我收到错误说 Object required 但我尝试了其他一些方法，我在不同的时间得到了不同的错误 .

UPDATE: 现在错误出现在该行：

qRP.Parameters("YearNb").Value = CurrentYear

    Item not found in this collection

对我做错了什么的任何想法？

vba ms-access access-vba

2 回答

  • 2

    许多错误：

    Dim CurrentWeek As Long
Dim RetailWeek As Long
Dim CurrentYear As Long
Dim CurrentDate As Date
Dim qRP As DAO.QueryDef
Dim qPP As DAO.QueryDef
Dim qMV As DAO.QueryDef
Dim dbs As DAO.Database

Set dbs = CurrentDb

CurrentYear = 2018
CurrentWeek = 31
RetailWeek = CurrentWeek - 8
CurrentDate = DateSerial(2018, 6, 1)

Set qRP = CurrentDb.QueryDefs("DPDashboardRetail")
Set qPP = CurrentDb.QueryDefs("DPDashboardPricePrediction")
Set qMV = CurrentDb.QueryDefs("DPDashboardVolume")

qRP.Parameters("YearNb").Value = CurrentYear
' Cannot do:
' qRP.Parameters("WeekNb").Value = ">" & RetailWeek

' Cannot do:
' qPP.Parameters("WeekNb").Value = ">" & CurrentWeek
qPP.Parameters("PreWeekNb").Value = CurrentWeek
qPP.Parameters("PreYearNb").Value = CurrentYear

qMV.Parameters("VolumeAndValueDate").Value = CurrentDate

qRP.Execute
qPP.Execute
qMV.Execute

    此系列中找不到物品

    这表示SQL中缺少或拼写错误的字段/参数 .

    回复于
  • 1

    Set 是一个只应与对象一起使用的关键字，因此Object需要出错 . 要为变量赋值，只需 VariableName = SomeValue 即可，仅在分配对象时使用 Set variableName = SomeObject .

    此外， qdf.Parameters 与 qdf.Execute 一起执行查询 . DoCmd.OpenQuery 与 DoCmd.SetParameter 一起设置参数 .

    当您使用 DoCmd.OpenQuery 时， DoCmd.SetParameter 创建的参数集合将被清除，因此需要对代码进行一些重组 .

    Option Compare Database
Option Explicit

Private Sub DevicePerformanceDashboard()

    Dim CurrentWeek, RetailWeek, CurrentYear As Long
    Dim CurrentDate As Date
    Dim dbs As DAO.Database

    Set dbs = CurrentDb()

    CurrentYear = 2018
    CurrentWeek = 31
    RetailWeek = CurrentWeek - 8
    CurrentDate = TimeValue("2018-06-01 00:00:00")

    DoCmd.SetParameter "YearNb", CurrentYear
    DoCmd.SetParameter "WeekNb", ">" & RetailWeek
    DoCmd.OpenQuery "DPDashboardRetail"        

    DoCmd.SetParameter "WeekNb", ">" & CurrentWeek
    DoCmd.SetParameter "PreWeekNb", CurrentWeek
    DoCmd.SetParameter "PreYearNb", CurrentYear
    DoCmd.OpenQuery "DPDashboardPricePrediction"

    DoCmd.SetParameter "VolumeAndValueDate", CurrentDate                
    DoCmd.OpenQuery "DPDashboardVolume"

End Sub
    回复于

相关问题