lstSearch error message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list box that uses the first name and last name fields. The first
name is displayed in the list box and the last name is hidden. The plan is
that when I click on the name in the list box and click the "show record"
button, it opens up the form to that record so that I may see all the other
data associated with that name.

When I click on the name in the list box and click the "Show Record" button,
I get the error message " Compile Error: Method or data member not found"

Here is my code:

Private Sub ShowRecord_Click()

DoCmd.OpenForm "Tour Book", , , _
"[First_Name.Last_Name]=" & "'" & Me.lstSearch.Column(0) & "'"

End Sub

Thanks A Lot!
 
Doug Dickey said:
I have a list box that uses the first name and last name fields. The first
name is displayed in the list box and the last name is hidden. The plan is
that when I click on the name in the list box and click the "show record"
button, it opens up the form to that record so that I may see all the other
data associated with that name.

When I click on the name in the list box and click the "Show Record" button,
I get the error message " Compile Error: Method or data member not found"

Here is my code:

Private Sub ShowRecord_Click()

DoCmd.OpenForm "Tour Book", , , _
"[First_Name.Last_Name]=" & "'" & Me.lstSearch.Column(0) & "'"

End Sub

Thanks A Lot!
Click to expand...

You are getting the error because you don't have a field in the recordsource
named "First_Name.Last_Name", or you shouldn't (can't have a period in the
name)

Please post the SQL of the recordsource for the list box.
 
Doug Dickey said:
I have a list box that uses the first name and last name fields. The first
name is displayed in the list box and the last name is hidden. The plan is
that when I click on the name in the list box and click the "show record"
button, it opens up the form to that record so that I may see all the other
data associated with that name.

When I click on the name in the list box and click the "Show Record" button,
I get the error message " Compile Error: Method or data member not found"

Here is my code:

Private Sub ShowRecord_Click()

DoCmd.OpenForm "Tour Book", , , _
"[First_Name.Last_Name]=" & "'" & Me.lstSearch.Column(0) & "'"
Click to expand...

I suspect that "First_Name" and "Last_Name" are both different fields in the
recordsource of form "Tour Book".
As in field [Tour Book].[First_Name] and field [Tour Book].[Last_Name].

<warning type="Air Code>

DoCmd.OpenForm "Tour Book", , , _
"[First_Name]=" & "'" & Me.lstSearch.Column(0) & "'" & AND _
"[Last_Name]=" & "'" & Me.lstSearch.Column(1) & "'"

<warning/>

Any Help ? - John
 
Thanks for the help, but I still get the same error message. To make things
easier I went and changed the names of those two fields to get rid of the
underscores. Then I used the code you gave me:

Private Sub ShowRecord_Click()
DoCmd.OpenForm "Tour Book", , , _
"[FirstName]=" & "'" & Me.lstSearch.Column(0) & "'" And _
"[LastName]=" & "'" & Me.lstSearch.Column(1) & "'"

End Sub

Thanks Again!
-Doug

John Griffiths said:
Doug Dickey said:
I have a list box that uses the first name and last name fields. The first
name is displayed in the list box and the last name is hidden. The plan is
that when I click on the name in the list box and click the "show record"
button, it opens up the form to that record so that I may see all the other
data associated with that name.

When I click on the name in the list box and click the "Show Record" button,
I get the error message " Compile Error: Method or data member not found"

Here is my code:

Private Sub ShowRecord_Click()

DoCmd.OpenForm "Tour Book", , , _
"[First_Name.Last_Name]=" & "'" & Me.lstSearch.Column(0) & "'"
Click to expand...

I suspect that "First_Name" and "Last_Name" are both different fields in the
recordsource of form "Tour Book".
As in field [Tour Book].[First_Name] and field [Tour Book].[Last_Name].

<warning type="Air Code>

DoCmd.OpenForm "Tour Book", , , _
"[First_Name]=" & "'" & Me.lstSearch.Column(0) & "'" & AND _
"[Last_Name]=" & "'" & Me.lstSearch.Column(1) & "'"

<warning/>

Any Help ? - John

End Sub

Thanks A Lot!
Click to expand...
Click to expand...
 
I hope this is what you are looking for:

SELECT [Tourists on Date].[Time of Tour], [Tourists on Date].FirstName,
[Tourists on Date].LastName, [Tourists on Date].[# of People], [Tourists on
Date].Major FROM [Tourists on Date] WHERE ((([Tourists on Date].[Date Of
Tour]) Like Forms!acsearch!calsearch)) ORDER BY [Tourists on Date].[Time of
Tour];

I changed the name of the two fields so that I don't have to worry about
spaces or underscores, so now they are FirstName and LastName. Also, I
changed up the original code thanks to John G. It's now:

