help with coee

[ppoole16]

help with code

I've created a search form where the user presses a command button and the possible matches are displayed in a list box. The user should then be able to click on the correct response in the list box to bring up the correct record in a form. I've used the following code posted here by Mile-O-Phile or Phil Hartman but I can't seem to get it to work properly. When the user clicks on the list box, a form is opened and displays all records rather than just the one that is needed. The unique id in my form is in the first column of my table.

Option Compare Database
Option Explicit

Private Sub cmdSearch_Click()
If IsNull(Me.txtFilter) Then
MsgBox "You have not entered any filter criteria.", vbExclamation, "Title"
Exit Sub
End If
With lstResults
.RowSource = "SELECT * FROM Booking WHERE [Last] Like '*" & Me.txtFilter & "*';"
.Requery
End With
End Sub

Private Sub lstResults_AfterUpdate()
DoCmd.OpenForm "Booking", acNormal, "[Booking Sheet Number] = " & lstResults.Column(0)
End Sub

Thanks for the help.

 

[Tay]

Have you checked the columns in your listbox? If for instance you had the search results in the first column (column0), and the unique id in the second column (column1) then you would have a problem as the code you have posted refers to the bound column as column0. So if this is the case, it should be easily rectified. HTH

 

[ppoole16]

I've double checked and the unique ID is in the first column. I'll post my example database if you can tell me how.

Thanks.

 

[WayneRyan]

ppoole,

Your rowsource for the listbox is "Select *"

Shouldn't it be: Select [Booking Sheet Number], [Booking Sheet Name] ...

The select * will not put them in a specific order, you can
set the list widths to 0"; 1" to make the number invisible,
but you can still reference it in the openform command.

to post a db:

Tools --> Database Utilities --> Compact/Repair
ZIP it
Post it

Wayne

 

[ppoole16]

Thanks for the help, but I'm still not getting it. I've posted a much simplified version of my dbase. I can see that it didn't post. I'll try again.

 

[ppoole16]

Here it is.

 

[WayneRyan]

ppoole,

Your select statement should not be "*"

How is SQL going to know what order to put them in? It will put
the fields in random order.

You need:

Select [Booking Sheet Number], [Last], [First] 
From    Booking
WHERE  [Last] Like '*" & Me.txtFilter & "*';"

That way .Column(0) will always reference your key field.

Wayne

 

[ppoole16]

still can't get it right

Tried that. It didn't make a difference. The form always opens with record 1 rather than the record I need. Any more suggestions?

 

[ppoole16]

I figured it out. I added a Recordsource and it seems to work.

Thanks for all the help

Private Sub lstResults_AfterUpdate()
DoCmd.OpenForm "Booking", RecordSource = "Booking", acNormal, "[Booking Sheet Number] = " & lstResults.Column(0)
End Sub

 

[beef]

Search Form

Have a look at the attached


Cheers!!!!!

Lou