Normalizing -- How to Read/Write to Form

  • Thread starter Thread starter wrldruler
  • Start date Start date
W

wrldruler

I've got a form that will be used to budget project costs for 60
months. I've got a tab interface that looks like a grid:

M1 M2 .... M60
HW Cost:
SW Cost:
....
Other Cost:

So there are 6 types of Costs x 60 months = 360 textboxes. I am happy
with the form. Code is effecient, I just iterate through the texboxes
using Controls and For, Next loops.

Right now the textboxes are Unbound, and the data is not being saved
anywhere. How do I save this data into a normalized table? I do NOT
want to bind these 360 textboxes into 360 table fields.

I want my table to look like this:

Project_ID
Cost_Type
Month
Value

Question #1: How do I save? How do I take data contained in 360
textboxes stretching horizontally across the screen, and save it into a
table that is only 4 rows across?

Question #2: How do I do the reverse? If I want to pull this data up
later using the same 360 textbox form, how do I read the data and put
them in the correct places on the form?

Thanks!
 
Assuming that your 360 text boxes are named something like txtCost11,
txtCost12,... txtCost160, txtCost21, txtCost22, ... txtCost260,...
txtCost61, txtCost62, ... txtCost660 (and that the costs in the first column
are txtCostType1, txtCostType2,...txtCostType6), you'd need something like:

Dim dbCurr As DAO.Database
Dim intCost As Integer
Dim intMonth As Integer
Dim strSQL As String

Set dbCurr = CurrentDb()

For intCost = 1 to 6
For intMonth = 1 To 60
strSQL = "INSERT INTO MyTable " & _
"(Project_ID, Cost_Type, WhatMonth, WhatValue) " & _
"VALUES (" & txtProjectId & ", '" & _
Me.Controls("txtCostType" & intCost) & "', " & _
intMonth & ", " & Me.Controls("txtCost" & intCost & intMonth) & ")"
dbCurr.Execute strSQL, dbFailOnError
Next intLoop
Next intCost

If some of the boxes might be empty, you'd want to check:


Dim dbCurr As DAO.Database
Dim intCost As Integer
Dim intMonth As Integer
Dim strSQL As String

Set dbCurr = CurrentDb()

For intCost = 1 to 6
For intMonth = 1 To 60
If IsNull(Me.Controls("txtCost" & intCost & intMonth) = False Then
strSQL = "INSERT INTO MyTable " & _
"(Project_ID, Cost_Type, WhatMonth, WhatValue) " & _
"VALUES (" & txtProjectId & ", '" & _
Me.Controls("txtCostType" & intCost) & "', " & _
intMonth & ", " & Me.Controls("txtCost" & intCost & intMonth) &
")"
dbCurr.Execute strSQL, dbFailOnError
End If
Next intLoop
Next intCost
 
Doug,

I think that will work -- I will try tommorrow.

Can you help me with Part 2 of the question. If the person enters the
data, I submit it via the Unbound method you descirbed. The next day,
the person wants to return to the screen and see what he or she has
already entered.

How do I get the normalized data back out, and thrown into the 360
textboxes where they should be?

Thanks,

Chris
 
Something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim intCost As Integer
Dim intMonth As Integer
Dim strSQL As String

strSQL = "SELECT Cost_Type, WhatMonth, WhatValue FROM MyTable " & _
"WHERE Project_ID = " & Me.txtProjectId
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)

Do Until rsCurr.EOF
Select Case rsCurr.Cost_Type
Case "HW Cost"
intCost = 1
Case "SW Cost"
intCost = 2
Case "Cost 3"
intCost = 3
...
Case "Cost 6"
intCost = 6
End Select
Me.Controls("txtCost" & intCost & rsCurr!WhatMonth) = rsCurr!WhatValue
rsCurr.MoveNext
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

One thing I forgot to explicitly point out is that I changed the names of
two of your fields in the original code, and the code above. Both Month and
Value are reserved words, and shouldn't be used as field names.
 
Actually, thinking about it some more, you should also be able to use a
crosstab query to return the data as 6 rows, similar to how you're
displaying it. That might make it easier to write to the text boxes.
 
Back
Top