Creating an array for month/year fields 2

[josephwc]

Hello,

I have never created an array before but I know enough to know that I probably need to create one now. Unfortunately the problem I have does not seem like a beginner array problem. Access XP

I have fields on a form called
txtpast01, txtjan200601, ...txtdec200701, txtfuture01
to
txtpast08, txtjan200608, ...txtdec200708, txtfuture08

The number at the end of the field name associate the date fields to fields called txtfunc01 through txtfunc08.

If txtfunc01 is null then no month/year fields for the 01 row need to be filled out and so on.

I want to create a button when clicked automatically enters values into the month/year fields for the rows that have the txtfunc filled out.

There is a field called "Estimated Start Date" which determines what month/year field the values should start on.

There is a field called "duration" which says how many month/year fields past the estimated start date data should be entered.

There is also a reference table called "tbldur" which contains fields called "duration", "m", "m1" ... "m25". The duration can be linked to the duration referenced in the form and the "m" fields contain values which can fill the month/year fields in.

What should happen on click would be if Estimated Start Date is "10/1/2007" and duration is 6 and only txtfunc01 is filled in then txtoct200701 = m1, txtnov200701 = m2, txtdec200701 = m3, txtfuture01 = m4 + m5 + m6.

Please help. I have no idea how to do this.

 

[MajP]

I think you mean to say you want to create a "Function" or "Prodcedure" not an "array". You want to write some code to do something, an array is a type of data structure.
The code for this should not be too hard, but your data base design may have some problems. I would say that you are not normalized, based on what you are describing. Can you describe your tables a little more? The reason I say that is that you have fields that represent months and years. I would think that as time goes by these change. Likely these should be records not fields. If next year you are adding fields to your db, it is probably not a good design. Sometimes this is more Art than Science, and normalizing your database may be more work than needed. I would hate to give you a band-aid that will not last very long.

 

[josephwc]

The month/year fields are not the way I would generally create a table. Unfortunately I am restrained by an external database structure that I cannot edit. The database that I am modifying along with its external database is not going to last for more than a year. My requirement was to work within the current design framework. Unfortunately the whole project is a band-aid that will not last very long. You are right I need to create a function or procedure but I assumed that to create what I need I would need to create an array and a series of loops. Maybe I am thinking completely wrong. Do you know a simple way to do what I need to do?

There are two main tables, one called project and one called tblfunction. The tblfunction table has a total possible amount of eight functions for each project having a many to one relationship with the project table.

the tbldur that I referenced in my earlier e-mail has project duration lengths with amounts adding up to 100 in its 26 fields and can be associated to the project duration in the project table.

 

[MajP]

Gotcha. Definately sounds like I said "normalizing may be more work than is needed". In this case your hands are tied and you need to work with what they gave you. When working with databases we use a datastructure known as a recordset, like a multi dimensional array on steroids. VB provides two object libraries to do this: Data Access Objects (DAO) or Active X Data Objects (ADO). Both very similar, but ADO is newer and was planned to replace DAO. I think it all can be done with recordsets and no arrays.
You have a good naming convention for the controls which will make this somewhat easier to do. Let me just check a couple of things first. Each project has a duration which is an integer. What is the name of this field in table projects and in tblDur? Also once you fill this form in, is this data saved anywhere. It appears that the fields are unbounded.
Also when I open a form I have a project identifier. What field or control has the project ID? What is the field name? What is the name of the field in the tblFunc which links a project to a function?

 

[josephwc]

thanks again.

in table "tbldur" the field name is "duration".
in table "project" the field is called "projectDuration"

The form "frmEditProposalProject" has all unbound fields.
The data is saved by clicking a "Save" command button which saves the data back to the tables "project" and "tblfunction" for function names, function amounts, and 26 fields called past, future, and jan2006 - dec2007 using sQueries.

The field name for each project is "txtProjectCode"
The project field which links the "Project" table to the "tblfunction" table is called "internalProjectId".

 

