Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

The separate query and View are not necessary. What you are attempting is simple filtering.

The list box gets its information from the table in a manner acceptable to you, like a built in query, such as last, first name along with the table key for each record. The actual list box is set to update a Filtering table. This is a one record table to temporarily hold a selected value.

On a sub-form, the data is retrieved from the table you want by way of a SQL command. This will retrieve the record wanted by using the selected information placed in the temporary filter record.

It is not as difficult as it may sound. I have attached a sample for you to see how this works. Pay attention to the properties of the List box and the sub-form. Those are key. The Button is necessary for refreshing the sub-form information (look at the Action property setting). This can also be accomplished without a button but requires macro coding. As you state you are new to this, basic coding is probably way beyond your reach right now.

Finally, first & last name may not always be enough for a unique selection. Additional concatenation of data is possible.

Sample: CustomerSelection.odb

If this answers your question please click on the ✔ (upper left area of answer).

The separate query and View are not necessary. What you are attempting is simple filtering.

The list box gets its information from the table in a manner acceptable to you, like a built in query, such as last, first name along with the table key for each record. The actual list box is set to update a Filtering table. This is a one record table to temporarily hold a selected value.

On a sub-form, the data is retrieved from the table you want by way of a SQL command. This will retrieve the record wanted by using the selected information placed in the temporary filter record.

It is not as difficult as it may sound. I have attached a sample for you to see how this works. Pay attention to the properties of the List box and the sub-form. Those are key. In the list box, there are two fields being selected: Field 0 is the combined name; Field 1 is the record key. Field 1 is used in the Data properties of the list box under Bound field. So the list box displays Field 0 but saves the data of Field 1 in the Filter table field. The Button is necessary for refreshing the sub-form information (look at the Action property setting). This can also be accomplished without a button but requires macro coding. As you state you are new to this, basic coding is probably way beyond your reach right now.

Finally, first & last name may not always be enough for a unique selection. Additional concatenation of data is possible.

Sample: CustomerSelection.odb

If this answers your question please click on the ✔ (upper left area of answer).

The separate query and View are not necessary. What you are attempting is simple filtering.

The list box gets its information from the table in a manner acceptable to you, like a built in query, such as last, first name along with the table key for each record. The actual list box is set to update a Filtering table. This is a one record table to temporarily hold a selected value.

On a sub-form, the data is retrieved from the table you want by way of a SQL command. This will retrieve the record wanted by using the selected information placed in the temporary filter record.

It is not as difficult as it may sound. I have attached a sample for you to see how this works. Pay attention to the properties of the List box and the sub-form. Those are key. In the list box, there are two fields being selected: Field 0 is the combined name; Field 1 is the record key. Field 1 is used in the Data properties of the list box under Bound field. So the list box displays Field 0 but saves the data of Field 1 in the Filter table field. The Button is necessary for refreshing the sub-form information (look at the Action property setting). This can also be accomplished without a button but requires macro coding. As you state you are new to this, basic coding is probably way beyond your reach right now.

Finally, first & last name may not always be enough for a unique selection. Additional concatenation of data is possible.

Sample: CustomerSelection.odb

Edit 6/19/2017:

I have attached your original file which includes a "Filter" table and a new form "PaymentsSelectByName". The problem with all your forms is the same. If you are going to use a list box for record selection, it can't be on the same internal form as the actual detail. It is basically the same concept as presented in my answer above - List box on the form and detail of selected information on a subform to the form. If you have difficulties with this concept, I suggest you go over the documentation I pointed to in my comment - specifically Chapter 4 - Forms.

The Query and table View are not necessary for any of this and should be removed.

Returned Sample - SAS Member Master.odb

If this answers your question please click on the ✔ (upper left area of answer).

The separate query and View are not necessary. What you are attempting is simple filtering.

The list box gets its information from the table in a manner acceptable to you, like a built in query, such as last, first name along with the table key for each record. The actual list box is set to update a Filtering table. This is a one record table to temporarily hold a selected value.

On a sub-form, the data is retrieved from the table you want by way of a SQL command. This will retrieve the record wanted by using the selected information placed in the temporary filter record.

