newbie need direction (1 Viewer)

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 didn't really get to the part about the issue you are posting about (I glanced at it), I mostly stopped when I saw your table set up. It is incorrect and you should read up on database normalization (https://en.wikipedia.org/wiki/Database_normalization) before trying to hack together a solution to your current issue.

Calculated fields shouldn't be in a table, instead you should write a query to calculate those values and have them live there. That means those 12 fields in Table1 come out, as well as the ControlType field since it seems to be a calculated field as well.

Also, you shouldn't store values in field names. With fields named cto500, cto600, ctoVAT, etc. I am pretty sure thats what you are doing. Instead those values should be stored in the table itself. Suppose all those previously mentioned fields contained number data like so:

Table2
cto500, cto600, ctoVAT, ...
12, 38, 1.9, ...

That is wrong. Instead that data should be stored like so:

ctoType, ctoValue
500, 12
600, 38
VAT, 1.9
...
...

Lastly, in general, data should not be moved or have a process to update itself from other data in the database. UPDATE processes and queries are usually hacks around poor structure or a lack of knowledge about normalization. Ultimately, I believe the ControlType field will be in a query somewhere, not stored in a table.

Again, read up on normalization and get your tables structured correctly.

Hello,
Thank you for Your reply.

Im sorry for my poor explanation - Table2 is a stand alone table where the user review a set of possible criterias. After user has made this review the choises that are done is then the definition of which criterias that defines ControlType. Reason why this are made in this table are of documention reasons.

Table1 is a export from the acounting system.

I do have a Query that calculate the 12 columns and Return the answer YES/NO. I agree, i don not need the same in table1.

But my problem remain - how to use the criterias user has defined in table two (that is; YES/NO or BLANK) to determine ControlType. As an example here is a sample line for table2:

Controltype: S1
Cto5000: NO
Cto6000;NO
Cto7000;YES
VAT: Blank

Query (and/or in table1):

Acount#: 7010
Cto5000: NO
Cto6000;NO
Cto7000;YES
Cto8000;NO
Amount<100T;NO
VAT: Blank

ControlType; nothing yet.. but wish to use those criterias in table2 that user has ticked off, if they match the row in table1 - then change value to "S1". If no match.. do nothing. Please note that the rows in table1 has values in ALL columns, but the user will not use all of those columns.

Please see my answer in your other thread:
https://www.access-programmers.co.uk/forums/showthread.php?p=1567767#post1567767

Cheers,
Vlad

I think I need to ask a question of clarification because one of the words you use has multiple values in English. When you say control, I have been in TWO environments where the word "control" was used but they meant different things.

1. Are you talking about specific controls that do something on an Access form?

Or

2. Are you talking "administrative control" as something you do as part of a formal series of procedural checks?

Your discussion SEEMS to point to form controls, but then the idea of recording the type of a control is bizarre because if you have a control named ctl, then ctl.Type tells you the type-code for the control. In a run-time context (but not in design context) you can also use TypeName(ctl) to find out what kind of control you have.

For example, I had a check-box named ForceWrite on one of my forms. So I had to put in a breakpoint to do this, but then from the immediate window, I got this:


The green text was the response.

If you do this in design context, that control is not fully extant and you get an error. But when the form is in Form view, the control is fully extant and you can use the TypeName function to get back a string of the control's type's name.

To see what else you can play with along these lines, get into a code window and open the Object Browser. Find the entry Information and look at the informational functions listed there. TypeName is one of them.

In any case, remembering the type of control is never needed at run time since you can query that information just knowing the control name.

Erik, your description is way too detailed for any of us to make sense of it. Stand back and in a single sentence tell us what the application does. Then describe what this function needs to do using only a few sentences at most.

Just looking at:
Acount#: 7010
Cto5000: NO
Cto6000;NO
Cto7000;YES
Cto8000;NO
It looks like the cto fields are groups and the Yes is indicating the group that 7010 belongs to. If that is the case, there are significantly easier ways to do this.

PS - object names should not contain embedded spaces or special characters. They should use only upper and lower case letters, numbers, and the underscore. Using other characters forces you to have to encapsulate the names using square brackets which just makes code and queries look overly busy. Plus offending names will cause problems with VBA. You also need to avoid function and property names such as Month, Year, Name, etc.

Does Me.Name refer to the name of the form or is it the Name field of your recordset? How is Access supposed to know?

The_Doc_Man said:

I think I need to ask a question of clarification because one of the words you use has multiple values in English. When you say control, I have been in TWO environments where the word "control" was used but they meant different things.

1. Are you talking about specific controls that do something on an Access form?

Or

2. Are you talking "administrative control" as something you do as part of a formal series of procedural checks?

Your discussion SEEMS to point to form controls, but then the idea of recording the type of a control is bizarre because if you have a control named ctl, then ctl.Type tells you the type-code for the control. In a run-time context (but not in design context) you can also use TypeName(ctl) to find out what kind of control you have.

For example, I had a check-box named ForceWrite on one of my forms. So I had to put in a breakpoint to do this, but then from the immediate window, I got this:

Code:Copy to clipboard

Debug.Print TypeName([ForceWrite])
[COLOR=seagreen]CheckBox[/COLOR]

The green text was the response.

If you do this in design context, that control is not fully extant and you get an error. But when the form is in Form view, the control is fully extant and you can use the TypeName function to get back a string of the control's type's name.

To see what else you can play with along these lines, get into a code window and open the Object Browser. Find the entry Information and look at the informational functions listed there. TypeName is one of them.

In any case, remembering the type of control is never needed at run time since you can query that information just knowing the control name.

Click to expand...

Hi,
i am sorry - as all that read my post can see, Programming is not what i know best . Maybe i explain the Whole ting better in this post (where i focus on the Query instead): under Query - and "multiple chose - dynamic Query".