[MajP]

One more thing, int tbl function you have fields:
txtFunc01 to txtFunc08. What is the datatype? You said it is a one to many, but is it just a one to one? There is one record of eight function fields for each project.

 

[josephwc]

the form has fields called txtFunc01 to txtFunc08.
the table on the other hand has a field called FunctionCode which takes the name selected in each of the fields txtFunc() and makes a record for each one. This may be too much information but here is the code below to save tblfunction data from the form.





Public Sub saveCurrentFunctions(ByVal pobjForm As Object, pID As String)
Dim frmObject As Object
Dim frmObject2 As Object
Dim frmObject3 As Object
Dim frmObject4 As Object
Dim frmObject5 As Object
Dim frmObject6 As Object
Dim frmObject7 As Object
Dim frmObject8 As Object
Dim frmObject9 As Object
Dim frmObject10 As Object
Dim frmObject11 As Object
Dim frmObject12 As Object
Dim frmObject13 As Object
Dim frmObject14 As Object
Dim frmObject15 As Object
Dim frmObject16 As Object
Dim frmObject17 As Object
Dim frmObject18 As Object
Dim frmObject19 As Object
Dim frmObject20 As Object
Dim frmObject21 As Object
Dim frmObject22 As Object
Dim frmObject23 As Object
Dim frmObject24 As Object
Dim frmObject25 As Object
Dim frmObject26 As Object
Dim frmObject27 As Object
Dim frmObject28 As Object
Dim sQuery As String
Dim iRow As Integer
Dim sIndex As String
Dim sFunctionCode As String
Dim sFunctionVal As String
Dim sjan2006 As String
Dim sfeb2006 As String
Dim smar2006 As String
Dim sapr2006 As String
Dim smay2006 As String
Dim sjun2006 As String
Dim sjul2006 As String
Dim saug2006 As String
Dim ssep2006 As String
Dim soct2006 As String
Dim snov2006 As String
Dim sdec2006 As String
Dim sjan2007 As String
Dim sfeb2007 As String
Dim smar2007 As String
Dim sapr2007 As String
Dim smay2007 As String
Dim sjun2007 As String
Dim sjul2007 As String
Dim saug2007 As String
Dim ssep2007 As String
Dim soct2007 As String
Dim snov2007 As String
Dim sdec2007 As String
Dim spast As String
Dim sfuture As String


On Error GoTo ErrorHandler

Set mobjForm = pobjForm

sQuery = "DELETE FROM ProjectFunction " & _
"WHERE ProjectFunction.internalProjectID = " & pID

CurrentDb.Execute sQuery

' Initialize iRow
iRow = 1

Do While (iRow <= 10)

If iRow < 10 Then
sIndex = "0" & iRow
Else
sIndex = iRow
End If

For Each frmObject In mobjForm
If (InStr(1, frmObject.ControlName, "txtFuncCode" & sIndex) > 0) Then

If Not IsNull(frmObject.Value) Then
sFunctionCode = frmObject.Value
Else
sFunctionCode = ""
End If

For Each frmObject2 In mobjForm

If (InStr(1, frmObject2.ControlName, "txtFuncVal" & sIndex) > 0) Then
If Not IsNull(frmObject2.Value) Then
sFunctionVal = frmObject2.Value
Else
sFunctionVal = ""
End If

For Each frmObject3 In mobjForm

If (InStr(1, frmObject3.ControlName, "txtjan2006" & sIndex) > 0) Then
If Not IsNull(frmObject3.Value) Then
sjan2006 = frmObject3.Value
Else
sjan2006 = "000"
End If

For Each frmObject4 In mobjForm

If (InStr(1, frmObject4.ControlName, "txtfeb2006" & sIndex) > 0) Then
If Not IsNull(frmObject4.Value) Then
sfeb2006 = frmObject4.Value
Else
sfeb2006 = "000"
End If

For Each frmObject5 In mobjForm

