ComboBox in a Subform referencing Main Form Record selection. Creating and using forms. Post a reply. 7 posts • Page 1 of 1. ComboBox in a Subform referencing Main Form Record selection. By Matt123 » Sat Apr 02, 2011 10:46 am. Do not use combo boxes. Combo boxes are text boxes with auto-fill functionality.
Hi everyone, I wonder if you are able to assist me at all? I am using Access 2010 and am attempting to build an internal skills database for my organisation. I am almost there, but am struggling with the final hurdle.
I have designed a form, which opens automatically when the database is opened. This is very similar to a well known search engine in relation to look and feel it has a single text box SearchBox and a command button to click on in order to search the database when this button is clicked. Attached to the button is a macro, which opens a pre-designed report and actions the filter based on what was entered in the SearchBox field. Below is the code that is run: Category Like '.' & Forms!frmSkillsSearch!SearchBox & '.' Or KeyWords Like '.' & Forms!frmSkillsSearch!SearchBox & '.'
Within the report is a SubForm, which has the same functionality as the original form (the text box is called SearchBox1), but I cannot get the macro attached to the command button on the subform to re-filter the results? I think it may have something to do with the original filter from the initial form. Any suggestions? Thanks for getting back and apologies maybe, I should have been clearer. I basically have a search form frmSkillsSearch in the same style as Google, which opens up as soon as anyone opens the database using the AutoExec macro. The only controls on this form is an unbound text box SearchBox and a command button Command10.
The user will type in the text box the information they want to search the database for e.g.' Housing' then click on the command button to an embedded macro.
The embedded macro opens a report rptSkillsSearchResults, which has a query as its record source qrySearchResults and the macro continues to filters results based on what the user entered in the unbound text box SearchBox on the frmSkillsSearch form with what is in the Category and KeyWords fields. =Category Like '.' & Forms!frmSkillsSearch!SearchBox & '.'
Or KeyWords Like '.' & Forms!frmSkillsSearch!SearchBox & '.' . This produces exactly the results that I require.
I then decided to add a subform frmSkillsSearchResults within the report rptSkillsSearchResults, which has the similar fields as the original form, but is just compressed in terms of space. The unbound textbox is called SearchBox1 and am using an image that will be clicked instead of a command button and this time is called image57. The macro attached to the on click event procedure is to open rptSkillsSearchResults and filter based on the information within the unbound text box of the subform SearchBox1 condition below: =Category Like '.'
& Forms!frmSkillsSearchResults!SearchBox1 & '.' Or KeyWords Like '.' & Forms!frmSkillsSearchResults!SearchBox1 & '.' When I action this macro, the filtered report rptSkillsSearchResults stays the same as what was originally searched for using the orginal form frmSkillsSearch. I have tried numerous macro options such as remove filter and refilter and cannot seem to get the results to update based on the information requested in the subform SearchBox1? Not sure what you mean in relation to the LinkMaster fields, do you mean what are the relationsips within the database?
Hi Ryan'Not sure what you mean in relation to the LinkMaster fields, do you mean what are the relationsips within the database?' No - how records in the subform relate to records on the main form. Because usually they do, or should. This is a general guideline that helps when you are beginning to use Access. As you get better and realize that certain records must be created before related records, you can bend this rule.
Create the main form and the form(s) that will be used as subform(s) - make sure to put the linking key fields on them (usually ID fields). To put a subform on a main form: Create a subform control on your main form using the subform/subreport tool in the toolbox (Cancel the wizard if it pops up and fill properties manually) Then, from the design view of the main form: 1.
Turn on the properties window – Right-Click anywhere and choose Properties from the shortcut menu 2. Click ONE time on the subform control if it is not already selected 3. Click on the DATA tab of the Properties window SourceObject - drop list and choose the name of the form you will use as a subform (You can also Drag a form object from the database window and drop it on the main form.
This automatically sets the SourceObject property.) LinkMasterFields - MainID LinkChildFields - MainID If you have multiple fields, delimit the list with semi-colon LinkMasterFields - MainID;Fieldnamemain LinkChildFields - MainID;Fieldnamechild WHERE: - MainID is replaced with your field name holding an AutoNumber field (usually) in the parent table and a Long Integer field in the child table. Fieldnamemain is the fieldname in the main RecordSet – and it is best to actually put the field on the main form. Fieldnamechild is the name of a field in the child RecordSet – and, once again, it is best that this field actually be ON the related subform. Even though the Help for Access says that the linking fields do not have to be ON the forms, I find this not be to be the case. It is best that you reference fields that are ON each of the respective forms.
If a control is bound, I usually make the Name of the control the same as the ControlSource (what is in it). This does not follow standards but I find that it eases confusion.
There are those who disagree and insist that controls should be named according to their favorite naming convention such as Leszynski/Reddick It is common to set the Visible property to No for the control(s) containing the field(s) used in LinkChildFields 4. While still on the subform control, Click the ALL tab in the Properties window - change the Name property to match the SourceObject property (minus Form. In the beginning if Access puts it there). Difference between Subform Control and Subform The first Click on a subform control puts handles. around the subform control.black squares in the corners and the middle of each side - resizing handles The subform control has properties such as Name SourceObject LinkMasterFields LinkChildFields Visible Locked Left Top Width Height The subform control is the container for the subform or subreport used as the SourceObject.