Change table datasheet from combobox. (1 Viewer)

nicolareina

New member
Local time
Today, 13:33
Joined
Apr 21, 2021
Messages
12
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.
 

Attachments

I got it to work with
Code:
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?
 
Last edited:
ok it works, but when I click on the "Open" item in the datasheet it always links to table "A"
 
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.

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

Second form
Code:
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. :)
 
I would not have spaces in object/control names. Makes it a pain when writing code using them. :(
 
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.
 
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.
 

Attachments


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.

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

Second form
Code:
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?
 
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.
 
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?
 

Users who are viewing this thread

Back
Top Bottom