If (InStr(1, frmObject5.ControlName, "txtmar2006" & sIndex) > 0) Then
If Not IsNull(frmObject5.Value) Then
smar2006 = frmObject5.Value
Else
smar2006 = "000"
End If

For Each frmObject6 In mobjForm

If (InStr(1, frmObject6.ControlName, "txtapr2006" & sIndex) > 0) Then
If Not IsNull(frmObject6.Value) Then
sapr2006 = frmObject6.Value
Else
sapr2006 = "000"
End If

For Each frmObject7 In mobjForm

If (InStr(1, frmObject7.ControlName, "txtmay2006" & sIndex) > 0) Then
If Not IsNull(frmObject7.Value) Then
smay2006 = frmObject7.Value
Else
smay2006 = "000"
End If

For Each frmObject8 In mobjForm

If (InStr(1, frmObject8.ControlName, "txtjun2006" & sIndex) > 0) Then
If Not IsNull(frmObject8.Value) Then
sjun2006 = frmObject8.Value
Else
sjun2006 = "000"
End If

For Each frmObject9 In mobjForm

If (InStr(1, frmObject9.ControlName, "txtjul2006" & sIndex) > 0) Then
If Not IsNull(frmObject9.Value) Then
sjul2006 = frmObject9.Value
Else
sjul2006 = "000"
End If

For Each frmObject10 In mobjForm

If (InStr(1, frmObject10.ControlName, "txtaug2006" & sIndex) > 0) Then
If Not IsNull(frmObject10.Value) Then
saug2006 = frmObject10.Value
Else
saug2006 = "000"
End If

For Each frmObject11 In mobjForm

If (InStr(1, frmObject11.ControlName, "txtsep2006" & sIndex) > 0) Then
If Not IsNull(frmObject11.Value) Then
ssep2006 = frmObject11.Value
Else
ssep2006 = "000"
End If

For Each frmObject12 In mobjForm

If (InStr(1, frmObject12.ControlName, "txtoct2006" & sIndex) > 0) Then
If Not IsNull(frmObject12.Value) Then
soct2006 = frmObject12.Value
Else
soct2006 = "000"
End If

For Each frmObject13 In mobjForm

If (InStr(1, frmObject13.ControlName, "txtnov2006" & sIndex) > 0) Then
If Not IsNull(frmObject13.Value) Then
snov2006 = frmObject13.Value
Else
snov2006 = "000"
End If

For Each frmObject14 In mobjForm

If (InStr(1, frmObject14.ControlName, "txtdec2006" & sIndex) > 0) Then
If Not IsNull(frmObject14.Value) Then
sdec2006 = frmObject14.Value
Else
sdec2006 = "000"
End If

For Each frmObject15 In mobjForm

If (InStr(1, frmObject15.ControlName, "txtjan2007" & sIndex) > 0) Then
If Not IsNull(frmObject15.Value) Then
sjan2007 = frmObject15.Value
Else
sjan2007 = "000"
End If

For Each frmObject16 In mobjForm

If (InStr(1, frmObject16.ControlName, "txtfeb2007" & sIndex) > 0) Then
If Not IsNull(frmObject16.Value) Then
sfeb2007 = frmObject16.Value
Else
sfeb2007 = "000"
End If


For Each frmObject17 In mobjForm

If (InStr(1, frmObject17.ControlName, "txtmar2007" & sIndex) > 0) Then
If Not IsNull(frmObject17.Value) Then
smar2007 = frmObject17.Value
Else
smar2007 = "000"
End If

For Each frmObject18 In mobjForm

If (InStr(1, frmObject18.ControlName, "txtapr2007" & sIndex) > 0) Then
If Not IsNull(frmObject18.Value) Then
sapr2007 = frmObject18.Value
Else
sapr2007 = "000"
End If

For Each frmObject19 In mobjForm

If (InStr(1, frmObject19.ControlName, "txtmay2007" & sIndex) > 0) Then
If Not IsNull(frmObject19.Value) Then
smay2007 = frmObject19.Value
Else
smay2007 = "000"
End If

