Combo/List box use with Access Forms

[RockKandee]

I am using Access 2013 on Windows 8

I have some forms where I use a list box (with row source - value list) to filter another listbox where the row source is a table. In some cases I use a combobox instead so that I can not restrict the data entry. In either case, the control source information is not displaying for a created record when the form is opened again. This only seems to be occurring with list/combo boxes that have another listbox filtering them AND have a column count greater than 1.

What is the best way around this? It makes it difficult to edit records when I cannot see the information that is already there.

I have created an entirely new form that only uses text boxes so I can look up and view the info, but it seems ridiculous to have to flip between the two just to make changes and a bit wasteful to need 2 forms for every form I have like this. What am I missing here? Must I have text boxes for each list box on the form?

I have searched for answers but the answers I find are way over my head and include VB code as the solution and are not really even discussing a box controlling another box. I am not using VB at all with these forms as I don't understand it well enough yet. I am not ready to add any VB to these forms as anytime I try it messes up the calendar I am using from this site.

Thanks much

[ADezii]

I can only assume that you are filtering ListBox2 after the AfterUpdate() Event of ListBox1 is executed. Should this be the case, kindly Post the Code by which you are doing this.

I am not ready to add any VB to these forms as anytime I try it messes up the calendar I am using from this site.

If you like, Post this question in a New Thread and we'll see if we can help you.

[RockKandee]

Main Form: 1st listbox - unbound with a value list and the on click event requerys the 2nd list box. Neither box retains the info selected on reopen. Both did when the unbound was using a table for its row source. Can remember if anything else has changed also. I'll have to test it.

Subform:
1st - Listbox - bound and a value list with no event - retains info.
2nd - textbox (date) - bound with after update event to requery box 3.
3rd - combobox - does not retain data - no events - column count 2 - row source SQL

SELECT DropFacilitiesSchedule.FacilityID, DropFacilities.Facility, DropFacilitiesSchedule.DayofWeek
FROM DropFacilities INNER JOIN DropFacilitiesSchedule ON DropFacilities.FacilityID = DropFacilitiesSchedule.FacilityID
WHERE (((DropFacilitiesSchedule.ActivityType)=[Forms]![MainScheduleCourses]![SubScheduleCourses].[Form]![SessionType]) AND ((IIf((WeekdayName(Weekday([SessionDate]))<>[DayofWeek]) And ([ActivityType]="Pool"),True,False))=False));
 

4th - combobox - retains data - no event - row source SQL

SELECT DropFacilitiesSchedule.HoursStartTime, DropFacilitiesSchedule.FacilitySchID, DropFacilitiesSchedule.DayofWeek, DropFacilitiesSchedule.ActivityType, IIf((WeekdayName(Weekday([SessionDate]))<>[DayofWeek]) And ([ActivityType]="Pool"),True,False) AS Expr1, [Forms]![MainScheduleCourses]![SubScheduleCourses].[Form]![FaciltyID] AS Expr2
FROM DropFacilities INNER JOIN DropFacilitiesSchedule ON DropFacilities.FacilityID = DropFacilitiesSchedule.FacilityID
WHERE (((DropFacilitiesSchedule.ActivityType)=[Forms]![MainScheduleCourses]![SubScheduleCourses].[Form]![SessionType]) AND ((IIf((WeekdayName(Weekday([SessionDate]))<>[DayofWeek]) And ([ActivityType]="Pool"),True,False))=False));
 

5th - combobox - retains data - no event - row source SQL

SELECT DropFacilitiesSchedule.HoursEndTime, DropFacilitiesSchedule.FacilitySchID, DropFacilitiesSchedule.DayofWeek, DropFacilitiesSchedule.ActivityType, IIf((WeekdayName(Weekday([SessionDate]))<>[DayofWeek]) And ([ActivityType]="Pool"),True,False) AS Expr1, [Forms]![MainScheduleCourses]![SubScheduleCourses].[Form]![FaciltyID] AS Expr2
FROM DropFacilities INNER JOIN DropFacilitiesSchedule ON DropFacilities.FacilityID = DropFacilitiesSchedule.FacilityID
WHERE (((DropFacilitiesSchedule.ActivityType)=[Forms]![MainScheduleCourses]![SubScheduleCourses].[Form]![SessionType]) AND ((IIf((WeekdayName(Weekday([SessionDate]))<>[DayofWeek]) And ([ActivityType]="Pool"),True,False))=False));
 

This last box doesn't rely on anything else yet.
6th - listbox - column count 2 - no event - row source SQL

SELECT DropStaff.StaffID, QStudentInfo.[Full Name], DropStaff.StudentID
FROM DropStaff INNER JOIN QStudentInfo ON DropStaff.StudentID = QStudentInfo.StudentID;
 

