Solved VBA to set USERFORM comboBox from SQL

[Marshall Brooks]

Stupid question time ...

I've been playing with Reply #34 (separate tables) and can't get it to work correctly.

From how you are explaining it, it seems like Reply #30 is preferable anyway.

How do I create the query from the SQL statement so that I can view it?

Right now, I've just been pasting the long SQL as the row source for the combo box in design mode and then switching to normal mode to see if it worked.

 

[Marshall Brooks]

Stupid question 2:

qrtSortedNames has two colums - "Full Name" and "Last Name". What do I put in the row source to tell the dropdown to use "Full Name"?

 

[MajP]

In a access combobox you have a couple of properties

Column Count: Identifies the number of columns to return from the query rowsource
if your query has 2 columns and you only need the first you can set this to 1. Does not work if you need the second column.

Column Widths: identifies the widths of the columns to show. The common trick is a 0 width column.
Assume you want to return the EmployeeID but show the Employee Name and your rowsource has two columns ID, and Name
then you can make your widths
0";1.5"
This will not show the ID and give Name 1.5 inches.

Bound Column; Number of column for the value.
In the above case if you bind column 1 (even though hidden) the combo will return the ID but show the name. This is very common

 

[MajP]

#34 (separate tables) and can't get it to work correctly.
From how you are explaining it, it seems like Reply #30 is preferable anyway.

It is not preferable but in my case can be done with less work. Do not have to create a new table just to add the extra rows. If this is used throughout the database, it may be worth the work.

How do I create the query from the SQL statement so that I can view it?

In the query Editor select SQL View. Now you can type a SQL statement. Then you can switch to Datasheet view to show your results. A Union query cannot be built in the QBE designer so it has to be typed manually.

 

[Josef P.]

[OT]

I use an auxiliary table with exactly one record for the inclusion of constant values.

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

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

This would create as many records as are contained in tblEmployees. Only by union (instead of union all) you don't see these duplicates.
Unfortunately, this is not visible in JetShoplan. In SQL Server you can see very well that first all records are retrieved and only with union the duplicate records are removed using distinct sort.

 

[MajP]

One trick to maybe make this easier. If you have two or more tables to Union you may want to first get them down to the fields you want by building two queries. If the number of fields match up then you can write a simpler union query. Assume you have two big tables with lots of fields and you create two queries with just the matching columns in the correct column order. (Lets say three columns). Then you can simply write

Select * from qryOne Union Select * from qryTwo Order by 1, 3, 2

The problem comes where you sort the first query one way and the second query a different way, and hope this order stays when combined. Likely it will not. That is why often you need to add some column to each query that will allow you to sort the entire union properly.

 

[Marshall Brooks]

Sorry - more questions ...

Reply #43 - I have Column 1 selected, so I should be good. I didn't follow the part about binding column 1 even though hidden, if setting the width to 0 does the same thing, I'm not seeing why this step is required.

It is not preferable but in my case can be done with less work. Do not have to create a new table just to add the extra rows. If this is used throughout the database, it may be worth the work.

That is what I meant. I'd prefer not to add an additional table that I have to keep up with. I think there are possible 7 drop-downs in the table that use drop-downs with additional info besides the employee names. I think some of them only have Unassigned and not Unassigned and SPO - but if I can get one of them working, I (hopefully) will be able to fix the others myself.

In the query Editor select SQL View. Now you can type a SQL statement. Then you can switch to Datasheet view to show your results.

Found that - create Query Design and then you have a toggle between SQL View and just View.

A Union query cannot be built in the QBE designer so it has to be typed manually.

Not following ... I'm copying and pasting into the SQL statement in the SQL View. Don't I just copy and paste the Union statement in there also? Or if not, how do I create it? Or are you saying I can't create it and the union query part has to be done in the combobox rowsource field?

I use an auxiliary table with exactly one record for the inclusion of constant values.

This code looks the same as what @MajP posted, except you are pulling the data from tabwith1record. Not sure how this is simpler/easier than what @MajP posted - seems like you have to update the table and then update the SQL Query also. Duplicate work ...

 

[Marshall Brooks]

Progress!!! - Reply #34 (Separate Tables is working) - Trying to do Reply #30 now. You can copy/paste UNION statements in the SQL view field.

 

[Marshall Brooks]

Reply #30 works!!! Thank you SO much!!! Sorry it took me so long to catch on!!!

 

[MajP]

1 even though hidden, if setting the width to 0 does the same thing, I'm not seeing why this step is required.

