Revisiting an old thread - advanced search feature 1

[Mayhem9]

thread702-1584791

Well I knew it had been a while since I had been here, so I wasn't surprised to see the post cosed. Health issues have kept me from developing this as I had hoped.

I encountered an error with the subCategory field in the filter initially but I had figured this may have been because I hadn't actually added in the final category and sub category values for all the items.

When I select a category, the appropriate choices appear in the sub category drop down. However, once a selection is made the following message box pops up:

Run-Time Error '3464' Data Type Mismatch In Criteria Expression

To be sure I hadn't screwed anything up, I downloaded the last working version from the previous post and it worked OK. I then linked to the tables with the most recent data and the error exists. I am unsure where to begin to look and would appreciate any suggestions.

 

[MajP]

Mayhem,
Hope you are feeling better. Looks like the post has my handy work in it. The original issue you had was with the lookup fields in your table and this caused a chain of problems in queries and form design. As hopefully I showed these are just a bad idea. The biggest problem is that although the table appears to save a text field, it is often saving a numeric key instead. So I can take a pretty good guess without looking at your database.
Assume you have a table of People
1 john Smith
2 Mike Jones
3 Sue Brown

In a data table you save the foreign keys to the People, but the lookup displays the text. Assume that field is called PersonAssignedID. So you assume if you want to query all the records associated to John Smith you write a query
where PersonAssignedID = 'John Smith'
and thus a datatype mismatch in the where expression. Instead it should be
where PersonAssignedID = 1
Also you need to be careful in code, because the presence or lack of single quotes defines it as a string. This also gives a datatype mistmatch
where PersonAssigned = '1' (it thinks it is a string)
or if in fact it is a text field then this gives an error
where PersonAssigned = John Smith (missing quotes)

So check your data and design. I think I designed the form for long integers. Somewhere it is building a where statement that uses quotes when it does not need it or leaves out quotes where it does need it.
Verify you use no look up fields in the tables. lookup fields are evil and should be a form design not a table design.

I believe in the debug I print out the filter string. Post that because it would show the issue.

 

[Mayhem9]

Hi MajP,

As always, thanks for the help.

I can confirm that there are no look up fields in the tables. That was an early issue and I managed to sort that out as well as use more conventional naming. The Sub Category does show the text label in the form and stores the numeric value in the table. This is the only instance where the actual characters typed into a field in the form are referenced to a numerical ID in the table.

However, this structure is identical to the version that is working, which you posted in the initial thread. This is what has me stumped, as all I have done since that point is update data. It works until I link it to my tables, as opposed to the ones you had linked to. In adding the data, a number of entries for the category were updated and sub categories were added. All were done via the form and not directly into the table.

I believe in the debug I print out the filter string. Post that because it would show the issue.

I'm sorry but I don't actually understand what you are referring to here. I did run the report database structure module you designed and here are the results:

'documentqueries
MainQuery
   Manufacturer   dbText
   Serial   dbText
   Category   dbText
   SubCategory   dbText
   Size   dbText
   Set   dbText
   Location   dbText
SELECT tblManufacturer.Manufacturer, tblTool_Log.Serial, tblCategory.Category, tblSubCategory.SubCategory, tblTool_Log.Size, tblTool_Log.Set, tblLocation.Location
FROM tblLocation, tblCategory, tblManufacturer, tblSubCategory INNER JOIN tblTool_Log ON tblSubCategory.CategoryID=tblTool_Log.CategoryID
WHERE (((tblManufacturer.Manufacturer) Like Forms!SearchForm.qMan1 & "*") And ((tblTool_Log.Serial) Like Forms!SearchForm.qSerial1 & "*") And ((tblCategory.Category) Like Forms!SearchForm.qCat1 & "*") And ((tblSubCategory.SubCategory) Like Forms!SearchForm.qSub1 & "*") And ((tblTool_Log.Size) Like Forms!SearchForm.qSize1 & "*") And ((tblTool_Log.Set) Like Forms!SearchForm.qSet1 & "*") And ((tblLocation.Location) Like Forms!SearchForm.qLoc1 & "*"));

'documentrelations
tblCategory
   Category   dbText  PrimaryKey  Indexed

tblLocation
   Location   dbText  PrimaryKey  Indexed

tblManufacturer
   Manufacturer   dbText  PrimaryKey  Indexed

tblPurchase
   Purchase   dbText  PrimaryKey  Indexed

tblSubCategory
   SubCategoryID   dbLong  PrimaryKey  Indexed
   CategoryID   dbText
   SubCategory   dbText

tblTool_Log
   ToolID   dbLong  PrimaryKey  Indexed
   ManufacturerID   dbText
   Serial   dbText
   CategoryID   dbText
   SubCategoryID   dbText  Indexed
   Size   dbText
   Description   dbText
   Set   dbText
   Year   dbText
   RRP   dbCurrency
   PurchaseID   dbText
   Date   dbDate
   LocationID   dbText
   ImagePath   dbText
   ImagePath2   dbText

tblToolFile
   ToolFileID   dbLong  PrimaryKey  Indexed
   ToolID   dbLong
   FileType   dbText
   FileTitle   dbText
   FilePath   dbText

Thanks again,
Darren

 

[MajP]

Can you repost the most current database, both front end and back end?

I see the problem, but want to see what you have to determine which way to fix it.

I am not sure where you are at with this and where you plan on going. But if you plan to develop this and add more features, I recommend some clean up of the work we did prior. Because it was developed over time, there are some inconsistencies in the table design and naming conventions. Some of these were my original recommendation, but now I see a reason to modify. This will require some work on the tables, forms, and code but once done it would make future development a lot easier.