Private Sub ShowRecord_Click()
DoCmd.OpenForm "Tour Book", , , _
"[FirstName]=" & "'" & Me.lstSearch.Column(0) & "'" And _
"[LastName]=" & "'" & Me.lstSearch.Column(1) & "'"

End Sub

Thanks for your Help!
-Doug



SteveS said:
Doug Dickey said:
I have a list box that uses the first name and last name fields. The first
name is displayed in the list box and the last name is hidden. The plan is
that when I click on the name in the list box and click the "show record"
button, it opens up the form to that record so that I may see all the other
data associated with that name.

When I click on the name in the list box and click the "Show Record" button,
I get the error message " Compile Error: Method or data member not found"

Here is my code:

Private Sub ShowRecord_Click()

DoCmd.OpenForm "Tour Book", , , _
"[First_Name.Last_Name]=" & "'" & Me.lstSearch.Column(0) & "'"

End Sub

Thanks A Lot!
Click to expand...

You are getting the error because you don't have a field in the recordsource
named "First_Name.Last_Name", or you shouldn't (can't have a period in the
name)

Please post the SQL of the recordsource for the list box.
Click to expand...
 
Oops, typo - the AND should be inside the string. - John

<warning type="Air Code>

DoCmd.OpenForm "Tour Book", , , _
"[FirstName]=" & "'" & Me.lstSearch.Column(0) & "' AND " & _
"[LastName]=" & "'" & Me.lstSearch.Column(1) & "'"

<warning/>


Doug Dickey said:
Thanks for the help, but I still get the same error message. To make things
easier I went and changed the names of those two fields to get rid of the
underscores. Then I used the code you gave me:

Private Sub ShowRecord_Click()
DoCmd.OpenForm "Tour Book", , , _
"[FirstName]=" & "'" & Me.lstSearch.Column(0) & "'" And _
"[LastName]=" & "'" & Me.lstSearch.Column(1) & "'"

End Sub

Thanks Again!
-Doug

John Griffiths said:
Doug Dickey said:
I have a list box that uses the first name and last name fields. The first
name is displayed in the list box and the last name is hidden. The plan is
that when I click on the name in the list box and click the "show record"
button, it opens up the form to that record so that I may see all the other
data associated with that name.

When I click on the name in the list box and click the "Show Record" button,
I get the error message " Compile Error: Method or data member not found"

Here is my code:

Private Sub ShowRecord_Click()

DoCmd.OpenForm "Tour Book", , , _
"[First_Name.Last_Name]=" & "'" & Me.lstSearch.Column(0) & "'"
Click to expand...

I suspect that "First_Name" and "Last_Name" are both different fields in the
recordsource of form "Tour Book".
As in field [Tour Book].[First_Name] and field [Tour Book].[Last_Name].

<warning type="Air Code>

DoCmd.OpenForm "Tour Book", , , _
"[First_Name]=" & "'" & Me.lstSearch.Column(0) & "'" & AND _
"[Last_Name]=" & "'" & Me.lstSearch.Column(1) & "'"

<warning/>

Any Help ? - John

End Sub

Thanks A Lot!
Click to expand...
Click to expand...
Click to expand...
 
Doug Dickey said:
I hope this is what you are looking for:

SELECT [Tourists on Date].[Time of Tour], [Tourists on Date].FirstName,
[Tourists on Date].LastName, [Tourists on Date].[# of People], [Tourists on
Date].Major FROM [Tourists on Date] WHERE ((([Tourists on Date].[Date Of
Tour]) Like Forms!acsearch!calsearch)) ORDER BY [Tourists on Date].[Time of
Tour];

I changed the name of the two fields so that I don't have to worry about
spaces or underscores, so now they are FirstName and LastName. Also, I
changed up the original code thanks to John G. It's now:

Private Sub ShowRecord_Click()
DoCmd.OpenForm "Tour Book", , , _
"[FirstName]=" & "'" & Me.lstSearch.Column(0) & "'" And _
"[LastName]=" & "'" & Me.lstSearch.Column(1) & "'"

End Sub

Thanks for your Help!
-Doug
Click to expand...

If the above Select statement is the row source for the *list box*, then the
open form command should be:

Private Sub ShowRecord_Click()
DoCmd.OpenForm "Tour Book", , , "[FirstName]= '" & Me.lstSearch.Column(1)_
& "' And [LastName]= '" & Me.lstSearch.Column(2) & "'"

End Sub

because Column(0) is [Tourists on Date].[Time of Tour],
Column(1) is [Tourists on Date].FirstName and
Column(2) is [Tourists on Date].LastName

Also, you had the "AND" outside of the quotes. I removed a couple of
ampersands that weren't really needed.

HTH
 
Back
Top