Option Group /Search help

[Guest]

I have a seach form somewhat based on Allen Browne's search form. In the
Header portion of the form I have an option group and one text box. In the
Detail of the form, it will show the results.
The option group has Titles as one option or Authors as another option.
Depending on which option the user selects will determine how the search is
performed.
If the user selects Titles, types a title in the unbound text box (in the
header) all titles will show up in the detail of the form. not only will the
titles show, but all relavent information. (author,publisher, etc.) I can get
this to work without the option however I can only seach by title. I would
like to have the option to search either by titles or authors.

 

[Allen Browne]

Presumably you are using code based on this article:
http://allenbrowne.com/ser-62.html

If so, you are building up the WHERE clause based on the field, e.g.:
If Not IsNull(Me.txtFindWhat) Then
strWhere = strWhere & "([Title] Like ""*" & Me.txtFindWhat & "*"")
AND "
End If

Now you want to change the field name to look in, based on the value of your
option group, so you would need something like this:
If Not IsNull(Me.txtFindWhat) Then
If Me.[Group1] = 2 Then 'Author search
strWhere = strWhere & "([Author] Like ""*" & Me.txtFindWhat &
"*"") AND "
Else 'Title search
strWhere = strWhere & "([Title] Like ""*" & Me.txtFindWhat &
"*"") AND "
End If
End If

 

[Guest]

Thank you very much, that worked!

Presumably you are using code based on this article:
http://allenbrowne.com/ser-62.html

If so, you are building up the WHERE clause based on the field, e.g.:
If Not IsNull(Me.txtFindWhat) Then
strWhere = strWhere & "([Title] Like ""*" & Me.txtFindWhat & "*"")
AND "
End If

Now you want to change the field name to look in, based on the value of your
option group, so you would need something like this:
If Not IsNull(Me.txtFindWhat) Then
If Me.[Group1] = 2 Then 'Author search
strWhere = strWhere & "([Author] Like ""*" & Me.txtFindWhat &
"*"") AND "
Else 'Title search
strWhere = strWhere & "([Title] Like ""*" & Me.txtFindWhat &
"*"") AND "
End If
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a seach form somewhat based on Allen Browne's search form. In the
Header portion of the form I have an option group and one text box. In the
Detail of the form, it will show the results.
The option group has Titles as one option or Authors as another option.
Depending on which option the user selects will determine how the search
is
performed.
If the user selects Titles, types a title in the unbound text box (in the
header) all titles will show up in the detail of the form. not only will
the
titles show, but all relavent information. (author,publisher, etc.) I can
get
this to work without the option however I can only seach by title. I would
like to have the option to search either by titles or authors.

 

[Guest]

Thanks again for your help!
Ran into a road block...The record source for the search form is a table
called Books. This table has a few fields (ie. AuthorID, PublisherID, etc.)
The fields that have "ID" pulls it's information from other tables. In the
detail section of the form, searching by title, the title comes up, but
Author comes up with the AuthorID (which is a number) and not the Author's
name. Questions is, is there a way to set the Author textbox control source
to a different table?

I hope this make sense!!!

Thank you
Soni

Presumably you are using code based on this article:
http://allenbrowne.com/ser-62.html

If so, you are building up the WHERE clause based on the field, e.g.:
If Not IsNull(Me.txtFindWhat) Then
strWhere = strWhere & "([Title] Like ""*" & Me.txtFindWhat & "*"")
AND "
End If

Now you want to change the field name to look in, based on the value of your
option group, so you would need something like this:
If Not IsNull(Me.txtFindWhat) Then
If Me.[Group1] = 2 Then 'Author search
strWhere = strWhere & "([Author] Like ""*" & Me.txtFindWhat &
"*"") AND "
Else 'Title search
strWhere = strWhere & "([Title] Like ""*" & Me.txtFindWhat &
"*"") AND "
End If
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a seach form somewhat based on Allen Browne's search form. In the
Header portion of the form I have an option group and one text box. In the
Detail of the form, it will show the results.
The option group has Titles as one option or Authors as another option.
Depending on which option the user selects will determine how the search
is
performed.
If the user selects Titles, types a title in the unbound text box (in the
header) all titles will show up in the detail of the form. not only will
the
titles show, but all relavent information. (author,publisher, etc.) I can
get
this to work without the option however I can only seach by title. I would
like to have the option to search either by titles or authors.

 

[Allen Browne]

For a search form (not a data entry form), a simple solution is to use a
query that contains all the tables you need, so you have the fields from the
other tables also.

If you have no more than a few thousand authors, you could use a combo
instead of a text box for selecting the author. The combo would have a
RowSource like this:
SELECT AuthorID, Surname & ", " + FirstName AS FullName
FROM tblAuthor
ORDER BY Surname, FirstName;
and properties like this:
Column Count 2
Column Width 0
Bound Column 1
The combo's value is the first column (AuthorID), but since it is
zero-width, you see the second column (the name.)

If that's not suitable, you could use DLookup() to get the AuthorID for the
name the user typed in. For details on how to use DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks again for your help!
Ran into a road block...The record source for the search form is a table
called Books. This table has a few fields (ie. AuthorID, PublisherID,
etc.)
The fields that have "ID" pulls it's information from other tables. In
the
detail section of the form, searching by title, the title comes up, but
Author comes up with the AuthorID (which is a number) and not the Author's
name. Questions is, is there a way to set the Author textbox control
source
to a different table?

I hope this make sense!!!

Thank you
Soni

Presumably you are using code based on this article:
http://allenbrowne.com/ser-62.html

If so, you are building up the WHERE clause based on the field, e.g.:
If Not IsNull(Me.txtFindWhat) Then
strWhere = strWhere & "([Title] Like ""*" & Me.txtFindWhat &
"*"")
AND "
End If

Now you want to change the field name to look in, based on the value of
your
option group, so you would need something like this:
If Not IsNull(Me.txtFindWhat) Then
If Me.[Group1] = 2 Then 'Author search
strWhere = strWhere & "([Author] Like ""*" & Me.txtFindWhat &
"*"") AND "
Else 'Title search
strWhere = strWhere & "([Title] Like ""*" & Me.txtFindWhat &
"*"") AND "
End If
End If

I have a seach form somewhat based on Allen Browne's search form. In
the
Header portion of the form I have an option group and one text box. In
the
Detail of the form, it will show the results.
The option group has Titles as one option or Authors as another option.
Depending on which option the user selects will determine how the
search
is
performed.
If the user selects Titles, types a title in the unbound text box (in
the
header) all titles will show up in the detail of the form. not only
will
the
titles show, but all relavent information. (author,publisher, etc.) I
can
get
this to work without the option however I can only seach by title. I
would
like to have the option to search either by titles or authors.