Referring to multiple columns in a listbox

[DougW via AccessMonster.com]

Hi,
I have a form that I am using for data entry for a table "Ordered Items".
I want to use the form to edit the value in one field of existing records in
this table.
There is an unbound listbox A on my form which displays columns from a query.
I use the value of column 3 in the selected row of this listbox to drive
another query, which populates another listbox B, also currently unbound, on
the same form. To accomplish this I have set column 3 in listbox A as the
"bound" column.

I have created a macro for the "onclick" event in listbox A, which requeries
listbox B.
The query driving listbox B has a criteria set equal to the bound value in
listbox A.
Basically listbox B refreshes dynamically as rows are clicked in listbox A.
So far this works as I would like.

I would like the bound value of listbox B to select the record for editing in
my table.
I would like the field in that record to be set equal to column 6 of listbox
A when I click an action button. It seems like I will need to refer to two
different columns in listbox A (column 3 to drive listbox B, and column 6 to
update the field in the table).

Can anybody give me some direction on how I might proceed? thanks!

 

[Jeanette Cunningham]

DougW,
the bound column in the list box is the one that writes data back to the
table.
You can use the value of any column in the list box to get the correct
values for another list or combo without using the bound column.

So bind the column that writes back to the table - ie make its control
source a field in the table, and use any column to create the row source for
the next combo or list box.



Jeanette Cunningham -- Melbourne Victoria Australia

 

[DougW via AccessMonster.com]

Hello Jeanette,

Thanks very much for your reply. From your note, I now understand the
significance of the bound column in the listbox. I was able to make
reference to the "other" column of that listbox by creating a textbox and
setting its control = ListboxAControl.Column(6). I am then able to use that
textbox control for the criteria value in the query that drives Listbox B.

What I now need to do is to use the selected row in Listbox B to choose a
record for editing in the underlying table of my form. (The bound column in
Listbox B is the primary key of the table). I would like to add an action
button that, when clicked, will select the record based on the bound column
of Listbox B, and will edit (overwrite) a particular field in that record
with the value of the bound column of Listbox A.

The data properties of my form are currently set as:
Allow Filters: Yes
Allow Edits: Yes
Allow Deletions: No
Allow Additions: No
Data Entry: No

What would be the most straightforward way to make the action button function
as I have described?

thanks in advance,
Doug

DougW,
the bound column in the list box is the one that writes data back to the
table.
You can use the value of any column in the list box to get the correct
values for another list or combo without using the bound column.

So bind the column that writes back to the table - ie make its control
source a field in the table, and use any column to create the row source for
the next combo or list box.

Jeanette Cunningham -- Melbourne Victoria Australia

Hi,
I have a form that I am using for data entry for a table "Ordered Items".

[quoted text clipped - 31 lines]

Can anybody give me some direction on how I might proceed? thanks!

 

[Jeanette Cunningham]

Doug,
the idea is to use an update query.
I will use an example table called tblProduct
with fields ProductID, ProductMake, ProductModel

-------------------------------------
Private Sub cmdButtonName_Click()
Dim strSQL as String

strSQL = "UPDATE tblProduct " _
& "SET tblProduct.ProductModel = """ & Me.ListBoxA & """ " _
& "WHERE tblProduct.ProductID = " & Me.ListboxB & ""

If Not IsNull(Me.ListBoxA) Then
CurrentDb.Execute strSQL, dbFailOnError
End If

End Sub
---------------------------------------

Note - the above assumes that the value from ListBoxA is a text data type
For a number use
" & Me.ListBoxA & "



Jeanette Cunningham -- Melbourne Victoria Australia

Hello Jeanette,

Thanks very much for your reply. From your note, I now understand the
significance of the bound column in the listbox. I was able to make
reference to the "other" column of that listbox by creating a textbox and
setting its control = ListboxAControl.Column(6). I am then able to use
that
textbox control for the criteria value in the query that drives Listbox B.

What I now need to do is to use the selected row in Listbox B to choose a
record for editing in the underlying table of my form. (The bound column
in
Listbox B is the primary key of the table). I would like to add an action
button that, when clicked, will select the record based on the bound
column
of Listbox B, and will edit (overwrite) a particular field in that record
with the value of the bound column of Listbox A.

The data properties of my form are currently set as:
Allow Filters: Yes
Allow Edits: Yes
Allow Deletions: No
Allow Additions: No
Data Entry: No

What would be the most straightforward way to make the action button
function
as I have described?

thanks in advance,
Doug

DougW,
the bound column in the list box is the one that writes data back to the
table.
You can use the value of any column in the list box to get the correct
values for another list or combo without using the bound column.

So bind the column that writes back to the table - ie make its control
source a field in the table, and use any column to create the row source
for
the next combo or list box.

Jeanette Cunningham -- Melbourne Victoria Australia

Hi,
I have a form that I am using for data entry for a table "Ordered
Items".

[quoted text clipped - 31 lines]

Can anybody give me some direction on how I might proceed? thanks!