Full Version: Search Records On Continious Forms.
UtterAccess Forums > Microsoft® Access > Access Forms
jazzthieve
I have a form (with subform) which is based on a query who's table relation looks like this:
attachment=46176:relation.jpg]
I want to filter all records of the field "Cathoofd". The way I do it now is on a main form I have an unbound combo box control with it's row source set to Categorie.tblamaltheesub (the one side of the relation), in its the after update event there's a search for record macro.
The subform is a continous form which shows the records of the many side of the relation.
The macro filters out the records fine when I make a selection in the combo box. However I would like to do this with vba because I would like access do show a message box when there are no records found. With the macro action when no records are present the continous form would just show the previously found records which is undesirable.
I have limited knowledge of vba and I have looked at previous search/filter examples but most code is too much (complicated) for what I want to accomplish.
theDBguy
Hi,
That version of Access are you using? For starters, you could convert your macro into VBA and then modify the result to add the extra steps you need.
Just my 2 cents...
jleach
Hi,
Have you looked at using the BookmarkProperty of a recordset (or recordsetclone)?
HAs a side note, I'd advise a change in field names so special characters (?, /, * () etc) are not part of the field names - they can cause strange things to happen sometimes.
hth
jazzthieve
Hi DBguy, I'm using Access 2007.
tried this in the after update event of the combo box on the main form
CODE
Private Sub Combo63_AfterUpdate()
  Dim strSQL As String

strSQL = "[Categorie] = " & Me![Combo63]
DoCmd.ApplyFilter wherecondition:=strSQL
End Sub

But that doesn't work, it gives me a data type mismatch error. I'm also not sure if this is the way to do it.
@ jLeach, my experience with vba is not much so I don't realy know how to work with DAO recordsets.
theDBguy
Hi,
Is Categorie a text field? If so, try modifying the code to this:
strSQL = "[Categorie]=" & Chr(34) & Me![Combo63] & Chr(34)
Hope that helps...
jazzthieve
Yes, that did it. Thanks.
Just one more question. The field 'categorie' is indeed a text field, but in the future there's a possibility that it might become an integer (the field is a seven digit number but my boss is considering of perhaps adding a letter to the end of it or not). If it would be an integer, what would strSQL have to look like?
theDBguy
Hi,
The way you originally had your code is already set up for numeric fields.
Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.