Pass value from subform to form

[JohnGo]

Hi,
I'm trying to pass a value from a subform to a form.
My subform is a filterable lookup form for companies.

So when someone point at a company he/she can click on a button to 'select' that company to filter the main form
I use a button with the following procedure in the subform

DoCmd.ApplyFilter , "Forms![FRM_COMPANY]!

=
Forms![FRM_COMPANY]![FRM_COMPANYLOOKUP]![CODE]"

My main form is Frm_company, my subform is Frm_companylookup.

When I push the button first time my record pointer goes back to the first record in the subform.  Within the main form the record also jump backs to the first value . But these two have nothing in common

when clicking the second time the form filter is being activated and automatically shows a new record (data-entry).

I also put a knob in the main form to pass the value but with the same strange effects. I've put my subform on a page/tab.

If you know (another) way to overcome this problem I would be thankful

 

[ColinEssex]

Sounds like you'd be better off using a ListBox.

There's an excellent search example using a ListBox in the sample Db section. Once something is selected on the listbox, it populates the main form.

Col

 

[JohnGo]

Hi Colin,
Problem with listbox is I can't filter 6 fields!!
So I'm using a subform on a page with 6 fields (query with 4 tables) to give the user the possibility to filter on company name, contact name etcetera.
All this data is spread throughout tables. Problems with listbox you can only 'filter' or better say sort the first column.
So this is not an option for my case

 

[JohnGo]

When taking a leak I realized I could put up an textbox on the subform with value * . When pushing a knob in my subform this box will be updated with the customer-Id.
Within the main form the source query should use a like operator in the criteria to this subform textbox.
Only hassle is I have to build an extra table (eg name=general lookup values) because unbound textboxes don't store values, I can do that by using a listbox so I don't have to change relations.
Well, a nice job for tomorrow

 

[ColinEssex]

Hi Colin,
Problem with listbox is I can't filter 6 fields!!. . . . . . . . . .
Problems with listbox you can only 'filter' or better say sort the first column.
So this is not an option for my case

The example I quoted filters on several fields at the same time. It is really good. I use it to filter on 4 fields but I'm sure it could be modified to encompass more.

Col

 

[JohnGo]

The example I quoted filters on several fields at the same time. It is really good. I use it to filter on 4 fields but I'm sure it could be modified to encompass more.

Col

Hi Colin, the user should be able to filter, not a pre-filtered listbox. I can show as many columns as i want in a listbox, and I'm able to put a query in the listbox with criteria in multiple fields but the prob is...the user has to give in criteria.
For instance the user determines the name of the contact person and the country. As far as I know a listbox will only allow the user to work with the first column, the other columns are determined by the first column.

With sample db I guess you mean northwind. I'll check it out whether you mean something else.

i already tried DoCmd applyfilter and the OpenArgs to pass value but both didn't work.

 

[JohnGo]

When Looking in Northwind I can only find orders as an example.
You select customer and the address field will be filled.
But this is selection on one field, not a 'filter' like the form filter which makes it possible to filter on the like statement.

In my current subform the user can filter multiple fields for intance:
contact name : winny*
company : *services*

So they can use multiple fields and joker-signs

 

[ColinEssex]

The search example I refer to is in the sample database section here

http://www.access-programmers.co.uk/forums/showthread.php?t=65331

Col

 

[JohnGo]

Hi Colin, thanks for your help the example looks nice. But it doesn't compete with the 'form-filter' search capability of access. The 'result' fields (with all of the data of the company) could easiliy filtered by a form-filter, so the search via the list-box is quite obsolete or better say less effective than a form filter.
There''s even a mistake in the example, it's based on a table which will lock the table in a multi-user environment. The table even does the trick because when locked it will automatically point to the filtered record. A more proficient way to coding is using queries as it doesn't lock the whole table.


My problem isn't searching but handing over the search result (the pointed record company-id) to the main-form.
But I'm going to try by using a field in the subform to store the result, within VBA I'll use code to change the recordset to a query filtered with the search result field. When the user inactivates searching the form will be resetted to the normal recordset source. Guess this must do, will tell more about it when it has been done.

 

[JohnGo]

Solved


FInally solved it. The original plan (in the posting above) didn't work out well.
Then I got back to my original idea of placing Docmd.Applyfilter in VBA code.
The first time I did it I placed it in the subform and tried to filter the mainform which didn't work.

Then I'd put a button in the mainform with the ApplyFilter method.
The generic code in the on click event is:
Private Sub Getselectedvalue_Click()
DoCmd.ApplyFilter , "YOUR FILTERED FIELD IN THE MAINFORM=Forms![YOURFORMNAME]![YOURSUBFORMNAME]![YOUR FIELD IN THE SUB FORM]"
End Sub

In this way it's a great filtering tool to put a list of fields in a subform based on multiple tables while using the form filter and the Docmd filter to apply the selected record in your mainform!