Text box as source criteria on subform (1 Viewer)

[JPR]

Hello friends,

Thanks to your terrific help. I have been able to almost complete by small application with exactly all the functions I wanted.

There is still one other thing which I just cannot resolve. I will briefly explain what I need and post a sample of my database.


My database has a main table named Clients.

When I open the program, I get the frmMenu which is a sort of a Main Menu form, on which I have placed an empty Sub form (SubMaster) uses as a sort of “container" for all the other sub forms that I want to load.

In my examples you will notice two cmd buttons which open two different Sub Forms. You can try the Search by File no. This will open the Sub Form named SubSearchbyFileNo.

On this Sub Form there is a cmd button, a text search box and a list box which uses the text box as query criteria.

What I am trying to do is to open an individual form (OpenClients) by double clicking on the listbox which at this point will be used as criteria to open the OpenClients form. This individual form should also open and viewed in the SubMaster (basically in my container).

I know the best way is to have a text box on the frmMenu which will be used as criteria to open the form OpenClients but just cannot make it work. I have tried both these codes but not working:

=[Forms]![SubSearchbyFileNo]![lstsearch].[column](0)

=[Forms]![frmMenu]![Submaster]![SubSearchbyFileNo]![lstsearch].[column](0)


The first column of the list box will be used as criteria.

Thank you for any help and please advise if you need further clarification.

 

[MajP]

Your design does not make much "user interface" sense to me. How about this? Seem much easier to use and understand. Plus the design is much simpler.

 

[JPR]

Thank you for your advice and example. Indeed this it a better solution. The reason I wanted to come up with that idea is that I have several queries (in my sample I have added only two) that users can access from the frmMenu, through different command buttons that obviously open different subforms. Without a solutions, I would need to almost redesign the db. Thank you

 

[MajP]

I know the best way is to have a text box on the frmMenu which will be used as criteria to open the form OpenClients but just cannot make it work. I have tried both these codes but not working:

IMO this is the worst way to do things. I have written thousands of queries and have never used a control reference in a query. It is IMO difficult to error check, very limiting, little flexibility, and cumbersome to write.

I do not think you would have to redesign anything. But if you are going to filter a single listbox then I still would have the filters on that listbox, not separate identical subforms to maintain. So you could have a button filter by "Name of FileNo" and bring up that form. Then have buttons for other queries. But if those queries simply filter the same listbox, that should be one subform.

 

[JPR]

Thank you for your advice and example. Indeed this it a better solution. The reason I wanted to come up with that idea is that I have several queries (in my sample I have added only two) that users can access from the frmMenu, through different command buttons that obviously open different subforms. Without a solutions, I would need to almost redesign the db. Thank you

Thank you for your advise. I will go ahead and make the necessary changes.