Forum Discussion
Set default value in ComboBox from a SQL query
Hello everybody!
I'm creating a DB and I have a problem with a form. In one of the ComboBoxes I use, I would like to put as default value the result of a SQL query, the problem is that it does not work because it does not detect it as a function.
Is there any other function that allows this? is it possible to do it through VBA?
Thanks in advance to all!
Okay, then, I was not treating the default as a string, which it needs to be.
Try this, using DLookup and the string delimiters.
Private Sub Form_Load()Dim strUserName As String
strUserName = DLookup("name", "users", "username = " & fncUser() )
Me.YourComboBoxNameGoesHere.DefaultValue = """" & strUserName & """"
End Sub
markarel Unfortunately, that problem description is too vague to base a suggestion on.
You mention the default value of a combo box, and that it should be the result of a SQL query. Queries can return multiple fields and multiple records, so exactly which one do you want to use in the combo box?
But then, "... it does not detect it as a function." What does that mean? What does the first "it" refer to? What does the second "it" refer to?
And exactly how are you trying to set the default value of the combo box? In VBA? As an expression in the control's default property?
Hi George_Hepworth , first of all thank you for your quick response.
I have a table that stores personal information. One of the fields is "User_reg" which is the user registered in Windows, another is the user's name. For example, a record can be ("Name and lastname", "username", ".....").
I have a query that returns a single value, which is the name of the person logged in Windows, the query being:
SELECT name FROM users Where username = fncUser().
I have developed a function that returns the registered username in Windows, and that is the one I use in the query:
Public Function fncUser() As String
fncUser = Environ("UserName")
End FunctionThe problem is that I have a form where I would like to automatically detect the logged in user and show his name in a Combobox as the default value.
If you have any questions about my project, please do not hesitate to contact me.
(sorry for my level of English, I hope everything is understood.)
Thank you. That's clear.
You can set the Default Value of a control in a couple of ways, either in VBA or in the property sheet.
This works using your function.
Note that the combo box in my example doesn't have a row source. I would anticipate that the row source in your combo box would be a list of potential users' usernames.
