Create a form to filter records in a table 2

[doobybug]

Hi,

I need to create a form to perform a search in a table, with any fields found in that table. Can anyone help me pls?

 

[Mayhem9]

Bugger! I have replaced the old front end with the back end. This contains the current active files:

http://www.4shared.com/file/UF61tR0W/20100430_-_Tool_Log_v5531.html

Thanks again,
Darren

 

[MajP]

In the FE you gave me, the filter form is called
"frmFilterSearch" not "frmFilterSearchAdvanced"

However that should have caused an error in this line of code
DoCmd.OpenForm "frmFilterSearchAdvanced", , , , , acDialog
"The form ... is mispelled or does not exist"
Not sure how you would even get to this line
Me.Filter = Forms("frmFilterSearchAdvanced").getFilter
unless you have a different front end.

The form appears to still return a valid filter string using your current names.

 

[MajP]

BTW, I can not get the tool_log form to open at all. I get a Type Mismatch error. This is normally due to trying to link two tables in a query where the linking fields are different data types. However, the form is not based on a query, and all the combo queries do not include joins. So I have no idea where the problem is occurring. If you are not having this problem, then it further suggests that the front end you sent me is different than the current FE.

 

[PHV]

I get a Type Mismatch error. This is normally due to trying to link two tables ...
The most common is a compare in the WHERE clause ...

 

[Mayhem9]

In the words of James May - "oh cock!"

Sorry MajP,

I think what has happened is that in trying to figure it out, I have mixed and matched from a previously working version and ended up sending you the screaming heap that I did. Originally, I had kept two different filters in there and one was frmFilterSearchAdvanced and the other frmFilterSearchBasic. I decided I only needed one.

I checked and the version I am working on is correctly names, so I have no idea how I screwed things up. The filter form itself filters correctly. The error occurs when I try to return to the main form from the filter (i.e. 'apply filter' or go to 'selected tool').

Hi PHV,

That error is probably due to my incompetence (as outlined in most of my posts). My issue is that I get a run time error when applying the filter or going to a selected tool:

Run-time error '2465':

Microsoft Office Access can't find the field '|' referred to in your expression.

I will keep trying to see if I can stumble on the issue but I have tried again to upload the database that I am actually struggling with.

http://www.4shared.com/file/gG5dYvMy/20100423_-_Tool_Log_v5531.html

Thanks,
Darren

 

[PHV]

referred to in your expression
Which expression ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Mayhem9]

Hi PHV,

That is all the error message shows. Depending on which button I press a different section of the code is highlighted once I click debug:

Private Sub frmFilterSearch_Click()
  'put in your error checking
  DoCmd.OpenForm "frmFilterSearch", , , , , acDialog
  'code stops here until search form closed/hidden
  If CurrentProject.AllForms("frmFilterSearch").IsLoaded Then
          [COLOR=green]'this section of code is highlighted when the 'Go To Selected Tool' button is clicked in the filter form[/color]
[COLOR=red]Me.Filter = Forms("frmFilterSearch").getFilter[/color]
    If Not Me.Filter = "" Then
      [COLOR=green]'this section of code is highlighted when the Apply Filter button is clicked in the filter form[/color]
      [COLOR=red]Me.FilterOn = True [/color]
    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
  DoCmd.Close acForm, "frmFilterSearch"
  End If

End Sub

Thanks,
Darren

 

[PHV]

As the form frmFilterSearch is open as modal (acDialog) the code below the DoCmd.OpenForm is executed only when frmFilterSearch is closed !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

The following combination of the front end and back end works fine. I am able to filter and search with no problem.

http://www.4shared.com/file/TWZMlb_o/Tool_Log_3May.html

I think you may want to check what back end you are linked to. Try this and list the steps needed to recreate the problem. I can not recreate.

 

[Mayhem9]

Thanks MajP,

I will have to have a closer look at this when I get back this evening, as it tells me the path to the back end is incorrect (it is looking for your desktop). Is there a way to change this link or should I just make a new database and import the components?

I probably shouldn't have split it but I had missed the error before I did.

Thanks,
Darren

 

[MajP]

Yes there is a link manager that allows you to relink.

http://office.microsoft.com/en-us/access/HP051876231033.aspx

Nothing wrong with splitting it. That is not an error. Every time you move the backend into a different folder you will need to relink the tables.

 

[Mayhem9]

Thanks MajP,

