Can't save text in listbox to table 1

[Gerbers404]

Hi there.

I have created a form with combobox controls that allows the user to select a customer and job number. Upon selecting these 2 fields and refreshing the form, two list boxes are populated by queries that select account managers and test codes specific to the job number.

For some reason, the contents of the list boxes never make it into the table. The text in the listboxes is displayed fine on the form, but it is considered a null at any given point in the process. I tried to make 2 text boxes that would equal the text or values of the list boxes, and when I stepped through the code, the values in the list boxes were null. Also, VBA didn't allow the .Text method when referring to the list boxes, only .Value.

I imagine that I'm missing something very basic here, but I haven't been able to figure it out yet. Any ideas?

 

[Bastien]

if you recreate the combobox with the tools on the form in design mode, you can specify the field in the table where the value is to be stored, you may not have this association..

but if you are running queries to answer a question, do you need to store the info required to run the query?

Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[Gerbers404]

I have the control sources for the listboxes set to the appropriate fields. When I tried the textbox workaround, I set the listboxes as unbound and the textboxes to the fields.

I still do need the info for the queries to be stored in the table. Seems like the easiest way for logging / reporting purposes.

 

[jebry]

Hi!

I have the same question as Bastien. If you can get the information by using a query then you can base a form/subforms and report/subreports on these queries and accessing the information should at that point be easier than storing it all in one field.

That said (and hopefully emphasized) here is the code to transfer the information from the list box to a text box (a procedure that has other uses):

Dim intRow As Integer

For intRow = 0 To YourListBox.ListCount - 1
If Nz(Len(YourTextBox.Value), 0) = 0 Then
YourTextBox.Value = YourListBox.Column(0, intRow)
Else
YourTextBox.Value = YourTextBox.Value & ";" & YourListBox.Column(0, intRow)
End If
Next intRow

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Gerbers404]

Jeff,

Thank you. The code worked like a charm!

I guess my justification for wanting to di it this way might lie in the fact that this is a small, seprate app. I fiured that there would be no need for excess lan traffic of running the qurey to produce reports and editing records. I query tables form another db, but I figured that once I recieved the result, I had no need to ever re-query. I do understand where both of you are coming from, and can give you the fact that you both are probably right. Thanks again for your help.

Gerbers404