locked
Access 2010-VBA Code Assistance RRS feed

  • Question

  • I have a database that has several codes in it that I want the user to have the ability to edit. However, I do not want to create a seperate form for each code table since the overall structure of each table is basically the same.

    So I have a popup continuous form called frmCodeMaintenance with the following fields:

    txtID

    txtDescription

    txtOrder (Note: this field determines the order in which the item shows up in the list. )

    Now, an example of two seperate tables would be:

    WorkOrderStatusT (table name)

    WorkOrderStatusID

    WorkOrderStatus

    Order

    another table is:

    CatagoryT (tableName)

    CatagoryID

    Catagory Name

    Order

    Now, there is a 'Code Maintenance' menu in my database with several command buttons, each one indicating a different code. 

    DESIRED RESULT: I want to use this same form (frmCodeMaintenance) to display either table of data depending on which button the user clicks on. So, I'm interested in finding out the VBA Code to set the fields of the form to display the proper code information.


    Steven Schuyler Berkeley, California USA

    Thursday, August 16, 2012 7:10 PM

Answers

  • How many "code" Tables do you have?

    You may want to create a ListBox in the Form Header Section for the user to select the required "code" Table and a CommandButton to assign the new RecordSource as well as changing the ControlSource of each bound Control in the Detail Section of the Form.


    Van Dinh

    • Marked as answer by Dummy yoyo Wednesday, September 5, 2012 3:54 AM
    Friday, August 17, 2012 12:31 AM
  • Another approach would be to use the control's tag properties for each control specific to Table One put 1 all other 2 or even 3 or more for other Tables. Set all Controls Visible = No except those that need to always show.

    Then in the OnClick Event of the Command Button you could put code like;

    Private Sub CommandTable1_OnClick()
        Dim ctl As Access.Control
        Dim blnEnable As Boolean
        'Now enable and make visible controls with Tag Value 1'
        On Error Resume Next
        For Each ctl In Me.Controls
            if clt.Tag = "1" Then
                ctl.Enabled = blnEnable
                ctl.Visible = True
            End If
        Next ctl
    End Sub

    You'll also need to create this for each Table and include a method for setting all controls Visible = No except those in list like this:

        On Error Resume Next
        For Each ctl In Me.Controls
            Select Case ctl.Name
                Case "ControlName1", "ControlName2", "ControlName3"
                    ' leave these controls alone
                Case Else
                    The other code above
            End Select
        Next ctl

    HTH


    Chris Ward

    • Proposed as answer by KCDW Thursday, August 30, 2012 9:43 PM
    • Marked as answer by Dummy yoyo Wednesday, September 5, 2012 3:54 AM
    Wednesday, August 22, 2012 5:00 PM

All replies

  • If I understand correctly you want a toggle button to change the Recordsource of the Form while it is active and then Refresh?

    Chris Ward

    Thursday, August 16, 2012 7:16 PM
  • you are right but I'm not using a toggle button, but command buttons.

    Steven Schuyler Berkeley, California USA

    Thursday, August 16, 2012 11:12 PM
  • Something like this?

    Private Sub Button1_Click

         Dim str1 As String

         str1 = "SELECT [CategoryID] AS txtID, [Category Name] AS txtDescription, [Order] AS txtOrder FROM [CategoryT] ORDER BY [Order]"

         Forms!frmCodeMaintenance.Form.RecordSource = str1

    End Sub

    Do something similar for the other button and table.  By the way, avoid using Order as a field name because it is a reserved SQL word.

    Friday, August 17, 2012 12:24 AM
  • How many "code" Tables do you have?

    You may want to create a ListBox in the Form Header Section for the user to select the required "code" Table and a CommandButton to assign the new RecordSource as well as changing the ControlSource of each bound Control in the Detail Section of the Form.


    Van Dinh

    • Marked as answer by Dummy yoyo Wednesday, September 5, 2012 3:54 AM
    Friday, August 17, 2012 12:31 AM
  • Another approach would be to use the control's tag properties for each control specific to Table One put 1 all other 2 or even 3 or more for other Tables. Set all Controls Visible = No except those that need to always show.

    Then in the OnClick Event of the Command Button you could put code like;

    Private Sub CommandTable1_OnClick()
        Dim ctl As Access.Control
        Dim blnEnable As Boolean
        'Now enable and make visible controls with Tag Value 1'
        On Error Resume Next
        For Each ctl In Me.Controls
            if clt.Tag = "1" Then
                ctl.Enabled = blnEnable
                ctl.Visible = True
            End If
        Next ctl
    End Sub

    You'll also need to create this for each Table and include a method for setting all controls Visible = No except those in list like this:

        On Error Resume Next
        For Each ctl In Me.Controls
            Select Case ctl.Name
                Case "ControlName1", "ControlName2", "ControlName3"
                    ' leave these controls alone
                Case Else
                    The other code above
            End Select
        Next ctl

    HTH


    Chris Ward

    • Proposed as answer by KCDW Thursday, August 30, 2012 9:43 PM
    • Marked as answer by Dummy yoyo Wednesday, September 5, 2012 3:54 AM
    Wednesday, August 22, 2012 5:00 PM