Select from a combo box or drop down list (2000)

Topic

New Reply

I might as well “fess up” that I know no Visual Basic, and almost no SQL. Until I was given directions to this forum, my only source of help was the help function in Access. Thanks to you kind folks, I succeeded yesterday in completing a query to choose a range of accounts and a department number – or no department number. I also managed to write a macro that resulted in the final query being exported to Excel.
Now my supervisor wants to be able to fill the “Dept number” by selecting a Department by name from a list.

So far I have a Table listing Departments with Department number as the primary field.
A form called Select Departments that has a combo box based on the above table. This box shows the names, not the numbers, but the field Department number is included – just hidden.

Now — how do I even start to build an Event Procedure that will result in the number for the selected Department being inserted into the Query that selects the parameters for the accounts and department? Or is that even what I want to do?

Reply | Quote

Replies

I don’t think you have to resort to event procedures and code just yet. The usual solution for this kind of thing is to create a criteria in your query that references the combo box control on your form. It typically looks something like:
Forms![MyFormName]!ComboBoxName
Note that the bound column for the form needs to be the same kind of data you are trying to restrict, or you will get a type conversion error. Hope this makes sense and helps – if not post back.

Reply | Quote

Thanks Wendell, I will give this a try right away.

Reply | Quote

Typing this into criteria doesn’t result in a dialog box of any kind. It just skips that field on the Table the the Query creates.
Maybe I need to elaborate. This combo box doesn’t appear to do anything at this point. The form comes up, click the arrow and you get a list of names. Choosing a name — does nothing.
Sorry to be such a know-nothing.

Reply | Quote

Guess I made an assumption about what you were trying to do that isn’t true. When you select a value in the combo box, what do you want to have happen? If you are wanting to display a record on the form based on the choice you make in the combo box, the simplest way is to use the combo box wizard. The third option it gives you the option of creating a combo box that will “Find a record on my form based on the value I selected in my combo box.” It creates a fairly simple VBA procedure that works on the AfterUpdate event for the combo box. If you are after a different effect, let us know.

Reply | Quote

Okay, let me start from scratch here. What the original request from my Supervisor was. Where I’ve gotten with it, and what he wants now. I may be off on a totally irrelevant track.
Original request:
“Build a Query that will compute the MTDC for an account.” ( MTDCis made up of specific expense codes)
Did That.
“Can I request a range of accounts?”
Changed to a Paremeter Query by putting Between [Type the beginning Account] and [Type the ending account] in the criteria for the account number
” We need to be able to request a specific Department as well”
Did this with help from this site. At this point the Department number has the crieria [Type Department number] or [Type Department number] IsNull.
Thought I was through. This does what is needed quite efficiently, and I got it to work in a Macro that exports the results into Excel.

Now —
” I would rather not have to look up the account number each time. Can we have a list of Departments to choose from?”

Based on the way Access help described a combo box, I thought that was what I needed. I have created a table of Department names and associated Department numbers. The Department number is the primary field. Then I created a Form with a combo box with two fields. Department number is the bound field, Department name is all that shows in the drop down list.

What I need this to do is insert the department number into the Query criteria upon selection of the Department name. As long as I’m asking, it also needs to select all departments within the range if there is no Department selected.

If this is not the way to accomplish my goal, any guidance would be appreciated.

Reply | Quote

<>
Does that mean you want to be able to select ALL accounts by not typing in the Account Number range?

<>
I thought you had solved this by entering [Type Department number] or [Type Department number] IsNull in the Department criteria, No?

It’s not clear (to me at least) of what you want to do.
Pat

Reply | Quote

<>

No. It will always be either a single account. (Which can be accomplished by typing the same beginning and ending account), or a range of accounts.
Sometimes we will require the entire range, regardless of what Department the account is assigned to. Sometimes we will require only the accounts, within the range, that are assigned to a specific Department.

<>

