CODEWITHSUNDEEP
ms-accessms-access-2007
dgBP
dgBP

Reputation: 1693

Can I make one form to get record data from multiple (unrelated) tables in Access?

I know only the basics of Access, so I am having trouble figuring out how to make a form that will access multiple unrelated tables.

My problem:

I have several tables which describe different card collections. There is no way that I want to link them, even if I could, e.g. by card names. This is because I want to keep the listings separate. I do, however, want to create one form that will access them all, so that I don't need to fiddle about clicking on different forms every time I want to update a card database. The more tables I include, the less practical this would get, anyway. I've tried looking this up and there are several sites which seem to suggest sql-like statements as solutions, but frankly these fly over my head - I don't even know where I'd write them!

So what I want is one form that has a combo box menu to select the table that I want to edit, then another box to select the card that I want to see and hey presto I can edit the details of that card. I can do this if I make one form for one table, but I don't know how to do it for many tables. I would also like it to check for new tables should I add them in. E.g. cards have different series that come out, so when a new one comes out I will create a new table for it, add the cards and so on...

I realise this is probably quite fiddly, especially in Access, but if anyone can give me some guidelines on how to do this it would be much appreciated. (I'm using Access 2007)

Upvotes: 0

Views: 7318

Answers (2)

Mathieu
Mathieu

Reputation: 64

This is quite old, but it might help others. I did figure a way to do exactly what you asked

I only have access 2016, so things might be in a different place, but not that much. First, create all the tables for each of your decks. Make sure you have in the first column : unique ID, second column the name of the deck (it's going to be the same for each entry in this deck) this is going to help us. The third column the name of the card. The other columns will contain your other values of your card, but for my example it doesn't matter. I'll name them like this

column   name
1       unique_ID
2       DeckName
3       CardName

Now, in the tab "create" choose "Query Design". Then in the tab "home" in the seciton Views, choose SQL. in the blanck section enter this I have 2 tables : Table1 and Table2

SELECT Table1.DeckName FROM Table1 UNION SELECT Table2.DeckName FROM Table2;

You have to add each table and add a UNION between each. You finish the sentence with one semi coma only ;

Click run and you should get all the DeckName. Save your query and close it.

Now, we want the combobox to help us choose between the different decks. in the tab "Create" choose "Blank Form". Go to "home/views" choose "design". In the "design" tab, choose the combobox. You can click cancel and skip the wizard In the property, click "rowsource" and select the query you have created. Name it Cmb_Deck

Add a second combobox Name it Cmb_Name . property "Column Count" put "2" because this combobox will show the name of the card, which is column2 in our tables.To be a little bit fancy, we will hide the first column, in the property "Column Widths" put "0";1"" (first column 0inch, second column 1inch).

Drag any of the table you have create into the form and click cancel when the wizard appear. Name it TableResult

Now that the layout is created, we need to change the table according to the combobox Cmb_Deck. In the Cmb_Deck property go in the event section and choose "After Update" click on the "..." and choose Code Builder In this Visual Basic editor window : delete everything that is there and enter this

Option Compare Database

Private Sub Cmb_Deck_AfterUpdate()
TableResult.SourceObject = "Table." & Cmb_Deck.Value
Cmb_Name.RowSource = "Table." & Cmb_Deck.Value
End Sub


Private Sub Cmb_Name_AfterUpdate()
        Me.TableResult.Form.Filter = "([" & Cmb_Deck.Value & "]." & "[CardName]=""" & Cmb_Name.text & """)"
        Me.TableResult.Form.FilterOn = True
End Sub

Now back in access, select your combobox Cmb_Name and go into property event/afterupdate and choose "[Event Procedure]

Now in your form, go to home/views and choose Form Views. First choose a deck in the first combobox, then choose the name of a card in the second combobox. Now the table underneath, shows only the card that you want to see.

I hope this helps

-Mathieu Paquin

Upvotes: 1

Mike
Mike

Reputation: 3015

To answer your question: Use subforms. Create few subforms and your combobox and hide/show the forms as you desire.

Better way to do it:

Creating separate tables for each card type is quite a bad practice. If the cards share features like MonsterName, MonsterPowerType or whatever, you should create another table like card_types and add a card_type field to the cards table. Then if you need to get only Pokemon cards create query with card_type_id = xx or card_type_name = "Pokemon" criterias. This way you only need one form and you can change the card_type criteria with use of your combobox.

Upvotes: 1

Related Questions