Fill out all records of a look up table with one form

[seocavaz]

I have this look up table:

ID	POF	COF	Iinterval	RiskMatrix
1	1	6	10	1
2	1	5	7	2
3	1	4	5	3
4	1	3	3	4
5	1	2	1	5
6	1	1	1	6
7	2	6	12	7
8	2	5	10	8
9	2	4	7	9
10	2	3	5	10
11	2	2	3	11
12	2	1	1	12
13	3	6	15	13
14	3	5	12	14
15	3	4	10	15
16	3	3	7	16
17	3	2	5	17
18	3	1	3	18
19	4	6	20	19
20	4	5	15	20
21	4	4	12	21
22	4	3	10	22
23	4	2	7	23
24	4	1	5	24
25	5	6	20	25
26	5	5	20	26
27	5	4	15	27
28	5	3	12	28
29	5	2	10	29
30	5	1	7	30
31	6	6	20	31
32	6	5	20	32
33	6	4	20	33
34	6	3	15	34
35	6	2	12	35
36	6	1	10	36

and I would like to use the form attached to fill out the [iinterval] column. [RiskMatrix] in the table corresponds with the square on the matrix-form attached.

any help would be much appreciated!

[ssanfu]

Not a clue what you want to do. Everything appears to be filled in.
Would you try and explain again??? What results do you want??

[seocavaz]

sorry, hopefully this is clearer.

I want to be able to type values in the colored squares in the matrix and have it update the corresponding record in the table. ie. where the cursor is is [riskmatrix]=1 so I want to be able to type 15 in that yellow square and it change the [iinterval] from 10 to 15.

[seocavaz]

In my google search I found this:

Private Sub Risk1CT_AfterUpdate()

update frm_matrix - inspectionintervals
Set [tbl_inspectioninterva].[Iinterval] = [frm_matrix-inspectionintervals].[Risk1CT]
WHERE tbl_inspectioninterval.[RiskMatrix] = 1

End Sub

however I keep getting sub or function not defined.

[June7]

CurrentDb.Execute "UPDATE [tbl_inspectioninterval] SET [Iinterval] = " & Me.Risk1CT & " WHERE [RiskMatrix] = 1"

[ssanfu]

So you will have to write 36 SQL update statements.

OR you might think about writing a UDF with arguments. Something like
(AIR CODE)

Sub UpdateInterval(intInterval As Integer, intRiskMatrix As Integer)

    CurrentDb.Execute "UPDATE [tbl_inspectioninterval] SET [Iinterval] = " & intInterval & " WHERE [RiskMatrix] = " & intRiskMatrix

End Sub

To update interval, you would use:

Private Sub Risk1CT_AfterUpdate()
   Call UpdateInterval(Risk1CT, 1)
End Sub

For another "colored box" (you have a control there, right?), you would use:

Private Sub Risk2CT_AfterUpdate()
   Call UpdateInterval(Risk2CT, 2)
End Sub

Would do this 36 times.


Be warned, "Interval" is a reserved word in Access - shouldn't be used for object names.

[seocavaz]

Perfect! thanks for everyone's help!

[seocavaz]

Is there any way to also have it show the latest in the form when you open it? ie. shows all of the original records in from the table, and as I update them, they stay for the next time we open to review/change?

[June7]

That would require code to pull the values from table and populate the unbound controls in some event, possibly form Open or Load. Something like:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset ("SELECT * FROM tablename;")
rs.MoveFirst
While Not rs.EOF
Me("Risk" & rs!RiskMatrix & "CT") = rs!linterval
rs.MoveNext
Wend

[seocavaz]

thanks! I had to figure out how to turn on the DAO library, but now it works perfect