Using a Combo Box to Find Records

[GRIFFO]

Hi,

I have successfully used Allen Browne's method to use a combo box to find a
record many times, it is excellent. Thanks Allen. But I was wondering if
anyone could help me with some code to do the same, but on a sub form.

Database structure: One program has many projects.

That is, I have a one to many relationship between tblProgram and tblProject.
The main form has the tblProgram data, with a subform using the related
records in tblProject.

I currently have a combo box that lists and navigates to the Programs in the
list, but I would like a similar combo box on the subform, to navigate to the
relevant (filtered) projects.

I have tried to use the code of allen at http://allenbrowne.com/ser-03.html
for the filtered subform, but to no success. It gets the filtered error
message.

I can get the correct data appearing in the list, but the navigation part
using the code in the after update event doesn't work for me.

Any assistance would be appreciated.

Griffo

 

[GRIFFO]

Hi,

I have successfully used Allen Browne's method to use a combo box to find a
record many times, it is excellent. Thanks Allen. But I was wondering if
anyone could help me with some code to do the same, but on a sub form.

Database structure: One program has many projects.

That is, I have a one to many relationship between tblProgram and tblProject.
The main form has the tblProgram data, with a subform using the related
records in tblProject.

I currently have a combo box that lists and navigates to the Programs in the
list, but I would like a similar combo box on the subform, to navigate to the
relevant (filtered) projects.

I have tried to use the code of allen at http://allenbrowne.com/ser-03.html
for the filtered subform, but to no success. It gets the filtered error
message.

I can get the correct data appearing in the list, but the navigation part
using the code in the after update event doesn't work for me.

I have tried to use this code I found in the forum by Marshall Barton (MVP),
but get an error message 2465 about not finding the field "sfrmProject"
referred to in the expression.

The code I tried was

Private Sub cboMoveTo_AfterUpdate()

On Error GoTo ErrorPoint

Dim rs As DAO.Recordset

Set rs = Me!sfrmProject.Form.RecordsetClone
rs.FindFirst "lngProjectID = " & Nz(Me!cboMoveTo, 0)
If Not rs.NoMatch Then
MsgBox "Project not found: filtered?"
Me!sfrmProject.Form.Bookmark = rs.Bookmark

End If

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " _
& Err.Description, vbExclamation, _
"Unexpected Error"
Resume ExitPoint
End Sub


Any assistance would be appreciated.

 

[Jeanette Cunningham]

Griffo,
where you use the name of the subform as sfrmProject, you must use the name
of the subform control instead of the name of the subform.
The subform is inside a subform control in much the same way as an image is
inside an image control.
The name of the subform control may be different from the name of the
subform.

Jeanette Cunningham

 

[GRIFFO]

Hi,
Thanks, but I am using the subform control name, not the subform name. I
even went in and renamed the subform control just to make sure. Access still
wont recognise it, so the problem must be elsewhere. (maybe between keyboard
and chair).

The exact error message is "Microsoft Access cant find the field
'sfrmObjectProject' referred to in your expression." Error 2465.

 

[Jeanette Cunningham]

Griffo,
I'm not sure why you are using subforms for this - it sounds like the sort
of thing people often do with cascading combos.
You could have 2 combos on the main form.
After users select the Progman in the first combo, the second combo has its
row source set to only the projects that are in that program.
After user selects the project, you can populate the subform with the
appropriate project details.

Jeanette Cunningham