Search Form

[asama-san]

i did try and it didn't work plz show me how

 

[rcdugge]

You might want want to give your db some good thought first. Have you had a look at the links I posted? If you solve this problem without taking into consideration the whole db, then you may end up doing yourself more bad than good. In any case, as you wish.

The Companies table will just be a list of companies, PK could be a unique 4 letter of the company (you would need to device what pattern) or it could be an autonumber PK. The advantage of having a 4 or 5 letter PK is that if you have a pattern, the chances of entering the same company twice would be slim, however, it would require more work from you when creating new records in your main table. So if you're not too bothered about that then just use an autonumber as the PK. I don't know what the date field is for but if you want one then include it.

This Companies table would now be referenced in your main table via a CompanyID field, so all the IBMs will be 1 in the main table if the first company you entered in the Companies table was IBM. Don't forget to ENFORCE REFERENTIAL INTEGRITY with Cascade Update.

So:

1. Create the new Companies table
2. Add all the companies into it
3. Duplicate say your IBM table, get rid of all the records and call this table something meaning because it will be the master
4. Add a new field, say CompanyID, Datatype - Number
5. In your original two tables, IBM and the other (can't remember the name), add the same new CompanyID field and in the IBM table write the number that IBM represents from the Companies table across all records. Do the same for the other table
6. Copy all the records from both tables into the new master table.
7. Create a query based on this new main table and call it qrySearch (you obviously would have deleted the original qrySearch query before doing this)

vbaInet,

I decided to go along with your suggestions and combine all of the questions and answers into one table with a company id in another, along with a query. I appreciate the help. The final step in this database would be after the include button has been clicked for it to be sent to a report. However, it doesn't seem to be working and instead of just that 1 question/answer being included, all of them are put on the report. How would I fix that error? Also, finally, is there a way that whatever was searched could go on the report above the selected question and answer. Thank you so much for your help

 

[vbaInet]

It's not really an error, you somehow need to filter the report using the WHERE argument of the OpenReport method. Think of it like how you would enter criteria in a query to filter the records. Have a look at this thread from pbaldy:

http://baldyweb.com/wherecondition.htm

Once you understand the syntax, substitute OpenForm for OpenReport.

 

[rcdugge]

It's not really an error, you somehow need to filter the report using the WHERE argument of the OpenReport method. Think of it like how you would enter criteria in a query to filter the records. Have a look at this thread from pbaldy:

http://baldyweb.com/wherecondition.htm

Once you understand the syntax, substitute OpenForm for OpenReport.

what i have now is DoCmd.OpenReport "qrysearchreport", , , "[YesNoFieldName]=" & Val(Nz(Me.Answer, 2)). how come the filter doesnt work?

 

[vbaInet]

Is the YesNoFieldName a Yes/No datatype?

How come you've called it YesNoFieldName? That's not very intuitive.

 

[rcdugge]

I have changed it to DoCmd.OpenReport "qrysearchreport", , , "[Include?]= ' " & Val(Nz(Me.Answer, 2)) &" ' "

it is a yes/no datatype. since it is a yes/no, would the val(nz(me.answer, "yes"))?

 

[vbaInet]

Just a Yes without the quotes, or you can put -1

And without Val()

And go back to your previous one.

 

[rcdugge]

When I have

DoCmd.OpenReport "qrysearchreport", , , "[Include?]= " & Val(Nz(Me.Answer, Yes))

an error comes up that says variable not defined.

yet it is a yes/no data type

 

[vbaInet]

-1 in that case and as previously mentioned you don't need Val()

 

[rcdugge]

as -1, it does not affect the report at all and there are no changes, but rather it prints the report which as all of the data on it

 

[vbaInet]

Let's see your db.

 

[rcdugge]

I dont know what I pushed, but the search and clear button have stopped working too. sorry.

 

[vbaInet]

Answer is not your [Include?] field. And please no double posting.

 

[rcdugge]

right. the name of the field that has the yes/no is Include?.

so what would the docmd statement look like if i want the row to show up on the report which contains a checked box in the include? field. I still do not understand. is val(nz(me.answer, yes)) wrong? should it just be me.yes? This is not a double post, I just do not understand coding enough to realize what the problem is. hence why I am asking

DoCmd.OpenReport "qrysearchreport", , , "[Include?]= ' " & Val(Nz(Me.Answer, Yes)) & " ' "

 

[vbaInet]

The same question was asked in another thread hence my double post statement.

Answer is not your checkbox field. You need to use the field that is your Yes/No field. And as mentioned several times, you don't need Val()

 

[vbaInet]

Just looking at your db now. Have you solved this problem? Basically you want to open the report based on the search results?

 

[rcdugge]

ooooo. do i need another field then. So the include? field has checkboxes. however, the TYPE of field it is is yes/no.

i havent solved the problem yet. bascially after doing the search, I would like to check off the box and click the add button which I would like to add to the report. then, for example, i would like to be able to go back and continue doing searches while each time if found what i want, i could check it and add it to the report.

 

[vbaInet]

No you don't need any extra fields.

See if the attached is what you're after.

 

[rcdugge]

the only additional thing would be could the words you search show up above the items you selected? i know that would be a tough one.

 

[rcdugge]

btw, you have helped me so much. i really appreciate it!