Hello,
I am new to this forum and need help J
I do know that this is not a code writer service, so I am not asking for this. What I am looking for is a “pointer” on possible way to achieve my desired result.
My database is for accounting purposes in which the user will log in and be presented for a number of transactions that must be verified. All functions (import of data, log in user, define criteria’s for control etc) are finished but one essential are missing. Please allow me to explain:
Table1: all transactions
Table1: 12 calculated columns (cto500, cto600, CtoVAT etc.) that have a YES or NO value
Table1: 1 empty field called ControlType
(These columns are calculated by criterias defined by the admin user In a own interface solution, expect the empty ControlType which are my problem)
Table2: 13 columns (ControlType and cto500, cto6oo, ctoVAT) etc.
(ControlType is the name of the control that the user shall perform and the rest are the criteria’s the admin user has chosen to define the control (name could be S1, S2 etc. but this could change by changing the name in table2). A control can be defined with two or moore of the total criteria`s. The system should allow new ControlType to be defined directly in table2)
Table2 are separate and stands alone in relation to table1. There are no uniqe ID that bind these two together.
What I trye to do is:
In a event triggered by “Update bottom” use the criteria fields per ControlType in Table2 as parameters for an update query that update Table1 field ControlType with control name S1, S2 etc. Table2 also hold a field called priority that defines the orders of ControlType, meaning that if a criteria in two controls are the same priority 1 shall be chosen..etc..
My idea was to use a BuildCritaria sql at table2 to define the exact sql defined per ControlType, and the use the same criterias as parameters in update query at table1. In VBA I would first use “buildCriteria” and the implement it as parameters in Insert INTO table1. Is this possible ? what I got this far is only this (and I am not sure on how to make it loop through all records. Not only the one in focus):
Private Sub Søk_DblClick(Cancel As Integer)
On Error Resume Next
Dim ctl As Control
Dim sSql As String
Dim Swhereclause As String
Dim comboBoxText As String
Swhereclause = " Where "
sSql = "select * from Beslutningsmatrise "
For Each ctl In Controls
With ctl
Select Case .ControlType
Case acTextBox
.SetFocus
If Swhereclause = " Where " Then
Swhereclause = Swhereclause & BuildCriteria(.Name, dbText, .Text)
Else
Swhereclause = Swhereclause & " and " & BuildCriteria(.Name, dbText, .Text)
End If
End Select
End With
Next ctl
Me.Txtsql = sSql & Swhereclause
Me.RecordSource = sSql & Swhereclause
Me.Requery
End Sub
I am new to this forum and need help J
I do know that this is not a code writer service, so I am not asking for this. What I am looking for is a “pointer” on possible way to achieve my desired result.
My database is for accounting purposes in which the user will log in and be presented for a number of transactions that must be verified. All functions (import of data, log in user, define criteria’s for control etc) are finished but one essential are missing. Please allow me to explain:
Table1: all transactions
Table1: 12 calculated columns (cto500, cto600, CtoVAT etc.) that have a YES or NO value
Table1: 1 empty field called ControlType
(These columns are calculated by criterias defined by the admin user In a own interface solution, expect the empty ControlType which are my problem)
Table2: 13 columns (ControlType and cto500, cto6oo, ctoVAT) etc.
(ControlType is the name of the control that the user shall perform and the rest are the criteria’s the admin user has chosen to define the control (name could be S1, S2 etc. but this could change by changing the name in table2). A control can be defined with two or moore of the total criteria`s. The system should allow new ControlType to be defined directly in table2)
Table2 are separate and stands alone in relation to table1. There are no uniqe ID that bind these two together.
What I trye to do is:
In a event triggered by “Update bottom” use the criteria fields per ControlType in Table2 as parameters for an update query that update Table1 field ControlType with control name S1, S2 etc. Table2 also hold a field called priority that defines the orders of ControlType, meaning that if a criteria in two controls are the same priority 1 shall be chosen..etc..
My idea was to use a BuildCritaria sql at table2 to define the exact sql defined per ControlType, and the use the same criterias as parameters in update query at table1. In VBA I would first use “buildCriteria” and the implement it as parameters in Insert INTO table1. Is this possible ? what I got this far is only this (and I am not sure on how to make it loop through all records. Not only the one in focus):
Private Sub Søk_DblClick(Cancel As Integer)
On Error Resume Next
Dim ctl As Control
Dim sSql As String
Dim Swhereclause As String
Dim comboBoxText As String
Swhereclause = " Where "
sSql = "select * from Beslutningsmatrise "
For Each ctl In Controls
With ctl
Select Case .ControlType
Case acTextBox
.SetFocus
If Swhereclause = " Where " Then
Swhereclause = Swhereclause & BuildCriteria(.Name, dbText, .Text)
Else
Swhereclause = Swhereclause & " and " & BuildCriteria(.Name, dbText, .Text)
End If
End Select
End With
Next ctl
Me.Txtsql = sSql & Swhereclause
Me.RecordSource = sSql & Swhereclause
Me.Requery
End Sub