how to improve code - vba programming reference to the text box field on form

[registoni]

in one form I try present editable cross-tab query like form for displaying values in several columns from flat table (the data is financial data from various periods = FFI_Period for each financial statement item =FinStmtItem). I first denormalize the table by retrieving required values from table onto the unbound fields on the form (via recordset.findfirst method and then assigning value from table field onto the unbound text field) and then save the fields back (via recordset.edit/.add method) to the table in normalized form. One piece of code keeps repeating several times in my procedure. Here is an example of retrieving data from flat table by denormalizing it (*PrevPerItem*X - is the name of the unbound text field on the form):

Set myR = CurrentDb.OpenRecordset("tbl_FFIFinancials", dbOpenDynaset)
myR.FindFirst ("[FFI_PERIOD] =" & intFFIPeriod1 & "And [FinStmtItem] = 1")
PrevPerItem1 = myR.Fields("Amount").Value

myR.FindFirst ("[FFI_PERIOD] =" & intFFIPeriod1 & "And [FinStmtItem] = 2")
PrevPerItem2 = myR.Fields("Amount").Value

myR.FindFirst ("[FFI_PERIOD] =" & intFFIPeriod1 & "And [FinStmtItem] = 3")
PrevPerItem3 = myR.Fields("Amount").Value

'.... [similar codes repeats here as well from No 4 throu 16]............


myR.FindFirst ("[FFI_PERIOD] =" & intFFIPeriod1 & "And [FinStmtItem] = 17")
PrevPerItem17 = myR.Fields("Amount").Value

Similar code is for edit and add method (putting back normalized data)

 Set myR = CurrentDb.OpenRecordset("tbl_FFIFinancials", dbOpenDynaset)

'updating values for the previous period

MsgBox "Updating values into Database for the period - " & intPrevYear & "-Q" & byteSelectQuarter

myR.FindFirst ("[FFI_PERIOD] =" & intFFIPeriod1 & "And [FinStmtItem] = 1")
myR.Edit
myR.Fields("Amount").Value = PrevPerItem1
myR.Update

myR.FindFirst ("[FFI_PERIOD] =" & intFFIPeriod1 & "And [FinStmtItem] = 2")
myR.Edit
myR.Fields("Amount").Value = PrevPerItem2
myR.Update

myR.FindFirst ("[FFI_PERIOD] =" & intFFIPeriod1 & "And [FinStmtItem] = 3")
myR.Edit
myR.Fields("Amount").Value = PrevPerItem3
myR.Update

'....[repeating code for values No 4 though 16]

myR.FindFirst ("[FFI_PERIOD] =" & intFFIPeriod1 & "And [FinStmtItem] = 17")
myR.Edit
myR.Fields("Amount").Value = PrevPerItem17
myR.Update

As names of text box fields are organized in some order, I believe there is some way to optimize this code, via referencing textbox name derived from some string variable.
At first, I tried I tried this one to achive optimization of code but it retuns mismatch error on my new custom function:

Set myR = CurrentDb.OpenRecordset("tbl_FFIFinancials", dbOpenDynaset)

For byteItemNumber = 1 To 17 Step 1
myR.FindFirst "[FFI_PERIOD] =" & intFFIPeriod1 & "And [FinStmtItem] =" & byteItemNumber
Me.MyFieldName(Name).Value = myR.Fields("Amount").Value 

Next byteItemNumber

.... my new custom function to reference textfield name on the current form ....

Function MyFieldName(Name As Field)
Dim strName As String

srtName = "PrevPerItem" & byteItemNumber
Name.Name = strName

End Function

This is my first project in Access and first experience in vba coding, so I am completely lost here. Plz help me out.

[amrut]

I cannot understand the first block of code. Regarding the reference to

Me.MyFieldName(Name).Value = myR.Fields("Amount").Value

The field on the form can be referred as
1.Me.YourTextboxName.Value=myR.Fields("Amount").Va lue
2.Me(Textbox).Value =myR.Fields("Amount").Value
the second one is useful if you have a series of textboxes and value is to be assigned dynamically
like if you have 3 textboxes named TxtBox1,TxtBox2 and TxtBox3 then
for i = 1 to 3
Me("TxtBox" & i).value=myR.Fields("Amount").Value
next i

