Access Search form-code to select method?

[Donkeygirl]

O.k. I have a newspaper catalog database that I am trying to create, so that we can find articales when we need them. I have the entry form all set, and now I am having trouble on the search side, which of course seems to be difficult.

The search form is being set up to allow the user to search by date, date range, articale title, and the most difficult keyword(s).
while I know how hard that will be, it is probably the most important capability to give to this search form.
For right now, I need to get the basic code working so that searching by date(s) and title work, and then I will focus in on the keyword(s) component.

Database info: 1 table with a primary key 'articaleid'
'description' field 255 length text which has a non punctuated sentence or list of buzz words:
mayor supports economic cushion for city school board to recover from deficit in budget as projects get off the ground

that is just side not info for the keyword in case someone already knows how to handle that.

Here is the big part.
On the form, I have the onclick to open a results form of articale matches to the search.
1st there is an option group to choose which search method. That enables that field on the form. Then they can fill it in appropriately. Now, If that field is null a msgbox tells them that, otherwise it opens form, based on field criteria.
My code is not right. Can anyone help me with this?
here is what I have:

Private Sub cmdsearch_Click()

If Me.optsearch = 1 And Me.txttopic = False Then DoCmd.RunMacro Mtopic

ElseIf Me.optsearch = 1 And Me.txttopic Is Not Null Then
DoCmd.OpenForm "frmresults", acNormal, , Me.txttopic = Forms![frmresults]![txtDes], acFormPropertySettings, acDialog

ElseIf Me.optsearch = 2 And Me.txtdate = False Then DoCmd.RunMacro Mdate

ElseIf Me.optsearch = 2 And Me.txtdate > 1 Then DoCmd.OpenForm "frmresults", acNormal, , Me.txtdate = Forms![frmresults]![txtADate], acFormPropertySettings, acDialog

ElseIf Me.optsearch = 3 And Me.txtdate = False Or Me.txtdates = False Then DoCmd.RunMacro Mdates

ElseIf Me.optsearch = 3 And Me.txttopic > 1 And Me.txttopic > 1 Then DoCmd.OpenForm "frmresults", acNormal, , "Forms![frmresults]![txtADate] Between '#" & Me.txtdate & "#' And '#" & Me.txtdates & "#'", acFormPropertySettings, acDialog

ElseIf Me.optsearch = 4 And Me.txttitle = False Then DoCmd.RunMacro Mtitle

ElseIf Me.optsearch = 4 And Me.txttitle = "*" Then DoCmd.OpenForm "frmresults", acNormal, , Me.txttitle = Forms![frmresults]![txtATitle], acFormPropertySettings, acDialog

Else: DoCmd.RunMacro Mnothing

End If
End Sub

___________________________

Now I was working on it this weekend, and I have it redone with dim code to declare everything at the beginning and it is probably closer to correct syntax. That can only mean one thing. I didnt bring it in from home. If I am told that this is completely wrong, I will post that code next. Please help.
Thanks in advance.

Donkeygirl,
Kickin' the crap out of Access

 

[paulbasel]

Set a breakpoint on the first line of your code and then run it, toggling each line to see which is the offending line of code in your routine.

paulbasel

 

[ScottMusich]

I just created a search form (based off of code from another site for SmartAccess - if you want I can email you the download I got of the example database!

 

[Donkeygirl]

That would be great if you could email me that, and also, I have tried to do that toggle break point thing, but I don't understand how it works lol.
courtneyfay@hotmail.com

Donkeygirl,
Kickin' the crap out of Access

 

[paulbasel]

If you are going to be writing VBA code, I strongly urge you to learn how to debug your code. Look in the Access Help menu under "debugging code - how to". It will make your life a lot easier and you will learn how to write cleaner, more efficient code.

PaulBasel

 

[Donkeygirl]

well, I got some good help on this from Scott, and he helped me create the code I need. Only problem is that while it works for one search method, when you add the code for a second one it doesnt like the Where any more.
Here is the full happening:

Code that works:
_______________________________________
Private Function IncludeTitle() As String

'-- Create the Title Where portion of the SQL statement

If Not IsNull(Me!txttitle) Then
IncludeTitle = "(([ATitle])Like " & "'*" & Forms!frmSearch!txttitle & "*'" & &quot"
End If

End Function
______________________________________


Private Function RequerySubform()

Dim strTitleSQL As String

Dim strWhereSQL As String
Dim strFullSQL As String


'-- Store all the criteria for the Where statement
'-- into variables.

strTitleSQL = IncludeTitle()

'-- Store the initial Where statement with whatever is from
'-- the Category criteria.
strWhereSQL = "Where " & strTitleSQL

'-- If a Title was passed back, then add it to the Where clause.
If Len(strTitleSQL) <> 0 Then

If strWhereSQL <> &quot;Where &quot; Then
strWhereSQL = strWhereSQL & &quot; And &quot;
End If
strWhereSQL = strWhereSQL & strTitleSQL
End If

'-- Create the new SQL String and Store it to
'the Recordsource.

strFullSQL = &quot;Select * From tblArticles &quot; & strWhereSQL
Me!frmresults.Form.RecordSource = strFullSQL


End Function

_________________________________________

CODE THAT DOES NOT WORK:

_______________________________
Private Function IncludeTitle() As String

'-- Create the Title Where portion of the SQL statement

If Not IsNull(Me!txttitle) Then
IncludeTitle = &quot;(([ATitle])Like &quot; & &quot;'*&quot; & Forms!frmSearch!txttitle & &quot;*'&quot; & &quot&quot;
End If

End Function
___________________________________

Private Function IncludeTopic() As String

'-- Create the Topic Where portion of the SQL statement

If Not IsNull(Me!txttopic) Then
IncludeTopic = &quot;(([ATopic])Like &quot; & &quot;'*&quot; & Forms!frmSearch!txttopic & &quot;*'&quot; & &quot&quot;
End If

End Function
_________________________________
Private Function RequerySubform()

Dim strTitleSQL As String
Dim strTopicSQL As String

Dim strWhereSQL As String
Dim strFullSQL As String


'-- Store all the criteria for the Where statement
'-- into variables.

strTitleSQL = IncludeTitle()
strTopicSQL = IncludeTopic()

'-- Store the initial Where statement with whatever is from
'-- the Category criteria.
strWhereSQL = &quot;Where &quot; & strTitleSQL

'-- If a Title was passed back, then add it to the Where clause.
If Len(strTitleSQL) <> 0 Then

If strWhereSQL <> &quot;Where &quot; Then
strWhereSQL = strWhereSQL & &quot; And &quot;
End If
strWhereSQL = strWhereSQL & strTitleSQL
End If

'-- Store the initial Where statement with whatever is from
'-- the Category criteria.
strWhereSQL = &quot;Where &quot; & strTopicSQL

'-- If a Topic was passed back, then add it to the Where clause.
If Len(strTopicSQL) <> 0 Then

If strWhereSQL <> &quot;Where &quot; Then
strWhereSQL = strWhereSQL & &quot; And &quot;
End If
strWhereSQL = strWhereSQL & strTopicSQL
End If
'-- Create the new SQL String and Store it to the Recordsource.

strFullSQL = &quot;Select * From tblArticles &quot; & strWhereSQL
Me!frmresults.Form.RecordSource = strFullSQL


End Function
____________________________
THANK YOU ALL SO MUCH
Please excuse the text wrapping that may be present.
Donkeygirl,
Kickin' the crap out of Access