Subforms with reference to multiple subforms (1 Viewer)

[JuanAlian]

Hey guys,

I am a proud new Access-operator who's still trying to get the "hang" of Access.

I have uploaded a file which shows 4 tables (Category 1,2, 3 and 4).
I have searched the web and gone through multiple online-courses without any luck.
Is there a way to make multiple forms for each of the tables that redirects me to the subform and only shows me the related data in the subform?

Example: I have made a category 1 form that connects me to a Category 2 form. I have added a button that should be able to redirect me to data that are linked to the specific object in category 2 but I keep failing. Is there a way to keep adding subforms with the use of the button I have created?


PS. I would be gratefull with some feedback and/or links to courses so that I can get even better
I am also the proud owner of Copilot which is fantastic in Excel whenever I need to make a "quick" macro etc. There is no Copilot in Access but surely whatever code it gives me I would be able to use in Access as well?..

King regards.
Juan Alian

 

[June7]

What is Copilot in Excel? Do you mean macro recorder? Any code generated by Excel would not be immediately usable in Access. It might give you some ideas about what code to write for Access but it is not simply transferrable.

If form/subform structure is not what you want, use WHERE argument of OpenForm to open child form to related records.
Like:
DoCmd.OpenForm "Category 2", , , "[Category1-ID]=" & Me.[Category1-ID]

Some recommendations:
1. Don't use punctuation or spaces in naming convention
2. Don't use exact same field name in multiple tables
3. Don't name controls exactly same as fields they are bound to - for a textbox I would use something like tbxCat1

 

[Gasman]

Normally carried out with main form as parent and subform as children. Using those properties in the subform control.

 

[JuanAlian]

Much appreciated with the recommendations.
"2. Don't use exact same field name in multiple tables" - How shall I "connect" the two tables together? (Lookup Wizard maybe?)

Do you perhaps know of a tutorial about forms? - I think I need to get the hang of access before moving further.

"DoCmd.OpenForm "Category 2", , , "[Category1-ID]=" & Me.[Category1-ID]" - Thanks for the advice with using the DoCmd.OpenForm in VBA. I assumme that the code can be attached to the buttons that I've created?

PS. You are absolutely right about the naming - I will change it right away.

Thanks for the feedback

 

[CJ_London]

"2. Don't use exact same field name in multiple tables" - How shall I "connect" the two tables together? (Lookup Wizard maybe?)

A simple example

you may have
Customers (table name)
ID
Name

instead use
tblCustomers - says what type of object it is
CustomerPK - identifying it a Primary Key to the customers table
CustomerName - a) identifies exactly what it contains and b) avoids using a reserved word (Name)

for the orders table you would have

tblOrders
OrderPK
CustomerFK - identifies it as the Foreign Key to the customers table

and to link the two tables in a query you would link on CustomerPK=CustomerFK - you are linking on the data in the fields, the name just specifies which fields

Don't use lookups in a table - same goes for calculated fields

The Access Web - The Evils of Lookup Fields in Tables

Do learn about database normalisation e.g.

Database normalization - Wikipedia

en.wikipedia.org

don't use reserved words - doing so can cause unexpected errors (e.g. Name could refer to a field name or could refer to the name of an object such as a form)

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

learn.microsoft.com

an example in vba if you have a field called 'Name', Me.Name might refer to the field or control or the name of the form. Or a field called Date might get confused with the Date function

Common examples are Date, Time, Year, Desc, Description, Currency, Document, Order, Value.

Forms are way down the learning curve -there are plenty of Access tutorials out there, find one that you can chime with - just google 'access tutorials for beginners' - here is an example

Access video training - Microsoft Support

Find training courses for Access.

support.microsoft.com

You might also like to look at the access templates available with access or google 'access templates'. Not sure if it is now included with Access but you will probably find the Northwind database a good thing to explore

Redirecting