Query >> combo-box on a form 1

[Turb]

Hello All,
I'm hoping someone can help me with a problem.

Some background:
In Access 97, I have a form with multiple text boxes, of the 50 or so TB's only about 6 are bound to the results of the query underlying the form, the remainder are 'unbound'.
This has worked flawlessly for a few years now.

Now the problem:
I have recently converted 1 of the TB's into a combo-box.
When I run the query (that underlays the form) by itself, it generates multiple records - this is good.
The problem arises when I try to have the combo-box on the form show me all the records of a single column of the query results... it won't do this - I need it to.
Is a combo-box even the right tool for the job?

Can anyone help me please?
Thanks!


- Turb

 

[Turb]

I should add, that of all the columns in the query results, the one I need the combo-box for is the only one that contains differing data, all the other columns contain identical data (hence the need for the combo-box).

Thanks again!


- Turb

 

[Turb]

No replies at all? From anyone?


- Turb

 

[dhookom]

I think you need to restate your question so that we can understand better.

What is the purpose of your form (edit data, supply criteria for a query/form/report,...)? Why are some controls bound and others are not? I can't picture the need for 44 unbound text boxes.

What do you mean by "have the combo-box on the form show me all the records of a single column of the query results"?


Duane
Hook'D on Access
MS Access MVP

 

[Turb]

Hi Dhookom, Thanks for your reply.

I'm sorry if I wasn't clear.

The form is for entering data that is ultimately collected by a SQL query and appended to a table - the form is not set to allow 'data entry' in it's properties, but is set to allow 'edits', 'deletions' and 'additions'.
This form has worked fine for years, but lately we have been getting data from our system that give multiple records (parts) for a single 'key' (order numbers). This is not a malfunction, but simply a new condition that I will have to revise my database form to acommodate.

Some controls are bound and others are not because some data is collected by the underlying query (call it 'MyQuery') and it is this data that populates the bound fields. The others are unbound so the user can enter other data (test results).

