Search Form

[rcdugge]

Hi, my name is Robbie and I was hoping someone could help me with some VBA programming on Access. I would like to have a search form where I can enter data in a box and click a search button to search all of my queries to find any like terms. Here are the specifics:

I have a couple of tables each with the field "company, date, question, and answer". In each table, the company and the date are the same, as the question and answer are all different. Forexample in Table "Walmart", every cell in the column company says "Walmart", every date in that table is "6/7/2010" and the questions and answers are different.

These all feed into a union select all query.

What I would like is a form that has the ability to look up any terms from the question and answer data I have in the query. I would like a box where you can type either information in and click a search button where the results of any questions or answers that have those words in them to show up below, along with the company and date. Therefore, if there is a data row with company "Walmart", date "6/7/2010", Question "How many jackets are there" and Answer "There are four jackets", and I search jackets, this row of data would show, along with any other questions or answers where the word like jackets shows up.

Also, if it is possible, I would like to be able to check a box next to a response that would send it to a report so that if I liked the results, i could check a box, send it to a report, then search another question/answer and if i found another data set, i liked, i could check the box on that one and send it to a report as well. Ideally, all of the data that i selected would go neatly into a report.

I appreciate any help I get on this as I am have some VBA code for the search button but it is not working correctly and I have no idea how to do the report idea. Please help. Thank you.

 

[vbaInet]

Welcome to the forum Robbie

Here's a tutorial for you (and there's a sample db on there too):

http://baldyweb.com/BuildSQL.htm

 

[rcdugge]

Thank you vba. At this point I have

Option Compare Database
Option Explicit
Private Sub btnClear_Click()
Dim intIndex As Integer
' Clear all search items
Me.txtquestion = ""
Me.txtanswer = ""

End Sub
Private Sub btnSearch_Click()

If BuildFilter = "" Then
Me.sub.Query.RecordSource = "Select * FROM query" & BuildFilter
Else
Me.sub.Query.RecordSource = "SELECT * FROM query WHERE" And BuildFilter
End If

' Update the record source
'Me.subfrm.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter

' Requery the subform
Me.frmsubClients.Requery
End Sub

Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varItem As Variant
Dim intIndex As Integer
varWhere = Null ' Main filter


' Check for LIKE Question
If Me.txtquestion > "" Then
varWhere = varWhere & "[Question] LIKE """ & Me.txtquestion & "*"" AND "
End If

' Check for LIKE Answer
If Me.txtanswer > "" Then
varWhere = varWhere & "[Answer] LIKE """ & Me.txtanswer & "*"" AND "
End If


Next

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function

However, the search will not work and I think i have quite a few errors. Nevertheless, the report part is not included and I have no clue how to go about that.

 

[rcdugge]

and one major problem is i dont have any relationships so I cannot simply do a sub form to get the data because it requires there to be a relationship.

 

[vbaInet]

I think it would be more beneficial for you to follow what is in the sample db found in the link. Then you will see how best to structure your code. There are lots of redundanies in your code and it will take longer to point them out.

By the way, what is Query in this line:

Me.sub.Query.RecordSource

?

 

[rcdugge]

Thank you all for your responses. At this point I have this(look at the attatchment). vba, i took a look at the db but couldnt quite figure out what to change of mine that would make it work.

 

[vbaInet]

Avoid using names like Datasheet, Query etc when naming a control, field, object or anything in Access.

See attached and read the notes.

 

[rcdugge]

Thank you so much VBAINET. This works great. Besides fixing the coding(which I am sure was a huge hassel), what else did you do? where is subfdatasheet?

 

[vbaInet]

You're welcome.

subfDatasheet is the name of the subform control. The subform is a control but the Control Source is the actual form. So, the subform control has a name and then there's the form used as the subform, which is (I think) called subfSearch in your forms list.

 

[rcdugge]

Once again, thank you so much for the help. This project is starting to look very good. I have added check boxes next to each result and I am searching the web looking for a method to have a checked results sent to a report or word document. Besides that though, i really appreciate your help.

 

[vbaInet]

You want to open up a report of all checked records?

 

[rcdugge]

Well i was hoping somehow there was a way to add a check box next to each result. Then, after searching, if I liked the result, I could check the checkbox and maybe click a button that would send it to a report or word. So if i did this for 6 different responses, then those 6 responses that I checked off would appear in word or a report. Or something like that.

Thanks again for the excellent db. I realized i was way over my head when i started (as was seen by the absolutly terrible coding)

 

[vbaInet]

Easily done on a report. Once you have the report up and running there's a button on the Ribbon that you can use to send to Word.

Have you created the report you would want to use for this? Create the report displaying all the data, that is based the report on a query that pulls all the records (without any criteria).

 

[rcdugge]

I have not created the report yet. Let me get this straight. So if I create a report based off of a query(obviously using the query that I used before would be easiest) that contained ALL of the data from all of the tables, I can somehow make it possible for the report to show only the results that were checked "yes" on the form which you helped build? How would you make that last part happen?

 

[vbaInet]

I have not created the report yet. Let me get this straight. So if I create a report based off of a query(obviously using the query that I used before would be easiest) that contained ALL of the data from all of the tables, I can somehow make it possible for the report to show only the results that were checked "yes" on the form which you helped build? How would you make that last part happen?

Correct. By using the WHERE argument of the OpenReport method and setting it to "[YesNoFieldName]=Yes"

Have a look at this:
http://baldyweb.com/wherecondition.htm

Change OpenForm to OpenReport.

 

[rcdugge]

vbainet,

So i inserted the code into the check box by building a code event. And this does not really seem to be working. All of the checkboxes are checked and when i uncheck one, all of them are unchecked. And i also must have messed up on the formula somehwhere because it keeps saying there is an error with the controlname. yikes. Thank you for the help(ive attatched what i have at the bottom of this post).

 

[vbaInet]

Much better for you if you can crack this one yourself.

Show me the code that you wrote then we can advise.

 

[rcdugge]

ok. Thanks vba. So I have created a report with all of the data on it like you said. Then i made a button on the form which should be clicked if you want to insert the selected criteria into the report. The code I have put in is:

Private Sub btnInsert_Click()

'I would like for the field "answer" to go onto the "search results" report

DoCmd.OpenReport "qrysearch", , , "[YesNoFieldName]=Yes '" & Me.Answer & "'"

End Sub


qrysearch=report
yesnofieldname=name of check box
answer=name of field that i want displaye on report
Error coming up: Run time error 3075. Syntax error(missing operator) in query expression. '[yesnofieldname]=yes 'sover......."

Also, another error i am dealing with is if i check one box, all of the boxes automatically become checked. If i uncheck one, they all become unchecked and I do not know how to fix this. Would i have to make it unconditional?

 

[vbaInet]

It was really incorrect syntax. What does Me.Answer return as a value?

When a control is not bound to a field and you have the form set to datasheet or continuous, that's what happens. You can't change the behaviour unfortunately.

 

[rcdugge]

I wouldnt mind having it bound to the field in the table. And i tried this but in the qry any unchecked values become zeros.

I changed me.answer to me.qrysearch because that is the name of the control source where the info is coming from but i keep getting an error.