I used Forms!ClientEditForm.Form.RecordSource = where1
Forms!ClientEditForm.Requery
instead of DoCmd.OpenForm stDocName, , , where1
If I only search the ClientName, the program retrieved the right
record;
but
if I fill in the txtEffectiveDate, it did not retrive anything. I
changed
the
code year as below:
year = "[EffectiveDate] Between #1/1/" & year & "# And #12/31/" & year
&
"#"
it still did not work, Could you help me again? Thanks a lot!
:
Here is a debugging technique you can use with a query.
Create a copy of qFeeSchedule so you have a backup, while testing
qFeeSchedule.
Open qFeeSchedule in design view and type "071CA" in the criteria row
for
ClientName, switch to design view and check if it returns any records.
If no records, then this is a clue to check out further.
For example you could look at the join in qFeeSchedule, if it is an
inner
join maybe you need to change it to an outer join.
Jeanette Cunningham
Hi Jeanette,
With the code I posted to you yesterday, I still got the error
message:
Sytax error. in query expression "Select * from qFeeSchedule Where
ClientName
= "071CA" (071CA is one of the test data in the query). What is
wrong?
:
The code looks fine. I am assuming that it now works the way you
want
it
to.
Jeanette Cunningham
Sorry last time the code did not attached in the back.
On the Row Source property of the cboClientName, I used SELECT
DISTINCT
qFeeSchedule.ClientName FROM qFeeSchedule ORDER BY [ClientName];
so
that
the
user can select the client name from the combo box without typing
it
wrong.
Thank you!
Private Sub cmdFind_Click()
Dim stDocName As String
Dim year, where1 As String
stDocName = "ClientEditForm"
year = Trim(Right([Me.txtEffectiveDate], 4))
year = "[EffectiveDate] Between #1/1/" & year & "# And
#12/31/" &
year
where1 = ""
If Not IsNull(cboClientName) Then
where1 = "Where ClientName= """ + cboClientName + """"
End If
If Not IsNull(txtEffectiveDate) Then
If Len(where1) = 0 Then
where1 = "Where EffectiveDate = """ + year + """"
Else
where1 = where1 + ("AND EffectiveDate =""" + year + """")
End If
End If
If Not IsNull(cboLocation) Then
If Len(where1) = 0 Then
where1 = "Where ClientLocation = """ + (cboLocation) +
""""
Else
where1 = where1 + ("AND ClientLocation =""" +
cboLocation
+
"""")
End If
End If
If Len(where1) > 0 Then
where1 = "Select * from qFeeSchedule " + where1
Forms!ClientEditForm.Form.RecordSource = where1
DoCmd.OpenForm stDocName, , , where1
'Forms!ClientEditForm.Form.RecordSource = where1
'Forms!ClientEditForm.Requery
End If
End Sub
:
I would keep ClientEditForm as a form and not a subform.
If the expression
"Select * from qFeeSchedule Where ClientName = "071CA"
is in the VBA code behind the form, it needs to be written out a
bit
differently
"Select * From qFeeSchedule Where qFeeSchedule.ClientName =
"""
&
me.txtClientName & """"
txtClientName is the name of the textbox control for the
ClientName
field
If the expression is from a saved query, I don't know what is
happening.
Where are you using the expression {"Select * from qFeeSchedule
Where
ClientName = "071CA" }?
Jeanette Cunningham
Hi Jeanette,
I created a search button on the footer of the ClientEditForm
which
is
the
single form to display client's info one at time. The search
button
will
open
a search form and there are three places that user can input
their
search
info. on it, cboClientName, txtEffectiveDate and cboLocation.
The
code
behind
the cmdFind button (on search form) will help to find the
record
and
display
it back on the ClientEditForm form. Now the ClientEditForm
form
is
not
a
subform (least time I used this form as a sub from) . Should I
change
this
form back to sub form?
The code got the error message: Sytax error. in query
expression
"Select *
from qFeeSchedule Where ClientName = "071CA" (071CA is one of
the
test
data
in the query).
I tried two different ways (see on the bottom of the code),
they
do
not
work. Please help me for the code. Thanks!
:
With the extra info supplied in your next post, I would
suggest
to
ignore
my
previous advice.
When you click the search button, make it open a search form.
Have
a
look
at
the search form on
http://www.allenbrowne.com/ser-62.html
After the user makes their choice from the search form, you
can
use
the
criteria to set the recordsource for your subform to display
that
record.
Post back if you get stuck.
Jeanette Cunningham
Thank you for your reply.
I should create three combo boxes, cboName, cboYear and
cboLocation.
The
user has to select the name and year first because the
clients
are
repeated
after a year. How can I write combining information code?
:
Hi,
sounds like you want to be able to filter the main form.
Put a combo on the main form that allows a user to choose
from a
list
the
record to display in the subform.
On the combo's after update event you could use a filter
If Not IsNull(Me.cboComboName) Then
Me.Filter = "YourTableName.ID = " & me.cboComboName &
""
Me.FilterOn = True
End If
You should be able to do this using the wizard to put a
combo
on
the
main
form.
Choose the 3rd option - Find a record on my form based on
the
value
I
selected in my combo box
Jeanette Cunningham
Hi,
I have a form contains a sub form and a search button.
The
sub
form
is
a
single form displaying a record from a query one at
time.
If I want to search a record from the query and display
the
retrieve
the
