How to get Query Results to Populate a Text Box on a Form?

Question

How to get Query Results to Populate a Text Box on a Form?

Anonymous

OK, here we are: I've got a 3-Tier heirarchal Form I'm using to display Functions performed by a Section, and then Impacts associated with non-performance of each Function. Basically, from cascading combo-boxes; a user selects A parent work echelon, then a Division based on that selection, and lastly their individual work 'Section' [cboSection]. This works flawlessly- thanks to advice from Ken Sheridan) The whole purpose of this form set-up is to then retrieve a set of records from a table [Functions], containing a pk of "FunctionID", "Function" and foreign key of "SectionID". I have a query that successfully runs based on the parameter of the 'Service' cbo- value matching.

This fires when the user clicks a {Next} button after the Section was selected above. The only problem is that the datasheet view of the Query results pops-up when I don't want it to. I tried using 'acHidden' instead of acNormal, but interestingly -it then pops open in Design View. I created a Text box that I intended to use as the 'destination' for the query results, but I can't get it to populate. I tried basing it's source off a Report based on the query, but nothing displays. (Another fun oddity is that when I tried changing the 'click' event of the 'Next' box to be DoCmd.OpenReport "Function_by_Section" it would send the report to the printer without prompting...). I reset the code back to the Query, and just deleted the report. So now I'm stuck with a query in my face, and a blank text box on my form.

Notes on functionality:

I thought about having this query run as soon as a selection was made in the 'cboSection' box, but didn't because I want to retain an 'undo' button that clears all the cboBox selections.
I also don't want the query results initially appearing in an editablle view - I'd be asking for trouble.
The Query and parent function table have multiple (2-5) unique Function records for each Section, so this isn't a single row /Record return.
Optimal: I need a user to see all the records as 'flat' output,THEN click an 'Edit' button if they want to change something.
Clicking {Edit} would either open a dialog box, with the function(s) or open: Sub-Form/Tab that can be 'cancelled' and closed.

Current Code for the Next Button:

'------------------------------------------------------------

' Function_by_Section_Click

'

'------------------------------------------------------------

Private Sub Function_by_Section_Click()

On Error GoTo Function_by_Section_Click_Err

DoCmd.OpenQuery "Function_by_Section", acNormal

Function_by_Section_Click_Exit:

Exit Sub

Function_by_Section_Click_Err:

MsgBox Error$

Resume Function_by_Section_Click_Exit

______________________________________________________________

Query info: "Function_by_Section"

Fields: FunctionID, Function, SectionID

SectionID Citeria: Forms]![frmFunctions_Single]![cboSection]

I've tried several variations, getting rid of the 'Next' button and having an event for the textbox, but no luck.

Thanks,

Rob

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

3 additional answers

Answer 1

You don't need to open the query. Put the three unbound combo boxes and the Next button in the form header, and set the RecordSource property of the form to the name of the query. In the form's detail section add text box controls bound to the columns from the query which you want to see in the result set. Line these up side by side, set the height of the detail section so it's just deep enough to house the controls, and set the DefaultView property of the form to Continuous Forms. Put labels in the header below the combo boxes and above the text boxes in the detail section to identify each column.

For the Next button's Click event procedure you need just one line of code:

Me.Requery

This is analogous to the 'drill down' form in my demo to which I referred you in your other thread. The form in my case is bound to a query which references all of the combo boxes rather than merely the last one, because my form progressively drills down through the hierarchy, rather than as a single final step via a command button. In essence it works in the same way, however.

To make the form read-only set the Enabled property of each of the controls in the detail section to False (No) and the Locked property of each to True (Yes).

To enable the user to edit the data you can put the following code in the Click event of a button in the form header or footer:

Dim ctrl As Control

On Error Resume Next

For Each ctrl In Me.Section(acDetail).Controls

ctrl.Enabled = Not (ctrl.Enabled)

ctrl.Locked = Not (ctrl.Locked)

Next ctrl

Clicking the button will toggle the controls between editable and read-only, assuming your query is updatable.

Answer 2

RE:

3. In edit, a new record can be added, but the corresponding foreign keys don't populate in the actual table(s). Is there a way for the form to do this?

[...]

In my demo the 'drill down' form does not allow a new row to be inserted, but if you open one of the two forms which use 'hybrid' controls you'll see that you can insert a new row in which you select values from the combo boxes' lists. You are in fact only entering a new value into the Location and ParishID columns as the other combo boxes are unbound. Try entering a new location in one of these forms and you'll see how it works.

This of course assumes that rows exist in the referenced tables for you to select a value. If this is not so, e.g. if you want to relate a row in the referencing table to a new section which does not yet exist as a row in the referenced Sections table, you need to invoke the combo box's NotInList event procedure. This functionality isn't included in my ComboDemo file, but you'll find a NotInList demo [...]

Ken,

I've been experimenting with different 'configurations' of the form and your demos have been a lot of help in allowing me to see the 'mechanics' of what's going on. I also was intrigued by the relationships and functionality in your Student Log, and Multi-Select demos. I'm now able to get the form to retrieve the correct record sets (at least in a DB Query), it will update the join or junction tables with a new row when a new entry is added in one of the fields that correspond to a parent table. (i.e. if a new 'impact' is added in the query, both the parent ImpactsTbl, and the Funct_Impact join or junction table updates with the correct data and keys from the corresponding tables.) In the straight query, I'm typing in the criteria/filter.

