Solved Trying to go to a record on one tab based on record on another tab (1 Viewer)

[Cal Varchar]

Hi everyone. I was hoping I wouldn't have to do this.

I'm trying to set it up so that on, let's say, Tab1 (of a tab control), when I select a record, then move to Tab2, the form in Tab2 will be focused on a related record (from Tab1), OR it will filter, as in what is indicated in the attached images. I will actually need to do both.

I've gotten to the point of placing text boxes in the main form and in the subform footer, which pick up the proper data and playing around with Macros, which I'm a little partial to, because of not having to worry about references in modules, but who knows if I should worry about that. But from there, I'm smoked, so to speak. or maybe I should start.

In these images, the longer field containing "abbeybeo1" is the main key, the unique number has never been used for anything that I can tell (it's a large DB). This second image is not a junction table. The key is a three part composite, if that matters, I suspect it might for certain such things. But Access has already taught me that I don't know anything. So forget I said that.

 

[arnelgp]

use Link Master/Child Fields on your second subform.
see this demo.
on the Current Event of the first subform, it set the txtAbbey on the Main form:

Me.Parent!txtAbbey = Me!Location

now, since there is a Link on the second subform, it will show only records
selected from the first subform?

open Form1.

 

[Cal Varchar]

use Link Master/Child Fields on your second subform.
see this demo.
on the Current Event of the first subform, it set the txtAbbey on the Main form:

Oh gee golly. That's embarrassing. I actually looked for that earlier, and was so hurried, I missed that it existed in there (the master/child stuff).

Okay, okay, I will look it over, and your example, and see what happens. Thanks again.

 

[arnelgp]

when you got it working, you will need to Hide the txtAbbey (set Visible property to No), to appear, it is not there.

 

[Cal Varchar]

when you got it working, you will need to Hide the txtAbbey (set Visible property to No), to appear, it is not there.

That much I'm good on. I would hide that. But I also just spent some time figuring out how to turn off the filtering. And it's just a simple setting of 'filter on empty master' y/n. Took awhile to focus my eyes on something so close. Thanks so much again.

Now, can I do this with multiple fields for composite keys?

 

[arnelgp]

you

Now, can I do this with multiple fields for composite keys?

you can, just add, as many unbound textbox on the main form.
and rename them properly.

Link Master Fields: [txtbox1], [txtbox2], [txtbox3]
Link Child Fields: [field1], [field2], [field3]

 

[Cal Varchar]

Yup. Two fields works.

 

[Cal Varchar]

you

you can, just add, as many unbound textbox on the main form.
and rename them properly.

Link Master Fields: [txtbox1], [txtbox2], [txtbox3]
Link Child Fields: [field1], [field2], [field3]

Cool to know. Thank you AGAIN, so much. This was a hair puller.. kind of.

 

[arnelgp]

also, suggest you add Composite Index to your fields.
this will make the Linking faster.

 

[Cal Varchar]

You mean to set all key fields to primary keys, right? Not leave them as merely indexed.

 

[arnelgp]

if they are your primary key (no dups), why not.

 

[Cal Varchar]

if they are your primary key (no dups), why not.

I'm sorry, I mean that even the composite keys should be set with little keys next to them, or is it sufficient to just make them indexed?

 

[arnelgp]

just make index on them.

 

[Cal Varchar]

just make index on them.

Okay. Thanks so much.

 

[Pat Hartman]

You mean to set all key fields to primary keys, right? Not leave them as merely indexed.

No, that is not what he meant.

It is very easy to make a composite PK. You select up to 10 columns by holding Shift- click (when columns are adjacent) or Cntl-click (when the columns are not adjacent). Once all are selected, you press the key icon.

A composite index requires the use of the indexes dialog. Here is a picture that shows three indexes. The PK (which is a special kind of index), a unique index of two columns and a non-unique index, also of two columns. To create multi-field indexes, you go to the first blank line in the dialog and give the index a name. Pick the first column and set the properties. Then you can add up to nine more fields, one row at a time. Just leave the index name field blank and Access assumes that the field belongs to the previously defined index.

 

[Cal Varchar]

No, that is not what he meant.

It is very easy to make a composite PK. You select up to 10 columns by holding Shift- click (when columns are adjacent) or Cntl-click (when the columns are not adjacent). Once all are selected, you press the key icon.

A composite index requires the use of the indexes dialog. Here is a picture that shows three indexes. The PK (which is a special kind of index), a unique index of two columns and a non-unique index, also of two columns. To create multi-field indexes, you go to the first blank line in the dialog and give the index a name. Pick the first column and set the properties. Then you can add up to nine more fields, one row at a time. Just leave the index name field blank and Access assumes that the field belongs to the previously defined index.

I've tried out the Index dialog; named the index, selected several fields to represent the unique characteristics of each entity and close the dialog, but the fields in the field properties do not say that they are indexed.

 

[ebs17]

Index dialog and property data sheet are assistants to support the developer. They can and do a lot of things, but by no means everything. They don't show everything either.

An index is a database object and can be determined as such using code.

Sub ShowIndexes()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index

    Set db = CurrentDb
    Set tdf = db.TableDefs("TableName")
    For Each idx In tdf.Indexes
        Debug.Print idx.Name, idx.Fields
    Next idx
End Sub

 

[Pat Hartman]

but the fields in the field properties do not say that they are indexed.

I understand your confusion. The table design interface has no way to display multi-column indexes, that is what the indexes dialog is for. The table design view only shows single field indexes EXCEPT for the PK, which because it has its own special symbol, can show multiple fields. So, if you open a table and see Key symbols on three fields, that doesn't mean that there are three primary keys. It means there is ONE PK but it is the combination of three fields.

Most of the time, your PK will be an autonumber but you can make PKs that are composed of data fields rather than an arbitrary number. If you have a State table and a Cities table, you could make the PK of the state table the Abbr field. Then in the cities table, you need a composite PK because city names are not unique across states so you need the state abbr as the qualifier. So for the cities table, the PK is StateAbbr + CityName and you will see two key symbols. In the indexes dialog, the PK will show the two fields as you see for the ordinary indexes I described earlier.

Access only supports a max of 10 columns for an index but SQL Server allows more. 32, last time I looked but I don't know the current limit.

Keep in mind, a table that needed to mush 32 fields to define uniqueness is almost certainly not well normalized so you are unlikely to ever run into any table like this except perhaps if you are working with a data warehouse type of database where archived data is used only for reporting and the whole database is replaced weekly or monthly. No updates happen in between full replaces unless there is a need for a data correction and that would be changed by the DBA rather than an ordinary user.

 

[Cal Varchar]

I understand your confusion. The table design interface has no way to display multi-column indexes, that is what the indexes dialog is for. The table design view only shows single field indexes EXCEPT for the PK, which because it has its own special symbol, can show multiple fields. So, if you open a table and see Key symbols on three fields, that doesn't mean that there are three primary keys. It means there is ONE PK but it is the combination of three fields.

Most of the time, your PK will be an autonumber but you can make PKs that are composed of data fields rather than an arbitrary number. If you have a State table and a Cities table, you could make the PK of the state table the Abbr field. Then in the cities table, you need a composite PK because city names are not unique across states so you need the state abbr as the qualifier. So for the cities table, the PK is StateAbbr + CityName and you will see two key symbols. In the indexes dialog, the PK will show the two fields as you see for the ordinary indexes I described earlier.

Access only supports a max of 10 columns for an index but SQL Server allows more. 32, last time I looked but I don't know the current limit.

Keep in mind, a table that needed to mush 32 fields to define uniqueness is almost certainly not well normalized so you are unlikely to ever run into any table like this except perhaps if you are working with a data warehouse type of database where archived data is used only for reporting and the whole database is replaced weekly or monthly. No updates happen in between full replaces unless there is a need for a data correction and that would be changed by the DBA rather than an ordinary user.

Thank you for bringing this all to my attention, I have focused on it, read a chapter in my "bible", and have watched several videos to get a more well-rounded understanding. This will help a great deal.

 

[Cal Varchar]

Index dialog and property data sheet are assistants to support the developer. They can and do a lot of things, but by no means everything. They don't show everything either.

An index is a database object and can be determined as such using code.

Sub ShowIndexes()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index

    Set db = CurrentDb
    Set tdf = db.TableDefs("TableName")
    For Each idx In tdf.Indexes
        Debug.Print idx.Name, idx.Fields
    Next idx
End Sub

Thank you for elaborating. All of this is helping to form a better understanding. And any coding info is always welcome.