Search Form - SubForm Advanced

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?
0
Utf
1/30/2008 12:09:21 AM
access 16762 articles. 3 followers. Follow

5 Replies
532 Views

Similar Articles

[PageSpeed] 9

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" wrote:

> 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?
0
Utf
1/30/2008 9:45:01 AM
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" wrote:

> 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" wrote:
> 
> > 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?
0
Utf
1/30/2008 5:04:02 PM
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" wrote:

> 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.
0
Utf
1/30/2008 5:40:03 PM
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.


"Tom Wickerath" wrote:

> 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" wrote:
> 
> > 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.
0
Utf
1/30/2008 6:17:03 PM
> 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
__________________________________________

"dbarmer" wrote:

> 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.
0
Utf
1/30/2008 6:46:00 PM
Reply:

Similar Artilces:

faxing form
Is there a way to fax the form page via the fax modem on my computer? Create a report based on your form then fax the resulting report. Take a look at http://support.microsoft.com/Default.aspx?kbid=231797 hopefully it is what you are looking for. -- Hope this helps, Daniel Pineault If this post was helpful, please rate it by using the vote buttons. "He cries for help" wrote: > Is there a way to fax the form page via the fax modem on my computer? "He cries for help" <Hecriesforhelp@discussions.microsoft.com> wrote in message news:252D07EA-5886-4B0C-99...

reference to master form
Hello there I have master form and inside there is subform. If an event procedure uccur on subform, how can i get refence to the master form in order to change there things when the name of the master form is diffrent always? Roy Look in Access HELP for the "Parent." property. Your code will look something like: Me.Parent.... from within the subform. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Roy Goldhammer" <roy@ho...

Anyone found an easy way to search Case history?
Specifically the case notes, but I'll take what I can get. Use the advanced find, Look for 'Notes'/ 'Activity', define your search criteria: Regarding(Case) --> Case: Contains data. Result returned: all notes/ activity created in cases. If no criteria set but click on Find directly, every notes/activity created in MSCRM returned. "Jon" wrote: > Specifically the case notes, but I'll take what I can get. > > ...

How can I refresh the master form from a subform?
Hello. On the After Update of the subform I am trying to refresh the Master form. I have tried things like Docmd.requery and Me.Refresh, but not sure what I am doing. Can you help me with this? Thanks. Iram Iram wrote: >On the After Update of the subform I am trying to refresh the Master form. I >have tried things like Docmd.requery and Me.Refresh, but not sure what I am >doing. Try using: Parent.Requery -- Marsh MVP [MS Access] ...

Modal Form from a child form
Hi, I have a little problem, I have a MDI form (Main Menu), this form open a child form (A), and I need to open a form B as modal form from form A. I can to this with showDialog(), but it open form B outside MDI form (Main Menu) and it is showed on the windows bar as another application opened. So the question is, there are any way to open a modal form from a child form inside a MDI form? Thanks El Alquimista wrote: > Hi, I have a little problem, I have a MDI form (Main Menu), this form > open a child form (A), and I need to open a form B as modal form from > form A. I c...

Data source for PivotTable-Form in ACCESS 2000
Hi, this drives me crazy, 4 years ago I defined in an ACCESS 2000 application a "PivotTable-Form". The resulting EXCEL table inclusive the "Data refresh" works perfect. I now want to update/change the query for the "Data Source" but cannot find which query is behind the "PivotTable-Form" or behind the resulting EXCEL spreadsheet. When editing the properties for the "PivotTable-Form" or the EXCEL spreadsheet the "Data Source" is always blank. Question: Where does Access 2000 or EXCEL 2000 hide the respective data source (Query)...

Money 2006 Standard
I've just started to use the Money 2006 and I'm trying to move from the Essential Budget to the Advanced Budget, but the software does not move to the selected option. Is there any action to correct this problem? I was using a very old Money version (97) and the file was converted without any problem with budget data in the Advanced format, but as I can't select the Advanced Budget I'm not able to change anything in it. Is it possible to somebody help me on solving this problem? Thank you very much -- Bob Campos Have you tried doing a File - Repair, Bob? -- Michael Gor...