Assume I have a rowsource with 2 fields ID, Name and Column Count of 2.
Regardless if one is hidden or not. if the Bound column is 1 then the value returned by the combo is the ID if the Bound Column is 2 then the value of the combo is the Name.
This is especially important if you are storing this value to a field.
If you simply have a column count of 1 then the bound column will default to 1.

I'm copying and pasting into the SQL statement in the SQL View. Don't I just copy and paste the Union statement in there also

I am speaking about the QBE (Query by Example). That is the graphic view that allows you to drop tables and select fields. In the SQL view you have to type it out. In many queries the SQL can be shown in QBE and you can toggle between QBE view and SQL view. There is no way to graphically show a Union query so you have to work in SQL view.

his code looks the same as what @MajP posted, except you are pulling the data from tabwith1record. Not sure how this is simpler/easier than what @MajP posted - seems like you have to update the table and then update the SQL Query also.

This is a fine point, and may be more confusing at this time. But what @Josef P. says is correct and more efficient. You will have to understand what a UNION and UNION ALL does.

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

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

What I demoed worked without adding a new table, but only because it filters out duplicates.

 

[Marshall Brooks]

What I demoed worked without adding a new table, but only because it filters out duplicates.

I saw the difference between Union and UnionAll, but not sure how it affects anything in this example - Well - actually, there might be a potential problem ...
We aren't going to hire anyone named Unassigned or SPO.

I checked and if I have "Joe Smith" and "John Smith", it still works and sorts on last name and then first name - Not sure how it decided that, but it is what I would want.

 

[Marshall Brooks]

Somewhat weird, but I'm not going to worry about it.

I had another drop-down where I just needed "Unassigned" added, not "SPO".

I changed your query to

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

Looked fine in Datasheet view in the Query Editor. Saved it and added it as the rowsource for the combobox. Went from design view to form view and I had "Unassigned", "SPO", "Names ..." in the drop-down.

Closed and re-opened the database and SPO is gone as it should be ...

 

[Pat Hartman]

USER FORM

User forms are not part of Access and they work differently. You might want to use a forum where they specialize in userforms. Of course those people are not likely to know anything about Access.

Using a normal Access data form correctly would have made this a trivial problem. Use the tool you have in the way it was intended to work. When you cobble on outside objects, you just make work for yourself. Is this User form actually better than a standard Access form? What does it do that an Access form does not do?

 

[Marshall Brooks]

@Pat Hartman - See Replies #13, #17, and #18.

@MajP knew exactly what I was talking about and got me working. Somewhat agreed, UserForms are not part of Access, and they work differently, and they often work differently in Access than they do in Excel or Word.

I likely got much better assistance here than I would have posting the question on an Excel forum - although I did link to replies on StackOverflow that pointed me in the correct direction.

The userform in question was a replacement for the InputBox control. It was somewhat inspired by Enhanced Message Box - https://datenbank-projekt.de/projekte/improved-enhanced-message-box-ms-access - but he DOES use a standard Access Form.

What it does that the standard input box doesn't do:
I have it set to automatically scale to fit any text that I put on the box.
I can have up to three combo boxes on it, and can show or hide any of them.
Each combobox can be configured as either a blank input box or a drop-down, and I can choose whether the drop down is mandatory, or whether other values can be manually entered.
I haven't tested it, but it likely would work the same way in Excel or Word.

Other than the last statement - yes, I probably could have done the same thing with a standard Access pop-up data form.

The short answer is that I have this userform called probably 100 or so different times in my database. It was much simpler to modify 6 of the instances to read the drop-down values from a table, than is would have been to create a standard Access data form that looked and worked identically to my userform and modify 100 callouts to call out the new form instead of the old one, and verify it worked correctly in each instance - just to make this one case able to use a query, than it was to modify the userform to work.

 

[Pat Hartman]

@MajP went the extra mile. He is a great resource and was very patient. You are lucky he decided to help you.

While it is true, the UserForm would be far better than an Input Box, I disagree totally that you couldn't have achieved the same thing more easily with a standard Access unbound form. And if you were using the UserForm in a hundred places, and wanted to substitute the Access form, you could have "retired" the UserForm and reused the name for your Access form. However, if the code to open a UserForm is different from the code to open an Access form, then you would have had to make a global code change which is generally one change using MZ tools and not 100.

But as long as you're happy we're happy and kudos to MajP for figuring out how to make the UserForm work.

 

[Marshall Brooks]

@MajP went the extra mile. He is a great resource and was very patient. You are lucky he decided to help you.

I couldn't agree more!!!

I disagree totally that you couldn't have achieved the same thing more easily with a standard Access unbound form.

I never claimed that I couldn't do so. @Maj P said that in this instance, it was what I should be using and would be simpler. In fact, my first attempt at solving this was to try to create a standard Access unbound form and I got fairly far with it. (My idea at the time would have been to use the standard form in these 7 instances and the user form everywhere else and gradually migrate the standard form to the other locations.) I'm not too humble to admit that I don't know how to do half the things with a standard form that I do all the time with the user form, but I never said they can't be done. Enhanced Message Box (EMB) does most of them.

However, if the code to open a UserForm is different from the code to open an Access form, then you would have had to make a global code change which is generally one change using MZ tools and not 100.

As I said, I don't know how to do this in Access, but I think you are greatly oversimplifying this. Here is ONE of the calls to my UserForm. (This userform is fairly simplistic. I have other ones with multiple comboboxes that I show or hide and adjust the form size at callup as required):

    With InputBox1
        .caption = "Short Title:"
        .TextBox1.value = "Enter/Edit Brief Subject for the E-mail Subject Line. (30 Character Limit.)"
        .TextBox1.Font.Size = 10
        .Height = 105
        .TextBox1.Height = 40
        .TextBox2.Height = 40
        .ComboBox1.Top = 50
        .ComboBox1.MaxLength = 30
        .ComboBox1.value = "Brief Subject"
        .ComboBox1.ShowDropButtonWhen = fmShowDropButtonWhenNever
        .Show
    End With

You know how to simply open a data form, but that would be equivalent of using only .Show above:

DoCmd.OpenForm "InputBox1", acNormal, "", "", , acNormal

Which I think @isladogs said could be just:

DoCmd.OpenForm "InputBox1", acNormal

OTOH, EMB uses an entire module to calculate the sizing dimensions and a separate form to generate the callout for the code, and a simple call to open it looks like this:

strResult = Dialog.Box(Prompt:= "Do you want to enter the E-mail Subject Line?" & "", Buttons:=(3+64))

Also - even if the code were similar, which I don't think it is, I think UserForms use Twips and Access data forms use inches, so at a minimum there would be a conversion function and I would have to change ".Height = 105" to ".Height = TwipsToInches(105)" - and verify that that gave desired results - which I don't think MzTools would allow me to do with one change.

But as long as you're happy we're happy and kudos to MajP for figuring out how to make the UserForm work.

I'm ecstatic and extremely grateful!!!

 

[MajP]

Maj P said that in this instance, it was what I should be using and would be simpler.

Just to be clear the above sentence should be interpreted as I suggested initially to use an Access Dataform not a USERFORM, until the complexity was explained.

Are you sticking with the USERFORM or switching to a data form? I think you can do this easier with a dataform.

However, making the combobox on UserForm work was trivial since it is exactly the same as method in Access when applying the Additem method. The answer was provided in thread #11 and the vast majority of the thread was about Union queries on a standard data form and unrelated to the USERFORM.

@Marshall Brooks could have switched out to a dataform, but since the UserForm solution was simple it would have been far more work to create the dataform.

UserForms use Twips and Access data forms use inches, so at a minimum there would be a conversion function and I would have to change ".Height = 105" to ".Height = TwipsToInches(105)"

Actually when writing vba code in Access it is also in TWIPS. However there are some properties you can specify in inches or centimeters based on regional settings. These are usually passed as strings.

 

[Marshall Brooks]

Actually when writing vba code in Access it is also in TWIPS. However there are some properties you can specify in inches or centimeters based on regional settings. These are usually passed as strings.

I wasn't aware of this. I've typically worked on both userforms and data forms in design view and there it is twips in userforms and inches in dataforms. I do know there is some complexity with centering my userforms that requires converting twips to pixels, but that's a different issue.

 

[MajP]

This may interest you in the future.

Automatic Form Resizing 1

Use automatic form resizing (AFR) to ensure that forms and their controls are automatically resized and repositioned for any screen size & resolution. It can also be used to add a zoom feature to Access forms

www.isladogs.co.uk

Besides the form resizing there is a lot of code that can be used for positioning and converting units.

 

[Marshall Brooks]

Thanks - I have seen that.

Super impressive code - but it caused me a bunch of errors b/c I didn't use it properly.

i.e. the code is designed to work off a template and then the template is scaled to whatever resolution the user is using. I somewhat started that way and then I needed more space so I added dimensions to the template and it threw all the scaling off. @isladogs was nice enough to point me to resizing options that got me back on track.

Personally - at least in an office environment - I think this idea would have been SUPER useful 10-years ago when people could be running anything from 640x480 to 1650x1080. Today, most people (at least in my group) are running either 1920x1080 or 1920x1200 or if they happen to be running 4K on a small screen, they use DPI scaling to make it readable, so I have the luxury of designing for 1920x1080 and calling everything good.