Find record based on Combo box selection after open with where clause

[btrini]

Hi all, I have a Review form that has a sub-form which lists all observations made for that particular review. I have a "VIEW" option for each record in the subform that allows you to open the Observation form and view or modify the selected observation. The "VIEW" option opens the observation form using a where clause. On the observation form, I have a combo box with a list of the Observation numbers for that same review. If I open the observation form not using the where clause, the combo box works and finds a record based on my selection in the combo box. However, if use the "View" option i.e. the where clause, the correct observation shows, but I am not able to use the combo box to go to other observations. I am guessing this is because the where clause has limited the records. Can anyone suggest a way to get around this. I would still like to use the "View" button to find a specific observation, but I would also like the user to have the option to use the combo box once the observation form is opened.

 

[PHV]

What is the RowSource property of your combo ?
BTW, please, define "I am not able to use the combo box to go to other observations"

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

 

[btrini]

Hi PHV. the following is the SQL statement in the rowsource property of the combo box.
SELECT RE_RECOVERIES.RE_OBSERVATION, RE_RECOVERIES.CASE_FILE_ID
FROM RE_RECOVERIES
WHERE (((RE_RECOVERIES.CASE_FILE_ID)=[Forms]![FORENSIC_OBSERVATION]![CASE_FILE_ID]));

When the review form is open, I have a button that allows me to open an observation form. The observation form opens to a new record. When the form opens, I am able to select a value from the combo box and it displays the matching record in the observation form. There is a field called CASE_FILE_ID on the observation form that gets it's value from the Review_form (which stays open in the background). So the Where clause above limits the combo box to only observations for that particular case.
Hope this helps.

 

[PHV]

So, the where clause of the rowsource property of the combo should references Forms!Review_form!CASE_FILE_ID instead of Forms!FORENSIC_OBSERVATION!CASE_FILE_ID

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

 

[btrini]

Would it matter, since Forms!FORENSIC_OBSERVATION!CASE_FILE_ID gets its value from Forms!Review_form!CASE_FILE_ID? I have no idea what difference it would cause?

 

[PHV]

Did you try it ?

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

 

[btrini]

Just tried and it did not make a difference. Still having the same issue.
I have a question for you PHV. Is the problem that the underlying source for the form being filtered and therefore the combo box is unable to find the record that matches the selection?

 

[PHV]

So,again, please, define "I am not able to use the combo box to go to other observations"
BTW, which code in any event procedure of the combo ?

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

 

[btrini]

Private Sub VIEW_Click()

Dim stDocName As String
Dim strFilter As String


stDocName = "FORENSIC_OBSERVATION"

strFilter = "[CASE_FILE_ID] = '" & [Forms]![REVIEW_FORM]![qry_FOR_OBSERVATIONS subform].[Form]![CASE_FILE_ID] & "' And [RE_OBSERVATION] = " & [Forms]![REVIEW_FORM]![qry_FOR_OBSERVATIONS subform].[Form]![RE_OBSERVATION] & ""

DoCmd.OpenForm stDocName, acNormal, , strFilter

Forms!FORENSIC_OBSERVATION.OBSERVATION = [Forms]![REVIEW_FORM]![qry_FOR_OBSERVATIONS subform].[Form]![RE_OBSERVATION]
End Sub

Above is the code for with the where clause.
Below is the code for the combo box.

Private Sub OBSERVATION_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[RE_OBSERVATION] = " & Str(Nz(Me![OBSERVATION], 0)) & " And [CASE_FILE_ID] = '" & Me.CASE_FILE_ID & "'"
If Not RS.NoMatch Then
Me.Bookmark = RS.Bookmark
Else
DoCmd.GoToRecord , , acNewRec
End If
Me.RE_OBSERVATION = Me.OBSERVATION
End Sub

The combo box works fine if I open the form without the where clause. My selection from the Combo box finds the record that matches the CASE_FILE_ID and the RE_OBSERVATION.

If I use a where clause to open the form, the corect record is found.

However, the combo box is no longer able to find matching records. For example, if my where clause finds Observation number 1, the form opens and displays observation 1. If I then select observation 2 in the combo box, observation 2 is not displayed.

 

[PHV]

What about this ?

Private Sub OBSERVATION_AfterUpdate()
' Find the record that matches the control.
Dim RS As Object
Me.FilterOn = False
...

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

 

[btrini]

It does not work if I put it into the AfterUpdate event. I am not seeing an after Load event for the form...but I am thinking it might work if I can get rid of the filter once the found record has already been displayed.

 

[PHV]

You may try this in the Current event procedure:
Me.FilterOn = False

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

 

[btrini]

That did the trick!!! Thank you very much PHV.

 

[btrini]

Actually PHV, I had to change it to the ON MOUSE DOWN event of the combo box. When I used the On Current event for the form, it did not show the filtered record.

Thanks again for your help.