Search folders Otlk-2003 don't work?
I create a new search folder in OUtlook folder with the criteria, "mail to and from certain people". And then I choose a person from the list. The email address for that person has always been the same. However the search folder only comes up with mail I have SENT to that person, not mail they have sent to me. Whats with that? thanks... jf ...

Tying tables to forms
I have four connected tables that work well as table input but when I put them in a form some of the fields will not let me make entries. Does this happen because I am using the Id fields and subsequent data from the wrong tables? -- Taylor It sounds like you have created a non-updatable form. One cautionary note first: Don't tie your forms directly to the tables. Use queries instead. The queries will act as a stop light for which data is written and when. If more than one person tries to make a change to the same record at the same time, you will run into problems. From wha...

Stop Buttons showing when opening up form
I have a button on my Main Start up page that when click makes these buttons visible, but when I open up my DB they automatically show on start up, is it possible they not be visible till I click ckbHelp.......Thanks for any Help.....Bob Private Sub ckbHelp_Click() If ckbHelp = True Then cmbHelpNewHorse.Visible = True cmbHelpActFinHorse.Visible = True End If If ckbHelp = False Then cmbHelpNewHorse.Visible = False cmbHelpActFinHorse.Visible = False End If End Sub Bob, In design view, set the property pf the *button* Visible=No Regards/Jacob "Bob" <xxx@xx.xx> wrote i...

Using Excel to design business forms
I have been using excel hit and miss to design business forms. I have been pretty successful but in many places I have found myself doing many inelegant things to get stuff to layout. Is there a good place to start looking to see how this is done properly, as I have seen many well done forms produced in excel. ...

Cannot view any form in crm 3.0
We have just installed CRM 3.0 on MS server 2003. I have created an account which has a system administrator role assigned to it. The issue is that when I open CRM web (on the server) when I select account, contact, activities, calendar etc... I get the following error: An error has occured. For more information, contact your system administrator. Now if I open CRM web using an account which has restricted access mode ticked, I don't get any errors, everything opens fine. The problem is that with an restricted access code i can't do much. I have tried assigning different roles ...

Bug with controls location and scrollbars in a Windows Form
Hi, I've noticed a bug with WinForm and scrollbars, and I would like to know if there is a workaround ... Bug description : ------------------------ 1) create a win form, and add 3 buttons : * FirstButton : upper left corner of the winform * SecondButton : location = (0 ; 600) * ThirdButton : location = (100, 600) 2) Set : ThirdButton.Visible = false 3) Set the Form size to 100 x 100 ... and set : AutoScroll = true 4) Add the following event handler to the Click event of FirstButton and SecondButton ... private vo...

List all row source for all forms, reports etc.
Hi I'm doing some work cleaning out old unused forms, reports and queries. I have been going through each report in a database (There are A LOT), determining its row source query, then marking it for deletion. I will eventually end up going through and deleting all the unused queries. As you can imagine this is time consuming, and I was thinkg 'there must be a FASTER way" Does anyone have a suggestion, or link to a pre-built function or model that could assist me? Regards Darragh On Thu, 17 Jan 2008 22:14:18 -0800 (PST), Darragh <darragh.murray@gmail.com> wrote: >Hi...

Minimizing form
Hi How can I minimize the current form? Thanks Regards DoCmd.Minimize in a command button code or other event. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "John" <John@nospam.infovis.co.uk> wrote in message news:uBcRcE73HHA.3600@TK2MSFTNGP02.phx.gbl... > Hi > > > > How can I minimize the current form? > > > > Thanks > > > > Regards > > ...

Sum of values in a form
Folks, I have read numerous threads on this topic, and understand that in order for my Sum() to work I need t ensure all references are made back to the Record Source. I have endeavoured to do this, but am still getting #Error. I am trying to Sum the revenue each tenant provides in a given date range, to give a total revenue figure. If the date range is a whole month, then the expression just takes the value of Rent PCM, and not Rent PCM * No. of Days, which would give some unusual results. My Expression looks like this: =Sum(Nz(IIf([Short Let]=-1,[Rent pcm],IIf(Day([Forms]...

