Querying data in Listbox

[KalEl33]

I'm having trouble with getting data from a listbox and placing it in a table. I am using an update-query to grab data from a listbox and place it in a table called tblData. The listbox gets its data from a select-query. If I set the listbox's default value to some number (e.g. =2), then the field in tblData will be filled with 2. However if I leave the listbox's default value blank and only have the row source reference the select-query, when viewing the form I can see the value in the listbox that I want to place in tblData, but the field in tblData is left blank (actually a null value is placed in the field).

Any ideas on what is going on here? My goal is to just place the value that I see in the listbox in tblData.

Thanks in advance for you help.

Sincerely,
Kal-El

 

[EarS]

if you put this code in the click event of the list box then it will add a new record of the value selected in the listbox to the table. is this helpful?

Dim db As dao.Database
Dim tbl As dao.Recordset
Dim SQLQuery As String
Set db = CurrentDb

SQLQuery = "SELECT * FROM tblData"
Set tbl = db.OpenRecordset(SQLQuery, dbOpenDynaset)

tbl.AddNew
'fill in fieldname and listboxname accordingly
tbl!FieldName = listboxname.Value
tbl.Update
tbl.Close

 

[KalEl33]

Dear EarS,

I was hoping to automatically select the value in the list box. I guess what I'm having trouble with is automatically selecting the first value in the listbox (NOTE: the query that gives the listbox its data only contains one record (e.g. the only choice in the list box is 2.29)). I can see the value in the listbox, but I can't seem to get the value. I created a textbox in the form to try to experiment on how to get the value in the listbox, but the only value it will retrieve is the listbox's default value. So when I leave the default value blank, the textbox retrieves nothing. Looking at your suggestion, I put the following in my textbox's default value

= lstBox1.Value

This grabbed the number I was looking for and placed it in the textbox. But when I closed the form and reopen it, the textbox was blank. I have tried to requery the textbox, but that didn't work either. It seems that the number will appear in the textbox only when I change its default value to something else, then change it back to =lstBox1.Value. Then after I close the form and reopen it, the textbox will remain blank.

I think what I'm asking is rather simple, but I can't seem to figure it out. My goal is to just take a value that appears in a text box (without a user making a selection) and place it in a textbox or table. Perhaps what I'm looking for is related to automatically selecting the first entry in a combobox. Any ideas?

Thanks,
Kal-El

 

[jebry]

Hi Kal-El!

Assuming the list boxes multiselect property is set to none then you set the control source of the box to the field you want to store the information in and set the bound column to the column which will hold the information to be stored. Now the value from the selected row in the bound column will be stored in the control source.

Note: The control source must be part of the record source of the form and the record source of the form must be updatable. Also, I must re-iterate, this will not work with a list box where you can select more than one row. The value of such a list box will be null and remain null.

hth
Jeff Bridgham
bridgham@purdue.edu