Solved VBA to set USERFORM comboBox from SQL

[Marshall Brooks]

Alternatively, is there a way to create a different query which would have the names from the employee table and these values (and update when the table is updated?)

 

[MajP]

You need to build a UNION query, but you need to add a second field so you can Sort Unassigned to the top or add a character to bring to the top
Set rs = CurrentDb.OpenRecordset("select LastName & ', ' & firstName as FullName from tblStudent UNION Select '-- Unassigned -- ' from tblStudent order by 1")

You can add those other names to another table and Union those too.
Unassigned

Union
Marshall Brooks
Tom Baker
Joe Smith

Union
Employee

 

[MajP]

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Marshall Brooks]

Thanks - will test tomorrow. I have the query sorted by last name, can I union the new table in front of the existing query?

 

[MajP]

can I union the new table in front of the existing query?

You can union many tables and queries as long as the number of columns and datatypes match.

 

[Gasman]

Union query?
Everything after post 21 was not visible to me, until after I posted.

 

[Marshall Brooks]

@Gasman - The thread went to 2 pages. Reply #22 is the top of Page 2 for me.

 

[Gasman]

@Gasman - The thread went to 2 pages. Reply #22 is the top of Page 2 for me.

Well, I had the right idea.

 

[Marshall Brooks]

@MajP

More questions ...

First off, you have the formats incorrect in your example - I don't have a first name field and a last name field, I have a name field and the sort order sorts by last name.

Second, the W3 Schools says I can join SQL statements not queries, and says both statements have to have the same sort order.

Third - I used "Unassigned" for the discussion, but I really want "Unassigned" and "SPO" at the top of the drop down in that order.

This what I have:

A table that I'll call TblEmployees with several fields - one of which is "Names" and contains "Marshall Brooks", "Tom Baker", "Joe Smith", etc.

A query called qryEmployeeNames with the Names column sorted by last name.

The SQL for qryEmployeeNames would be:
SELECT tblEmployees.Names FROM tblEmployees ORDER BY
Trim$(Mid$(tblEmployees.Names, InStr(tblEmployees.Name, " ") + 1))

I'm going to make a new table called tblEmployeesOther with a field Names and values of "Unassigned" and "SPO"

Can I make a qryEmployeesOther and sort that descending and then join the two queries (And not sort them together)?

Questions since I haven't tested this ...

Can the row source field (on the Access Form, not a user form) be:
qryEmployeesOther UNION qryEmployees

Or can it be
Select statement for tbl Employees other UNION qryEmployees

Or does it need to be W3Schools format, which would be:
SELECT tblEmployees.Names FROM tblEmployees UNION SELECT tblEmployeesOther.Names FROM tblEmployeesOther ORDER BY
Trim$(Mid$(tblEmployees.Names, InStr(tblEmployees.Names, " ") + 1))

By an odd quirk - I am sorting after the space and "Unassigned" and "SPO" don't have a " ", so Instr(.Names, " ") returns 0 so I think the above will give me:
SPO
Unassigned
Tom Baker ...

