Need Help with List box and table relationship

[Daama via AccessMonster.com]

Hi,

I really need some help!!!!

I have a list box displaying several items. The same items are also found in
a separate table named: "FieldDescription". In this table, there are 2 column:

one listing those items and the other column contains the description of each
item> What I want to do is create an OnClick event that checks if the
selected item from the list box is the same as one of the items in the table.
If true, then populate the textbox with the corresponding description from
the table.

I am using the code below but just can't get to work. It's not giving the
corresponding description.

P.S. I can write out the vba code and include the descriptions into the If..
then statement. But I'd like to keep the description seperate in a table so
that a user can edit it easily without going into the code.

Any Help!!!!

Below is the code

Daama

Public Sub btnOK_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

Dim strField As String
Dim X As Integer
Dim i As Integer

i = lstSelected.ListIndex

If i <> -1 Then

For X = 0 To Me.lstSelected.ListCount - 1
strField = Me.lstSelected.Value
Next X

rs.Open "FieldDescription", cn

If strField = rs.Fields.Item("FieldName") Then

txtFieldsDescription.SetFocus
txtFieldsDescription.Text = rs.Fields.Item("Description")

End If

End If

End Sub

 

[guido via AccessMonster.com]

Are you using the "FieldDescription" table to populate the list box? If there
is no problem with that, then proceed as follows:
Set the row source to the FieldDescription table, or a sql query containing
both columns with the item first and the description as the second column.
Set the 'Bound Column' property to 1, 'Column Count' property to 2, 'Column
Widths' property to 1";0". This way only the first field actually displays in
the list box.

Event Code:

txtFieldsDescription.Value = lstSelected.Column(1)

 

[Jeff Boyce]

What are you using the listbox for? How is it that it could contain items
not found in the table?

If it can ONLY contain items in the table, why not set the listbox's source
to the table? That way, your listbox could show the description, straight
from the table, without needing to use an additional textbox.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Daama via AccessMonster.com]

Thanks, Jeff,

It's because i have this code below that queries and populates the listbox
with all the field names (columns) that exist in a number of queries. (and
those source queries are: buyer; Geographic; Demographics, etc). This happens
at an AfterUpdate event. This part is working fine! But thats what makes it
hard as well. Thats why I created another table "FieldDescription" where I
can allow the user to enter/edit description for earch column via a form. Now
the challenge is how to display those descriptions in a text box (it can be
something else) thru any sort of event that matches the item selected and the
description that goes with it.

I hope this won't be confusing to you. But I really need some help here.

Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim strMsg As String
Dim strStart As String


If Me.cmbCategory = "buyer" Then
strSQL = "Select * from buyer"

ElseIf Me.cmbCategory = "Geographic" Then
strSQL = "Select * from Geographic"

ElseIf Me.cmbCategory = "Demographics" Then
strSQL = "Select * from Demographics"

ElseIf Me.cmbCategory = "Property" Then
strSQL = "Select * from Property"

ElseIf Me.cmbCategory = "Seller" Then
strSQL = "Select * from Seller"

ElseIf Me.cmbCategory = "Transaction" Then
strSQL = "Select * from Transaction"

End If


Set rs = CurrentDb.OpenRecordset(strSQL)

For Each fld In rs.Fields
strMsg = strMsg & fld.Name & ";"
next

Me.lstSelectFrom.RowSource = strMsg
Me.lstSelectFrom.RowSourceType = "value list"

What are you using the listbox for? How is it that it could contain items
not found in the table?

If it can ONLY contain items in the table, why not set the listbox's source
to the table? That way, your listbox could show the description, straight
from the table, without needing to use an additional textbox.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

[quoted text clipped - 60 lines]

End Sub

 

[Jeff Boyce]

I am perhaps not confused (enough?!), but concerned.

Can you describe why you want users to have access to fieldnames?
Typically, an Access application will hide these from the users, and use
forms to allow the users to add/edit data.

Are you allowing users to modify the table structure? I guess I'm asking
more about the underlying business need you are trying to solve than the
specific way you are approaching solving the need.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks, Jeff,

It's because i have this code below that queries and populates the listbox
with all the field names (columns) that exist in a number of queries. (and
those source queries are: buyer; Geographic; Demographics, etc). This
happens
at an AfterUpdate event. This part is working fine! But thats what makes
it
hard as well. Thats why I created another table "FieldDescription" where I
can allow the user to enter/edit description for earch column via a form.
Now
the challenge is how to display those descriptions in a text box (it can
be
something else) thru any sort of event that matches the item selected and
the
description that goes with it.

I hope this won't be confusing to you. But I really need some help here.

Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSQL As String
Dim strMsg As String
Dim strStart As String


If Me.cmbCategory = "buyer" Then
strSQL = "Select * from buyer"

ElseIf Me.cmbCategory = "Geographic" Then
strSQL = "Select * from Geographic"

ElseIf Me.cmbCategory = "Demographics" Then
strSQL = "Select * from Demographics"

ElseIf Me.cmbCategory = "Property" Then
strSQL = "Select * from Property"

ElseIf Me.cmbCategory = "Seller" Then
strSQL = "Select * from Seller"

ElseIf Me.cmbCategory = "Transaction" Then
strSQL = "Select * from Transaction"

End If


Set rs = CurrentDb.OpenRecordset(strSQL)

For Each fld In rs.Fields
strMsg = strMsg & fld.Name & ";"
next

Me.lstSelectFrom.RowSource = strMsg
Me.lstSelectFrom.RowSourceType = "value list"

What are you using the listbox for? How is it that it could contain items
not found in the table?

If it can ONLY contain items in the table, why not set the listbox's
source
to the table? That way, your listbox could show the description, straight
from the table, without needing to use an additional textbox.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

[quoted text clipped - 60 lines]

End Sub