Actually, yes, and quite beautifully. The thing is, we have over a hundred Departments. Rather than having to drag out the list and look up the number before typing it in, my Supervisor wants to be able to select from a list -by name- of Departments and have the appropriate number inserted into the Query. The reason I cannot use Department Name directly is that the Read Only Data Base I link to for Account numbers has them assigned by Department Number only.
Thanks for your interest.

Reply | Quote

OK, it does look like you need some VBA, but the wizard can do it for you. If you are simply opening a query, then you could do that manually, once you have the form with the combo box open. To automate it a bit more, you could put a command button on your form that says display the results, and the wizard will let you choose to run a query. If you are actually creating a report, it will also open a report for you – in that case the report would need to have the query with the reference to the combo box as its data source. If you really want it to be quite seamless, you could actually move the code for the command button to the event AfterUpdate for the combo box, and then each time a new value was selected in the combo box, it would open the query or report with the appropriate data.

Things get a fair bit more complicated when you want to do a range of accounts – combo boxes don’t work well with ranges unless you use two. And then someone will say they want two different ranges. Multi-select List Boxes may be a reasonable choice, but they still don’t deal with ranges. You may need to resort to some sort of Like criteria, but that gets to be fairly complicated, as you are creating a query on the fly. You might also want to look at filters and how they are used – they are fairly powerful in their own right even when working with tables of queries. You have an interesting challenge.

Reply | Quote

This gets me closer. Much closer. In fact, if push comes to shove, this will work. I am frustrated though. I really want the Department number to be automatically entered into the criteria for the Query. I was expecting something like the old TAKE, PUT commands from Basic. Essentially TAKE value from Here-PUT value There. Doesn’t exist in Visual Basic?

Reply | Quote

The department number should be taken from the appropriate column in the combo box – my original post presumed it was the bound value. If the combo is unbound, or if you are using other than the bound column, then you need to refer to the column number as Pat suggests. Also note that columns start with zero (0), so if the department number is hidden as the second column in your rowsource for the combo, then it is refered to as
Forms!MyFormName!MyComboBox.Column(1)

