Set default value in ComboBox from a SQL query

[markarel]

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!

[#2]

@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? 

[#3]

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.)

[#4]

@markarel 

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.

[#5]

@George Hepworth 

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().

[#6]

@markarel 

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. 

[#7]

Okay, thank you very much, I look forward your answer

[#8 · Accepted solution]

@George Hepworth 

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

[#9]

@George Hepworth 

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?

[#10]

@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...

[#11]

@markarel 

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

[#12]

there is no need to Use Default value:

private sub form_load()
Me!nombre_caja = Dlookup("name", "yourQueryThatReturnsUserName")
end sub