What I meant by "have the combo-box on the form show me all the records of a single column of the query results" is that when I run just the form's underlying query by itself, it gives me multiple records (as it should for my application), but when run for the form's on-open event I only get a single record (probably the 'top most' or 'first' record in the query's results).
I was hoping there was a way to have the combo box show all the selections of the underlying query (or rather, the one column in which the data is different) so the user can select the proper data for the part he is testing.

Note: I have found by messing around with a copy of the form, that if I leave the 'control source' of the combo box to the same field of the form's underlying query as it was when it was a text box (call this 'MyField') and set the 'row source' to "SELECT MyQuery.MyField FROM MyQuery;" I almost get what I need.
This approach reruns the form's underlying query again when the user clicks on the combo box. I really don't want the query running twice (It's annoying ;-)) but this is closer to what I need.

I hope this helps,

Thanks!


- Turb

 

[dhookom]

If you want the selected value of the combo box to display a value from your form's record source, it must be bound to a field in the record source. Selecting a different value in the combo box will change the value of the field in the form's record source.

Duane
Hook'D on Access
MS Access MVP

 

[Turb]

dhookom,
I'm sorry, I thought I stated that it was bound to a field in the record source "if I leave the 'control source' of the combo box to the same field of the form's underlying query as it was when it was a text box".

I've never 'unbound' the combo box from the query's field, the problem is that the combo box only shows me a single record from that underlying query (drop-down menu showing a single item doesn't help much ;-)).

Is there any way to have the combo box show ALL the records collected by the form's underlying query? Or if not, is there some way to accomplish the same type of thing?

Thanks!


- Turb

 

[PHV]

Follow the combo wizard to create an unbound combo as a navigation tool in, say, the header section of your form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Turb]

PHV,
Thank you for your respnose.

Um... how does having an 'unbound' combo box help me to get ALL the records from the form's query?

I may be doing this wrong but all an unbound got me was a blank drop-down menu and if I make it bound to the same query as is 'under' the form, the query just runs 2X just like I stated above.

Thanks!


- Turb

 

[dhookom]

The Row Source of a combo box determines what values are displayed in the combo box when it is dropped down. The control source determines what is displayed when it is not dropped down. An unbound combo box will display either nothing, a default value, or a selected value.

If your form's record source is like:

SELECT FirstName, LastName, Dept, Phone
FROM tblEmployee;

And you want to display all unique Dept values from tblEmployees in the combo box, the Row Source of the combo box would be:

SELECT DISTINCT Dept
FROM tblEmployee;

Duane
Hook'D on Access
MS Access MVP

 

[Turb]

dhookum, thank you for the clarification.

Maybe I should approach this from another angle...

If I cannot get a combo box to allow me to select more than a single record from a query, even if that query has multiple records in it when run, maybe I can populate the form fields (the bound ones, normally populated by the query), by making them unbound and then populating them from a chosen selection from the combo box?
Is this possible?


- Turb

 

[dhookom]

Apparently you want to use the selected and displayed values in your form and append one or multiple records to another table.

If you want to select multiple values, then you can possibly use a list box. A multi-select list box can't be bound to a field (prior to Access 2007). You would need code to loop through the selected items in the list box and run append queries.

Duane
Hook'D on Access
MS Access MVP

 

[Turb]

Duane,
Actually, no.

I want to populate text fields on a form based on the data selected by the user (one record of many) from the combo box.
Well, since I cannot seem to get the combo box to show more than one record from a select query under a form...




- Turb

 

[dhookom]

Ok, I give. Good luck.

Duane
Hook'D on Access
MS Access MVP

 

[Turb]

Duane,
Ok.
Thanks for your input.
So far, I looks like I will have to make some changes on this form to get what I need.

I can make my form's underlying query an 'append' query (copy the existing and change it's type to 'append' - after I first make the 'temp' table), to append to the 'temp' table, and create another query that will delete the contents of the 'temp' table on form close.

I can create a couple of macros to run these queries and place the 'open' macro under the open form command button on my menu.
The 'open' macro would set warnings to 'off', run the append query and then open the form.
The 'close' macro would trigger from the form's 'on close' event and run the 'delete' query to clear the 'temp' table.

If I make the form's 'record source' the new 'temp' table, maybe I can get the combo box to list all the items and maybe get the few 'bound' text boxes on the form to populate based on what is selected from the drop-down of the combo box.
Actually, I could have sworn I saw something to that effect here on Tek-Tips, but am having a very hard time finding once again.

Thanks!


- Turb

 

[Turb]

Ok, here's what works so far:

1. Created a make table query (based on the original select query) to make the temp table and once it was created I then changed it to an append query to append to the temp table; this gets me the data I need.
2. Created a macro (OpenForm) to run:
SetWarnings Warnings On - 'No'
... Open Query - MyAppendqry
... Open Form - MyForm
3. Placed this macro under the command button on my menu for this form
4. Removed the record source for the form
5. 'Unbound' the previously bound text boxes on my form
6. Created a combo box (SBUILDcbo) on the form where I previously had a text box
7. Combo box's record source is:
"SELECT DISTINCTROW [TempTable].[Column1], [TempTable].[Column2], [TempTable].[Column3], [TempTable].[Column4], [TempTable].[Column5], [TempTable].[Column6], [TempTable].[Column9] FROM [TempTable];"
8. Combo box's first three columns have width, the remainder are 0 inches
9. Combo box is bound to column 2 (column3 above)
10. Combo box has an 'After Update' event procedure:
Me.Textbox1 = Me.SBUILDcbo.Column(0)
Me.Textbox2 = Me.SBUILDcbo.Column(1)
Me.Textbox3 = Me.SBUILDcbo.Column(3)
Me.Textbox4 = Me.SBUILDcbo.Column(4)
Me.Textbox5 = Me.SBUILDcbo.Column(5)
Me.Textbox6 = Me.SBUILDcbo.Column(6)

Items 6, 7, 8, 9 & 10 populate the now 'unbound' text boxes on my form once a selection is made from the combo box's list

11. Created a delete query to empty the temp table
12. Created a macro (CloseForm) to run:
SetWarnings Warnings On - 'No'
... Open Query - My Delete query
13. Created an On Close event for the form to run the CloseForm macro

All of this works flawlessly to do what I need with one exception:
Even though the combo box is bound to column 2, it does not show the column 2 data when a record is selected from it's list; it shows column 0's data no matter what I bind it to.

Any ideas?

Thanks!


- Turb

 

[Turb]

Oh geez...
Punctuation error!

Everything works fine now.

Thanks again!


- Turb

 

[SRRK2083]

Hi Turb,

I am very new to tek-tips and as well VBA. I am very new to programming though I am from MIS background. I have been a Business Analyst and did not have to do programming before. But my new job profile is all about automating the systems. I am trying to automate one of the applications but I am stuck.

It might be very simple to do it but am not sure how to start. I have a database in which I have one main form and a subform. In the main form, the values get populated directly from the tables with simple "select" statements in the respective combo boxes. Where as in the subform, I have 2 combo boxes with a list of values in each, respectively. Two of them are referring to the same table. I also have 10 other fields that need to get populated from the TABLE.

For example, I chose value a from the combo box 1 and value b from combo box 2 then all Ten fields should get populated with their respective values from the table. I can conceptualize the way it suppose to work but, I am unable to put the lines of code. Pleas help.

I apologize if am not clear but I felt your database looked similar to mine and I have to get started with this at somewhere rather than trying to figure out things by myself.

Please let me know if am not clear and I can try and submit the details again.

Thanks in advance.