FYI, it is possible to create SQL strings in VBA and make them the source of a report or query, which is usually a bit more work and involves more code. But the concept of TAKE and PUT dealt with reading and writing files as I recall, and not retrieving data from a database structure. The equivalent of those commands still exist if you are doing sequential file I/O in VBA. And don’t let your frustration get the best of you – many of us have been at this for 10 years or more, and we often assume others know most of what we do. In addition, Access is (IMHO and in that of most serious Access developers) the most powerful and complex Microsoft Office application. (I can just see all those Word and Excel gurus going ballistic about now

Reply | Quote

Here is what I’m getting when I try to reference the Combo Box in the Query

Forms![Find Departments]!Combo0.Column(0)
Undefined function Forms!(etc) Column’ in expression.
Forms![Find Departments]!Combo0
Treats this as a parameter – automatically adding [ ] around Forms! and Combo0

I’m not sure this method will have the result I want, even if I get it to work. I looks to me like it would just pick up whatever value is currently in the column. No selection ability. Am I wrong??

The closest I’ve gotten to what I need is the control button on the form. For this morning, I had the Macro open the form – selected my department (after making the dept number visible), and then ran the query from the control button.
Since the form was open, I could then type in the correct Department number. On closing the query, the Macro continued to completion.

My supervisor was not unhappy with this. It is much better than dragging out the manual to look up the Department number. As I said — really close.

How do I do this?? It looks like it would have exactly the result I want.

Reply | Quote

It looks like you are really close – what is your RowSource for you department number combo box (Combo0)?

In the interest of solving your problem more quickly, you might want to compact and repair your database, then ZIP it and attach it to a post in this thread. That way someone should be able to quickly spot the reason the combo box isn’t working. You may need to sanitize it so there isn’t any real data, and if you have tables with more than a few records, you may have to delete most of the records so it will fit within the attachment file size constraints. (Be sure to do that in a copy, not your working original!)

Reply | Quote

The RowSource for my Combo Box reads SELECT[Departments].[Department number],[Departments].[Department name]FROM[Departments;

Departments is a table that has at this point about 20 departments with associated numbers. When I get this to work, I will finish filling it.

I’m linked to some Read Only files. Plus the information I work with is considered “confidential”.
Let me think about this a bit. I may be able to create a “dummy” base that has no real information, but is set up like my database.

Thanks again

Reply | Quote

First of all, Howdee to a fella Texan….Dallas no less!

Okay. From all the posts in this thread, it sounds like you just want to have a querries criteria based on the value of a combobox. This combobox has 2 columns, a number and a name. The number is hidden, the name is not. What the user sees is the Name, but the combo box ‘value’ is the number.

Okay, first let’s check your combo box. To begin with, your Bound Column (Data Tab of the Combo Box properties) should say 1. Under the format tab, Column Count should be 2. Column Widths should be 0″;1.5″ (that 1.5″ can be whatever size you want your dropdown to showup as….). And the List Width should be 1.5″ (just a sum of the ColumnWidths….so if you change the 1.5, change this to the same).

Now, that should set everything right on the combo box.

Go into your query. Drag the field you want to have the ‘criteria’ for. (Keep the form with the combo box open). Right click in the Criteria box, and select Build. Now, in the bottom left ‘pane’ of the Expression Builder, you should see a ‘folder’ with a Plus in it, which says Forms. Double click that. It should expand to two folders, one of which says Loaded Forms, double click that. Select the form with the combo box, from the list that expands from that. In the middle pane now, you should have a list of all the objects on your form. Find the Combo box, and double click it. Click Okay (to close the Expression builder, and now your query should run, showing data based on the ‘hidden’ ID of the combo box.

Let me know if you want a sample db on this. (I haven’t used the expression builder in a while, so I actually just built an example as I was writing this…….)

Reply | Quote

Hi right back fellow Texan.
How ’bout that? It does run. To my eye doesn’t look a hair different than what I typed from Wendell’s original reply. But Hey! The machine knows, I don’t.
However, as I suspected, it is showing all Departments in the selected Account range. Still no ability to choose a specific Department.
Hmmph– I wonder if I could select a department on the form then run the query from the command button I created awhile back??
Well the Query runs anyway. Thanks a bunch podner.

Reply | Quote

See, I was wondering if that is where you were trying to go. Your reply confirms it. You really need TWO combo boxes. The first one is what you currently have. Build a second one that lists the ‘Departments’ by account. Use the same ‘query’ technique. Essentially, build a query that lists the Department ID and Department Name. Add the Account ID field (but uncheck the ‘Show’ checkbox. Use the technique described before to set the criteria for the Account ID to be ‘retrieved’ from the first combo box. (Unchecking the ‘Show’ just ‘unclutters’ the results, while maintaining a criteria field!).

Then, put a second combo box on that form, and set it’s RowSource to the query you just made. Set it up EXACTLY like the first (2 columns, widths, etc.). Now here is the ‘code’ part, but it’s pretty easy. Go into the Properties for the first combobox. Go to the Event tab, and then Right Click on the ‘On Click’ event (anywhere there is white space across from ‘On Click’.). Select ‘Build’, and then select Code Builder and press OK.

You should now see something like this:

Private Sub Combo0_Click()

End Sub

But Combo0 will be the name of your first combobox. What we want to do, is refresh the second combo box, when the first combobox is clicked. (For comboboxes, the OnClick event is fired whenever a combobox is changed.) So here’s what to put in for your code:

Me.cmbMyComboBox.Requery

So you’re code page will have this in it:

Private Sub Combo0_Click()
Me.cmbMyCombo.Requery
End Sub

Where Combo0 should be the name of the first combo box, and cmbMyCombo is the name of the second combo box. TYPE the code in, because you will find that the VBE will help you out. When you type the Dot after Me, you’ll get a dropdown of all the properties, controls and procedures that your form has, so you only need to type a few letters of your second combobox’s name. When you hit the . again, after the name, you’ll find that typing r, will bring up Requery right away (since it is the first property or procedure starting with R).

Now, test your combo boxes. When the first one is changed, you should get the appropriate Departments displayed in the second one. (It will be blank…or should be, when the first one is changed…this is advantageous…I’ll explain in a bit).

Then Go back into your original Query, and add the Department ID field. Use the same process to have the DepartmentID ‘key’ off of the second combobox. HOWEVER, put below that criteria line, ‘Is Null (and a copy of what’s above it…..)’ (so it would be ‘Is Null [Forms]![Form1]![cmbMyCombo]’ (without quotes, and with the right name of course).

That way, if they don’t select anything in the second combo box, it will display all departments. If they do select a department, then it will display only that department.

I hope that makes sense. Again, if you need a sample, I’ll be glad to send one to you.

By the way, fellow Texan, I have a VBA tutorial on my website (http://www.wolfwares.com) in the General section. It’s free, and about 60 pages long. But I’ve gotten a lot of good feedback to it’s usefulness for beginning developers.

Reply | Quote

Yes — I think I’m going to need to see this. I keep getting lost between box one and box two and who gets what done when. My problem, not yours.

Reply | Quote

Here ya go. The attached database has three tables. tblAccounts, tblDepartments, and tblData. tblAccounts has an AccountID and an Account Name. tblDepartments has a DepartmentID, Department Name, and AccountID. Then there are two querries. qryData is the ‘results’ query, which is run from the form frmExample. qryDepartmentsByAccounts is used in the second combo box.

The form has two comboboxes. The first lets you select an Account. (When it is changed, it requerries the Department combobox, and sets it to 0). The second combobox shows the departments under the account in the first combo box. When you first select an Account, if you run the query (click Show Data), the query will display data for ALL departments under that account. If you select a department, then the Show Data query will only show data for that department.

Give me a holler if you have a problem with this sample db.

Reply | Quote

Well, the fog is clearing to some extent. This actually works now. Except — (Isn’t there always an “Except”?) the IS Null isn’t doing it’s thing properly. Worked fine when my criteria was “[Type in a Department number] – isn’t working when it references the combo box. If the combo box is empty, it just doesn’t return anything.

Can’t tell you how much help you’ve been. The tutorial is awesome. Can’t wait to have a chance to dig into it.

Reply | Quote

I had the same problem. I was just guessing that it would work like that. My sample should work fine though. What I did in my sample, is on the OnClick Event of the first combobox, where you refresh the second one, after you refresh it, set it to 0. 0 should not be a Department ID (unless you get into Billions of Departments, then it can roll over to 0). Then, in the query, instead of using IsNull, just check if it equals 0. (Take a look at the sample I posted, it all works in there.)

Glad to help. A little warning on the tutorial, the grammar gets pretty bad sometimes, I just haven’t had time to go back and edit it.

Reply | Quote

Hi.
Well it sure does work in your sample. But I sure can’t get it to work in my database. I’m useing Access 2000. Could there be a slight difference in the way the Code has to be written? I won”t be able to work on this again until Monday. Maybe if I send a small database with my forms and queries as written, someone will spot where I have a colon intstead of a semi-colon, or something equally novice.
Thanks again for your help.

Reply | Quote

Are you going to post your database?
Pat

Reply | Quote

Hi Pat,
I only have Access at work. I can check the board from home, but I can’t do anything in Access. I will build and send a sample database Monday.
Thanks.

Reply | Quote

Well talk about frustrated! I can’t get the sample to work as well as my database is now. I’m going to send it anyway.
Things to be aware of;
I am actually linked to a read only database that has literally thousands of account numbers. Many are irrelevant to what we are doing. We will never call for all accounts. It will always be one account or a range of accounts.

There are many more departments than I have listed.

Maybe this will give a better idea of what I am trying to do.

Reply | Quote

In your query, change the join between the tables from an equi-join to right join – all records from accounts table and only those that match from transactions. You should then get records returned.

Right click on the join line, select join properties and then choice 2 should be the one you need.

Reply | Quote

I’m replying to Paul’s post here because I cannot reply to LadyGnome’s last post.
Paul’s right if you want to show the Accounts that have no transations as well as those that have transactions.

LadyGnome, what are you trying to do?
Are you wishing to :
1. Select an Account number range (even if it is only one account) followed by
2. A Department that is in the Account number range.

If so, then include the From and To Account numbers on a form and then select the Department on the same form by a combobox (as you have now) that lists Departments that are in the Account number range you have already selected. Then have a button, as Wendell suggested, that runs the query.

Am I on the right track?

HTH
Pat

Reply | Quote

Close – close.
Yes – I want to select a range of accounts, even if the range is only one
Sometimes I will want one Department that is in the range.
Sometimes I will want all the Accounts in the range with the Departments displayed.

Right now I have a query that Has ‘Between[Type beginning account] and [Type ending account]’ in the Account column
That works well for choosing the account.
One use of the query would be to learn if a given Department has any accounts in the selected range.
I can get my Combo box to select a department. — Thank you Drew.
Nothing I have tried has gotten it to display all accounts if none is selected.

Now — why wont it work with a Combo Box as elegantly as your solution worked with the Typed in Dept number?

My work day is thankfully over. I will see you all tomorrow. I am sure.

Reply | Quote

<>
What exactly do you mean by this?

<>
Your criteria for Account should be:
Between [Type beginning account] and [Type ending account] or [Type beginning account] Is Null

<>
Again, what do you mean by this?

Pat

Reply | Quote

<>
What exactly do you mean by this.

I was trying to say that I want the query to return all the accounts in the requested range, with the Department that they are assigned to displayed in the Department number column. One of the results I’ve gotten is all of the accounts in the range, but the Department number blank.

<>
Your criteria for Account should be:
Between [Type beginning account] and [Type ending account] or [Type beginning account] Is Null

Sorry — It was time to leave and I was hurrying. I should have written
Nothing I have tried has gotten it to display all accounts if no Department is selected.
The account selection is working fine.

>
Again, what do you mean by this?

When I first started this project, I had [Type the Department number] in the criteria. It didn’t return anything if I didn’t type a number. I posted on this list, and you ( I am sure it was you) gave me the solution of adding OR Null[Type the Department number]. And it works beautifully.
Then my supervisor wanted a list of Department names to choose from. Thus the Combo Box.

I am frustratingly close to getting it to work. But it doesn’t.

Reply | Quote

Try this database and see what you think, post back after you look at it.

The query’s where clause is:
WHERE (((Accounts.[Account Number]) Between [Type Beginning Account Number] And [Type Ending Account Number]) AND ((Accounts.[Department Number])=[Forms]![Select Department]![Combo0])) OR (((Accounts.[Department Number])=[Forms]![Select Department]![Combo0]) AND (([Type Beginning Account Number]) Is Null)) OR (((Accounts.[Account Number]) Between [Type Beginning Account Number] And [Type Ending Account Number]) AND (([Forms]![Select Department]![Combo0]) Is Null)) OR ((([Type Beginning Account Number]) Is Null) AND (([Forms]![Select Department]![Combo0]) Is Null))

Pat

Reply | Quote

Thanks Pat,
I have downloaded it. Will look at it first thing in the morning.

Reply | Quote

I have been busy moving this weekend, where are you at on this? Just want to make sure you get this finished up.

Reply | Quote

Not too bad. I can get it to run correctly when I select a Department

I still get wierd results when trying to pull all departments (none selected) I just downloaded a sample from Patt that I will have to look at tomorrow.

Meanwhile — I have a second question up regarding how to get it to work in a Macro.

What?? Me greedy?

Reply | Quote

Don’t bother with macros, they are not worth it, you have far more control with VBA, and as Andy Ainscrow (I think that’ts how it’s spelt, sorry if it’s not) said it is just there for legacy reasons.
Pat

Reply | Quote

I agree with Patt. There are only two macros worth using, AutoExec and Autokeys.

Can you post a sample of what you currently have, I’d be happy to take a look. (I just don’t know how caught up the last one is.)

Reply | Quote

Hi.
I’m about to look at what Patt sent me yesterday. Maybe that will solve the ‘show all when none are selected’ problem.
My question on macros is on a thread called Pauseing a Macro. I’d appreciate your taking a look.

Reply | Quote

Well, no, I’m still not there. Here is my latest sample (Not including the Macro).
I got this to work ONCE!! I have not been able to repeat the accomplishment. When it worked, I had typed something in the ON CLICK event for Combo6. I was attempting to requery my Select-accounts-parameter-experiment Query. I don’t know if you can even do that. But something did work once.
In this DB, the Query Select-accounts-parameter, is the one that is successful for typing in all information,
Select-accounts-parameter-experiment is the one where I’ve been playing around with a Combo Box.

Before you ask, I have a Make Table Query because the ‘real’ table I work from for accounts is huge. The query takes a long time to run. Sometimes we want to get further information from the same range of accounts. It’s just faster if I have smaller table to work from at that point.

Reply | Quote

OOOPS.
Try again.

Reply | Quote

Well – here is the latest development.
I’m working with the Form that has a query which is referenced by the second box on the form.
I tried tieing my Select accounts query directly to this query rather than to the form.
It returned all accounts selected when no Department was selected. – I was so excited.
Soo – when I selected a Department – It still returned all accounts, with the Department number displayed only in the Department I selected. This is not the result I was expecting.

Well — I’m back to the slog.

Reply | Quote

I think you sent my database back to me, it doesn’t have all the queries you mention.
Pat

Reply | Quote

Here ya go. I modified the stripped down version you sent. When it opens, you’ll get your form. That form has 2 combo boxes. Department and Account. Both will initially say All. (Yes, they are query driven, but All will show up as the first and ‘default’ value every time.). When you select a Department, the Account combo will requery (and start by showing all again, but if you drop it down, you’ll find only the accounts for that department).

There is also a button, that opens qryData, which is the query you originally had, changed to handle the two combo boxes as your means of criteria. If both boxes show all, then you get ALL of the data. If only one says all, it only filters by the other (either Department or Account), if both are selected, then it shows only the selected data for that department AND that account.

I think my original sample was backwards, putting multiple departments per account, what you posted is opposite of that, multiple accounts per department.

Just an FYI, hitting the ‘Show Query’ button will show the query, but it will not ‘refresh’ the query. Either close the query, or hit Shift-F9 to requery it.

Reply | Quote

I just downloaded this. It looks promising. I’m going to try to recreate it in my working database, and combine it with Pat’s solution to my Macro question. If it all works, that should do it. I hope, I hope, I hope.

Reply | Quote

I really can’t seem to incorporate these solutions into my working database. I made a new DB for the express purpose of getting them to work together before putting them in my “real” database.
Drew — How do I select a range of accounts in your solution? IE Between this and that? The table I am linked to has literally thousands of accounts, I will never want ‘all’.
Pat — I think your Macro solution (the command box ‘Export to Excel’ will eventually be the answer. Right now, it’s coming up as a dialog box when the query runs.
Take a look.
For some reason, my server here at work won’t let me send a file. Drat!

Reply | Quote

Okay, try this sample. I have left the departments to show ‘All’ (I thought that was a neat trick), and now you have two combo boxes for the Account #’s. A start and last. The Last will refresh whenever the ‘start’ is changed, to show only account numbers lower (or equal) to the start.

Hope this helps.

Reply | Quote

This is indeed very clever. I have imported your Form and related queries into a Trial DB where I will attempt to link to my “real” tables for accounts. I have a couple of questions first.
There are 310 accounts in the Account table, but when “All” is selected, along with the entire range that is in this table, only 8 rows are displayed. Why? and how do I fix it?

Second — What is a Union Query? and how do I go about making one on my own? I don’t find any mention of it in Access Help.
Working away.

Reply | Quote

<>
You probably have an INNER join between the Accounts and Transactions table as someone had previously mentioned, you will need to make it a LEFT join.

<>
A UNION query can be described as:
SELECT a, b, c
FROM TblA
UNION
SELECT a, b, c
FROM TblB

You can have up to 49 SELECTs in a UNION query (or so was the case in A97).

Pat

Reply | Quote

Well, of course! Actually it is doing what I want, since I only need those accounts with transactions.

And– I believe this will answer both my questions. I will need another step in here, since this only selects the accounts and I will need some computations before exporting. I think I can do this is another query and just change the name in the code you so obligingly wrote for me. Stop me if I’m wrong.

Reply | Quote

My server still wont allow me to send a file.
What I did, was add Pat’s VBA to export to Excel to the Form Drew provided which selects both Department and Accounts and has the results I needed. It works quite nicely. Now on to the real world, and the extra step.

Many thanks to Pat, Drew, and all others who responded.

Reply | Quote

Not to be a bearer of bad tidings, but when I executed Drew’s form selecting only a department and no account range I found there were no records returned.

Pat

Reply | Quote

I noticed that Pat. It’s alright though, because in real life there won’t be a time when there isn’t a range, or at least one account selected.
Thanks

Reply | Quote

I just checked the sample, and only 7 of the 9 transactions showed up. I researched one of them, 31326. That Account has no department. It’s not the criteria that is keeping it out, it’s the joins with the Accounts and Departments table. Since there is no department matching that account, it doesn’t show up. Does that make sense? They are essentially ‘ghost’ transactions….until their account # is assigned to a department.

What’s a Union query???…good question. The tricky part of a union query, is that there is no ‘wizard’ for them. You have to build them completely with SQL. I don’t doubt that you didn’t find anything in the help files for them, Access 2000’s help files suck. There is info on them in Access 97’s help files (which is a far superior help file system).

What a union query is, is a query that allows you to put multiple ‘recordsets’ together.

SQL, or Structured Query Language, is really a relatively simple language to learn. (I personally use the query builder in Access 99% of the time, because it builds them really quickly, and pretty easily…..my hangup is getting the joins right when writing SQL manually.).

Let’s take two tables:

tblEmployees: FirstName LastName
Drew Wutka
Bob Smith
Jane Doe

tblCustomers: FirstName LastName
John Doe
Julie May

Okay, now let’s write a query to pull up all employees:

SQL:
Select FirstName, LastName
From tblEmployees

Put that SQL into a query (top left corner, or right clicking a querries ‘border frame’ will let you switch between Design, Datasheet and SQL view…you can copy or paste SQL directly into a query that way). When run, that query will display all three ’employees’ first name, then last name.

If we put this:
Select LastName, FirstName
From tblEmployees
Order By Lastname, FirstName

We’ll get a list which is sorted by last name first, then by first name.

If we go:
Select FirstName, LastName
From tblEmployees
Where FirstName=”Drew”

We’ll only get one record, me…Drew Wutka

Now, what a union query does, is allow you to put multiple SQL statements together, to produce one list.

Select FirstName, LastName
From tblEmployees

That gives us all of the employees.

Select FirstName, LastName
From tblCustomers

That gives us all of the customers.

What if we want a list of both employees AND customers…in the same query (or used in a form/report)

Then we can go:

Select FirstName, LastName
From tblEmployees

Union Select FirstName, LastName
From tblCustomers

Now when we run that, we will get one recordset, that has 5 names in it (3 from employees, and 2 from customers). Notice that once you use the ‘union’ statement, the Access Query builder won’t let you go into Design mode (but it does know it’s a union query, it will have a different icon in the database window).

Does that answer your question about Union Querries?

Reply | Quote

Yes, that does make sense. But again, in the real world it won’t be a problem since the system does not allow an account to be created without being assigned to a Department.

Union Queries — Does that explain them? We-e-e-el, yes– and no. It gives me the basics. I could ask a dozen more questions, but they should probably be on another thread

My mission today is to attach the true tables to this, and add the query (really multiple queries) that actually adds up the transactions and generates a total. I will probably be back.

Reply | Quote

Okay good luck.

We probably should save the Union Query discussion for another (appropriately named) thread. This one is getting LONG!

Reply | Quote

Yes!!! It is done. Attached to real tables, it does more than I was asked to do. Offers selection of accounts, selection of Department, has TWO export buttons, one for All expenses, one for a specific type of expenses.
I earned an ATTAGIRL, and I am happy to share.

Thanks again to Drew, Pat, and all others who helped.

Reply | Quote

You’re quite welcome….where’s my beer!

Glad to help….sometimes I almost depend on helping out with other people’s ‘issues’, because it gives me a breather from my own!

Reply | Quote

Hello All

I use the Union Query often. It has the feel of

Reply | Quote

Hi Matteo,

Your suggestion to add a column identifying the source table of the records is fine, but I would advice against naming it RecordSource. Probably, it won’t do any harm, but it might cause problems in some circumstances since RecordSource is a property of forms and reports. If you base a form on the union query and add the RecordSource field to the form, Access will name it just RecordSource by default. If you omit to give it a better name, and you use Me.RecordSource in VBA code, you might get unexpected results.

Reply | Quote

<>
It’s not a Macro solution, it’s aVBA solution.
What does the dialogue say?
Pat

Reply | Quote

I found that your query did work where there is no Department, however where a Department is selected from the combo box then it shows nothing at all.
Also I would put the Requery code in the AfterUpdate event.
You don’t need to set the Department to 0 as you have, since it causes the query to look for a Department when a Department has been selected and therefore you get no records returned by the query.
I have changed your database to reflect these changes.
Pat

Reply | Quote

In which row of the query grid are you referencing the combobox? It should go in the criteria row under the department code column.

Reply | Quote

It is under the Department Number, Department number is the bound column.

Thanks

Reply | Quote

Am I right in assuming that you want to select a department by name and then use the corredsponding department number to do the lookup?
If so, then you need to change the query so that you are looking at the form control (ComboBox) for the criteria, this can be done as:
Forms![your form name]!cmbDepartment.Column(1)
If the .Column(1) doesn’t work then you will need to setup an invisible control on your form that is the Department number and reference that in the query.

Pat

Reply | Quote

This looks very similar to Wendell’s original reply, which just resulted in the column being skipped. Curious, because that solution is exactly what Access help says will work. Maybe I’m just typing it wrong. Yours is a bit different, I will try it.

Dummy didn’t name her Combo Box, so it is called Combo0. The column that the Department number is in is dept-nbr.
So I’m writing this as Forms![Find Departments]!Combo0.dept-nbr.
We shall see.

Reply | Quote

[indent]

---

Forms![Find Departments]!Combo0.dept-nbr.

---

[/indent]What exactly is this supposed to represent? You can’t refer to a column of a combobox as if it were a named property of that combobox. If you want a value, you have to refer to the column number for that value, and columns are a zero-based collection. So the second column in your combobox woudl be Combo0.Column(1). If you want to make your code readable, create a constant for dept_nbr (no hyphen, please, Access doesn’t like them) and then substitute the constant for the number in the column index.

Reply | Quote

Forms![Find Departments]!Combo0.dept-nbr.

I guess this represents complete confusion. I don’t know VB. I thought I was copying Pat’s solution. Except substituting the name of the column. The one I want would be column 0 per your explanation.

I have no idea how to do this. — I have changed the name of the column to Department number.

I do not know Visual Basic. I mean I REALLY REALLY do not know it. Please be patient with me.

Thank you for your interest

Reply | Quote