General Question

manuel_alarcon's avatar

How to get the value from a listbox populated by a query in access 2007?

Asked by manuel_alarcon (299points) March 14th, 2010

Hi, i have a listbox in a form populated with
SELECT DISTINCT tabla_proyecto.id_proyecto FROM tabla_proyecto, tabla_muestra WHERE[Forms]![selecciona_proyecto]![txt_id];
This is a selection made in the welcome form that opens another more specific form; and it works cool. But now I put a button that triggers a query that creates a new record in a table, adding code to the button like so:

Private Sub button_Click()
DoCmd.SetWarnings False

DoCmd.RunSQL “INSERT INTO tabla_analisis (id_proyecto, id_muestra, analisis, [metodos de analisis], [resultados del analisis elemental], [resultados del analisis], laboratorio) VALUES ( ’” & _
Forms!crea_analisis.id_proyecto.Value & ”’, ’” & _ <—this is a listbox
Forms!crea_analisis.id_muestra.Value & ”’, ’” & _ <—this is a listbox
Forms!crea_analisis.analisis & ”’, ’” & _ <—this is a textbox
Forms!crea_analisis.metodos & ”’, ’” & _ <—textbox
Forms!crea_analisis.elemental & ”’, ’” & _ <—textbox
Forms!crea_analisis.resultados_analisis & ”’, ’” & _ <—textbox
Forms!crea_analisis.laboratorio & ”’ );”

MsgBox “Nuevo Analisis ” & Me!id_muestra.Value & ” creado”


End Sub

The problem is that I cant get the value of the listboxes, Im trying different sintax, like


but doesnt seem to work. what am I doing wrong here?

Observing members: 0 Composing members: 0

4 Answers

jaytkay's avatar

Can the code be placed in the form with the list boxes?
Then you could refer to them without the qualifiers, like this

manuel_alarcon's avatar

you mean, in the main form, like “On Current”? Im sorry, im not an expert… i mean, access is a little strange for me.
I’ve put code on buttons, via the macro builder, for opening forms.
But the code for this is in the main form window…. Look, im trying a simpler approach: the button pops a message box with the listbox value. but im not getting nothing, just a blank space between my strings…. (also with the whole window code here)

Option Compare Database
Private Sub Form_Load()
Me.Move _
Left:=100, Top:=100
End Sub

Private Sub creanalisis_Click()
MsgBox “the value is ” & id_proyecto.Value & ” for the listbox”
End Sub

jaytkay's avatar

You probably tried this already, but is there a selection in the list box? When it first opens, nothing is selected, and you would get blank space between strings.

manuel_alarcon's avatar

Now that you say it, I’m thinking that I used the Listbox control to display the current selection; I populate it with a sql query with a value from the another open form by writing in its row source:

SELECT DISTINCT tabla_proyecto.id_proyecto FROM tabla_proyecto, tabla_muestra WHERE[Forms]![selecciona_proyecto]![txt_id];

so, the Listbox control displays the result of the query; now Im thinking that maybe the result here is one option, maybe there’s more options and Im not referring programmatically right to the option; but, i tried that before by trying with “Me!id_muestra.SelectedItems(0)” and “Me!id_muestra.Value” and nothing

maybe its the control type, but i tried a textbox control and couldnt make it work with a query like the listbox…

Answer this question




to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
Knowledge Networking @ Fluther