M
magmike
In the past, I posted (although I can't seem to find it - perhaps in a
different newsgroup?) a request for help on a "Limit to List" problem,
where I wanted to set it to "No", but because my bound column (an auto
number) was not visible (and therefore, the actual value of the
unbound field), Access would not allow me to do so. No one was able to
recommend a solution. However, I have since solved my problem and
wanted to share it and be able to contribute TO the newsgroup for
once.
So you understand why on earth I would want to do this, I'll explain.
My field's (unfortunately named Combo137 - it's been with me awhile!)
sole purpose initially, was to find a record in the set by typing the
name. Combo137 used a query to populate itself organized first by
company name, then by city, then by street name. I used the id number
which was the first column and hidden, to find the record in the
record set. To search, I would start typing, and of course, the more
letters I typed, the closer I got to a match. On AfterUpdate (the user
hits ENTER when the right company name shows itself), Access would
look for the record in the set matching the id number (column number
1, which was set to 0"). Using the ID Number is necessary, because
there can exist multiple records with the same name - for example,
there are multiple McDonald's in town. Each location would be its own
record. As the user typed they would see the combo expanded, and may
see McDonald's in Column 1, and "Nashville" in Column 2, and for cases
where necessary, the street name in Column 3.
Eventually, I wanted to look for companies where I wasn't real sure of
the exact name, where the word I was using may not be the first word
in the actual company name - which wouldn't work in Combo137. So I
created a separate form for this search activity using a "Like"
command for the name field. An efficiency addict, my goal was to be
able to use the same control to do both types of searches. Using
DblClick, I made it easier to open the search form. But, I still
wanted to be able to use the users text in Combo137 to search both
ways. In creating a command button, I tried to capture the text from
Combo137 and carry it over to the search form, however, unless the
text was a match for a record in the query, Combo137 wouldn't allow it
as long as it was set to Limit To List, and we all know by now in this
story why that doesn't work.For some reason, tonight it hit me!
First, I moved the ID Number field to the last column - the fourth
column, or .Column(3). Then I set the "bound" column to the name
field, what I was searching by, and allowing me to set Limit to List
to No. The first search method still works, so that as I type letters,
the name in the combo updates. If I hit ENTER, the AfterUpdate event
now uses Combo137.Column(3) to find the record in the set. But this is
where it really gets fun.
Now, on afterupdate, it checks to see if column(3) has a value, and if
so, it finds the record in the set. If not, it opens the search form
and performs the search. For button pushers, I also added a button
next to the field which also calls the function - here is the code:
Private Sub Combo137_AfterUpdate()
Dim rs As Object
If Me.Combo137.Column(3) > 0 Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo137].Column(3), 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Else:
MyCompanySearch
End If
End Sub
Public Function MyCompanySearch()
Dim stForm As String
stForm = "ProspectSearch"
If IsNull([Combo137]) Then
GoTo OpenSesame
Else:
GoTo GrabValue
End If
GrabValue:
DoCmd.OpenForm stForm
Forms.ProspectSearch.findco = Me.Combo137
Forms.ProspectSearch.findcoButton.SetFocus
GoTo Search
Search:
Forms!ProspectSearch!SearchByLabel.Value = " by Company"
Forms![ProspectSearch]!findquery.Form.Filter = "Company Like '*" & _
Replace(Forms!ProspectSearch!findco, "'", "''") & "*'"
Forms![ProspectSearch]!findquery.Form.FilterOn = True
Forms![ProspectSearch]!findquery.Form.Visible = True
Forms!ProspectSearch!findco.SetFocus
Exit Function
OpenSesame:
DoCmd.OpenForm stForm
Forms.ProspectSearch.findco.SetFocus
End Function
For a hack like me, I was excited to figure this out finally. And by
posting this, perhaps someone else searching in the future will find
their answer here.
magmike
different newsgroup?) a request for help on a "Limit to List" problem,
where I wanted to set it to "No", but because my bound column (an auto
number) was not visible (and therefore, the actual value of the
unbound field), Access would not allow me to do so. No one was able to
recommend a solution. However, I have since solved my problem and
wanted to share it and be able to contribute TO the newsgroup for
once.
So you understand why on earth I would want to do this, I'll explain.
My field's (unfortunately named Combo137 - it's been with me awhile!)
sole purpose initially, was to find a record in the set by typing the
name. Combo137 used a query to populate itself organized first by
company name, then by city, then by street name. I used the id number
which was the first column and hidden, to find the record in the
record set. To search, I would start typing, and of course, the more
letters I typed, the closer I got to a match. On AfterUpdate (the user
hits ENTER when the right company name shows itself), Access would
look for the record in the set matching the id number (column number
1, which was set to 0"). Using the ID Number is necessary, because
there can exist multiple records with the same name - for example,
there are multiple McDonald's in town. Each location would be its own
record. As the user typed they would see the combo expanded, and may
see McDonald's in Column 1, and "Nashville" in Column 2, and for cases
where necessary, the street name in Column 3.
Eventually, I wanted to look for companies where I wasn't real sure of
the exact name, where the word I was using may not be the first word
in the actual company name - which wouldn't work in Combo137. So I
created a separate form for this search activity using a "Like"
command for the name field. An efficiency addict, my goal was to be
able to use the same control to do both types of searches. Using
DblClick, I made it easier to open the search form. But, I still
wanted to be able to use the users text in Combo137 to search both
ways. In creating a command button, I tried to capture the text from
Combo137 and carry it over to the search form, however, unless the
text was a match for a record in the query, Combo137 wouldn't allow it
as long as it was set to Limit To List, and we all know by now in this
story why that doesn't work.For some reason, tonight it hit me!
First, I moved the ID Number field to the last column - the fourth
column, or .Column(3). Then I set the "bound" column to the name
field, what I was searching by, and allowing me to set Limit to List
to No. The first search method still works, so that as I type letters,
the name in the combo updates. If I hit ENTER, the AfterUpdate event
now uses Combo137.Column(3) to find the record in the set. But this is
where it really gets fun.
Now, on afterupdate, it checks to see if column(3) has a value, and if
so, it finds the record in the set. If not, it opens the search form
and performs the search. For button pushers, I also added a button
next to the field which also calls the function - here is the code:
Private Sub Combo137_AfterUpdate()
Dim rs As Object
If Me.Combo137.Column(3) > 0 Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo137].Column(3), 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Else:
MyCompanySearch
End If
End Sub
Public Function MyCompanySearch()
Dim stForm As String
stForm = "ProspectSearch"
If IsNull([Combo137]) Then
GoTo OpenSesame
Else:
GoTo GrabValue
End If
GrabValue:
DoCmd.OpenForm stForm
Forms.ProspectSearch.findco = Me.Combo137
Forms.ProspectSearch.findcoButton.SetFocus
GoTo Search
Search:
Forms!ProspectSearch!SearchByLabel.Value = " by Company"
Forms![ProspectSearch]!findquery.Form.Filter = "Company Like '*" & _
Replace(Forms!ProspectSearch!findco, "'", "''") & "*'"
Forms![ProspectSearch]!findquery.Form.FilterOn = True
Forms![ProspectSearch]!findquery.Form.Visible = True
Forms!ProspectSearch!findco.SetFocus
Exit Function
OpenSesame:
DoCmd.OpenForm stForm
Forms.ProspectSearch.findco.SetFocus
End Function
For a hack like me, I was excited to figure this out finally. And by
posting this, perhaps someone else searching in the future will find
their answer here.
magmike