Database Form Query

[KeithG]

Your text box name is the problem "Profile#". You are getting the error because the # sign in the textbox name. You can either put the name in brackets Me.[Profile#] or rename the box to something like cmbProfile which I would recomend.

 

[mogul0212]

Your text box name is the problem "Profile#". You are getting the error because the # sign in the textbox name. You can either put the name in brackets Me.[Profile#] or rename the box to something like cmbProfile which I would recomend.

Oh ok, thanks for the info. Got it working. Again, I really appreciate all your help with this. You have saved my head from further bruising as I was beating it into my desk for a few days now.

I didn't know the whole issue with symbols in field names would be a problem. I should be able to roll forward without too much problem now.

Do I need to change anything code wise if I have a few criteria that are "yes/no" check boxes?

 

[KeithG]

Do I need to change anything code wise if I have a few criteria that are "yes/no" check boxes

Yeah instead of checking to see if the combo box is null

if isnull(me.cmbProfile)=False then

You would need to see if the check box was checked


if me.chkProfile=-1 then

 

[mogul0212]

Yeah instead of checking to see if the combo box is null

if isnull(me.cmbProfile)=False then

You would need to see if the check box was checked


if me.chkProfile=-1 then

Ok, I've named the check box on the form "chkUsedFFallsVeneer" (no quotes)


If Me.chkUsedFFallsVeneer = -1 Then
If IsNull(strWhere) = False And Len(strWhere) > 2 Then
strWhere = strWhere & " AND ([Profile # or Name]='" & Me.cmbProfile & "')"
Else
strWhere = " Where([Profile # or Name]='" & Me.cmbProfile & "')"
End If
End If


I'm assuming the code needs to change for the "then" statment. But what type of syntax do I need to use then?

 

[KeithG]

The correct format is below.

if Me.[NameOfCheckBox]=-1 then
     if Len(strWhere)>2 then
          strWhere= strWhere & " AND [FieldNameRelatedToCheckBox]=-1"
     Else
          strWhere= " Where [FieldNameRelatedToCheckBox]=-1"
     End if
End if

 

[mogul0212]

Got it. You rock. If there was a Internet Bar, I'd be buying the rounds for a while for sure.

Last question for a bit. I have two criteria that I want to put on the form that are Notes and Description that would have key words in them.

How do I set up a text box to allow for someone to type *whatever* or any word and the search looks for that word/phrase anywhere in that field? With the * meaning anything before or anything after

 

[KeithG]

if isnull(me.txtNotes)=False then
     if len(strWhere)>2 then
          strWhere= strWhere & " AND [Notes] Like '*" & Me.txtNotes & "*'"
     Else
          strWhere= " Where [Notes] Like '*" & Me.txtNotes & "*'"
     End if
End if

The above will check a textbox to see if it contains data and if so it will take the value from the box and search the notes field.

 

[mogul0212]

if isnull(me.txtNotes)=False then
     if len(strWhere)>2 then
          strWhere= strWhere & " AND [Notes] Like '*" & Me.txtNotes & "*'"
     Else
          strWhere= " Where [Notes] Like '*" & Me.txtNotes & "*'"
     End if
End if

The above will check a textbox to see if it contains data and if so it will take the value from the box and search the notes field.

Everything you've given me has worked just great. I have just one last question before I assmeble everything based on what you've given me. (I hope!) If you remember from my "Tooling Information" form, I had a subtable.

I've tried to setup up a pull down menu to search based on criteria from that, but I get every record when I try.

Because it's a subform, do I need to have a completely separate search form that opens a completely different designed form from my "Tooling Information" form?

Does any of that make sense?

 

[KeithG]

Do you have the sub-form linked to a field on the main form?

 

[mogul0212]

There is a link in the relationship window I believe.

The "Individual Tool Information" tool does have the "NC Tool #" field in it as well. That link allows the subtable in "Tool Information" form to change as the "NC Tool #" changes as you go through the form results.

So does that answer you question?

 

[KeithG]

It looks like the forms are linked on the NC Tool # field. So if the tool # is 004 the subform will sow each tool with that number. Is that what you want? Sorry I am a little confused.

 

[mogul0212]

It looks like the forms are linked on the NC Tool # field. So if the tool # is 004 the subform will sow each tool with that number. Is that what you want? Sorry I am a little confused.

I'm sorry to confuse you. What I guess I'm trying to ask, I would like to use some of the fields in "Individual Tool Information" table to search for results as well.

Bascially, if any of the "Individual Tool Information" fields are used in a search, it filters the results in the "Individual Tool Information" subform on the "Tooling Information" form.

Right now all the data I have in my database is just generic crap just to have an entry in it to test for results, evetually I'll have 1000's of "NC Tool #'s". With Each NC Tool # having a subtool number called "Individual Tool ID." The NC Tool # is a group of tools, and the "Individual Tool ID" is a specific tool. Say have I have 10 tools, exactly alike, but 2 of the 10 are made my Manufacture "X" and 8 are made by manufacture "Y". And at times, I may want to search for all the tools that Manufacture "X" has made for us.

Can I still use the same form (Tooling Information) to view these results?

I hope that claifies it a bit.

 

[KeithG]

So for example lets say you add a combobox called Individual Tool Id to your form. If it contains a value have the sub-Form only bring back tools with this ID? Is this correct?

 

[mogul0212]

So for example lets say you add a combobox called Individual Tool Id to your form. If it contains a value have the sub-Form only bring back tools with this ID? Is this correct?

Yes, but that would only bring back one result, because that is one physical tool. If that makes sense.

 

[KeithG]

Okay, you are correct that was a bad example. You can do that by applying a similar logic to the Sql Statement be hind the sub-form. Basically make the form modify the SQL statement behind the sub-form as well as the main form. If I have time today and came make an example for you.

 

[mogul0212]

Okay, you are correct that was a bad example. You can do that by applying a similar logic to the Sql Statement be hind the sub-form. Basically make the form modify the SQL statement behind the sub-form as well as the main form. If I have time today and came make an example for you.

Ok, I'll try my best to do that.

I did have one more question. Some of my drop down boxes will have the exact same info in many lines in teh drop down box, is there a way to delete/filter the duplicate out so each available entry is only listed once?

 

[KeithG]

Okay, I added Tool Vendor to the criteria form and I also based your drop down boxes on Grouped queries so each entry will only appear once

 

[mogul0212]

Okay, I added Tool Vendor to the criteria form and I also based your drop down boxes on Grouped queries so each entry will only appear once

Just so I can learn and understand how to do it for future reference. How did you change the drop down boxes to be based on Grouped queries so each entry is listed only once?

 

[KeithG]

I made a new query for each combo box and used the totals function in the query to group the records. Look at the quires in the example database.

 

[mogul0212]

wow, getting more and more complicated now huh? Going to take me a bit to figure all that out. Again, thank you for your help once again. Truly appreciate it.