Mar 23 2022 05:40 AM
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!
Mar 23 2022 06:09 AM - edited Mar 23 2022 06:09 AM
@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?
Mar 23 2022 07:01 AM
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 Function
The 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.)
Mar 23 2022 07:10 AM
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.
Mar 23 2022 07:28 AM
If, as you say, the combo box already has a list of users to select, what I would like to do is that depending on the user registered in Windows, it automatically selects the user's name.
In this list you select the name and surname of the user, if I put as default value "=fncUser()" it would write me the Windows user name, and what I need is that according to the registered Windows user name, it puts its name and surname.
Or what is the same, the result of this query:
SELECT name FROM users Where username = fncUser().
Mar 23 2022 07:40 AM
Ah, sorry I missed that you wanted different values than the username.
I was going to suggest a different approach, but I'm getting the same error you reported!
I have to do some additional testing to see what I'm missing.
Mar 23 2022 07:44 AM
Mar 23 2022 07:52 AM
Solution
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
Mar 23 2022 08:00 AM
Thank you very much for your solution, but I get the following erro executing the form:
Table name: fichaje
User name/lastname: nombre
Windows user: usuario_registrado
what could be wrong?
Mar 23 2022 09:25 AM
@George Hepwor
If I put the user directly, the code works correctly, it seems that what is wrong is this part.
This works, but putting the user manually:
Maybe this will help to solve the problem...
Mar 24 2022 09:45 AM
I apologize for the delay in responding. This forum is uniquely bad at showing new posts on existing threads.
Yes, you need to qualify the call to the function with text delimiters, as shown here.
Private Sub Form_Load()
Dim strUserName As String
strUserName = DLookup("nombre", "fichaje", "usuario_registrado= '" & fncUser & "'")
Me.nombre_caja.DefaultValue = """" & strUserName & """"
End Sub
Mar 27 2022 12:10 AM - edited Mar 27 2022 12:11 AM
there is no need to Use Default value:
private sub form_load()
Me!nombre_caja = Dlookup("name", "yourQueryThatReturnsUserName")
end sub
Mar 23 2022 07:52 AM
Solution
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