If you want to just put a bandaid on it, then that would take little time.

If this is about as far as you will be developing than maybe a band aid, if you plan to add a lot more to it and make it a multi-user database I recommend the bigger fix.

 

[Mayhem9]

Thank you MajP,

I have uploaded the Current version. I have not included the images or documents but I don't think these are necessary.

In reality, the band-aid solution is probably going to be sufficient as the only other things I had considered was showing the sub category in the filter results (although this is not essential) and possibly some reports but I haven't thought too hard about these yet. The primary purpose of this database is for inventory control and insurance purposes.

Having said that, I would like to learn how to bring it up to scratch but would require direction to do so. I thought I had sorted out the naming etc but obviously I have missed something. You are correct that this has be worked on sporadicly as time permits.

Thanks again.

 

[MajP]

In the subcategory pull down change the bound column to 2.
Then in the getFilter code change this line

If Not Trim(Me.qSub1 & " ") = "" Then
strSubCategory = "[SubCategoryID] = '" & qSub1 & "'" & andOR
End If

To the outside observer this will be very confusing.

Here is what is going on with the sub category. This is not your mistake, but because it evolved over time. If it was me I would change it, but should work as is.

The subcategory has a primarykey that is an autonumber, and a descriptive field. The truth is the primary key is never used. Instead you are saving the descriptive field into the tool log table as the foreign key. This made more confusing in that the field in the tool log for the foreign key is called
subCategoryID
However it is not storing the subCategoryID from the subCategory table, but the descriptive field. Instead of storing a value of 7 it is storing some text like "1/2 Drive".

So to filter that field it has to be treated like a text string.

So with the features you have now you are not going to have a problem, but this could cause future issues (or at least confusion).

See if that works.

 

[Mayhem9]

Thanks MajP,

I made the changes and it has fixed the error that I was getting. However, it has now revealed some other issues.

If you try to search with the search type set as "OR", the sub category doesn't appear to do anything (which makes sense given that it should narrow the tool type selected and therefore require "AND"). However, if I set the search type to "AND" it returns a "No

Something I just noticed was if I open the FilterSearch form from within the Tool_Log form and then switch the view to design, a VB error box appears, with the message:

Run Time Error '2465':
Application-defined or object-defined error

When I click on Debug, it takes me to the following piece of code for the Tool_Log form:

Private Sub cmdFilter_Click()
  'put in your error checking
  DoCmd.OpenForm "frmFilterSearch", , , , , acDialog
  'code stops here until search form closed/hidden
  If CurrentProject.AllForms("frmFilterSearch").IsLoaded Then
    [highlight]Me.Filter = Forms("frmFilterSearch").getFilter[/highlight]
    If Not Me.Filter = "" Then
      Me.FilterOn = True
    End If
    If Me.Recordset.RecordCount = 0 Then
      MsgBox "No Records Found"
      Me.Filter = ""
      Me.FilterOn = False
    Else
      MsgBox Me.Recordset.RecordCount & " Tools found meeting the Filter."
    End If
      Me.TotRec = Me.RecordsetClone.RecordCount
  DoCmd.Close acForm, "frmFilterSearch"
  End If

End Sub

This does not occur if the Tool_Log or SearchFilter forms are opened in design view from the control menu.

I would be interested to learn how to fix the design flaw so that when a sub category is selected in the Tool_Log form, it stored the text value in the Tool_Log table and not the numerical one. This would then allow me to display the SubCategory in the FilterSearch form

I have updated the Database

 

[MajP]

Something I just noticed was if I open the FilterSearch form from within the Tool_Log form and then switch the view to design, a VB error box appears, with the message

You should put some error checking there, but it is not a big deal. It only happens in this instance.
When you open a form as dialog the code that calls the pop up form stops until the pop up form closes, becomes visible, or put into design view. When you put it into design view the code in the calling form continues. The calling form tries to finish its code and it calls the getfilter function of the popup. But since it is in design view you get an error in the calling form.

I would be interested to learn how to fix the design flaw so that when a sub category is selected in the Tool_Log form, it stored the text value in the Tool_Log table and not the numerical one.  This would then allow me to display the SubCategory in the FilterSearch form

The fix I suggested should have fixed the subcategory in the search form. The fix leaves the textual description as the foreign key, but modified the search form to handle it. I will send you my front end to see if we are talking the same thing

If you try to search with the search type set as "OR", the sub category doesn't appear to do anything (which makes sense given that it should narrow the tool type selected and therefore require "AND").  However, if I set the search type to "AND" it returns a "No"

I am not sure if I understand this. I will test the or and switch back to And to see. What do you mean it returns a "No"

 

[Mayhem9]

Oops - sorry, that was meant to read "No Records Found

 

[MajP]

I fixed the front end. This is how the sub category combo works now.
If you select OR it returns all subcategories that exist in the tool log (does not return subcategories that do not exist in the tool log records). If you select And it returns all subcategories in the toollog that also related to the category in the type combo.
You will not see subcategories that do not exist in the tool log.

Go to the backend database and just change the subcategoryID field in the toollog from text to number. I was actually confused. It was saving the ID number, but saving it as text ("12" instead of 12).

See if this works:

http://www.4shared.com/document/gBurMlvW/20101221_-_Tool_Log_v62.html

 

[Mayhem9]

Thanks MajP,

That seems to have solved the problem. I really appreciate your assistance in this.