Conditions on form calculated textbox
Hello, In a form called Schedule (based on the table Schedule) I have the following textboxes that are bound to the table Schedule: Time In, Time Out, Time Off, Anomaly and Overtime I also have an unbound textbox called HRS that gives me the result of the following equation: =[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] The above result gives me the daily hours that someone worked. The formating is decimal, as an example. 16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0 I also added another figure for lunch the hour [16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0...

Editing Password protected form fields in Word 2007
We have created a Word document with a legacy text form field. We inserted a word date field and protected the document allowing Filling in Forms. After protecting the document when we try going to the form field and clicking on back space to delete the entry we get "This is not a valid selection" message box. We have existing word documents in 2003 with similar form fields. We were able to delete the entries and add new data. After converting the documents to 2007 or create new documents in 2007 and try to delete the entries we get the above error. Hi Veera, ...

Form Opening with Blank Column(s)
I have created a couple applications that are working well on our remote access server, Access 2003. However, when I have people put the application on there standard desktop on the network, one or more columns appear blank when the form is opened in Access 2003. Any ideas why the data retrieval or function would change between our server addition of Access vs client workstations? -- Message posted via http://www.accessmonster.com I have one user in my organization that is experiencing this problem also. The problem started the week of the 25th of February. The user could not give me a ...

Form pages
Is it possible to have a two page form and only print the first page or second page when needed.? Depending on your design, you should be able to use the File/Print dialog box selection to print 1 of 2, or 2 of 2 pages. But, forms are made for data entry, and viewing... not printing. Better that you develop a rptPageOne and a rptPageTwo and select either for printing from your form. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." &quo...

sending a form
Good day, I am trying to create an issue tracker where one can use a form to assign someone from a given list to a task and then notify them of the task by being able to send the person an email containing the form. So far, I have only been able to create a macro that sends the entire database (and the user has to manually enter the email address). Any ideas on how to automate all of this? You can only natively do this in Access 2007. For earlier versions try Easy Survey: http://www.mapilab.com/outlook/easy_survey/ -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org...

New contact form population
Hi everyone! Here is the problem we are having. When adding a new account and contacts to CRM we start by adding an account. From that account/contact page, we click on "new contact". Previously, this contact form would pop up and many of the fields would be populated. For some reason, some of these fields are not populating. The parent account and several of the address fields remain blank. Any ideas? Have you changed the field mappings in Deployment Manager? I'd start by verifying that there is still a mapping between Accounts and Contacts, and that the fields ...

Adding Contact Person to SOP Other Invoice Form
We would like to add a field to the "Bill to" and "Ship to" addresses that is the "Contact Person" field from the Address ID palette. How do I do this? This is done easily, check out Knowledgebase Article 874176. You will need to add some additional calculated fields and then modify the existing calculated fields. Using the rw_SelectAddrLine user function you can add the following fields (for example) to an address and have it grow and shrink dynamically with no empty lines. Customer Name Contact Person Address1 Address2 Address3 City State Zip Country ...

changing the default type of searching in the find function
When I try to search something in Excel 2003 the default LOOK IN type is FORMULAS. How do I set VALUE as default? Saved from a previous post: Excel tries to help by remembering the last settings you used--except for the first search in that session. You can use that to your advantage. You could make a dummy workbook and put it in your xlStart folder. Have a macro in that workbook that does a find (and sets all the stuff the way you like). Then closes and gets out of the way. Option Explicit Sub auto_open() Worksheets("sheet1").Cells.Find What:="", After:=A...

Find First on Other Form
Happy Spring Groupies!! I am trying to have access open a second form (and find the first occurrence of a job number) based on what I select on my first form. Unfortunately, I am getting a syntax error in my code: Dim rs As DAO.Recordset Dim strFind As String Dim JobNumber As String Set rs = Me.RecordsetClone strFind = Me.cmbJob docmd.openform "frmPopEditMLT",acnormal,rs.FindFirst "[JobNumber]=""" & strFind & """" <syntax error> I am not very good with code. Can somebody please help me fix this? -...