Unanswered: Multi-tabbed form with subform linked to listbox
I have a multi-tabbed form with several pages. One of the pages is titled "Account Review."
My concept is that the end-user will log into the database and will be sent to the "Account Review" page. The end-user will only be able to see his/her assigned accounts.
Therefore, I assume that there should be a subform on the page titled "Account Review" and I would like a list box
below the subform that lists all of the accounts assigned to the employee. By clicking on the record in the listbox, all of the related controls for the particular account will be displayed in the subform!
To restrict the employee's access to just their accounts, I am using the following query;
SELECT tblTest.AcctNo, tblTest.Name, tblTest.Balance, tblTest.TotalCharges,
WHERE (((tblTest.AssignedTo) In (select employee_id from Employee_Access_List)));
Should the record source for all three forms (main form, subform 1 containing the grouped controls, and the form containing the listbox) be based on this query?
My initial thought is that I should follow the steps below:
1st. Create the multi-tabbed form
2nd. Create a form with all of the desired controls
3rd. Create another form with a listbox
I was hoping someone else would respond who may understand what you want better than I do. In general, you wouldn't want a subform to be tied to the same source as the main form (you'd run the risk of write conflicts). If the info is the same, what's the purpose of the subform? As to the listbox, it doesn't need to be on its own subform. Add a listbox to the bound form/subform using the wizard, choosing the third option "Find a record...".
The subform at the top of the page titled "Account Review" contain controls that are grouped. In other words, if accounts "123BC", "124BD", and "125BE" are assigned to Sally by the supervisor, then when Sally types in her username and password on the first form in the database, she will be routed to the page titled "Account Review." In the listbox, there will be three records with account numbers "123BC", "124BD" and "125BE."
Sally an scroll down the list box and select one of the records. If Sally selects "123BC", then all of the other associated data will populate the subform that is above the listbox.
Note, the listbox contains just 10 fields. Therefore, it appears that two queries are necessary - 1) The query that returns all of the fields for all of the records assigned to the employee that will populate the subform at the top of the page and 2) The query that will populate the listbox which is the first query that has been modified to only include the 10 fields. (The number of fields was limited to just 10 so that the end user would not have to scroll horizontally.)
What is the preferred method to set this up? Are there subforms necessary? Is a subform necessary for the listbox?
I thought I answered those questions, but just to be clear:
A subform is NOT necessary for the listbox. Try the wizard I mentioned.
I don't believe any subforms are necessary, if the data source on the "Account Review" tab is the same as that of the rest of the form (which is what it sounds like). You generally only use a subform when the data is from a different source than the main form. For instance, a main form containing customers (data from customer table) might have a subform containing the sales to each customer (data from sales table).
Perhaps if you posted a sample db containing your form, I can either see what I'm missing or show you what I mean.
I was able to get the listbox synchronized with the other controls at the top of the page. However, upon trying to exit the application, the following error appears;
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change teh data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
Upon review of the "Account Review" page in the multi-tabbed form, the record source for the form is "qryAccounts1." Some key properties for the list box are;
I did not use a subform at all on the "Account Review" page. Also, all of the data resides on one table that has approximately 32 columns. This is not normalized. However, this is what I have to work with.
Any insight as to the possible resolution of the error message?
(Not able to send a copy of the database at this time.)
Hopefully, I am not going in circles and asking redundant questions.