Set Up a ComboBox

[desperateloser]

All I'm trying to do is this:

I have an existing database of prospective clients, and want to make a combo box out of the Organization Name so that it fills in any name currently in the database if you type in the first few letters. I'd also like to do the same with the Notes Box.

Can anyone describe how this would work on a step-by-step, "this person is a complete idiot who can't figure out ANYTHING on their own and has to be told how to go to the bathroom step-by-step or they'll wind up covered in their own filth" kind of way???

[desperateloser]

Alternately, can anyone give me an url for a site where this would be more appropriate to ask ("Access-for-the-Clueless.com")???

[nico5038]

No need for another site, here the knowledge is available and remember that all of us started clueless :-)

1) Add a new combobox
2) Use the wizard to select the OrganisationName as the only field.
3) Finish the wizard
4) Open the properties when the combo is selected (The hand with white sheet button)
5) Under the Data tab you'll find a SELECT OrganisationName from tblX;
change that into:
SELECT Distinct OrganisationName from tblX;
6) Close and save the form.

That's all, let me know when you get stuck and at which point.

Nic;o)

[desperateloser]

And, as predicted, that would be Step 2 - how do I use the wizard???

[desperateloser]

Okay, I got the wizard to work...but I don't see the words you mention anywhere...

[desperateloser]

Anywhere in the properites box, I mean...also, if I understandit right, I'm creating a >new< combo box taking values from the >old< field - is there any way to turn the >original field< into a combo box with the properties I mentioned?

[nico5038]

Anywhere in the properites box, I mean...also, if I understandit right, I'm creating a >new< combo box taking values from the >old< field - is there any way to turn the >original field< into a combo box with the properties I mentioned?

Yes, right-click the field and select from the popup "Change into" > "Combobox"
Next goto the Data tab and press the [...] button behind the Rowsource property when the cursor is in the field.
Then select the Organizations field from your table and after closing the query editor add the "DISTINCT" as indicated above.

Nic;o)

[desperateloser]

Next goto the Data tab and press the [...] button behind the Rowsource property when the cursor is in the field.
Nic;o)

Now when I do that, 2 boxes come up.

- A small box labeled "Show Table" with 3 tabs - "Tables", "Queriries", "Both", and options to "Add" or "Close"

- A long, wide box labeled "OrganizationName: Query Builder" split into 2 fields:

The upper one is blank.

The lower is some kind of grid with the labels "Field", Table", Show", "Sort", Criteria", and "or".

Both have X and Y scroll bars, which don't seem to do much.

I don't see anything that looks like my form, or any of the field names for it.

[desperateloser]

AHA!

I pretty sure I just figured out how that works...

[desperateloser]

Hey! Am I understanding from the "Do you want to save and update" box that that is an SQL script???

I just made my very own

(painfully simple, ludicrously obviously brief, pathetically wizard-and-copious-online-help-guided)

SQL script???

[nico5038]

The wizard first asks for the table and there you place the table holding the OrganizationName.
The "Grid like form" is the graphical query editor.
Only doubleclick the OrganizationName to get it placed as a field.
Then close the form and continue with the Recordsource instruction, indicating you need to change SELECT into SELECT DISTINCT in the Recordsource.
It's no SQL script, but an SQL statement :-)

Nic;o)

[desperateloser]

Still can't seem to puzzle out where the "Select Distinct" part should happen - when I'm done setting up the Statement I don't see "Recordsource" anywhere, just references to "Row source"....

[nico5038]

Always mixing the property names, use the Row source when it shows a:
SELECT OrganizationName FROMYourTable;

make that:
SELECT Distinct OrganizationName FROMYourTable;

Nic;o)

[desperateloser]

Hurm. Still can't seem to find what you're talking about. My "Row Source" field >just< Says "Organization Name", and if I try to put anything else in there, it crashes the statement - I just get a statement saying something's wrong with that name.

It works right now >almost< the way I want, pulling up a name from the records I've done so far based on what I type in. It does not actually fill out the rest of the form with data from the original record, which I presume is what you're trying to teach me how to tell it do.

[nico5038]

The first property under the Data tab is the Control source and will hold the "Organisation Name" as field where the data will be placed.
The Type of Rowsource property will hold the value "Table/query" and there is in the Rowsource field a SELECT .... statement.

This will only set the value of an Organisation to a value already entered in the table. It won't position the current record to another row. A new copy with the same data can't be created with a combo, without additional VBA coding.

Nic;o)