List Box Will Not Display Correct Value

[Guest]

Hi,

I have a list box that is bound to a table, which displays two rows from the
table, the location and box number (these are in accesending order).

I used the wizard to do the 'locate record on my form' list box.

I have two problems:

1) the value that lists in the list box after it has been selected is from
column 1 (location) - I would like column two (Box Number) to be displayed

2) the record I select does not get updated on the form

If I run the wizard and select only the Box number column, the whole thing
works perfectly. Can anyone advise why this will not work with two columns?

Cheers,
GLT.

 

[John W. Vinson]

Hi,

I have a list box that is bound to a table, which displays two rows from the
table, the location and box number (these are in accesending order).

I used the wizard to do the 'locate record on my form' list box.

I have two problems:

1) the value that lists in the list box after it has been selected is from
column 1 (location) - I would like column two (Box Number) to be displayed

2) the record I select does not get updated on the form

If I run the wizard and select only the Box number column, the whole thing
works perfectly. Can anyone advise why this will not work with two columns?

Sounds like you may have the incorrect column as the listbox's Bound Column
property. The value of the listbox is the value in that column - which isn't
necessarily the first column, nor the visible column.

What are the listbox's RowSource, ColumnCount, and Bound Column properties?
Could you post the (wizard generated???) code from the AfterUpdate event?

John W. Vinson [MVP]

 

[Guest]

Hi John,

Thanks for your reply - I had changed the Name of the combo box but did not
realise there was code behind the After Update property.

What I tried to do was have a form that the user could add new records (by
selecting an "add new record" button, and have the list box that users could
retreive existing records and edit them all on one screen.

I have now seperated these functions across two forms (one for new records /
the other for editing). The list box now works (when the user selects a
value, it populates all fields on the form for editing), but I could not get
the bound column to work on column 2 - it just shows a blank...

Here is the SQL:

SELECT [tbl_TM_REC: BoxReg].ID, [tbl_TM_REC: BoxReg].[Box No], [tbl_TM_REC:
BoxReg].Site
FROM [tbl_TM_REC: BoxReg]
ORDER BY [tbl_TM_REC: BoxReg].Site;

Here what I tried to get the SQL to do:

SELECT [tbl_TM_REC: BoxReg].ID, [tbl_TM_REC: BoxReg].Site, [tbl_TM_REC:
BoxReg].[Box No]
FROM [tbl_TM_REC: BoxReg]
ORDER BY [tbl_TM_REC: BoxReg].Site;

When I do the above, and set the bound column to 3, thenit still only
displays column 2 in the list box and then the following code displays a type
mismatch error:


Private Sub Combo57_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo57], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Cheers,
GLT.

 

[Douglas J. Steele]

What have you got for the ColumnCount and ColumnWidths properties?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi John,

Thanks for your reply - I had changed the Name of the combo box but did
not
realise there was code behind the After Update property.

What I tried to do was have a form that the user could add new records (by
selecting an "add new record" button, and have the list box that users
could
retreive existing records and edit them all on one screen.

I have now seperated these functions across two forms (one for new records
/
the other for editing). The list box now works (when the user selects a
value, it populates all fields on the form for editing), but I could not
get
the bound column to work on column 2 - it just shows a blank...

Here is the SQL:

SELECT [tbl_TM_REC: BoxReg].ID, [tbl_TM_REC: BoxReg].[Box No],
[tbl_TM_REC:
BoxReg].Site
FROM [tbl_TM_REC: BoxReg]
ORDER BY [tbl_TM_REC: BoxReg].Site;

Here what I tried to get the SQL to do:

SELECT [tbl_TM_REC: BoxReg].ID, [tbl_TM_REC: BoxReg].Site, [tbl_TM_REC:
BoxReg].[Box No]
FROM [tbl_TM_REC: BoxReg]
ORDER BY [tbl_TM_REC: BoxReg].Site;

When I do the above, and set the bound column to 3, thenit still only
displays column 2 in the list box and then the following code displays a
type
mismatch error:


Private Sub Combo57_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo57], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Cheers,
GLT.

Sounds like you may have the incorrect column as the listbox's Bound
Column
property. The value of the listbox is the value in that column - which
isn't
necessarily the first column, nor the visible column.

What are the listbox's RowSource, ColumnCount, and Bound Column
properties?
Could you post the (wizard generated???) code from the AfterUpdate event?

John W. Vinson [MVP]

 

[Guest]

Hi Doug,

The column count and column with properties are as follows:

Column count : 3
Column witdths: 0cm;2.54cm;2.54cm

Cheers,
GLT

What have you got for the ColumnCount and ColumnWidths properties?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi John,

Thanks for your reply - I had changed the Name of the combo box but did
not
realise there was code behind the After Update property.

What I tried to do was have a form that the user could add new records (by
selecting an "add new record" button, and have the list box that users
could
retreive existing records and edit them all on one screen.

I have now seperated these functions across two forms (one for new records
/
the other for editing). The list box now works (when the user selects a
value, it populates all fields on the form for editing), but I could not
get
the bound column to work on column 2 - it just shows a blank...

Here is the SQL:

SELECT [tbl_TM_REC: BoxReg].ID, [tbl_TM_REC: BoxReg].[Box No],
[tbl_TM_REC:
BoxReg].Site
FROM [tbl_TM_REC: BoxReg]
ORDER BY [tbl_TM_REC: BoxReg].Site;

Here what I tried to get the SQL to do:

SELECT [tbl_TM_REC: BoxReg].ID, [tbl_TM_REC: BoxReg].Site, [tbl_TM_REC:
BoxReg].[Box No]
FROM [tbl_TM_REC: BoxReg]
ORDER BY [tbl_TM_REC: BoxReg].Site;

When I do the above, and set the bound column to 3, thenit still only
displays column 2 in the list box and then the following code displays a
type
mismatch error:


Private Sub Combo57_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo57], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Cheers,
GLT.

Hi,

I have a list box that is bound to a table, which displays two rows from
the
table, the location and box number (these are in accesending order).

I used the wizard to do the 'locate record on my form' list box.

I have two problems:

1) the value that lists in the list box after it has been selected is
from
column 1 (location) - I would like column two (Box Number) to be
displayed

2) the record I select does not get updated on the form

If I run the wizard and select only the Box number column, the whole
thing
works perfectly. Can anyone advise why this will not work with two
columns?

Sounds like you may have the incorrect column as the listbox's Bound
Column
property. The value of the listbox is the value in that column - which
isn't
necessarily the first column, nor the visible column.

What are the listbox's RowSource, ColumnCount, and Bound Column
properties?
Could you post the (wizard generated???) code from the AfterUpdate event?

John W. Vinson [MVP]