Easy Combo Box Question...

[attrofy]

I am trying to use a combo box to provide a list of choices for user selection. This is based on a table that has one field with the choices in that field. I can sort the data alphabetically in data sheet view, but when I drop down the combo box in my form, it is not alphabetized. I have tried almost everything I can think of. I would retype the entries in correct order if this was a fixed list, but this list should be able to be updated, and I want all future choices in alphabetical order.
Any suggestions????

 

[outpatient]

Heres whacha do,
go to your box in design view, bring up the properties. Go to the data tab a look for the row source field. There should be an sql statement in there. click the three dots to the right. This should bring up a query builder. find the field you want to sort and then click in the sort box, then make your sorting choice. If that doesn't work, let me know Sam Greene
anyone in need of a rock induced headache? if so

http://www.mp3.com/outpatient

 

[Maquis]

Set the combo box's row source to a simple query that lists your field in sorted order. Maq B-)
<insert witty signature here>

 

[WildHare]

Following on what OutPatient suggests, if you see a bunch of SQL code on the &quot;Rowsource&quot; line instead, go to the end of the line, just before the semi-colon and add the words

ORDER BY {the field you want to sort by}

so you end up with
SELECT ...... ORDER BY FOO;

This should do it for you.

Jim Hare
&quot;Remember, you're unique - just like everyone else&quot;
Feel free to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[attrofy]

Thanks for the suggestions, I was trying to avoid the query method because I wanted something sleeker, sexier, and more correct (granted if it works and provides the right solution it is correct....and since an SQL statement is like a query....). ANyway, initial attempts wouldn't work. I forgot to mention that the combo box is in a subform, and when going through the main forms design view, it wasn't saving the changes, even though it said it was. I could press the Run (!) button, and get the correct results, it just wasn't updating when I saved. So anyone following this thread, be sure to open the actual form (subform whatever) that has the combo box before making any changes.
Thanks again for all your help.

 

[xvertex]

Private ComboName_AfterUpdate
Me.ComboName.Requery
EndSub

 

[WildHare]

query method because I wanted something sleeker, sexier, and more correct

Just as an FYI - It's a generally accepted fact, and backed up by people who should know, that using a stored query in an Access database will in almost every situation, be more efficient than setting SQL into string values and using VBA calls back and forth.

Reason being that stored queries are parameterized and parsed into more efficient pseudo-machine code, so the actual over head of running a query after the first execute is minimal. If you stick a bunch of SQL into a string and then execute it with the &quot;docmd.Runsql&quot; method or something, it has to go through the parsing and chunking every single time. There's just a lot more overhead. And you're defeating the whole idea of Access.

It might seem cooler and more sophisticated to code a bunch of VBA into a function or proc, but it's usually not. Once you understand how to use them, stored queries are EXTREMELY functional and efficient.




&quot;Remember, you're unique - just like everyone else&quot;
Feel free to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[attrofy]

Jim,

Not to be argumentative, as you are probably right, it just seems more time intensive to do queries. My problem has been with running simple queries that something must launch them, usually a button. When entering data, how do you keep your end users from having to click several buttons (granted they can fall into the logical tab order sequence, but most of our end users are looking for automated results.) Is there a way to launch a query without resorting to manual intput from the user? Also, how do you pass the value of the query to a field in a table (in order to store that value)? I have had to run a second query to get a calculated value to store in a table field (Not this threads particular example, but in clculating totals from multiple user input fields.)
Thanks for your input.
RUssell

 

[attrofy]

Jim,

Not to be argumentative, as you are probably right, it just seems more time intensive to do queries. My problem has been with running simple queries that something must launch them, usually a button. When entering data, how do you keep your end users from having to click several buttons (granted they can fall into the logical tab order sequence, but most of our end users are looking for automated results.) Is there a way to launch a query without resorting to manual intput from the user? Also, how do you pass the value of the query to a field in a table (in order to store that value)? I have had to run a second query to get a calculated value to store in a table field (Not this threads particular example, but in clculating totals from multiple fields input by the user.)
Thanks for your input.
RUssell

 

[outpatient]

>> Is there a way to launch a query without resorting to manual intput from the user?

Keyword Search: Telepathy

But seriously, to pass the value of a query to a field, use an update query. Sam Greene
Workflow,DRM,DB
MUSIC

http://www.mp3.com/outpatient

 

[WildHare]

Also, how do you pass the value of the query to a field in a table (in order to store that value)? I have had to run a second query to get a calculated value to store in a table field

If I understand you correctly, this is not a recommended method - there is no reason to store a &quot;calculated&quot; field in a table. That's why they invented queries. When you need a calculation done on fields in a record or table, a query does that. Then DISPLAY the results on your form or report.