For Each frmObject20 In mobjForm

If (InStr(1, frmObject20.ControlName, "txtjun2007" & sIndex) > 0) Then
If Not IsNull(frmObject20.Value) Then
sjun2007 = frmObject20.Value
Else
sjun2007 = "000"
End If

For Each frmObject21 In mobjForm

If (InStr(1, frmObject21.ControlName, "txtjul2007" & sIndex) > 0) Then
If Not IsNull(frmObject21.Value) Then
sjul2007 = frmObject21.Value
Else
sjul2007 = "000"
End If

For Each frmObject22 In mobjForm

If (InStr(1, frmObject22.ControlName, "txtaug2007" & sIndex) > 0) Then
If Not IsNull(frmObject22.Value) Then
saug2007 = frmObject22.Value
Else
saug2007 = "000"
End If

For Each frmObject23 In mobjForm

If (InStr(1, frmObject23.ControlName, "txtsep2007" & sIndex) > 0) Then
If Not IsNull(frmObject23.Value) Then
ssep2007 = frmObject23.Value
Else
ssep2007 = "000"
End If

For Each frmObject24 In mobjForm

If (InStr(1, frmObject24.ControlName, "txtoct2007" & sIndex) > 0) Then
If Not IsNull(frmObject24.Value) Then
soct2007 = frmObject24.Value
Else
soct2007 = "000"
End If

For Each frmObject25 In mobjForm

If (InStr(1, frmObject25.ControlName, "txtnov2007" & sIndex) > 0) Then
If Not IsNull(frmObject25.Value) Then
snov2007 = frmObject25.Value
Else
snov2007 = "000"
End If

For Each frmObject26 In mobjForm

If (InStr(1, frmObject26.ControlName, "txtdec2007" & sIndex) > 0) Then
If Not IsNull(frmObject26.Value) Then
sdec2007 = frmObject26.Value
Else
sdec2007 = "000"
End If

For Each frmObject27 In mobjForm

If (InStr(1, frmObject27.ControlName, "txtpast" & sIndex) > 0) Then
If Not IsNull(frmObject27.Value) Then
spast = frmObject27.Value
Else
spast = "000"
End If

For Each frmObject28 In mobjForm

If (InStr(1, frmObject28.ControlName, "txtfuture" & sIndex) > 0) Then
If Not IsNull(frmObject28.Value) Then
sfuture = frmObject28.Value
Else
sfuture = "000"
End If

If sFunctionCode <> "" Then
sQuery = "INSERT INTO ProjectFunction ( " & _
"internalProjectId, FunctionCode, jan2006, feb2006, mar2006, apr2006, may2006, jun2006, jul2006, aug2006, sep2006, oct2006, nov2006, dec2006, jan2007, feb2007, mar2007, apr2007, may2007, jun2007, jul2007, aug2007, sep2007, oct2007, nov2007, dec2007, past, future, " & _
"FunctionValue) " & _
"VALUES (" & pID & "," & _
"'" & sFunctionCode & "', '" & sjan2006 & "', '" & sfeb2006 & "', '" & smar2006 & "', '" & sapr2006 & "', '" & smay2006 & "', '" & sjun2006 & "', '" & sjul2006 & "', '" & saug2006 & "', '" & ssep2006 & "', '" & soct2006 & "', '" & snov2006 & "', '" & sdec2006 & "', '" & sjan2007 & "', '" & sfeb2007 & "', '" & smar2007 & "', '" & sapr2007 & "', '" & smay2007 & "', '" & sjun2007 & "', '" & sjul2007 & "', '" & saug2007 & "', '" & ssep2007 & "', '" & soct2007 & "', '" & snov2007 & "', '" & sdec2007 & "', '" & spast & "', '" & sfuture & "', " & _
"'" & sFunctionVal & "')"

CurrentDb.Execute (sQuery)
End If
Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

Exit For
End If