The problem I'm having is getting that same functionality on the form. Because the form is essentially a series of correlated combo boxes (like your Student Log but bigger); I need a successive combo to function like my DB Query did, using it's predecessor as criteria to work accross a join or junction to retrieve or 'filter' results for the current combo. Rather than the form be based on a query, have the boxes do the work relative to the tables they need to pull from. See my (lengthy as always) post here: Correlated combo box using data via a junction table, using criteria from another combo box?

Answer 3

1. If in 'edit' mode, a then a user selects a new Section, or completely new heirarchy; and clicks 'Next'- the results populate, but the TextBoxes remain in edit mode.

2. If 'Undo' is clicked after the query populates the boxes, those records have remain visible (and can be edited). I tried several methods to have them reset, none of which worked. In many code variations, it deleted the data form the fields and parent records.

3. In edit, a new record can be added, but the corresponding foreign keys don't populate in the actual table(s). Is there a way for the form to do this?

1. Set the detail section's controls' Enabled and Locked properties to False and True respectively in the button's Click event procedure after requerying the form:

Dim ctrl As Control

Me.Requery

On Error Resume Next

For Each ctrl In Me.Section(acDetail).Controls

ctrl.Enabled = False

ctrl.Locked = True

Next ctrl

2. In the Undo button's Click event procedure assign Nulls to the unbound combo boxes, requery the form and set the detail section's controls Enabled and Locked properties to False and True respectively.

Dim ctrl As Control

Me.cboService = Null

Me.cboDivision = Null

Me.cboSection = Null

Me.Requery

On Error Resume Next

For Each ctrl In Me.Section(acDetail).Controls

ctrl.Enabled = False

ctrl.Locked = True

Next ctrl

3. I'm not quite sure what you are asking here. If you are inserting a row into a referencing table (Locations in my demo) then it is the column in that table which represents the lowest level of the hierarchy (ParishID in my case SectionID in yours) which is the foreign key, so it's simply a matter of selecting a value in the relevant bound combo box. In my demo the 'drill down' form does not allow a new row to be inserted, but if you open one of the two forms which use 'hybrid' controls you'll see that you can insert a new row in which you select values from the combo boxes' lists. You are in fact only entering a new value into the Location and ParishID columns as the other combo boxes are unbound. Try entering a new location in one of these forms and you'll see how it works.

This of course assumes that rows exist in the referenced tables for you to select a value. If this is not so, e.g. if you want to relate a row in the referencing table to a new section which does not yet exist as a row in the referenced Sections table, you need to invoke the combo box's NotInList event procedure. This functionality isn't included in my ComboDemo file, but you'll find a NotInList demo in my same OneDrive folder which illustrates this in a variety of scenarios. If you enter a new city name for instance in the frmContacts form you'll be taken through a series of forms in dialogue mode to add the city and the region in which it is located.

I'm not keen on downloading people's files, I'm afraid. My approach is to make demo files available so that people can examine the methodologies employed and adapt them to their own applications. I do assume that people will have, or be prepared to gain, the necessary technical abilities to do this. I don't think simply serving a solution up on a plate does anyone any service unless they understand it, i.e. I expect people to follow the great Richard Feynman's advice to his students as quoted below my signature.

I don't have any occasion to use SharePoint myself as my work has always been confined to small specialised workgroup environments, for which Access provides a convenient 'cheap and cheerful' means of processing the sort of scientific data with which we are concerned, but be aware that VBA is not supported by web applications. Access's macro language has been developed considerably in recent years for this, but it's not something I have any experience of.

Answer 4

Ken- You are truly a wizard ! It works great, and returns the correct info. There are some behavioral quirks, but they are hopefully just a matter of tweaking the code or controls:

If in 'edit' mode, a then a user selects a new Section, or completely new heirarchy; and clicks 'Next'- the results populate, but the TextBoxes remain in edit mode.
If 'Undo' is clicked after the query populates the boxes, those records have remain visible (and can be edited). I tried several methods to have them reset, none of which worked. In many code variations, it deleted the data form the fields and parent records.
In edit, a new record can be added, but the corresponding foreign keys don't populate in the actual table(s). Is there a way for the form to do this?

I upoaded the DB w/ Form here:

https://drive.google.com/folderview?id=0B-vQVG-3HP-5QjJna2dETE9ld28&usp=sharing

There's also a copy of our 'development' DB, which is the work in progress copy of our original (very amateurish) DB. My assistant did the best he could throwing the original together on the fly with the skill he had. We've since changed project course a few times, and we're now trying to rebuild it to be more agile and capable. The ultimate goal is to have an intuative form that will allow us to correctly enter all the data elements while preserving the relationships. (there's a crude form in the Development db that he'd been using for input). We also plan on using the final version of the 'Test' form via SharePoint or some other mechanism for users to review and edit certain portions of the data.

I had the great idea earlier today of evolving the current test form (Show_Function) into a 'dashboard' that collects the user's organizational info at the start, then allows them to review different groupings of data via Menu commands, much like your 'Open' forms do.

Any thoughts, advice or critique is appreciated. Could you recommend any books, tutorials or online courses that could help me move up to more intermediate or advanced work in Access?

Thanks again,

Rob C.

Share via

How to get Query Results to Populate a Text Box on a Form?

3 additional answers