Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    88

    Unanswered: Dialog box for extra data items

    Hi

    Short: With Access 2007, how can I get a dialog box to pop up to add extra items to my record?

    Longer:
    I have a form gathering lots of data for a single record. However, there is one group of data which is only occasionally required, so I want the user to be prompted for it only when they select a particular tick box.

    I know I could have the relevant controls on my form all the time and just hide or disable them, but I am short of screen real estate and, as this data is rarely entered, this would be a big waste of space.

    So I'd like to pop up a modal window (form) to populate these items of data. However, I can't figure out how to have that pop up form point to the same record as the one that is still open in the main form.

    I don't want to use the InputBox function as that's really messy when you have more than one piece of data to collect.

    I'm sure I'm missing something simple, but what is it?

    TIA

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is an example of what can be done. It seems to be relatively complex but its mains advantage is that it is very supple and easy to re-use.

    Specifications:
    1. I call the main form "F_Master" and the extra form to be opened "F_Slave".
    2. The data-bound controls (Textboxes, etc.) have the same name as the associated fields in F_Master.
    3. F_Slave is an unbound form.
    4. Data controls (Textboxes, etc.) have the same name in both forms.
    5. SysCounter is the name of the Identity columns (primary key) in the table F_Master is bound to. You'll have to adapt the code to replace SysCounter by the name of the Identity column in your application.
    6. "Command_Open" is the name of a button in F_Master that opens F_Slave and "Command_Close" is the name of a button in F_Slave that closes it.

    -Add this code in the class module of F_Master:
    Code:
    Private Sub Command_Open_Click()
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim fld As DAO.Field
        Dim frm As Form
        Dim DataSource As Variant
        
        Set dbs = CurrentDb
        Set rst = Me.RecordsetClone
        rst.FindFirst "SysCounter = " & Me!SysCounter
        DataSource = rst.GetRows(2)
        For Each fld In rst.Fields
            If fld.Name <> "SysCounter" Then DataSource(fld.OrdinalPosition, 1) = fld.Name
        Next
        rst.Close
        Set rst = Nothing
        DoCmd.OpenForm "F_Slave"
        Set frm = Forms!F_Slave
        frm.Modal = True
        frm.Initialize DataSource, Me
    
    End Sub
    
    Public Function UpdateData(ByVal DataSource As Variant)
    
        Dim ctl As Control
        Dim i As Integer
        
        For Each ctl In Me.Controls
            For i = 0 To UBound(DataSource, 1)
                If ctl.Name = DataSource(i, 1) Then
                    ctl.Value = DataSource(i, 0)
                    Exit For
                End If
            Next i
        Next
        Me.Dirty = False
        
    End Function
    - Create F_Slave according to the specifications and add this code in its class module:
    Code:
    Private m_DataArray As Variant
    Private m_Caller As Form
    
    Public Function Initialize(ByVal DataArray As Variant, ByVal Caller As Form)
    
        Dim ctl As Control
        Dim strCtlName As String
        Dim i As Integer
        
        m_DataArray = DataArray
        Set m_Caller = Caller
        For Each ctl In Me.Controls
            strCtlName = ctl.Name
            For i = 0 To UBound(m_DataArray, 1)
                If m_DataArray(i, 1) = strCtlName Then
                    ctl.Value = m_DataArray(i, 0)
                    ctl.Tag = i
                    Exit For
                End If
            Next i
        Next ctl
    
    End Function
    
    Private Function SaveValues()
    
        Dim ctl As Control
        Dim lngOrdinalPosition As Long
        
        For Each ctl In Me.Controls
            lngOrdinalPosition = IIf(IsNumeric(ctl.Tag), Val(ctl.Tag), -1)
            If lngOrdinalPosition > -1 Then
                m_DataArray(lngOrdinalPosition, 0) = ctl.Value
            End If
        Next ctl
    
    End Function
    
    Private Sub Command_Close_Click()
    
        SaveValues
        m_Caller.UpdateData m_DataArray
        DoCmd.Close acForm, Me.Name
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Mar 2010
    Posts
    88
    Thanks - that is certainly more complex than I was hoping for, but I can see that it is quite a versatile approach.

    Reading it has made me think of another approach: how about making a local table (all the real data is in shared backends) of which I only ever use one record: bind the secondary form to it, clear it on opening, fill in all the data, and then when I get back to the main form just copy the data over in VBA?

    Comments on this approach welcome

  4. #4
    Join Date
    Mar 2010
    Posts
    88
    For the record, I did try the method I outlined in my last post and I did make it work, but it was getting quite messy and I was concerned that it might be a bit fragile (with subtle error conditions I hadn't covered).

    So in the end I just created a Public variable called "CallIndex" and a subform bound to the same data as the main form. Then in the main form, whenever I want to kick off the subform, I do this:
    Code:
    CallIndex = Me.Recordset.AbsolutePosition
    DoCmd.OpenForm "<popupformname>",,,,,acDialog
    In the subform I have an Event hander for Load which does
    Code:
    Me.Recordset.AbsolutePosition = CallIndex
    To allow users to "Cancel" from the subform I use unbound controls which are filled in on the Load event, and copied to the real items only if "OK" is pressed.

    Seems to work...

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Where is the data stored? If you don't gather it very often then is it in a separate table from your main table? Or do you have it in with the main data? Either way I would:

    1. Create you dialog form with all the controls you want the user to enter and bind it to the appropriate table
    2. Add a cancel button and an OK button
    3. When the user presses OK have the form do any necessary data validation and if everything is OK then close the form

    The key is the call to open the form the DoCmd.OpenForm has several variables. Use the parameters to open the form as a dialog box and also filter for the proper unique ID with:

    Code:
    docmd.OpenForm FormName,acNormal,,"ID=" & MainForm.ID,acFormEdit,acDialog
    The fourth parameter is where the form is being filtered for the current ID in the main form. The acDialog will open the form as a dialog box and not allow the user to do anything else before pressing OK or Cancel to close the form.

  6. #6
    Join Date
    Mar 2010
    Posts
    88
    Hello

    DCKunkle - you are absolutely right, the key is those parameters and I had missed the Filter option: that was exactly what I was looking for!

    Much better than the global variable option (though that does work).

    Thank you!

    CeejayDBF

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •