Solved VBA to set USERFORM comboBox from SQL (1 Viewer)

[Marshall Brooks]

Okay - obfuscating, but let's say I have three employee's in my group: Marshall Brooks, Tom Baker, and Joe Smith.

I have these people in a table named tblEmployees in a field called Names.

I have a combobox on a data form and I was using RowSourceType = Value List. and RowSource = "Marshall Brooks"; "Tom Baker"; "Joe Smith"

I changed this to RowSourceType = Table/Query and RowSourceType =
SELECT tblEmployees.Names FROM tblEmployees ORDER BY
Trim$(Mid$(tblEmployees.Names, InStr(tblEmployees.Names, " ") + 1))

Which sorts by last name (I don't include middle names).

Works fine.

Elsewhere, I have a USER FORM (not data form), that simulates an inputbox and has a combobox on it. For this, I was using:

            With InputBox1
                .caption = "Select Employee:"
                .TextBox1.value = "Please select the employee's name:"
                .TextBox1.Font.Size = 10
                .Height = 105
                .TextBox1.Height = 40
                .TextBox2.Height = 40
                .ComboBox1.Top = 50
                .ComboBox1.Style = fmStyleDropDownList
                .ComboBox1.value = "Select Name"
                .ComboBox1.AddItem "Marshall Brooks"
                .ComboBox1.AddItem "Tom Baker"
                .ComboBox1.AddItem "Joe Smith"
                ' Remove focus from the ComboBox so that the default value is NOT highlighted when the box opens.'
               .OkButton.SetFocus
               .Show
            End With

Works fine.

I tried changing the comboBox1 values to:

                .ComboBox1.Top = 50
                .ComboBox1.Style = fmStyleDropDownList
                .ComboBox1.value = "Select Verifier"
                .ComboBox1.RowSourceType = "Table/Query"
                .ComboBox1.RowSource = "SELECT tblEmployees.Names FROM tblEmployees"

With the ORDERBY clause it gave me a syntax error. With the above, I don't get a syntax error, but the user form never pops up either.

I think I could fix it with a form as opposed to a user form, but I already have the userform working elsewhere in the database.

Any ideas how to make it work?

 

[Ranman256]

there is no reason to do this in code,
in form design, set the properties on the combo, and assign the source query

 

[Marshall Brooks]

@Ranman256 - The userform simulates an InputBox. I use it elsewhere so I need to set the values in code (or else I need to create a different userform every time I want a different input question.)

Okay, I'm somewhat between approaches ...

According to this: I need to use the .AddItem method and loop through the table/Query. That code uses the record set and I'm not sure how to adapt it to an Access Query.

Otherwise, I created a blank form with a comboBox on it and that works, but I couldn't see how to get rid of "Record 1 of 1" at the bottom so that it looks like an input box.

I'd prefer the first approach.

 

[MajP]

I do not really understand why you need a User Form instead of an Access Form. Should able to get the same effect in Access.

Otherwise, I created a blank form with a comboBox on it and that works, but I couldn't see how to get rid of "Record 1 of 1" at the bottom so that it looks like an input box

You want to set Navigation Buttons, and Record Selectors to "No" in the format tab.

 

[Marshall Brooks]

I saved the output as a query - so I have a simple Access query with the names that I want in the combo box.

Could someone explain how to add them to the combobox on the user form.

The code would be something like:
Open qryEmployeeNames
For i = 1 to qryEmployeeNames.RecordCount
ComboBox1.AddItem qryEmployeeName(i)
Next i

@MajP - setting Navigation buttons to No was what I was looking for. Also - in the user form, it opens with "Select Employee" and I use that as a test that something was actually chosen. I couldn't see how to do that with the Access form. Default value was ignored. Otherwise, I probably could make it work with an Access Form (probably should have done that initially), but it means re-creating a userform that I use in a lot of other places without error.

 

[MajP]

Could someone explain how to add them to the combobox on the user form

An Access Combobox can be provided a rowsource that is a query. This then requires no code. No need to use the additem method.

 

[Marshall Brooks]

But a combobox on an Access USERFORM apparently cannot ...

 

[MajP]

But a combobox on an Access USERFORM apparently cannot ..

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

 

[Marshall Brooks]

I'd prefer to stick with the USERFORM. I just need to figure out how to get the info from the query into the combobox.

 

[Gasman]

Use a recordset?
Are you not able to set rowsource to a query name?

 

[MajP]

This is how you use the Additem method with either a UserForm or Dataform

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

 

[MajP]

With that said, the USERFORM does have a row source property, but I could not figure out the syntax to get it to work.

I'd prefer to stick with the USERFORM.

Why? You are struggling already, and now have chosen a more complicated solution to do a trivial task. Guarantee whatever you want to do with the USERFORM and be done with an Access form.

 

[Marshall Brooks]

Reply #11 works!!!! Thank you SO MUCH!!!

With that said, the USERFORM does have a row source property, but I could not figure out the syntax to get it to work.

Saw that and tried it. I got it to compile, but never got the form to open.

Why? You are struggling already, and now have chosen a more complicated solution to do a trivial task.

No, not really. I already have the user form working. It is used probably 100 places in the database, it adjusts size based on the length of the question, it uses a drop-down in some cases, it uses a simple text input in other cases.

In about 8 places, I was using it with hard-coded names and now I just needed to code it to use a query - so adding four lines of code in about 8 places, vs. creating a new form to use throughout the database.

Now, in 20/20 hindsight, what I should have done was create an Access form rather than an Access UserForm when I created the input box, but ...

 

[Marshall Brooks]

Found something else about it.

I have the combobox open with "Select Employee" and then the drop-down list.

.Combobox1.value = "Select Employee" must come BEFORE all the add item steps. Otherwise the userform doesn't open - no error message, but it doesn't open.

 

[MajP]

That is some unrelated issue. It will work fine before or after.

Private Sub UserForm_Activate()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("select lastname & ', ' & firstname as FullName from tblStudent order by LastName, firstname")
 'Works either before or after adding employees
 ' Me.cmboNames.Value = "--- Select Employee ---"
  Do While Not rs.EOF
    Me.cmboNames.AddItem rs!FullName
    rs.MoveNext
  Loop
  'Me.cmboNames.Value = "--- Select Employee ---"
End Sub

 

[Marshall Brooks]

Interesting - well, it works for me with the .value statement before it - thanks to your help.

Again, I appreciate it!!!

 

[MajP]

Access UserForm when I created the input box,

That is an issue because it is not an "Access USERFORM" it is an MSFORMS Userform. So it is not tailored for Access, really designed for Excel, Word, and other Office Apps. The only reason in my opinion to use a USERFORM is if you plan to add and delete controls at runtime (not hide and show). Then you need to. Besides that there is no benefit and there are drawbacks as you can see.

 

[Marshall Brooks]

Concur. Hindsight is 20/20. My background was Word and Excel. I was using userforms in them and found out they (somewhat) work in Access also.

 

[MajP]

This demos adding controls at runtime to a USERFORM. This cannot be done in Access. This is the case for using USERFORMS within Access.

Excel, Access: An MSForms (all VBA) treeview

jkp-ads.com: Treeview control

jkp-ads.com

I have demoed this with large trees of 10k nodes.

 

[Marshall Brooks]

Different issue - back on the data form. I have a combobox where I have the value list as "Unassigned"; "Marshall Brooks"; "Tom Baker"; "Joe Smith"

Is there an easy way to set this to "Unassigned" & qryEmployeeNames ?