Change table datasheet from combobox.

[nicolareina]

Good morning everyone, I have a problem. I'd like the combobox button to query the form once the table is selected and extract the data from the specified table. I can't find a solution.

I need to manage multiple tables. The other problem is this: each table can have identical IDs. So when I click the "Open" button in the datasheet, it should recognize both the ID and the table it is linked to. Is there a way?

Thank you for your valuable help in this forum.

 

[Gasman]

I got it to work with

Private Sub txtTable_AfterUpdate()
Me.RecordSource = Me.txtTable
End Sub

I renamed your combo to txtTable

Then set the other form recordsource to that forms recordsource or the txtTable control as I did for the first form.
To my mind recordsource to recordsource is more logical and self explanatory?

 

[nicolareina]

ok it works, but when I click on the "Open" item in the datasheet it always links to table "A"

 

[Gasman]

ok it works, but when I click on the "Open" item in the datasheet it always links to table "A"

Then set the other form recordsource to that forms recordsource

Yes, you will need to find that particular record.
I do not use macroes.
I used OpenArgs, as the WHERE on the OpenForm does not seem to work as I had changed the recordsource.

Private Sub Testo376_Click()
DoCmd.OpenForm "Dettaglio Sottoposto", , , "ID=" & Me.ID, , , Me.ID
End Sub

Second form

Private Sub Form_Load()
Me.RecordSource = Forms![Lista Sottoposti].RecordSource
If Not IsNull(Me.OpenArgs) Then
    Me.Recordset.FindFirst "ID = " & Me.OpenArgs
End If
End Sub

Normally opening a form, the code "ID=" & Me.ID would locate the record as well. Not in this case though, so can be omitted. I left it there as I am lazy.

 

[Gasman]

I would not have spaces in object/control names. Makes it a pain when writing code using them.

 

[The_Doc_Man]

Let's start with the fact that the two tables you named are identical in structure, differing only in specific content. This already STRONGLY suggests a moderate-to-severe design flaw. You only need to have ONE table with Nomen and Cognomen fields. If you REALLY needed to know anything else, typically you add another field to let you know which "original" table was the source of the two "nomen" fields. If the two tables have overlapping ID fields, perhaps those ID fields aren't chosen correctly.

To know what you really needed, we would need a brief overview of the purpose of your database. We can't see the purpose of the two fields if all you have are code samples, and to be honest, my Italian ends when I put down the menu at my favorite Italian ristorante. Therefore, I only peeked at the contents of test.zip to check those tables.

I very strongly recommend that you find some articles on Database Normalization. You can find some in this forum just by searching for "Normalization" - but if you wanted to look on the Internet, you must search for "Database Normalization" because by itself, Normalization is used in Chemistry, Mathematics, Psychology, Diplomacy, ... and Databases. Normalization will at first seem like a complex problem, but generally it is not that complex once you learn how to do it right. It would help you avoid problems in having to change RecordSource on forms and just allow you to FILTER if you only wanted to see one portion of your data. Filtering isn't as harsh a tactic as changing underlying data tables because changing tables limits the way you can bind to tables in code.

 

[Ken Sheridan]

In most cases having multiple tables modelling subsets of a single entity type would mean that data is being encoded as table names. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at column positions in rows in tables, and in no other way.

Correcting the database design might simply require merging the tables into a single table, and adding a column which differentiates the multiple subsets of rows. In a more complex scenario it might be that each table models a sub-type of a single (super) type in a tree-structured hierarchy. In such a hierarchy each sub-type shares all of the attributes of its (super) type, but not those of other sub-types. The relationship type between a (super) type and a sub-type is one to one, with the primary key of the sub-type also being a foreign key referencing the primary key of the (super) type. Such relationship types can continue down through however may tiers of the hierarchy are required.

I attach a little demo file which illustrates a type hierarchy modelled in this way, using the personnel of a simple academic institution as its example.

 

[nicolareina]

to

Then set the other form recordsource to that forms recordsource

