Hi Jeanette,
My subform is a datasheet, not continuous so I can't do the text box
infront
of the control box. What exactly does that accomplish? I'm sorry I'm
a
little slow, but I'm nowhere close to being a programmer.
Also, for some reason, when I do the code for the rowsource for the
combox
box, Access doesn't recognize the field name for the law firm id - the
syntax
is "Law Firm ID". Is it because I have spaces in the field name?
:
Tia,
your subform is continuous. We can put 2 combos on each row of the
subform
to allow user to choose attorney and rate.
On a continuous Form, there is really only *one* combo box - displayed
many
times. If you change its properties (its RowSource in this case), all
rows
reflect that change.
One way around this is to put a dummy textbox onto the form, carefully
superimposed over the text area of the combo box (don't cover the
dropdown
arrow though). For attorney's name set its control source to the
attorney
name in the form's recordsource.
Set the textbox's Enabled = No, Locked = Yes, Tab Stop = No so the
user
can't do anything with it; it's for display only. You may need to move
it
in
front of the combo box (with Move To Front on the Format menu).
Private Sub txtAttorney_Enter()
' Move focus to combo box behind it
Me.cboAttorneyID.SetFocus
End Sub
When the user selects the dropdown, the combo box data will come in
front
and allow (filtered) selection; when it's not selected, the user will
see
the textbox.
Now for the rowsource for the combo for attorney.
We need the ID for the attorney, the name for the attorney.Which table
has
this info?
We need something like "SELECT YourTable.AttorneyID,
YourTable.AttorneyName
FROMYourTable WHERE YourTable.LawFirmID = " & "Parent.cboLawFirmID &
""
if LawFirmID is a text field, add the appropriate quotes.
This assumes that there is a combo on the main form to choose a law
firm
and
that its bound column is the ID for the law firm.
Maybe YourTable is the Timekeepers table?
What is the field/fields in the Link master and child fields for the
subform
control?
The rowsource for the combo for Rate will be something like
"SELECT TableName.RateID, TableName.Rate FROM TableName WHERE
TableName.AttorneyID = " & me.cboAttorneyID & ""
Jeanette Cunningham
Hi,
the timekeeper ID is in the subform.
This is what I'm trying to do: Each record in a form = 1 invoice
I am trying allow someone to enter in the details of a law firm
invoice -
which includes seeing a breakout of attorney information
1) Choose Law Firm associated with Invoice
2) See a list of all attorneys associated with law firm chosen,
select
attorney
3) Enter in details of attorney work (hours, rate, project worked
on)
4) Based on attorney selected, choose proper rate from drop down
list.
There is a rate table where each attorney can have more than one
rate
(record)
5) Total fees will calculate based on hours entered and rate
selected
6) Add information for next attorney listed on Invoice
:
Tia,
you don't need a row source query.
the error is Access trying to tell you that when it gets to the
code
for
enter event of combo 19, it can't find timekeeperID in table
timekeepers.
Is there a text box for timekeeper ID on your form or is it on the
subform?
Also, is there a way to do this without moving the attorney combo
box
to
the
main form? Each invoice can have several attorneys so the user
has
to
be
able to select more than one attorney.
Please confirm my understanding of what you are trying to do.
--Choose the law firm
--see a list of all the invoices for that law firm with their
associated
attorneys
--these is a missing link somewhere for finding the rate that each
attorney
charged on the project?
--for each project show the total project cost broken down by
hours,
rate
and attorney
Jeanette Cunningham
Thanks Jeanette,
I'm getting close. Unfortunately, I keep getting syntax error
messages.
I
moved the combo box that selects the attorney to the main form.
After
I
create the combo box, do I enter in the event or do I select a
Row
Source
Type and create a Row Source query? My event procedure looks
like
this:
Private Sub Combo19_Enter()
strSQL = "SELECT Timekeepers.Timekeeper ID,
Timekeepers.Timekeeper"
_
& "FROM Timekeepers" _
& "WHERE Timekeepers.Law Firm ID = " & Me.Combo3 & " " _
& "ORDER BY Timekeepers.Timekeeper"
Me.Combo19.RowSource = strSQL
End Sub
When I try to select an attorney, I get "Syntax error (missing
operator)
in
query expresion 'Timekeepers.Timekeeper ID'
What am I doing wrong?
Also, is there a way to do this without moving the attorney combo
box
to
the
main form? Each invoice can have several attorneys so the user
has
to
be
able to select more than one attorney.
Thanks so much for your assistance!
:
Oops?
that should read
put a second combo on the main form to choose an attorney.
After user chooses a law firm . . . .
message
Tia,
put a second combo on the main form to choose an attorney.
After user chooses an attorney, set the row source of the
second
combo
to
a query that selects only the attorneys from the law firm
chosen
in
the
1st combo.
On the enter event for the second combo put code to set its
drop
down
list
something like below
Dim strSQL as string
strSQL = "SELECT YourTable.AttorneyID, YourTable.AttorneyName
"
_
& "FROM YourTable " _
& "WHERE YourTable.LawFirmID = " & me.FirstComboName & " "
_
& "ORDER BY YourTable.AttorneyName"
Me.SecondComboName.Rowsource = strSQL
On your invoice form there is code to show the appropriate
details
in
the
subform after a law firm is chosen.
Move this code to the after update event of the second combo
instead
and
change the strSQL to reflect user's choice in the 2nd combo.
How do you know which rates can be used for a particular
attorney?
Is
there a table with this info for each AttorneyID?
Jeanette Cunningham
I have an invoice form that includes a combo box that allows
the
user
to
select the Law Firm from a Law Firm Lookup table. Then, I
have
a
subform
that includes the invoice detail - where the attorney detail
is
stored.
Based on the law firm selected on the inovice form, I want
the
user
to
be
able to use a drop down box to select the attorneys for that
law
firm.
I
have an attorney table that lists the attorney, the law
firm,
and
the
attorney's current rate. How do I get the subform to allow
the
user
only
to
see a drop down of attorneys related to law firm selected?
There's
also
another field for the rate that should only allow the user to
select
rates
for the attorney selected.
:
Hi,
sounds like there is a many to many relationship between
attorneys
and
rates, is this how the database has its tables set up?
If so you could use the attorney ID in a query that joins
the
table
containing attorney ID with the attorney ID in the table
with
rates.
This
would return only the rates for a particular attorney if you
had a
form
where you could choose the attorney ID.
Jeanette Cunningham
The legal dept at my company wants to track invoices from
outside
law
firms.
They want to be able to track who worked on a project, how
many
hours
and
at
what rate.
How do I create a query or lookup that will allow the user
to
select a
specific attorney from a lookup table - then once the
attorney
is
selected,