Next

iRow = iRow + 1
Loop

 

[MajP]

I will trust you on that one! When you want to run the code to populate these fields where do I pull the Project Code, Estimated Start Date, and Project Duration. Are they all on form already? Or is the form bound to the project table. If they are in on the form what are the control names.

 

[josephwc]

The Project Code
Estimated Start Date
and Project Duration values are all stored in the "project" table. The form is not bound to the project table. There are no control names on the fields.
The field names on the form are
txtProjectCode
txtEstProjStartDate
txtProjectDuration

 

[MajP]

I tried to think of a simple way to do this, but could not. So I went back to what you said about an array of controls, and it started to make a lot of sense. So the first thing I did was dimension a two dimensional array. The rows are indexed 1 to 8, the columns are indexed 0 to 25.
aCntrl(1,0) is txtPast01
aCntrl(1,1) is txtJan200601
(1,12) is txtDec200601
(1,13) is txtJan200701
aCntrl(1,25) is txtFuture01

Assuming your name convention is complete this loads the array

Option Compare Database
Option Explicit
Dim aCntrls(1 To 8, 0 To 25) As Object

Public Sub dateControlArray()
Dim myCntrl As Access.Control
Dim myCol As Integer
Dim myRow As Integer
 For Each myCntrl In Forms("frmEditProposalProject").Controls
   If myCntrl.ControlType = acTextBox Then
     'a monthYear control
     If (InStr(1, myCntrl.Name, "2006") + InStr(1, myCntrl.Name, "2007")) > 0 Then
       myRow = fncRowIndex(myCntrl)
       myCol = fncColIndex(myCntrl)
       Set aCntrls(myRow, myCol) = myCntrl
     End If
     'a txtPastXX control
     If InStr(1, myCntrl.Name, "txtPast") Then
       myRow = fncRowIndex(myCntrl)
       myCol = 0
       Set aCntrls(myRow, myCol) = myCntrl
     End If
     'a txtFuture control
     If InStr(1, myCntrl.Name, "txtFuture") Then
       myRow = fncRowIndex(myCntrl)
       myCol = 25
   End If
 Next myCntrl
End Sub


Public Function fncRowIndex(cntrl As Access.Control) As Integer
   fncRowIndex = CInt(Right(cntrl.Name, 1))
End Function
Public Function fncColIndex(cntrl As Access.Control) As Integer
    If InStr(1, cntrl.Name, "2006") > 0 Then
      fncColIndex = fncMonth(Mid(cntrl.Name, 4, 3))
    End If
    If InStr(1, cntrl.Name, "2007") > 0 Then
      fncColIndex = 12 + fncMonth((Mid(cntrl.Name, 4, 3)))
    End If
 End Function

Public Function fncMonth(strMonth As String) As Integer
  Select Case Format(strMonth, ">")
    Case "jan"
      fncMonth = 1
    Case "feb"
      fncMonth = 2
    Case "mar"
      fncMonth = 3
    Case "apr"
      fncMonth = 4
    Case "may"
      fncMonth = 5
    Case "jun"
      fncMonth = 6
    Case "jul"
      fncMonth = 7
    Case "aug"
      fncMonth = 8
    Case "sep"
      fncMonth = 9
    Case "oct"
      fncMonth = 10
    Case "nov"
      fncMonth = 11
    Case "dec"
      fncMonth = 12
    End Select
End Function

I had to build "fncMonth" because I could not get the built in Month function to work


Now I get the Project Code, the Est Start Date, and project duration off of the form. I make a recordset of those records in the function table that match a given project code. I make another recordset that returns the record that matches the duration from tbl duration. I loop through all the function records, and all the duration fields for each function.

