Can't get my subform to filter correctly 1

[tomatojo]

Hi, I have a subform (Child?) on a form that shows filtered data. I want an additional button that will search the filtered data (data is sorted alphabetically) for the first occurrence of the item chosen in a particular column. The code I've tried is below (its some Filter code I got online but doesn't really work all the time). So I want to click my control button for the letter "Z" and it will search the third column fro the first item that begins with "Z". It sometimes works but not as I want it to. Then when I click, say, the letter "C", the filter maintains the "Z" search feature and doesn't clear. I'm hoping you can help me fix my code or give me a diff suggestion. Thnx!

I'm not a programmer but can work with code. Using Access 97.

Private Sub SearchZ_Click()

 Screen.PreviousControl.SetFocus
 SendKeys "%eZ%hs%e", False
 DoCmd.RunCommand acCmdFind
End Sub

 

[MajP]

Can you be a little clearer on the interface and order? This is my understanding
1) Main form with subform
2) You want to click in the subform on a specific column
3) You then want to leave that column and select some letter from another control. What is that control? Is is a combo with a single letter. Is it on the main form or the subform?
4) once selected you want to search the previous selected column


Regardless, the provided code is very poor and I would definately scrap it. The solution will be easy, but it is confusing of how the interface is supposed to work.

 

[tomatojo]

I uploaded a screen shot, hope you can see it (sorry but I'm new to this). So the form will be presorted by the Order column already (that part works, in my example, Diptera). Then the user would click, say, the letter "E" command button (on main form), then the pointer would go to the first item in the 3rd column (GenusSpecies) that begins with "E".

Your question #2 isn't necessary, I was hoping the VBA would accomplish this step. Your help is greatly appreciated!

 

[MajP]

Hilite all the command buttons.
in the on click event property type in
=findLetter()

add this to the code

Public Function findLetter()
  Dim rs As DAO.Recordset
  Dim strWhere As String
  Const subFrmName = "yourSubFormName"
  Const fldName = "your field name"
  
  Set rs = Me.Controls(subFrmName).Form.Recordset
  rs.MoveFirst
  strWhere = fldName & " Like '" & Screen.ActiveControl.Caption & "*'"
  rs.FindFirst strWhere
End Function

Change the constants to be the correct names.

 

[tomatojo]

OK, I tried your code and got the 2465 error, "can't find the field rptBuglist1". That (rptBuglist1) is the name of my subform, see screenshot at link below (unless I'm not calling this correctly). The code I used is below:
Any suggestions what I'm doing wrong here ('cause I got this same error before with something else I tried)?

Public Function findLetter()
  Dim rs As DAO.Recordset
  Dim strWhere As String
  Const subFrmName = "rptBugList1"
  Const fldName = "GenusSpecies"
  
  Set rs = Me.Controls(subFrmName).Form.Recordset
  rs.MoveFirst
  strWhere = fldName & " Like '" & Screen.ActiveControl.Caption & "*'"
  rs.FindFirst strWhere
End Function

 

[MajP]

I really should have called it subFrmControl.

The subform control is the container that holds a subform (source object). If you click inside the container you will get the properties of the source object. So you can select outside the control or inside the control. You need to select the outside. It is likely called something like "child1

 

[MajP]

here is a demo

http://www.4shared.com/document/75FzuSP-/AlphaSearch.html

 

[tomatojo]

Yours does Exactly what I'm looking to do but it just isn't working for me. Could it be because you're using Access 2000 and I'm using Access 97? Maybe 97 doesn't allow this code.

When I look at my form properties, the subform source object is "rptBugList1" as I said. But, just in case, I tried "child1" in your code and got the same error. Not sure what else to do.

 

[MajP]

Sorry for not being clear. You need to put in the name of the subform control not the source object. This will work in 97.

 

[MajP]

This should explain

http://www.4shared.com/photo/CIisT43e/SubformControl.html

 

[MajP]

Actually, that could will not work in 97 now that I think of it. 97 did not expose the forms recordset. It can be modified to work. I am heading out the door, but I will send an update. BTW how did you view the demo if you only have 97?

 

[MajP]

Here are two other versions. I think both work in 97.

Public Function findLetter2()
  Dim rs As DAO.Recordset
  Dim strWhere As String
  Const subFrmName = "subFrmBugs"
  Const fldName = "ProductName"
  Set rs = Me.Controls(subFrmName).Form.RecordsetClone
  rs.MoveFirst
  strWhere = fldName & " Like '" & Screen.ActiveControl.Caption & "*'"
  rs.FindFirst strWhere
  If Not rs.NoMatch Then
    Me.Controls(subFrmName).Form.Bookmark = rs.Bookmark
  Else
    MsgBox "No matching records"
  End If
End Function

Public Function findLetter3()
  Dim strWhere As String
  Const subFrmName = "subFrmBugs"
  Const ctrlName = "ProductName"
  Me.Controls(subFrmName).SetFocus
  Me.Controls(subFrmName).Form.Controls(ctrlName).SetFocus
  DoCmd.FindRecord "A", acStart, False, acSearchAll, , acCurrent
End Function

 

[MajP]

in findLetter3 change
DoCmd.FindRecord "A", acStart, False, acSearchAll, , acCurrent
to
DoCmd.FindRecord screen.activeControl.caption, acStart, False, acSearchAll, , acCurrent

 

[tomatojo]

findLetter2() works Great! Thanks MajP! findLetter3() also works (works without the edit you suggested in your last post, that gave me another error) but it would require more coding, so I will go with #2. Sorry for the delay in response but this is a work project and I don't work weekends. The program I created is used by all staff and they require the Access 97, but I also have a Access 2000 on my computer. You've been a great help, Much appreciated!

 

[MajP]

You could also add a textbox and allow the user to type part of a word.

So if they type "AE" and it would go to "Aedes"

You would just have to do a slight modification of the function. So you could have both the buttons and a textbox.