Ok orange got me a copy of the database, if you are only using the subform FrmTestDescGam2 as a way to look up values for the main form, why not use a list box instead of a subform?
This is the code currently associated with the SUBFORM FrmTestDescGam2 ON CHANGE (which I have changed to ON EXIT) property of the SEARCHFOR field:
Code:
Dim vsearchString
vsearchString = SearchFor.Text
SrchText.Value = vsearchString
Me.SearchResults.Requery
If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
Exit Sub
End If
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus
DoCmd.Requery
Me.SearchFor.SetFocus
If Not IsNull(Len(Me.SearchFor)) Then
Me.SearchFor.SelStart = Len(Me.SearchFor)
End If
Can you tell me what you are trying to do with this code?
Be plain, step by step.
When I modify the RECORD SOURCE of the subform FRMTESTDESCGAM2 (Query QRY_TestDescGam) to:
Code:
SELECT TBL_TestDescGam.ID, TBL_TestDescGam.Abbrv, TBL_TestDescGam.Sch, TBL_TestDescGam.Size, TBL_TestDescGam.Source, TBL_TestDescGam.Technique, TBL_TestDescGam.Equip, TBL_TestDescGam.SizenActiveSize, TBL_TestDescGam.Exosure, TBL_TestDescGam.SFDnDFL, TBL_TestDescGam.OFD, TBL_TestDescGam.Film, TBL_TestDescGam.ScreenType, TBL_TestDescGam.Process, TBL_TestDescGam.Density, TBL_TestDescGam.IQILocation, TBL_TestDescGam.WireReq, TBL_TestDescGam.WireAchieved, TBL_TestDescGam.Material, TBL_TestDescGam.FocalnActiveSize, TBL_TestDescGam.FFDnDFL
FROM TBL_TestDescGam
WHERE (((TBL_TestDescGam.Abbrv) Like "*" & [Forms]![FrmRecommendation]![SearchFor] & "*"))
ORDER BY TBL_TestDescGam.ID;
And I remove the ON CHANGE property but add an ON EXIT property of the SEARCHFOR field of
Code:
Private Sub SearchFor_Exit(Cancel As Integer)
FrmTestDescGam2.Requery
RecFoundBox = DCount("*", "QRY_TestDescGam")
End Sub
The subform correctly updates with valid values in the ABBRV field and the record count field also updates correctly.
So my confusion is you seem to have a lot of extra code with no discernable end result