A simple query of a one-field table sorted in Ascending order takes about 10 seconds to generate and save. When your combo gets the focus, Access runs the query to pull the data and populate the combo. And it's easier for Access to do that than to parse up SQL code that's in the combo's recordsource property.





&quot;Remember, you're unique - just like everyone else&quot;
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[attrofy]

...there is no reason to store a &quot;calculated&quot; field in a table. That's why they invented queries. When you need a calculation done on fields in a record or table, a query does that. Then DISPLAY the results on your form or report.
That is what all the books say also. And as I mentioned before, this particular thread is a poor example of needing to store a calculated field. However, a real life example from a current DB I am working on is for our Marine Dealership to control inventory of all the used boats that come in on trade. The boat is not the only thing to be inventoried. Usually there is a packaged cost of boat, motor and trailer. TO determine cost, we get the value of the 3 pieces individually and combine them into a package deal. Then any additional costs have to be added (such as prep, paint, repairs, trade in allowance, etc.) and added to the package total. This gives a total cost. Now to determine a list value, we have to determine certain multipliers (i.e. condition of boat, amount of time boat is stored, associated costs for maintenance, etc). Also, for convenience, it would be nice to factor in a high and low percentage for special sales and promotions. THis is hard to perform all the calculations needed for the &quot;Cost&quot; feild without storing the value of the cost. This total would be added in a query based on trailer, boat and motor costs. The result would be the displayed value of the query. You wouldn't want to recalculate the individual costs, and add any additional multipiers to the query total each time, would you??? Soon you would be dealing with endless queries to calculate input values for more queries. Where as a stored cost value would allow easier calculations. Also, for year end totals, reports, etc, this would seem the most logical approach (to pass the value of the query to a field). Am I correct in my assumptions, or am I missing something? I am relatively new to Access, and may have a fundamental flaw in my design approaches. I am not trying to be argumentative or win my point, I am truly currious as to the best approach to design proper DB's. Is there a simpler or more idealogical approach to this particular DB? Many of the Databases I am working with involve inventory and quantity tracking, as well as statistical analysis of inventory, so this is going to be a reoccuring problem. If there is a better approach, let me know.

 

[WildHare]

As far as I'm concerned, the best approach is the one that works. I think sometimes &quot;professional&quot; database people get a little too worked up over strict adherence to theoretical guidelines and forget that for many people, just getting the dam' thing to work is the major task. And I have to unfortunately count myself in that group on occasion.

Attrofy, if what you are doing is working for you, and giving you the results you want and the answers you need, then great. You don't need some stuffed shirt telling you the 'right' way to do it if your way is working. And, re-reading your post, it seems that the &quot;cost&quot; field, even though it's calculated, is constant once the record is in the table, right? It's all the other factors that build on cost that are calculated from it. In this instance, I have no philosophical problem with storing &quot;cost&quot; as a discrete piece of data...

Good luck, and remember what Mark Twain once said:

'Thankfully, I have never let my schooling get in the way of my education.&quot;

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[Jusfire]

I really don't want to get in the middle of this but I have to ...

Attrofy, I'll agree that you could use either (SQL/Query) to get proper results as long as data maintains proper form.

Your question - how do you keep your end users from having to click several buttons ...

Manipulate whatever events necessary to complete your user's requirements. This doesn't 'always' require a button for the user to click. Anticipating the users next move is a part of application design. For instance, after the user changes data, you can have calculated results updated (without having to click a button). This can be accomplished with the control's event triggers (such as Lost_Focus).

In my opinion, the design of well thought applications often requires at least intermediate knowledge of the users, the user's data, and the relationship between them. Getting all the data together and getting the best way to manipulate it afterwards can be difficult. My suggestions are to utilize every method you can to eliminate keystrokes/ mouse clicks without restricting the user's rights to his/her data. Always think as a user when you are doing user interface, &quot;Man, I really hate having to click this button every time...&quot;. Fix it! You can, the user can't!

 

[gusbrunston]

My goodness!
Queries ARE sexy, sleek, easy and invisible to users. No reason to have to use several buttons to invoke them. Just select the query as the data source in the property sheet for the combo box. You can display as many rows and columns as you want, size them, color 'em up, and choose the column that binds. Can't imagine a user-friendly database with query-based combo boxes and list boxes. Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[gusbrunston]

Of course, I meant &quot;I can't imagine a user-friendly database WITHOUT query-based combo boxes and list boxes.&quot; Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[attrofy]

Thanks for all the input....so what did we learn here....scratch.....hmmmmmm.......I hate the fact that there are multiple solutions to a given problem that result in the same answer.......programming is hard......it is inherently against my nature to not look at things as having an answer of either yes or no....and when someone says &quot;both are right&quot;.... well that is just terrible....


Thanks for the thought input, that is what is all about.

Russ

 

[attrofy]

GusBrunston,

our Point of Sale program is written in PICK, now I know who the third programmer is they always reffer to that knows PICK....

lol