Bob Quintal said:
Bob Quintal said:
Without the Key ID field [PhoneRecsID] in [qryFindProduct] it
works but only if I bind the main form to the underlying
table. Here is a review:
main form unbound & no Key ID field in [qryFindProduct] --
repeating parameter query boxes
main form unbound & Key ID field in [qryFindProduct] --
repeating parameter boxes. Search eventually fails as
described in the original post. main form bound to main table
& Key Id in [qryFindProduct] -- search fails -- find no
records main form bound to main table & no Key ID in
[qryFindProduct] -- works (but defeats my original purpose)
Thanks again Bob, this is about as clear as I can make it!
--
Best Wishes,
Richard Harison
1) Does the [qryFindProduct] query return a valid recordset
with and without the tblPhoneRecs.PhoneRecsID field when opened
in datasheet mode as a query?
Yes -- perfectly
2) what are the properties of link parent fields and link child
fields in the subform?.- There should not be any.
None -- nothing related -- the database is flat file.
3) Are there filters set in the properties of the form or
subform?
No. They were "allowed" but none applied in either form
--
Bob Q.
PA is y I've altered my address.
Thank you, Bob
Answers to your questions noted above
-- Richard
Must be something in your code.
That's the most frustrating part!
The code is exactly the same as in the program that works.
I've stepped through the code and the line that's odd is this one:
'If no records match criteria, set Found to False
If Me![Find Product Subform].Form.RecordsetClone.RecordCount = 0
Then
(dsp. "no recs match criteria" msg)
With the KeyID field in the underlying query, that line returns 0.
With the KeyID field removed, the line returns the correct # of
records I've checked for spelling errors, etc. so far haven't
found any Go figger!
I know USENET protocol frowns on attachments, etc., but would it
help if I sent you a dummy version by some other means.
In the meantime, well, I'll keep looking!
Thanks
The If Me!... line is working correctly..
We need to see the code that sets the recordset.and probably the
rest of the code in that procedure
Many thanks in advance!!
Here goes:
Look For Product and Look For City are the names of the text input controls
btnFindProduct is the command button that triggers the search
--------------------------------------------------------------------------------------------------------------
Private Sub AddToWhere(FieldValue As Variant, FieldName As String,
MyCriteria As String, ArgCount As Integer)
'Create criteria for WHERE clause.
If FieldValue <> "" Then 'Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "
End If
'Append criterion to existing criteria. Enclose FieldValue and * in
quotes.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue &
Chr(42) & Chr(39))
ArgCount = ArgCount + 1 'Increase Argument Count
End If
End Sub
-----------------------------------------------------------------------------------------------------------------
Private Sub btnFindProduct_Click()
'Create a WHERE clause using search criteria entered by user and
'Set RecordSource property of FindProduct Subform to SQL WHERE Statement.
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer, ReTry As Integer, Found As Integer
Dim ChkCity As Variant, Tmp As Variant
Found = False 'Initialize Variable
Me![Find Product Subform].Form.RowHeight = 250
'Initialize SELECT Statement
MySQL = "SELECT * FROM [qryFindProduct] WHERE "
'Use data entered in form header text boxes to create criteria for WHERE
clause.
ArgCount = 0: MyCriteria = ""
AddToWhere [Look For Product], "[ProductService]", MyCriteria, ArgCount
AddToWhere [Look For City], "[City]", MyCriteria, ArgCount
'If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If
'Create SELECT stmnt. for subform Recordsource property
MyRecordSource = MySQL & MyCriteria
'Set RecordSource property-FindProduct Subform.
Me![Find Product Subform].Form.RecordSource = MyRecordSource
'Lookup City From qryFindProduct--message if not found
ChkCity = DLookup("[City]", "qryFindProduct", "[City] Like [Look For
City]&'*'")
If IsNull(ChkCity) Then
MsgBox "Unknown City! Please Re-Enter..."
Me![Look For City].Value = Null
Me![Look For City].SetFocus
Exit Sub
End If
'If no records match criteria, set Found to False return focus to text
input control
If Me![Find Product Subform].Form.RecordsetClone.RecordCount = 0 Then
Found = False
Else
Tmp = EnableControls("Detail", True) 'Enable control in detail section
of sub form
Me![Look For Product].SetFocus 'Move insert point to Product
Control
Found = True
End If
If Not Found Then 'reset Look For Product text input window
MsgBox "No Records Match Specified Criteria.", 48, "No Records Found"
Me![Look For Product].Value = Null
Me![Look For Product].SetFocus
End If
End Sub
----------------------------------------------------------------------------