[registoni]

I cannot understand the first block of code. Regarding the reference to


The field on the form can be referred as
1.Me.YourTextboxName.Value=myR.Fields("Amount").Va lue
2.Me(Textbox).Value =myR.Fields("Amount").Value
the second one is useful if you have a series of textboxes and value is to be assigned dynamically
like if you have 3 textboxes named TxtBox1,TxtBox2 and TxtBox3 then
for i = 1 to 3
Me("TxtBox" & i).value=myR.Fields("Amount").Value
next i

thanks, worked like charm. Shortened my code very much!

[ssanfu]

It is not necessary to type ".Value". "Value" is the default property.

"Name" is a reserved word and should not be used as object names.

---------------------------------------------------------------

You didn't post the entire subroutine, so I did what I could.
(Warning: untested!!)
For retrieving data:

Public Sub RetrievingData()
    Dim myR As DAO.Recordset
    Dim i As Integer
    Dim Max As Integer

    'num of unbound controls to loop through
    'the numbers must be consecutive
    Max = 17

    Set myR = CurrentDb.OpenRecordset("tbl_FFIFinancials", dbOpenDynaset)

    If Not (myR.BOF And myR.EOF) Then
        For i = 1 To Max

            ' NOTE:
            'Always check the value of the NoMatch property to determine whether the Find operation has succeeded.
            'If the search succeeds, NoMatch is False.
            'If it fails, NoMatch is True and the current record isn't defined.
            'In this case, you must position the current record pointer back to a valid record.

            myR.FindFirst ("[FFI_PERIOD] = " & intFFIPeriod1 & " And [FinStmtItem] = " & i)
            If rst.NoMatch Then  ' No record found
                myR.MoveFirst
                Me("PrevPerItem" & i) = 0

            Else   'record found
                Me("PrevPerItem" & i) = myR.Fields("Amount")
            End If
        Next i
    Else
        MsgBox "No Records"
    End If

    'clean up
    myR.Close
    Set myR = Nothing

End Sub

For updating values:

Public Sub UpdatingValues()
    Dim myR As DAO.Recordset
    Dim i As Integer
    Dim Max As Integer

    'num of unbound controls to loop through
    'the numbers must be consecutive
    Max = 17

    Set myR = CurrentDb.OpenRecordset("tbl_FFIFinancials", dbOpenDynaset)

    If Not (myR.BOF And myR.EOF) Then
        'updating values for the previous period
        MsgBox "Updating values into Database for the period - " & intPrevYear & "-Q" & byteSelectQuarter
        For i = 1 To Max

            myR.FindFirst ("[FFI_PERIOD] = " & intFFIPeriod1 & " And [FinStmtItem] = " & i)
            If rst.NoMatch Then  ' No record found
                myR.MoveFirst
            Else   'record found
                myR.Edit
                myR.Fields("Amount") = Me("PrevPerItem" & i)
                myR.Update
            End If
        Next i
    Else
        MsgBox "No Records"
    End If

    'clean up
    myR.Close
    Set myR = Nothing

End Sub

Note that neither sub has error handling code....

[registoni]

If the record exists and unique (no other to be found under the findfirst criteria), do I still need to use .MoveFirst method as a error handling procedure?

[ssanfu]

You might get away with not using it, but is it worth the risk?
If a record is not found, the current will not be defined. All .MoveFirst does is move to an actual record. To me, it is not worth the risk.

How does it look if your dB bombs?
Some of my subroutines do not have an error handler because they are very simple. The more critical and very critical (or complex) have error handlers. Your choice.......

[registoni]

I have question regarding unbound textboxes. Currently I constructed my form as SINGLE FORM (not continuous form type), and added all possible textboxes.
Is it possible just to add one row of unbound textboxes and then load form as CONTINUOUS FORM, will it expand as it does with bound textboxes to cover all possible values?
If does so, then how do put in vba code reference to each unbound textbox in such continuous form?