Public Sub insertMValues()
  Dim rsMValues As DAO.Recordset
  Dim rsFunction As DAO.Recordset
  Dim dtmEstStartDate As Date
  Dim intDuration As Integer
  Dim intStartMonth As Integer
  Dim intStartYear As Integer
  Dim strSqlMValues As String
  Dim strSqlFuncs As String
  Dim strProjectCode As String
  Dim strFuncCode As String
  Dim strFuncValue As String
  Dim intMCounter As Integer
  
  intDuration = Me.txtProjectDuration
  strProjectCode = Me.txtProjectCode
  dtmEstStartDate = Me.txtEstProjStartDate
  intStartMonth = Month(dtmEstStartDate)
  intStartYear = Year(dtmEstStartYear)
  strSqlMValues = "Select * from tblDur Where duration = " & intDuration
  Set rsMValues = CurrentDb.OpenRecordset(strSqlMValues, dbOpenDynaset)
  strSqlFunc = "Select * from tblFunc where internalProjectID = '" & strProjectCode & "'"
  Set rsFunction = CurrentDb.OpenRecordset(strSqlFunc, dbOpenDynaset)
  If intStartYear = 2007 Then
    intStartMonth = intStartMonth + 12
  End If
  'Loop through all functions associated with a project
  Do While Not rsFunction.EOF
    If Not IsNull(rsFunction.Fields("FunctionCode")) Then
      strFuncCode = rsFunction.Fields("FunctionCode")
      strFuncValue = Right(strFuncCode, 2)
      rsMValues.MoveFirst
      'loop through the relevant M value fields
      For intMCounter = 0 To intDuration - 1
        'if you get to the last contrl "txtDec2007xx" put everything in future
        If intMCounter + instStartMonth = 25 Then
          aCntrls(CInt(strFuncValue), 25) = aCntrls(CInt(strFuncValue), 25) + rsMvalue.Fields("m" & intMCounter + 1)
        Else
          aCntrls(CInt(strFuncValue), intMCounter + intStartMonth) = rsMvalue.Fields("m" & intMCounter + 1)
        End If
      rsFunction.MoveNext
    End If
  Loop
End Sub

I am sure there are a lot of bugs in here, and there is no error checking. But I think the logic is sound, and it is pretty close.

 

[josephwc]

Thanks alot MajP. You have been extremely helpful! Do you have any suggestions for a novice at VBA writing how to grow in skill?

 

[josephwc]

Last question. Do I put all these functions in a module? How do I reference these in the on click event of a button on the form?

 

[josephwc]

This portion of the statement is saying that there is a loop without a "Do"

Do While Not rsFunction.EOF
If Not IsNull(rsFunction.Fields("FunctionCode")) Then
strFuncCode = rsFunction.Fields("FunctionCode")
strFuncValue = Right(strFuncCode, 2)
rsMValues.MoveFirst
End If

'loop through the relevant M value fields
For intMCounter = 0 To intDuration - 1
'if you get to the last contrl "txtDec2007xx" put everything in future
If intMCounter + intStartMonth = 25 Then
aCntrls(CInt(strFuncValue), 25) = aCntrls(CInt(strFuncValue), 25) + rsMValues.Fields("m" & intMCounter + 1)
Else
aCntrls(CInt(strFuncValue), intMCounter + intStartMonth) = rsMValues.Fields("m" & intMCounter + 1)
End If
rsFunction.MoveNext


Loop

 

[MajP]

1. Place the code in a module. On your form, you should have something to initiate the code, probably a command button. The code for the command buttons click event should be on the forms module. Something like

private sub cmdFillForm_click()
call dateControlArray()
call insertMValues()
end sub

2. Do not thank me yet, until you get this code to work. I am sure that you will have to play with the code a lot. Like I said, it is more like a prototype. Without having the actual Form and tables, it is hard to error check anything. If you can not figure something out, just post back.

3. I am self taught, and not in the IT field. But, I have learned a tremendous amount on this site from some really smart people. I find I learn a lot by answering posts like these. Get yourself a good general VBA book (the Black Books, Unleashed, Bible, etc.). I have a basic Access Book (Inside Out), but by far the best resource I have for really making Access Applications are the Access Developer's Handbook series (Litwin, Getz, and Gunderloy)

