Continuous Form Question

[fauxanadu]

I have a continuous form and a button in the header of the form. When I click the button, the following code is executed:

Private Sub cmdTest_Click()
 
    Dim objControl As Control
 
    For Each objControl In Me.Controls
        If (objControl.ControlType = acTextBox) Then
            Debug.Print objControl.Value
        End If
    Next
 
End Sub
 

However, this code only returns the currently highlighted row of the continuous form.

Is there a way to either

1. Access controls in a continuous form directly; OR
2. Move the focus of the currently highlighted row within a continuous form

?

[fauxanadu]

Here is a solution that does what is needed, although in a rather ugly way:

Private Sub cmdTest_Click()
 
    Dim objControl As Control
    Dim intRecordsLeft As Integer
 
    DoCmd.GoToRecord acDataForm, Me.Name, acLast
    intRecordsLeft = Form.CurrentRecord
 
    DoCmd.GoToRecord acDataForm, Me.Name, acFirst
 
    While (intRecordsLeft)
        For Each objControl In Me.Controls
            If (objControl.ControlType = acTextBox) Then
                Debug.Print objControl.Value
            End If
        Next
 
        intRecordsLeft = intRecordsLeft - 1
        If (intRecordsLeft > 0) Then 
            DoCmd.GoToRecord acDataForm, Me.Name, acNext
        End If
    Wend
 
End Sub
 

I still hope there is a better way though.

[Stewart Ross]

Hi. I'm not clear what it is you want to do here; you seem to be traversing all records in your subform one by one for some reason - perhaps you could explain what it is you want to achieve?

In a continuous form there is ony the one set of controls, displayed repeatedly for each different record shown on the form. It is not possible to refer to controls on other records - Access does not set the continuous form up as an independent array of controls. When referring to such a control it is always the one for the current record which is accessed, and to change the record accessed you move the current record to another record (as you do in the code supplied in post 2).

If I knew what you wanted to do I could advise further on alternative approaches which might suit.

-Stewart

[fauxanadu]

There are about 100 forms I am going to impliment in a database. Each form has a save function. This save function will do the following:

1. Loop through each control on the form
2. When it finds a textbox or combo box, get the value
3. Record the text/combo box value, the controls item number to the table
4. Record the location of the control to the database (Was it on the main form or one of the subforms? If subform, which one?)

Once this is done, to restore the data for the form when a user selects a form that has already been filled out, the code would only have to determine which part of the form the info was on via the location data, and then simply loop through the data adding it back piece by piece.

The reason I am implimenting it this was is because the users need to be able to add forms with minimal effort at later dates.

[Stewart Ross]

Hi. When you say 'add forms' do you mean 'add records'? Users are not really adding forms to your database are they?

I do not understand what is going on here, as unless your forms are unbound records entered in a data entry form in Access are saved automatically.

Sorry, but I don't think I have sufficient understanding of what it is you are doing to assist you.

-Stewart

[fauxanadu]

That's fine. I've found a reasonable, although not ideal, solution. Thank you for the attempt, however.

Just to clarify, yes, users will be adding forms to the database at a later date. I think the confusion comes from there being two different kinds of forms I am refering to. I tried to keep them distinct, but let me clarify.

First, there are forms that are database objects that users can interact with (the ones you are likely thinking of when I say forms). Second, there are paper forms that the users currently use. The solution is converting the paper forms into the digital forms so as to save input data for posterities sake and for various other reasons.

The paper forms change quite often and have new ones added and old ones removed quite often. Therefore the users need to be able to create revisions of the existing digital forms and create new digital forms entirely from scratch. I am trying to create a generic set of methods that will provide all the functionality required via VBA that is robust enough that they can simply copy a template and put the form elements where needed and not have to write a smidgeon of vba.

However, the normal method of data manipulation will not work, so I must create a different method. I've done this by using a few VBA methods to loop through all controls on a digital form, finding all textboxes and comboboxes (the fields users can change) and record their controls.item(number) number and value to the database, so the information can be retrieved later.

As stated before, thanks for taking the time to respond, but I think the solution I outlined above is workable if not perfect^.^