I have linked the database and the filter works fine! I have no idea what you did but it has made a difference. Perhaps your presence helped because I went back to the one I uploaded (to make sure I wasn't going mad) and I still had the error.

The record number doesn't work now but I feel more confident about fixing that.

Cheers,
Darren

 

[MajP]

My guess it was a version control thing. You had the wrong backend linked to the new front end.

 

[MajP]

One reason the count does not work is I commented out the procedures in the OnCurrent event. If you un comment SubB it should work.

 

[MajP]

Also in the cmdFilter code add a line to update TotRec
...
End If
'add line below
Me.TotRec = Me.RecordsetClone.RecordCount
DoCmd.Close acForm, "frmFilterSearch"
End If
end sub

This will give you the correct count after applying the filter.

 

[Mayhem9]

Hi MajP,

My plan was to have the db working before splitting it but I hadn't noticed the problem when I did.

So when I modify the front end do I have to go through the link process again? I would had thought that modifications to the front end would have remained linked (i.e. they would have been linked when the modifications began).

Thanks for the code. I will give it a try. My next challenge is to go and clean up the code. Access enters it as you build events but is there any problem in moving it, so items are in sequence (i.e. the navigation buttons are together etc)?

Cheers,
Darren

 

[MajP]

No that is the beauty of it. The front end points to the backend. You can make lots of copies of the front end and each points to the backend. However, link path is static. If you move the backend the front ends need to relink to the new location.

So when you sent me the front end its path was set to some folder on your computer. I have to use the link table manager to point to the BE located on my computer. If I would move the BE to another directory on my computer, I then would have to relink the tables because the path is static.

No problem moving the code around. Look at places for encapsulating and compartmentalizing your code. I am in the habit for the majority of my code to build stand alone procedures, and then have the event procedures call the sub routines. So for example you have record movement events that all look something like this:

Private Sub LastRecord_Click()
On Error GoTo Err_LastRecord_Click
    DoCmd.GoToRecord , , acLast
Exit_LastRecord_Click:
    Exit Sub
Err_LastRecord_Click:
    MsgBox Err.Description
    Resume Exit_LastRecord_Click
End Sub

for first, previous,last,
This could be replaced with a single routine

Private Sub moveToRecord(lngRecord as long)
On Error GoTo Err_Record
    DoCmd.GoToRecord , , lngRecord
    Exit Sub
Err_Record:
    MsgBox Err.Description
End Sub

Then from your different buttons you can call it like so:
Private Sub NewRecord_Click()
moveToRecord(acNewRec)
End Sub

Private Sub LastRecord_Click()
moveToRecord(acLastRec)
End Sub

couple of other things
never use Docmd.close without other parameters, you may not close what you desire
docmd.close acForm, me.name

Also never use
docmd.doMenuItem...
this is deprecated, unreadable, and may change with version. It has been replaced by
docmd.runcommand acCmdSomeConstant

you normally can find the correct constant using intellisense. Type acCmd followed by the next logical letter. if I type
docmd.runcommand acCmdDe...
I will start seeing things with the word delete, eventually seeing
acCmdDeleteRecord

 

[Mayhem9]

Hi MajP,

I wonder if you would have time to take a look at my DB for me and in particular the filter that you created, as I have attempted (without success) to add in the SubCategory field.

I think the problem is that this field stores a number into the table, which was the only way I could get the SubCategory to link to the Category. So if you select Screwdriver from the Category list, only variations of Screwdrivers are made available in the SubCategory menu.

I don't need to see the SubCategory listed in the filter, as it will always be used in conjunction with the Category.

Any help would be greatly appreciated.

http://www.4shared.com/file/Ae0prHMo/20101008_-_Tool_Log_v56_sub_ca.html

Thanks
Darren

 

[MajP]

Here is the update:

http://www.4shared.com/file/GPDzhYiC/Maj_P_Tool_Log_20101008_.html

Several Problems had to be fixed:

1) In the tool log table the subcategoryID foreign key field was set as "text" not number so it was saving the value as a string. Until that was fixed nothing would work.

2) In the get filter code you were concatenating the string with
' And
' Or

This worked before because all of the search criteria were strings, but now this is an integer. The single apostrophe was needed to close the other opening apostrophe. But now you wanted a filter string like

categoryID = '1/2 drive' AND subCategory = 11

The way it was previously designed you would have ended up with apostrophes around the 11
categoryID = '1/2 drive' AND subCategory = 11'
This was modified by adding the apostrophes in seperately

3) The filter string never added the "strSubCategory" so that needed to be added.


However, the next step would be to actually show the "subCategory" in the main form not the subCategoryID. To do that the lstSearch rowsource query needs to join subcategory table to the tool_log table by subcategoryID. In the list rowsource include both the subcategoryID field and the subcategory description field but only show the descripion field.

 

[Mayhem9]

Thanks MajP,

I will look over the code to see if I can follow the changes. I knew the text/number issue would be a problem but couldn't figure out how to get past that.

I will try to see if I can get the subcategory to show in the filter results but it isn't an essential component (would look nice though).

Thanks again,
Darren