It is not as difficult as it may sound. I have attached a sample for you to see how this works. Pay attention to the properties of the List box and the sub-form. Those are key. In the list box, there are two fields being selected: Field 0 is the combined name; Field 1 is the record key. Field 1 is used in the Data properties of the list box under Bound field. So the list box displays Field 0 but saves the data of Field 1 in the Filter table field. The Button is necessary for refreshing the sub-form information (look at the Action property setting). This can also be accomplished without a button but requires macro coding. As you state you are new to this, basic coding is probably way beyond your reach right now.

Finally, first & last name may not always be enough for a unique selection. Additional concatenation of data is possible.

Sample: CustomerSelection.odb

Edit 6/19/2017:

I have attached your original file which includes a "Filter" table and a new form "PaymentsSelectByName". The problem with all your forms is the same. If you are going to use a list box for record selection, it can't be on the same internal form as the actual detail. It is basically the same concept as presented in my answer above - List box on the form and detail of selected information on a subform to the form. If you have difficulties with this concept, I suggest you go over the documentation I pointed to in my comment - specifically Chapter 4 - Forms.

The Query and table View are not necessary for any of this and should be removed.

Returned Sample - SAS Member Master.odb

Edit 6/19/2017:

So I took a few minutes to look at you master form. Got some problems there. If this is just to view records, not much - just filter problem. But if you are looking to modify or add records - A few problems. In this sample I modified the "MemberMaster" form. It now has the list box in ascending alpha (last name) seq. The button on sub form titled "Select Record" to display the record in the list box. Another button on the Main form titled "Refresh List" which after adding a NEW record will refresh the list in the List Box. If adding a NEW record, there was no place for entry of the record ID! Put this box in upper left of form. To enter a NEW record, select New Record icon on navigation bar and the fields will be blank except for the ID field. This is because you did not set the ID as an Integer & to auto increment. The new record will still add if you enter an unused ID.

Another Sample: YET ANOTHER SAS Member Master.odb

If this answers your question please click on the ✔ (upper left area of answer).

The separate query and View are not necessary. What you are attempting is simple filtering.

The list box gets its information from the table in a manner acceptable to you, like a built in query, such as last, first name along with the table key for each record. The actual list box is set to update a Filtering table. This is a one record table to temporarily hold a selected value.

On a sub-form, the data is retrieved from the table you want by way of a SQL command. This will retrieve the record wanted by using the selected information placed in the temporary filter record.

It is not as difficult as it may sound. I have attached a sample for you to see how this works. Pay attention to the properties of the List box and the sub-form. Those are key. In the list box, there are two fields being selected: Field 0 is the combined name; Field 1 is the record key. Field 1 is used in the Data properties of the list box under Bound field. So the list box displays Field 0 but saves the data of Field 1 in the Filter table field. The Button is necessary for refreshing the sub-form information (look at the Action property setting). This can also be accomplished without a button but requires macro coding. As you state you are new to this, basic coding is probably way beyond your reach right now.

Finally, first & last name may not always be enough for a unique selection. Additional concatenation of data is possible.

Sample: CustomerSelection.odb

Edit 6/19/2017:

I have attached your original file which includes a "Filter" table and a new form "PaymentsSelectByName". The problem with all your forms is the same. If you are going to use a list box for record selection, it can't be on the same internal form as the actual detail. It is basically the same concept as presented in my answer above - List box on the form and detail of selected information on a subform to the form. If you have difficulties with this concept, I suggest you go over the documentation I pointed to in my comment - specifically Chapter 4 - Forms.

The Query and table View are not necessary for any of this and should be removed.

Returned Sample - SAS Member Master.odb

Edit 6/19/2017:

So I took a few minutes to look at you master form. Got some problems there. If this is just to view records, not much - just filter problem. But if you are looking to modify or add records - A few problems. In this sample I modified the "MemberMaster" form. It now has the list box in ascending alpha (last name) seq. The button on sub form titled "Select Record" to display the record in the list box. Another button on the Main form titled "Refresh List" which after adding a NEW record will refresh the list in the List Box. If adding a NEW record, there was no place for entry of the record ID! Put this box in upper left of form. To enter a NEW record, select New Record icon on navigation bar and the fields will be blank except for the ID (Badge) field. This is because you did not set the ID as an Integer & to auto increment. The new record will still add if you enter an unused ID.

Another Sample: YET ANOTHER Corrected SAS Member Master.odbMaster (copy).odb

If this answers your question please click on the ✔ (upper left area of answer).