Display record from list

  • Thread starter Thread starter Michael Nol
  • Start date Start date
M

Michael Nol

Hi

I need to be able to display a record which is based on a selected line of a
list box.

The list box is unbound and populated from another list box using

Private Sub List0_AfterUpdate()
Me![List2].RowSource = "SELECT qry_Plant.[Product Name],
qry_Plant.PlantTypeId, qry_Plant.PlantID FROM qry_Plant WHERE
qry_Plant.PlantTypeID = '" & Me![List0] & "'"
End Sub

I then have an onclick event to search for the relevant record

Private Sub List2_Click()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PlantID] = '" & Me![List2] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

It does not seem to be finding any records

Could someone help with this??


Thanks

Michael
 
Michael said:
I need to be able to display a record which is based on a selected line of a
list box.

The list box is unbound and populated from another list box using

Private Sub List0_AfterUpdate()
Me![List2].RowSource = "SELECT qry_Plant.[Product Name],
qry_Plant.PlantTypeId, qry_Plant.PlantID FROM qry_Plant WHERE
qry_Plant.PlantTypeID = '" & Me![List0] & "'"
End Sub

I then have an onclick event to search for the relevant record

Private Sub List2_Click()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PlantID] = '" & Me![List2] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

It does not seem to be finding any records
Click to expand...


Are you sure the list box is set for single select?

OTOH, maybe you should be using Me.RecordsetClone instead of
Me.Recordset.Clone
 
Hi Marshall

Yes I the list box is set for single select only and I have taken the '.'
out of Me.Recordset.Clone with no luck...

any other ideas

Michael

Marshall Barton said:
Michael said:
I need to be able to display a record which is based on a selected line of a
list box.

The list box is unbound and populated from another list box using

Private Sub List0_AfterUpdate()
Me![List2].RowSource = "SELECT qry_Plant.[Product Name],
qry_Plant.PlantTypeId, qry_Plant.PlantID FROM qry_Plant WHERE
qry_Plant.PlantTypeID = '" & Me![List0] & "'"
End Sub

I then have an onclick event to search for the relevant record

Private Sub List2_Click()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PlantID] = '" & Me![List2] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

It does not seem to be finding any records
Click to expand...


Are you sure the list box is set for single select?

OTOH, maybe you should be using Me.RecordsetClone instead of
Me.Recordset.Clone
Click to expand...
 
Michael said:
Yes I the list box is set for single select only and I have taken the '.'
out of Me.Recordset.Clone with no luck...
Click to expand...

What happens if you replace the line

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

with

If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

usimg the Me.RecordsetClone recordset.

With those two changes, you would be doing the same thing
I've done hundreds of times without a problem.

If that doesn't povide enlightenment, then try placing break
points and tracing the list box value.
--
Marsh
MVP [MS Access]


Michael said:
I need to be able to display a record which is based on a selected line of a
list box.

The list box is unbound and populated from another list box using

Private Sub List0_AfterUpdate()
Me![List2].RowSource = "SELECT qry_Plant.[Product Name],
qry_Plant.PlantTypeId, qry_Plant.PlantID FROM qry_Plant WHERE
qry_Plant.PlantTypeID = '" & Me![List0] & "'"
End Sub

I then have an onclick event to search for the relevant record

Private Sub List2_Click()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PlantID] = '" & Me![List2] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

It does not seem to be finding any records
Click to expand...

"Marshall Barton" wrote
Are you sure the list box is set for single select?

OTOH, maybe you should be using Me.RecordsetClone instead of
Me.Recordset.Clone
Click to expand...
Click to expand...
 
Back
Top