[RockKandee]

BTW: I don't have code for the requery - I am using macros and I just select requery and add the name of the box.

[ADezii]

To be perfectly honest, RockKandee, I am having a little trouble following the Logic. I'll simply wait and see what happens when other Experts join in, perhaps they will have a better perspective/understanding of this situation.

[RockKandee]

I have searched every possible way I can think of and have failed to find the magic word needed to find an answer.

I do have a creative solution. Not very elegant, but it gets the job done.

For the Main form mentioned, when I added a bound text box, the list box started displaying properly, like it had before. I had given up on a solution and added the text box just so I could see the info. This text box is now invisible, as I no longer need to see it, but I am scared to remove it to see if it really was the solution. The list box does what I want and I am not changing anything. I think Access has PMS.

For the Sub Form mentioned: The problem list box has a column count of 2. 1 column is the bound info not shown, and the other is what is displayed for selection.
I added a bound text box that now displays this data.

I placed this text box over the list box and sized it to cover all of the list box except the drop down arrow.

In the on got focus event of the text box:

Go to control >> list box control name

Set property >> text box control name >> visible >> 0

NOTES:
Use a zero for false/no.
Visibility can NOT be changed on a control that has focus. You MUST have the go to control first, then set property.

At this point the text box will disappear and the focus will be on the list box.

In the List box on lost focus event:

Set property >> text box control name >> visible >> -1

Now, after focus is off of the list box, the text box reappears on top and displays the data.

I used the on got/lost focus so that the tab control will make this work. The drop down arrow was left visible so it can be accessed directly.

These are the details I wish I found when searching for answers. I always seem to find what appears to be my solution, but it leaves me with more answers to find to figure out how to use the solution. This is for newbies like me who are still trying to understand the basics. I am not trying to insult anyone's intelligence.

Feel free to pick apart or improve on my solution. I simply used the info I know to date to make it work. I am sure there is a better way.

I love the experts on this site - they ROCK!!!

[zmbd]

RK:
I've reset your "best answer" for the moment.

In post#3 you cite a litany of 6 combo/list boxes.

Are any of these bound to a record source when the form opens?
Control Source = Bound to a Query or Table
Record Source = Look-up values, this could be a list, a table, or a query.
>record source values may be restricted via table relationships so that a value in the record source must be related to the allowed values of the field due to the table relationship or the field datatype.

In anycase: Control Source is NOT the same as record source
Bound Column ONLY applies to the value returned by the RECORD source and is only related to the CONTROL source in that he value returned must be valid for the field in the CONTROL that ties the control to the table.

[RockKandee]

Box 1:
control source bound = yes
record source bound = value list = yes

Box 2:
control source bound = yes
record source bound = no (it is a text box)

Box 3:

control source bound = yes (The first column of the box is bound, so bound column = 1)The first column is entered into the table's field where it is bound to, but does not show on my list (width set to 0")

record source bound = yes (query builder with 2 tables being used)

Column 2 is what I want displayed and is the column showing when selecting from the list.

This box is where the problem is. Using 2 different tables? Using a formula? Using a list box and a text box to determine what shows on the list? I don't know.

boxes 4 and 5 are control bound as well as source bound and the source is dependent on the selection from box 3.

Not worrying about box 6 right now - it is scheduled for a mess up soon and doesn't connect to the others. I only mentioned it to show the different behaviors I don't understand.

Sorry for using the word "bound" too loosely. Is this explanation better?

Let me know if I am missing something.

Thanks and kisses as always.

[zmbd]

Box 3:
control source bound = yes (The first column of the box is (..)
record source bound = yes (query builder with 2 tables (..)
Column 2 is what I want displayed and is the column showing when selecting from the list.
This box is where the problem is. (..)

This is what I suspect you have so far for this control, please fill in the blanks
DataTab:
[Control Source]=
[Row Source]=

SELECT DropFacilitiesSchedule.FacilityID, DropFacilities.Facility, DropFacilitiesSchedule.DayofWeek 
FROM DropFacilities INNER JOIN DropFacilitiesSchedule ON DropFacilities.FacilityID = DropFacilitiesSchedule.FacilityID 
WHERE (((DropFacilitiesSchedule.ActivityType)=[Forms]![MainScheduleCourses]![SubScheduleCourses].[Form]![SessionType]) AND ((IIf((WeekdayName(Weekday([SessionDate]))<>[DayofWeek]) And ([ActivityType]="Pool"),True,False))=False));

[Row Source Type]= Table/Query
[Bound Column]= 1
[Limit To List]=

[RockKandee]

DataTab:
[Control Source]= FacilityID (this is in a table)
[Limit To List]= Yes

You are correct in what I had for the others.