Can some please look at the below for me and advice what i am doing wrong. I am trying to run a sql query based on data selected from comboboxes on a form, results of which are generated in a subform attached to the main form
有些人可以请看下面的内容,并告诉我我做错了什么。我试图根据从表单上的组合框中选择的数据运行SQL查询,其结果是在附加到主窗体的子窗体中生成的
Private Sub Form_AfterUpdate()
Dim LSQL As String
Dim cmb As ComboBox
Dim txt As TextBox
Dim chk As CheckBox
For Each Control In Me.Controls
If IsNull(cmb.Value) Then
Form_ReportSubForm.RecordSource = "datamanager"
requerysubform
Else
If IsNull(txt.Value) Then
Form_ReportSubForm.RecordSource = "datamanager"
requerysubform
Else
If chk.Value = False Then
Form_ReportSubForm.RecordSource = "datamanager"
requerysubform
Else
LSQL = "SELECT * from datamanager"
LSQL = LSQL & " WHERE engineerid = cmbengid AND membername = cmbtm AND department = cmbdept"
Form_ReportSubForm.RecordSource = LSQL
requerysubform
End If
End If
End If
Next
End Sub
When the 3 controls on the form are updated, nothing happens in the attached subform which tells me I am probably inputting this code in the wrong event. Can you please help with this?
当表单上的3个控件更新时,附加的子表单中没有任何内容发生,它告诉我可能在错误的事件中输入了此代码。你能帮忙吗?
2 个解决方案
#1
Your code is a bit unclear to me, but I would use a syntax like
您的代码对我来说有点不清楚,但我会使用类似的语法
Form_ReportSubForm.form.RecordSource = "datamanager"
if Form_ReportSubForm is your subform object in the main form.
如果Form_ReportSubForm是主窗体中的子窗体对象。
#2
If I recall corectly if the form is unbound, that is there isn't a table or query in the forms Record Source property, then the form's AfterUpdate event doesn't get fired. Therefore you should be calling the same code from each of the three combo boxes AfterUpdate event.
如果我核心地知道如果表单是未绑定的,即表单Record Source属性中没有表或查询,则表单的AfterUpdate事件不会被触发。因此,您应该从三个组合框AfterUpdate事件中的每一个调用相同的代码。
And if you change a controls record source you might not need the requery. I can't recall right off hand so try it without the requery first.
如果更改控件记录源,则可能不需要重新查询。我无法回想起现在,所以在没有重新查询的情况下尝试它。
And if you create your sub as a Function, you can just select the controls you want it to run on and paste =MyFunction() into the event textbox, instead of having to code it in VBA.
如果您将sub创建为Function,则只需选择要运行它的控件并将= MyFunction()粘贴到事件文本框中,而不必在VBA中对其进行编码。
OnwDayWhen added the above paragraph. I would never, ever do that as that isn't done very often and isn't at all obvious what is going on when you look at the VBA.
OnwDay当添加上面的段落。我永远不会这样做,因为这不是经常做的,而且当你看VBA时,一切都不明显。
#1
Your code is a bit unclear to me, but I would use a syntax like
您的代码对我来说有点不清楚,但我会使用类似的语法
Form_ReportSubForm.form.RecordSource = "datamanager"
if Form_ReportSubForm is your subform object in the main form.
如果Form_ReportSubForm是主窗体中的子窗体对象。
#2
If I recall corectly if the form is unbound, that is there isn't a table or query in the forms Record Source property, then the form's AfterUpdate event doesn't get fired. Therefore you should be calling the same code from each of the three combo boxes AfterUpdate event.
如果我核心地知道如果表单是未绑定的,即表单Record Source属性中没有表或查询,则表单的AfterUpdate事件不会被触发。因此,您应该从三个组合框AfterUpdate事件中的每一个调用相同的代码。
And if you change a controls record source you might not need the requery. I can't recall right off hand so try it without the requery first.
如果更改控件记录源,则可能不需要重新查询。我无法回想起现在,所以在没有重新查询的情况下尝试它。
And if you create your sub as a Function, you can just select the controls you want it to run on and paste =MyFunction() into the event textbox, instead of having to code it in VBA.
如果您将sub创建为Function,则只需选择要运行它的控件并将= MyFunction()粘贴到事件文本框中,而不必在VBA中对其进行编码。
OnwDayWhen added the above paragraph. I would never, ever do that as that isn't done very often and isn't at all obvious what is going on when you look at the VBA.
OnwDay当添加上面的段落。我永远不会这样做,因为这不是经常做的,而且当你看VBA时,一切都不明显。