Search Form - SubForm Advanced

[dbarmer]

I have read some help articles on sub forms, however, have yet to find the
solution that helps me. Some groups have stated that this is not known,
however I know there has to be an easy solution:

I have a form that also has a subform. The two are linked by a field,
[Eno]. When I add records, etc, everything works beautifully. However, If I
wanted to search the form from a SUB-FORM field [EMPLOYEE] from the MAIN
form, It will now work.

I started the search with a unbound text box, - after update which will be
the search terms, however, I am trying to figure out VBA code that will
Search the user's input search text against the subform field, and then go to
that record so the user can edit it.

Can anyone provide me with help?

 

[Tom Wickerath]

Start with an unbound QBF (Query by Form) form to create your search form. I
think this will be easier and more flexible in the long run versus attempting
to add search capability to your pre-existing bound form / subform.

Your new unbound QBF form can include a subform for displaying records that
meet the criteria of your search. Initially, you set the subform bound to a
query that is guaranteed to return 0 records, for example:

SELECT field1, field2, field3 FROM TableName WHERE FALSE

Use the AfterUpdate event procedure for unbound controls (ie. no control
source), on your unbound QBF form, to call a function that creates the
appropriate SQL statement, and sets the recordsource for the subform to this
new SQL statement. I know this probably sounds a bit confusing by now. Here
are two examples you can download to take a look:

http://home.comcast.net/~tutorme2/samples/elements.zip
http://home.comcast.net/~tutorme2/samples/Chap08QBF.zip

The first one, elements.zip, contains only one multi-select list box. This
is the easiest example to learn from, by reverse engineering it. The second
example is a little bit more involved, but still not too complex.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[dbarmer]

Thanks so much Tom. If I have my "new search form" where the user finds
the information that is needed, [ENO] and [EMPLOYEE] because of the query,
how do I then "grab" this data that matches the original edit form, where the
user could change the info?

The whole problem started, I did not want to duplicate data in several
different tables that was alike, hence the [EMPLOYEE] field key'd to the
[ENO]. However, I beginning to wonder would it be much much easier if I DID
have all the search material on the orig form.

Start with an unbound QBF (Query by Form) form to create your search form. I
think this will be easier and more flexible in the long run versus attempting
to add search capability to your pre-existing bound form / subform.

Your new unbound QBF form can include a subform for displaying records that
meet the criteria of your search. Initially, you set the subform bound to a
query that is guaranteed to return 0 records, for example:

SELECT field1, field2, field3 FROM TableName WHERE FALSE

Use the AfterUpdate event procedure for unbound controls (ie. no control
source), on your unbound QBF form, to call a function that creates the
appropriate SQL statement, and sets the recordsource for the subform to this
new SQL statement. I know this probably sounds a bit confusing by now. Here
are two examples you can download to take a look:

http://home.comcast.net/~tutorme2/samples/elements.zip
http://home.comcast.net/~tutorme2/samples/Chap08QBF.zip

The first one, elements.zip, contains only one multi-select list box. This
is the easiest example to learn from, by reverse engineering it. The second
example is a little bit more involved, but still not too complex.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have read some help articles on sub forms, however, have yet to find the
solution that helps me. Some groups have stated that this is not known,
however I know there has to be an easy solution:

I have a form that also has a subform. The two are linked by a field,
[Eno]. When I add records, etc, everything works beautifully. However, If I
wanted to search the form from a SUB-FORM field [EMPLOYEE] from the MAIN
form, It will now work.

I started the search with a unbound text box, - after update which will be
the search terms, however, I am trying to figure out VBA code that will
Search the user's input search text against the subform field, and then go to
that record so the user can edit it.

Can anyone provide me with help?

 

[Tom Wickerath]

The second example includes the ability to double-click on a record in the
subform to open just that record for editing. Any record(s) displayed in the
subform are those that meet the search criteria. Did you try out this example?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[dbarmer]

Again, Thanks Tom. I failed to look at the second example, I had jut looked
at the first. That is amazing to have the ability to click on an existing
record and have it open up for editing. I need to figure that part out by
reading your code. I will play with this. May have another Q if it is ok.

The second example includes the ability to double-click on a record in the
subform to open just that record for editing. Any record(s) displayed in the
subform are those that meet the search criteria. Did you try out this example?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Thanks so much Tom. If I have my "new search form" where the user finds
the information that is needed, [ENO] and [EMPLOYEE] because of the query,
how do I then "grab" this data that matches the original edit form, where the
user could change the info?

The whole problem started, I did not want to duplicate data in several
different tables that was alike, hence the [EMPLOYEE] field key'd to the
[ENO]. However, I beginning to wonder would it be much much easier if I DID
have all the search material on the orig form.

 

[Tom Wickerath]

That is amazing to have the ability to click on an existing record and

have it open up for editing. I need to figure that part out by reading
your code. I will play with this. May have another Q if it is ok.

I tried to keep the first example, Elements, as simple as possible by
omitting the extra code to open a record for editing by double-clicking. On
the second example, open the form named "frmQueryByFormExampleSub" in design
view. With the Event tab of the Properties dialog displayed (View |
Properties), do a multiple selection of the controls in the detail section.
You should see this:

On Dbl Click........=OpenRecordForEditing()

Now open the class module associated with this form (View | Code). Here, you
should find the function named "OpenRecordForEditing".

Feel free to fire away with any questions!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________