To get the order correct, I think I can do something like (not sure how Numbers/Letters/Special Characters are sorted:
In the Other table:
Unassigned AAAAAAAAAAAAAAAAAAAA
SPO AAAAAAAAAAAAAAAAAAAB

Or simpler:
Unassigned 1
SPO 2
if it sorts numeral-alpha

And then my statement probably needs to be:
SELECT tblEmployees.Names FROM tblEmployees UNION SELECT Trim$(Left(tblEmployeesOther.Names, InStr(tblEmployees.Names, " "))) FROM tblEmployeesOther ORDER BY
Trim$(Mid$(tblEmployees.Names, InStr(tblEmployees.Names, " ") + 1))

To summarize:
Can I union two queries (with different sort orders), or does it have to be two SQL Statements, or does it have to be two SQL statements with the same sort order?

 

[MajP]

You can create a second fake column. No second table needed
tblEmployees tblEmployees

SELECT tblEmployees.FullName, Trim(Mid([tblEmployees].[FullName],InStr([tblEmployees].[FullName]," ")+1)) AS LastName
FROM tblEmployees
UNION
SELECT "Unassigned", "1"
FROM
tblEmployees
Union
SELECT
"SPO", "2"
From
tblEmployees
Order by 2,1

qrySortedNames qrySortedNames

FullName	LastName
Unassigned	1
SPO	2
Steve Anderson	Anderson
Tom Baker	Baker
Marshall Brooks	Brooks
Joe Smith	Smith

Modify your code to use the new query

Private Sub UserForm_Activate()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("Select FullName from qrySortedNames order by lastName")
 
  Me.cmboNames.Value = "--- Select Employee ---"
  Do While Not rs.EOF
    Me.cmboNames.AddItem rs!FullName
    rs.MoveNext
  Loop
End Sub

 

[Marshall Brooks]

@MajP - Not quite fully following you ...

First off, "Modify your code to use the new query" - is written if this were for the userform. This is for a combobox on an Access dataform. If I were updating the userform, I think I could just put the new names at the front - i.e.

Private Sub UserForm_Activate()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("select lastname & ', ' & firstname as FullName from tblStudent order by LastName, firstname")
  Me.cmboName.AddItem "Unassigned"
  Me.CmboName.AddItem "SPO"
  Do While Not rs.EOF
    Me.cmboNames.AddItem rs!FullName
    rs.MoveNext
  Loop
End Sub

Actually, that code MIGHT work on the Access data form, but I'm not sure where I would put it - i.e. for the combobox's On Enter event, maybe?

Secondly - if you just mean to add essentially a Last Name column to the existing table, I don't want to do that. The table is a linked table in the back end, that gets copied into a cleared local table with the same name at startup, so if I add columns to it, I need to update the back end and distribute the new front end at the same time as the back end is updated, as the current FE would generate errors with the new BE, and vice versa.

If your query somehow creates it's own second column in the query itself, that is pretty cool.

I'd prefer to use separate tables.

Finally - I did a bit of testing. I don't seem to be able to union queries. I'm having a problem with the Union statement, but I didn't make the column names the same in the new table - I think it will work if I change that.

 

[MajP]

First off, "Modify your code to use the new query" - is written if this were for the userform. This is for a combobox on an Access dataform.

Now I am getting confused. We are back to using an Access data form not a User Form?

If that is the case, the query "qrySortedNames", produces the results shown below. This query can simply be the rowsource of a combobox in a data form. No code is required.
qrySortedNames qrySortedNames

FullName
Unassigned
SPO
Steve Anderson
Tom Baker
Marshall Brooks
Joe Smith

If your query somehow creates it's own second column in the query itself, that is pretty cool

Kind of confused again. Did you not look at the query and the results?
The query clearly creates a second calculated column called "LastName" taken from the FullName column. The unions stick either a 1 or 2 in this column to get the correct order.

 

[Marshall Brooks]

I changed the column names ...

SELECT tblEmployees.Names FROM tblEmployees ORDER BY
Trim$(Mid$(tblEmployees.Names, InStr(tblEmployees.Names, " ") + 1))

Works but doesn't give me the "Unassigned" and "SPO" values.

Right now, I'm just trying to add them and then I will worry about sorting.

Both

SELECT tblEmployees.Names FROM tblEmployees UNION SELECT tblEmployeesOther.Names FROM tblEmployeesOther ORDER BY
Trim$(Mid$(tblEmployees.Names, InStr(tblEmployees.Names, " ") + 1))

And

SELECT tblEmployees.Names FROM tblEmployees UNION SELECT tblEmployeesOther.Names FROM tblEmployeesOther ORDER BY
Trim$(Mid$(Names, InStr(Names, " ") + 1))

give me an error "The ORDER BY expression Trim$(Mid$(Names, InStr(Names, " ") + 1)) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.

Will reply to Reply #32 separately.

 

[MajP]

I'd prefer to use separate tables

If you want to store Unassigned and SPO in a separate table then to make this easier in that table
tblChoices
-- ChoiceDescription
-- ChoiceSort

Then your values in that table are
Unassigned 1
SPO 2

Now you can union them

SELECT tblEmployees.FullName, Trim(Mid([tblEmployees].[FullName],InStr([tblEmployees].[FullName]," ")+1)) AS LastName
FROM tblEmployees
UNION
SELECT ChoiceDescription, ChoiceSort
FROM
tblChoices
Order by 2,1

 

[MajP]

Works but doesn't give me the "Unassigned" and "SPO" values

Are you following anything I am saying? Sorry, but I do not know how to be any more clear. Need to read what I am writing. What I am saying and what you are doing seems to be 180 out.

 

[Marshall Brooks]

Now I am getting confused. We are back to using an Access data form not a User Form?

Correct - I said that in Reply #20, but I think you missed it. I have an Access data form with three comboboxes. Two of the boxes just use the employees names. That was working properly, so I didn't post it here. The UserForm combobox didn't work with the query, so you got that working for me. One of the data form combo boxes needs "Unassigned" and "SPO" added at the top and that is what I am working on now.

Kind of confused again. Did you not look at the query and the results?
The query clearly creates a second calculated column called "LastName" taken from the FullName column. The unions stick either a 1 or 2 in this column to get the correct order.

I looked at the query and results. I haven't tested them, and I don't follow exactly how it is working. i.e. The query creates a second calculated column IN THE QUERY would work great and would be simple. The query creates a second calculated column IN THE TABLE will probably create issues when I re-open the FE, but perhaps not.

Are you following anything I am saying? Sorry, but I do not know how to be any more clear. Need to read what I am writing. What I am saying and what you are doing seems to be 180 out.

Yes, I'm following - so far, I've tested Reply #22 and couldn't get it to work, leading to Reply #33.

Reply #30 looks promising - if it won't modify the table structure. I'm wanting to try that next.

Reply #34 looks promising if Reply #30 changes the table structure.

I'm going to wait to hear back from you before I do anything with either #30 or #34.

 

[MajP]

The query creates a second calculated column IN THE TABLE will probably create issues when I re-open the FE

No it creates it in the query not in a table. This is a fundamental of sql queries. You can create a column by concatenating other fields, using a literal, performing functions (min, max, sum, avg etc, iif), etc.

If I remove the Union then the first select is

SELECT tblEmployees.FullName, Trim(Mid([tblEmployees].[FullName],InStr([tblEmployees].[FullName]," ")+1)) AS LastName
FROM tblEmployees

That creates a second Column called "LastName" calculated by:
Trim(Mid([tblEmployees].[FullName],InStr([tblEmployees].[FullName]," ")+1))

 

[MajP]

I'm going to make a new table called tblEmployeesOther with a field Names and values of "Unassigned" and "SPO"

In my example I called this tblChoices because EmployeesOther sounded like a poor name unless there are other values in there.

 

[Marshall Brooks]

Okay, what I didn't quite follow was:
SELECT tblEmployees.FullName, Trim(Mid([tblEmployees].[FullName],InStr([tblEmployees].[FullName]," ")+1)) AS LastName
FROM tblEmployees
UNION
SELECT "Unassigned", "1"
FROM
tblEmployees
Union
SELECT
"SPO", "2"
From
tblEmployees
Order by 2,1

So to use this method, does it just work somehow, or do I need to add records to tblEmployees with SPO and Unassigned in the Names field, or do I need to add fields to the tblEmployees with Unassigned and SPO.

Adding records can be done without errors - well - if I do that, my other comboboxes and my userform will have Unassigned and SPO added as well, which I don't want to have happen.

 

[MajP]

In a query you can make columns using literals (hard coded values).
So this query by itself

SELECT "Unassigned", "1" FROM tblEmployees

would create a query with one row (eventhough no fields are choosen)
Unassigned 1

Adding records can be done without errors - well - if I do that, my other comboboxes and my userform will have Unassigned and SPO added as well, which I don't want to have happen.

There are no records Added anywhere. In the query it creates columns and rows, but these do not reside in any table. Records are stored in tables.

So this query is the rowsource only for a specific combobox, not for all your comboboxes.

So to use this method, does it just work somehow, or do I need to add records to tblEmployees with SPO and Unassigned in the Names field

I do not know how to prove it any better. I post real results from the query and clearly it does as described.