Yes, you will need to find that particular record.
I do not use macroes.
I used OpenArgs, as the WHERE on the OpenForm does not seem to work as I had changed the recordsource.

Private Sub Testo376_Click()
DoCmd.OpenForm "Dettaglio Sottoposto", , , "ID=" & Me.ID, , , Me.ID
End Sub

Second form

Private Sub Form_Load()
Me.RecordSource = Forms![Lista Sottoposti].RecordSource
If Not IsNull(Me.OpenArgs) Then
    Me.Recordset.FindFirst "ID = " & Me.OpenArgs
End If
End Sub

Normally opening a form, the code "ID=" & Me.ID would locate the record as well. Not in this case though, so can be omitted. I left it there as I am lazy.

Thanks, Gassman, you introduced me to new MS Access functions. Everything works perfectly. If I want to display all the records from the two tables (tbl1 and tbl2) in the submitted list form, how do I do it?

 

[Gasman]

Do as advised and have all the records in one table. Have a field that identifies the source, then filter on that with your combo.

 

[nicolareina]

Do as advised and have all the records in one table. Have a field that identifies the source, then filter on that with your combo.

I don't understand how to do it. Do I have to put a filter in the subject list mask?

 

[Gasman]

In the afterupdate of the combo set the filter of the form to the value of the combo for that field.

https://www.youtube.com/results?search_query=set+filter+access+form

 

[Ken Sheridan]

I don't understand how to do it. Do I have to put a filter in the subject list mask?

As has been explained, to filter the form to alternative subsets of rows from a single table you would do so on the basis of the value in a single column matching that selected in the unbound combo box. You might want to be a little more ambitious, however, and filter the form on values in a number of columns, e.g. all customers based in London. You could include a series of unbound controls in the form, in which values could be entered or selected in some or all of the controls. That could make the form a little crowded, however, so an alternative would be have a Search button on the form which opens a dialogue form in which the search criteria could be entered. The following code is an example of such a button's Click event procedure:

Private Sub cmdSearch_Click()

    Const Message_Text = "No matching records found.  Search aborted."
    
    ' ensure current record is saved
    Me.Dirty = False
    ' open search form in dialogue mode
    DoCmd.OpenForm "frmSearchAddressDlg", WindowMode:=acDialog
    
    ' if no matching records found, inform user and turn off filter
    If Me.Recordset.RecordCount = 0 Then
        MsgBox Message_Text, vbInformation, "Warning"
        Me.FilterOn = False
    End If
    
End Sub

In the dialogue form which opens the user can then enter or select criteria values in any or all of the controls in the form, and then confirm the search by a button whose code below filters the bound form. Each of the dialogue form's controls in which values can be entered or selected has its Tag property set to SearchByMe to enable the code to loop through just those controls:

Private Sub cmdSearch_Click()

    Dim ctrl As Control
    Dim strFilter As String
    
    ' loop through form's Controls collection
    ' and build filter string expression
    ' for those controls not Null
    For Each ctrl In Me.Controls
        If ctrl.Tag = "SearchByMe" Then
            If Not IsNull(ctrl) Then
                Select Case ctrl.Name
                    Case "cboLastName"
                    strFilter = strFilter & _
                        "And LastName = """ & ctrl & """ "
                    Case "cboCity"
                    strFilter = strFilter & _
                        "And CityID = " & ctrl & " "
                    Case "cboCounty"
                    strFilter = strFilter & _
                        "And CountyID = " & ctrl & " "
                    Case "cboPostCode"
                    strFilter = strFilter & _
                        "And PostCode = """ & ctrl & """ "
                End Select
            End If
        End If
    Next ctrl
    
    With Forms!frmAddresses
        If Len(strFilter) > 0 Then
            ' remove leading 'And '
            strFilter = Mid(strFilter, 5)
            ' apply filter
            .Filter = strFilter
            .FilterOn = True
        Else
            'turn filter off
            .FilterOn = False
        End If
    End With

    ' close dialogue form
    DoCmd.Close acForm, Me.Name

End Sub