Search Form

[vbaInet]

Is the field actually a Yes/No field?

Did you convert it to a String?

The syntax is still wrong which is why I'm trying to assert what is the value of Me.Anser or Me.qrySearch? I thought qrySearch is the name of the query?

 

[rcdugge]

I am not sure if it is a yes/no field or not.

I did not convert anything to a string. What do i need to convert to a string?

qrysearch IS the name of the query. wouldnt that be where i need the information from?

 

[rcdugge]

(sorry i am not getting this. it must be frusterating. a little too advanceded for me)

 

[vbaInet]

Take things one step at a time and you will get it. In that link I gave it explains that the FieldName argument should be a field name not the name of the query. The query name goes in the first parameter which you had it as before.

So does Me.Answer return a 0/-1 or Yes/No value? Look in the table what the values are.

You will know whether the field is a Yes/No field by looking at the datatype of the field. Again, look in the table for the datatype of that field.

 

[rcdugge]

ok. sorry vba. well Answer is the field that has the answers of the questions which were part of the data set. Therefore, i should change me.answer to me.yesnofieldname since that is the fieldname that i named it. however, in the query, the boxes are not there. only in the forms because that is where i added them. i initially created a check box in the sub form so the checkboxes are only in the form and subform. Therefore, i am not exactly sure what it shows becuase it is only in the form, as you can see on the db since i cannot get to the datatype of the field on the form.

 

[vbaInet]

We're getting somewhere. Like I mentioned, you wouldn't be able to curb the situation of being able to select one checkbox without having it bound and when I say bound I mean, there must be a field in the table itself as a Yes/No field, not via an alias. The checkbox would then be bound to this field. So, what you do is create a Yes/No field in your table and set the control source of the checkbox to this new field.

Sort that out then you will see that your query will not show 0s anymore but a checkbox.

 

[rcdugge]

vba, i did as you said and added a yes/no field name in both tables and then with the check boxes in the form, put their record source as the field in the table(labeled "Include?"). however, in the union all query it still shows up as either 0/-1 .

 

[vbaInet]

It must be the union that is displaying the actual value in place of the control but that's not a problem.

So your code will look like this:

DoCmd.OpenReport "qrysearch", , , "[[COLOR=Red][B]YesNoFieldName[/B][/COLOR]]=" & Val(Nz(Me.Answer, 2))

But you need to change YesNoFieldName to the actual name of the Yes/No field if it isn't called that.

 

[rcdugge]

ok, i put that as the code. it is still not working and not only that but in that awesome form that we created where we could look up certain criteria, you can no longer check it off.....only in the table can you check it off. i have the db down below

 

[vbaInet]

I completely forgot that Union queries are not updateable. Can't do that with a union I'm afraid. Either you create a query an change the Join to an Right Join or you create your form based on one of the tables.

 

[rcdugge]

How would you suggest I go about it? Could i make a subtable feed into a table and then just replace the query with the table? i am not sure how exactly a right join works and i could try figuring it out BUT would it require more work and reformatting than just creating sub tables(if that is even possible). i would like to keep the tables seperate but if worse comes to worse i could combine them

 

[vbaInet]

Let me just clarify, what you have in the sample db, are those the only tables present in your original db?

 

[rcdugge]

In the real database, there are probably going to be more like 15-16 tables and growing. Everything else for the most part should stay relatively the same.

 

[vbaInet]

"Probably" sounds like these tables haven't been created. The problems you're experiencing now are as a result of lack of normalisation. Those two tables should be one, and there should be another field for entering the name of the company. But that isn't still fully normalised because you could have another table with the list of companies and create a field in your main table to reference the PK in the Companies list table. If you don't normalise then you can't cross this hurdle.

To read up more on this here are some links:

http://r937.com/relational.html
http://support.microsoft.com/kb/100139

By the way, we would normally request an OP to post a db when we feel the problem cannot be resolved without seeing it so it's not necessary to continually post your db without me requesting.

 

[rcdugge]

Sorry about that VBA. Right now the db has 10 tables, each with about 30-40 questions and answers. I still need to enter in the next 5-6 tables but just havent started since I want to get this done first. I can only see the 2nd link because the server I am on is blocking the first one.

So to get over this hurdle, as you said, I could put all of the Q&A and the checkbox field in one table corresponding to either a 1,2,3,4,5...... and then in another table put a field for company, date, and PK(primary key being the numbers 1,2,3,4,5). Then build a relationship. Would that get over the problem that we had?

 

[vbaInet]

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)

 

[asama-san]

hi can someone help me i whant to make multi search it work fine with one search but i whant to make it two search ???? this is the code for the search (access 2007)

Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString & txtSearchString1) = 0 Or IsNull(txtSearchString & txtSearchString1) = True Then
MsgBox "You must enter a search string."

Else

strField = UCase(cboSearchField.Value)
strSrchStr = UCase(txtSearchString.Value)


'Filter Employee based on search criteria

Form_frmEmployee.RecordSource = "SELECT * FROM Employee WHERE [" & strField & "] Like '*" & strSrchStr & "*'"



'Close frmSearch
DoCmd.Close acForm, "frmSearch"

MsgBox "Results have been filtered."

End If
End Sub

 

[vbaInet]

hi can someone help me i whant to make multi search it work fine with one search but i whant to make it two search ???? this is the code for the search (access 2007)

Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString & txtSearchString1) = 0 Or IsNull(txtSearchString & txtSearchString1) = True Then
MsgBox "You must enter a search string."

Else

strField = UCase(cboSearchField.Value)
strSrchStr = UCase(txtSearchString.Value)


'Filter Employee based on search criteria

Form_frmEmployee.RecordSource = "SELECT * FROM Employee WHERE [" & strField & "] Like '*" & strSrchStr & "*'"



'Close frmSearch
DoCmd.Close acForm, "frmSearch"

MsgBox "Results have been filtered."

End If
End Sub

Look at the sample databases within this thread and if not successful post a NEW thread.

 

[asama-san]

plZZZZZZZZZZ i need help fast this cod work fine but i want multi search

Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

strField = UCase(cboSearchField.Value)
strSrchStr = UCase(txtSearchString.Value)


'Filter Employee based on search criteria

Form_frmEmployee.RecordSource = "SELECT * FROM Employee WHERE [" & strField & "] Like '*" & strSrchStr & "*'"



'Close frmSearch
DoCmd.Close acForm, "frmSearch"

MsgBox "Results have been filtered."

End If
End Sub

 

[vbaInet]

See my my last post, create a new thread. In the meantime look into using AND or OR.

WHERE LIKE '*' & [fieldname] & '*' [COLOR=Red][B]AND[/B][/COLOR] LIKE ...