In a SQL database I have a table, Table1. This table is related to another table, Table2 which in turn is related to Table3. There is a query Query1 that selects certain records from Table1.
在SQL数据库中,我有一个表Table1。该表与另一个表Table2有关,Table2又与Table3有关。有一个查询Query1,它从Table1中选择某些记录。
This database is linked to in an Access database project
此数据库链接到Access数据库项目中
A form Table1Data is based on Table1, with a datasheet containing related Table2 data (and subsequently Table3 data). This form is opened by another form (Switchboard). The problem comes when the form is opened. I want the form to be filtered, but when I set up a macro and open the form and set the Filter to Query1, the data in the form is not filtered. Why does this happen, is this not the way to do it? Query1 selects all the columns from Table1, so mismatching columns should not be an issue.
Table1Data表格基于Table1,数据表包含相关的Table2数据(以及随后的Table3数据)。此表单由另一个表单(Switchboard)打开。打开表单时出现问题。我想要过滤表单,但是当我设置一个宏并打开表单并将Filter设置为Query1时,表单中的数据不会被过滤。为什么会发生这种情况,这不是这样做的方法吗? Query1选择Table1中的所有列,因此不匹配的列不应成为问题。
Additionally I want to lock it down - only certain people can execute Query1, same with other queries (Query2, Query3 etc). So they can only edit the data that they are permitted to edit.
另外我想把它锁定 - 只有某些人可以执行Query1,与其他查询(Query2,Query3等)相同。因此,他们只能编辑允许编辑的数据。
1 个解决方案
#1
My preferred solution is to set the recordsource in the Form Open event. This gives me the most control over what is going on.
我首选的解决方案是在Form Open事件中设置记录源。这让我能够最大程度地控制正在发生的事情。
Here is my boilerplate for doing this. It also includes looking up the OpenArgs which are passed on calling the form. You can just comment out or remove the If/Then statement if you aren't looking to specify anything from the calling form in your SQL.
这是我做这个的样板。它还包括查找调用表单时传递的OpenArgs。如果您不想在SQL中调用表单中的任何内容,则可以注释掉或删除If / Then语句。
Private Sub Form_Open(Cancel As Integer)
' Comments :
' Parameters: Cancel -
' Modified :
' --------------------------------------------------
On Error GoTo Err_Form_Open
Dim strSQL As String
Dim strVariable As String
Dim strDateVariable As String
Dim dteDateVariable As String
Dim i As Integer
Dim n As Integer
'Get variables from Left and right of | in OpenArgs
If Not (IsNull(Me.OpenArgs)) Then
i = InStr(1, Me.OpenArgs, "|")
n = Len(Me.OpenArgs)
strVariable = Left(Me.OpenArgs, n - (n - i + 1))
strDateVariable = Right(Me.OpenArgs, (n - i))
dteDateVariable = CDate(strDateVariable)
Else
GoTo Exit_Form_Open
End If
strSQL = "SELECT ... " _
& "FROM ... " _
& "WHERE (((Field1)='" & strVariable & "') " _
& " AND ((Field2)=#" & dteDateVariable & "#));"
Me.RecordSource = strSQL
Me.Requery
Exit_Form_Open:
Exit Sub
Err_Form_Open:
Select Case Err.Number
Case Else
Call ErrorLog(Err.Number, Err.Description, "Form_Open", "frmName", Erl)
GoTo Exit_Form_Open
End Select
End Sub
#1
My preferred solution is to set the recordsource in the Form Open event. This gives me the most control over what is going on.
我首选的解决方案是在Form Open事件中设置记录源。这让我能够最大程度地控制正在发生的事情。
Here is my boilerplate for doing this. It also includes looking up the OpenArgs which are passed on calling the form. You can just comment out or remove the If/Then statement if you aren't looking to specify anything from the calling form in your SQL.
这是我做这个的样板。它还包括查找调用表单时传递的OpenArgs。如果您不想在SQL中调用表单中的任何内容,则可以注释掉或删除If / Then语句。
Private Sub Form_Open(Cancel As Integer)
' Comments :
' Parameters: Cancel -
' Modified :
' --------------------------------------------------
On Error GoTo Err_Form_Open
Dim strSQL As String
Dim strVariable As String
Dim strDateVariable As String
Dim dteDateVariable As String
Dim i As Integer
Dim n As Integer
'Get variables from Left and right of | in OpenArgs
If Not (IsNull(Me.OpenArgs)) Then
i = InStr(1, Me.OpenArgs, "|")
n = Len(Me.OpenArgs)
strVariable = Left(Me.OpenArgs, n - (n - i + 1))
strDateVariable = Right(Me.OpenArgs, (n - i))
dteDateVariable = CDate(strDateVariable)
Else
GoTo Exit_Form_Open
End If
strSQL = "SELECT ... " _
& "FROM ... " _
& "WHERE (((Field1)='" & strVariable & "') " _
& " AND ((Field2)=#" & dteDateVariable & "#));"
Me.RecordSource = strSQL
Me.Requery
Exit_Form_Open:
Exit Sub
Err_Form_Open:
Select Case Err.Number
Case Else
Call ErrorLog(Err.Number, Err.Description, "Form_Open", "frmName", Erl)
GoTo Exit_Form_Open
End Select
End Sub