Im new. form filter or load ID from subform.

[Bkndbrown]

I am sure this is easy to fix but I am going circles as I am new to this.



Design
Main table (TblMaincontactList) has multiple fields that will be populated by combo box from other tables. One of these fields is "Squadron" which the combo box is linked to the table (TblDropDownSqdn).

TblDropDownSqdn also has another field that is a check box (SelectUnit). The goal of this is to make it so users can limit the form so it will only show records from their squadron and they can only make entries for their squadron. I want it so at the top they can either enter a new record or click on a record from the bottom and edit it at the top.

I am trying to make the form to populate the fields for the main table where they are all filtered by the field in the second table..

I have tried three methods

Frist
I made a query that just filtered the TblDropDownSqdn. I then made a split form. I pointed the combo box to the query and stored it in the main form. This worked to limit the selections in the combo box, however the table at the bottom still showed all entries in the main table as it was not filtered.

Secound.
I made a query that collect all of the fields and filter them based on this checkbox. obviously this worked great for filtering the results and I could select a record on the table and it would display it on the top however I could not edit or make new records because it was no longer based on the tables themselves.

Third
I made a form with an unbound table. the unbound table was based on the query where the main form was linked directly to the main table. This worked with everything except I do not know how to make it so I can select a record and get it to display on the main form. I wouldnt even mind selecting a record and hitting a button to move it up to the main form.

fourth
I set it up as in first try but on the open form button that opens this form I did a filter on load for that "SelectUnit = true" but then I cannot create a new entry.

[Bkndbrown]

I have a unbound subform with a table due to it requiring filtering through a query.

I want to add a button that will pull the ID field from the unbound subform and put it in the primary form.'

Subform name is QryFilterebySqdnsubform and the id is simply "ID"

[davegri]

I want to add a button that will pull the ID field from the unbound subform and put it in the primary form.'

This sounds backwards. Have you considered reversing the main form and subform?

[Bkndbrown]

I am a novice. Any recommendations are welcome. Basically I am have a split form. However I have one field that needs to be filtered on a check box from a linked table. I have not found a way to filter based on this checkbox while not eliminating the ability to edit or add entries. My main form is linked directly to the table as a data entry form. The unlinked subform is the table after it goes through a query to filter based on the check box. If I could do this an easier way that I do not know please point me in that direction. I am banging my head against a wall

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

Why depend on user to filter form? Have a login procedure so that code can limit records displayed on form.

[June7]

Related/duplicate thread https://www.accessforums.net/showthread.php?t=81947

If you are using a split form, selecting record in bottom section should cause the same record to display in top section. If you are emulating a split form with form/subform, that is entirely different and can be difficult to manage. Has been topic of numerous threads.

Again, suggest you provide db for analysis.

[Bkndbrown]

I have attached the file. there is a Zfirstattempt form that is the first way that I mentioned, and a zsecoundattemptsimulatedsplit
.


Ultimately I need a data entry form to imput data into TblMainContact that somehow filters based on the checkbox in the TblDropDownSdqdn table. Or redesigned so I get the same functionality.

My job across the base will go in here to enter topics they discuss with their people. However on the data entry form I only want them to see the entries pertaining to their unit.

Then once I get this working I will create a report that will allow us to get charts that we can that we can filter off of date, Rank, Referal, Sqdn, and Topic. so we can track trends accross the entire base.

[Bkndbrown]

I have uploaded my database

[June7]

Which form(s)? There is no form named QryFilterebySqdnsubform.

You are not saving designated primary key SqdnID of TblDropDownSqdn into TblMainContactList, are saving Squadron descriptive text. The other lookups are saving ID value to text field. All lookup relationship links are on descriptive text fields. Need to change foreign key fields to Long Integer and change relationships. Will have to delete relationships first to fix this. Or set descriptive field as primary key and save that data into TblMainContactList.

Might want to fix misspelled field name: Squaqdron

What's wrong with using Split form?

So each contact can have only one referral?

Why is SelectUnit field in TblDropDownSqdn and TblMainContactList?

[June7]

See my comments in your other thread https://www.accessforums.net/showthread.php?t=81949.

Have you studied an introductory tutorial book? A solid week of study on relational database concepts and Access functionality would greatly advance your understanding and prevent basic design errors I identified.

[Bkndbrown]

I cannot use a regular split form becouse I do not want it to show all entries just those that match their squadron which I had identified with the check box on the TblDropDownSqdn.

Unfortunately if something is not misspelled then I probably didn't do it.

Yes each contact will only have one referral.

Last TblDropDownSquadron only exists as a list of all units on base, and to create the list for the combo box under TblMainContactList. The first is an identifier of all units the second is to identify the unit that the person is in.

[Bob Fitz]

I have no understanding of what your db is to be used for but before you develop any more forms I would suggest that you take another look at your table relationships.
In "TblMainContactList" you have four fields related to other tables. Those fields should be of type "Number" not "Short Text" and they should be related to the Primary Key field of the tables.

[Bob Fitz]

I have no understanding of what your db is to be used for but before you develop any more forms I would suggest that you take another look at your table relationships.
In "TblMainContactList" you have four fields related to other tables. Those fields should be of type "Number" not "Short Text" and they should be related to the Primary Key field of the tables.

[Bkndbrown]

nuked it and started over

[June7]

A Split form can be filtered to display only a specific squadron dataset.

My question about units was asking why SelectUnit field is in both tables. I know purpose of tables, not why both have this yes/no field.