4. One thing to consider, especially if you get the Access Developers Handbook, is to learn about custom classes and custom collections. Looking at your function and the form you describe, I would had made a custom class for the controls that we put in the array. I would have also made a custom collection. There would have been some overhead in doing this, but your code could be extremely simplified.

 

[MajP]

I see the problem. It is actually my For Next Loop

For intMCounter = 0 To intDuration - 1
'if you get to the last contrl "txtDec2007xx" put everything in future
If intMCounter + intStartMonth = 25 Then
aCntrls(CInt(strFuncValue), 25) = aCntrls(CInt(strFuncValue), 25) + rsMValues.Fields("m" & intMCounter + 1)
Else
aCntrls(CInt(strFuncValue), intMCounter + intStartMonth) = rsMValues.Fields("m" & intMCounter + 1)
End If
next intMCounter
rsFunction.MoveNext

Also I said to put this in a module. So you need to change wherever I put the "Me" keyword.
intDuration = Me.txtProjectDuration
strProjectCode = Me.txtProjectCode
dtmEstStartDate = Me.txtEstProjStartDate

Something more like
intDuration = Forms("frmEditProposalProject").txtProjectDuration

 

[josephwc]

I am getting the message "Data Type mismatch in criteria expression"

On debug the line
Set rsFunction = CurrentDb.OpenRecordset(strSqlFunc, dbOpenDynaset)
is highlighted

What do you think is causing this?

 

[PHV]

If internalProjectID is defined as numeric in tblFunc, then replace this:
strSqlFunc = "Select * from tblFunc where internalProjectID = '" & strProjectCode & "'"
with this:
strSqlFunc = "Select * from tblFunc where internalProjectID = " & strProjectCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[josephwc]

I found the Data Type mismatch problem. I was referencing the wrong field value. internal Project Id is actually the number of open arguments.

msInternalProjectId = Forms!frmEditProposalProject.OpenArgs

I am getting another error but I think I know why. In the portion of the argument below the "Function Code" value stored in the table does not have a number between 01 and 08. Only the name of the fields on the form do.

strSqlFunc = "Select * from ProjectFunction Where internalProjectID = " & msInternalProjectId

Set rsFunction = CurrentDb.OpenRecordset(strSqlFunc, dbOpenDynaset)

Do While Not rsFunction.EOF
If Not IsNull(rsFunction.Fields("FunctionCode")) Then
strFuncCode = rsFunction.Fields("FunctionCode")
strFuncValue = Right(strFuncCode, 2)

rsMValues.MoveFirst
End If

'loop through the relevant M value fields
For intMCounter = 0 To intDuration - 1
'if you get to the last contrl "txtDec2007xx" put everything in future
If intMCounter + intStartMonth = 25 Then
aCntrls(CInt(strFuncValue), 25) = aCntrls(CInt(strFuncValue), 25) + rsMValues.Fields("m" & intMCounter + 1)
Else
aCntrls(CInt(strFuncValue), intMCounter + intStartMonth) = rsMValues.Fields("m" & intMCounter + 1)
End If
Next intMCounter
rsFunction.MoveNext


Should I reference the form instead of the ProjectFunction table?

Type mismatch error on the line
aCntrls(CInt(strFuncValue), intMCounter + intStartMonth) = rsMValues.Fields("m" & intMCounter + 1)

 

[MajP]

For some reason I thought that in the table ProjectFunction, you had something like where the FunctionCode field had a code for the function with the funciton number in it:

InternalProjectID FunctionCode
ProjectOne Func01
ProjectOne Func03
ProjectTwo Func01
ProjectTwo Func08

What is in the Function code field? Is this what the table looks like, and the data?

 

[MajP]

The reason that this assumption was important was that was how I was determining which "function rows" to fill in. For example if I was doing Project One I would return two records. Using my function I would get 1 and fill in the row then I would get 3 and fill in row 3.