selecting records from a listbox

[tecman]

I have a form with a listbox and several text boxes. I used the wizard
to design the form with text boxes to show the fields in a table. I
added a listbox to the form using the same table. I want to be able to
click on the record in the listbox and populate the text boxes with the
record selected. Your help is greatly appreciated

 

[tecman]

Thanks for the advice, but for my application I like the Listbox. The
database is a workorder program. I want to be able to see all the
pending workorders and be able to click on the one I want to update. I
will be updating the fields of the selected record in the same form

When you want to select a single value in a situation like this, a combo box
is a better control than a list box. It offers the user a better way to look
up values and it is easier for you to code.

The typical way this is done is to use an unbound combo box. Make the row
source of the combo a query that returns the primary key field of you table:
SELECT MyKeyField FROM MyTable;

Then to navigate to the selected record, use the combo box's After Update
event:

With Me.RecordsetClone
.FindFirst "[TableKeyField] = " & Me.MyComboBoxName
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

[TableKeyField] should be the name of the field in your table that is the
primary key of the table.

This line:
.FindFirst "[TableKeyField] = " & Me.MyComboBoxName
assumes [TableKeyField] is a numeric field. If it is a text field, then use
this syntax:
.FindFirst "[TableKeyField] = '" & Me.MyComboBoxName & "'"

--
Dave Hargis, Microsoft Access MVP

I have a form with a listbox and several text boxes. I used the wizard
to design the form with text boxes to show the fields in a table. I
added a listbox to the form using the same table. I want to be able to
click on the record in the listbox and populate the text boxes with the
record selected. Your help is greatly appreciated

 

[tecman]

Thanks for your help on this. I Inserted the code behind the on Click
event. My listbox is named moupd. the records in the listbox are pulled
from a query. the bound field in the listbox is Maintenance Order. I
modified the code, changing MyListBox to moupd and [TableKeyField] to
[Maintenance_Order]. I get " runtime error 2480 - you referred to a
property by a numeric argument that isnt one of the property numbers in
the collection". the Maintenance Order field is the data type
autonumber. here is the code:

Private Sub moupd_Click()
Dim strFindValue As String


strFindValue = Me.moupd.ItemData(Me!moupd.ItemsSelected(0))
With Me.RecordsetClone
.FindFirst "[Maintenance_Order] = '" & strFindValue & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Okay.

Dim strFindValue As String

strFindValue = Me.MyListBox.Itemdata(Me!MyListBox.ItemsSelected(0))
With Me.RecordsetClone
.FindFirst "[TableKeyField] = '" & strFindValue & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

--
Dave Hargis, Microsoft Access MVP

Thanks for the advice, but for my application I like the Listbox. The
database is a workorder program. I want to be able to see all the
pending workorders and be able to click on the one I want to update. I
will be updating the fields of the selected record in the same form

When you want to select a single value in a situation like this, a combo box
is a better control than a list box. It offers the user a better way to look
up values and it is easier for you to code.

The typical way this is done is to use an unbound combo box. Make the row
source of the combo a query that returns the primary key field of you table:
SELECT MyKeyField FROM MyTable;

Then to navigate to the selected record, use the combo box's After Update
event:

With Me.RecordsetClone
.FindFirst "[TableKeyField] = " & Me.MyComboBoxName
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

[TableKeyField] should be the name of the field in your table that is the
primary key of the table.

This line:
.FindFirst "[TableKeyField] = " & Me.MyComboBoxName
assumes [TableKeyField] is a numeric field. If it is a text field, then use
this syntax:
.FindFirst "[TableKeyField] = '" & Me.MyComboBoxName & "'"

--
Dave Hargis, Microsoft Access MVP


:

I have a form with a listbox and several text boxes. I used the wizard
to design the form with text boxes to show the fields in a table. I
added a listbox to the form using the same table. I want to be able to
click on the record in the listbox and populate the text boxes with the
record selected. Your help is greatly appreciated

 

[tecman]

I am still getting the error. I clicked debug and this line was
highlighted in yellow
strFindValue = Me.moupd.ItemData(Me!moupd.ItemsSelected(0))

This syntax is for a text field. Based on your previous post, I incorrectly
assumed you would be looking for a text value.
.FindFirst "[Maintenance_Order] = '" & strFindValue & "'"

Try it this way for a numeric field:
.FindFirst "[Maintenance_Order] = " & strFindValue


--
Dave Hargis, Microsoft Access MVP

Thanks for your help on this. I Inserted the code behind the on Click
event. My listbox is named moupd. the records in the listbox are pulled
from a query. the bound field in the listbox is Maintenance Order. I
modified the code, changing MyListBox to moupd and [TableKeyField] to
[Maintenance_Order]. I get " runtime error 2480 - you referred to a
property by a numeric argument that isnt one of the property numbers in
the collection". the Maintenance Order field is the data type
autonumber. here is the code:

Private Sub moupd_Click()
Dim strFindValue As String


strFindValue = Me.moupd.ItemData(Me!moupd.ItemsSelected(0))
With Me.RecordsetClone
.FindFirst "[Maintenance_Order] = '" & strFindValue & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Okay.

Dim strFindValue As String

strFindValue = Me.MyListBox.Itemdata(Me!MyListBox.ItemsSelected(0))
With Me.RecordsetClone
.FindFirst "[TableKeyField] = '" & strFindValue & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

--
Dave Hargis, Microsoft Access MVP


:

Thanks for the advice, but for my application I like the Listbox. The
database is a workorder program. I want to be able to see all the
pending workorders and be able to click on the one I want to update. I
will be updating the fields of the selected record in the same form

Klatuu wrote:
When you want to select a single value in a situation like this, a combo box
is a better control than a list box. It offers the user a better way to look
up values and it is easier for you to code.

The typical way this is done is to use an unbound combo box. Make the row
source of the combo a query that returns the primary key field of you table:
SELECT MyKeyField FROM MyTable;

Then to navigate to the selected record, use the combo box's After Update
event:

With Me.RecordsetClone
.FindFirst "[TableKeyField] = " & Me.MyComboBoxName
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

[TableKeyField] should be the name of the field in your table that is the
primary key of the table.

This line:
.FindFirst "[TableKeyField] = " & Me.MyComboBoxName
assumes [TableKeyField] is a numeric field. If it is a text field, then use
this syntax:
.FindFirst "[TableKeyField] = '" & Me.MyComboBoxName & "'"

--
Dave Hargis, Microsoft Access MVP


:

I have a form with a listbox and several text boxes. I used the wizard
to design the form with text boxes to show the fields in a table. I
added a listbox to the form using the same table. I want to be able to
click on the record in the listbox and populate the text boxes with the
record selected. Your help is greatly appreciated

 

[Ruel Cespedes via AccessMonster.com]

Your situation is very similar to this Thread:

http://www.accessmonster.com/Uwe/Forum.aspx/access-forms/43836/Retrieving-Existing-Records-on-a-Form


The difference is you are using a listbox. Make sure that in your listbox
the Bound Column is the primary key field in your table.

Then create an AfterUpdate Procedure that looks like this:

Private Sub Listbox_AfterUpdate()

Me.Filter = "((Projects.ProjectID=" & Me.Listbox & "))"
Me.FilterOn = True

End Sub




Ruel