G
Guest
I seem to have lost my correspondent from previously so I’ll try my question
again.
I’m trying to find the right function, or query procedure to look up a
registration fee in a table, based on pricing factors a registrar will
identify at sign-in time.
A table called tblScheduleRetreatFees holds 100 individual fees. It has four
fields in it - FeeID, RegistrantTypeID, DurationTypeID, and RetreatFee
(currency data type) - and, of course, 100 records. Perhaps I should add
that RegistrantTypeID and DurationTypeID are in the form of combo boxes in
this table.
Joined one-to-many to tblScheduleRetreatFees are 2 pricing factor tables:
RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it -
RegistrantTypeID, and RegistrantType - and 24 records, with values like
"Adult", "Teen", "Student", "Couple", "TwoAdult1kid," "TwoAdult2kids",
"TwoAdult2kids1teen", etc in the RegistrantType field. DurationTypes also
has 2 fields in it - DurationTypeID and DurationType - with values like
"Full Retreat", "HalfDay", "EveningProgram", etc., 6 records in all.
These 2 pricing factor tables are also joined one-to-many to the table which
will accumulated the fee charges for everyone who attends the retreat,
called InvoiceChargeRetreatFee. That gem has 4 fields in it -
ICRetreatFeeID, MainRegistrantID (a dropdown list box of peoples' names),
RegistrantTypeID (dropdown list), and DurationTypeID (ditto) - and no records
as yet, except my test data. One main registrant pays for himself/his
family, but can also opt to sponsor another individual; hence there could be
more than 1 retreat fee charge attributed to the same registrant.
I've based a data entry form, frmInvoiceChargeRetreatFee, on the
InvoiceChargeRetreatFee table; the registrar will use it as people arrive at
the desk to sign in. It has 3 combo boxes on it, where the registrar can
click on the person’s name, and then click their registrant type (family
type) and duration of stay. Once that’s done, I’d like the form to display
the correct fee, in a text box, looked up from tblScheduleRetreatFees.
I’ve tried setting the text box’s control source to
=DLookUp(tblScheduleRetreatFees!RetreatFee,[tblScheduleRetreatFees],tblScheduleRetreatFees!DurationTypeID=[Combo20]
And tblScheduleRetreatFees!RegistrantTypeID=[Combo16])
but this didn’t work. (I realize the Combo box numbers are high; it took a
while fiddling with the form and discarding the earlier, lower-numbered ones
to get the form roughly the way I wanted it.)
Have I got the DLookup code wrong? Or should I be trying to do this with
some other function?
again.
I’m trying to find the right function, or query procedure to look up a
registration fee in a table, based on pricing factors a registrar will
identify at sign-in time.
A table called tblScheduleRetreatFees holds 100 individual fees. It has four
fields in it - FeeID, RegistrantTypeID, DurationTypeID, and RetreatFee
(currency data type) - and, of course, 100 records. Perhaps I should add
that RegistrantTypeID and DurationTypeID are in the form of combo boxes in
this table.
Joined one-to-many to tblScheduleRetreatFees are 2 pricing factor tables:
RegistrantTypes, and DurationTypes. RegistrantTypes has 2 fields in it -
RegistrantTypeID, and RegistrantType - and 24 records, with values like
"Adult", "Teen", "Student", "Couple", "TwoAdult1kid," "TwoAdult2kids",
"TwoAdult2kids1teen", etc in the RegistrantType field. DurationTypes also
has 2 fields in it - DurationTypeID and DurationType - with values like
"Full Retreat", "HalfDay", "EveningProgram", etc., 6 records in all.
These 2 pricing factor tables are also joined one-to-many to the table which
will accumulated the fee charges for everyone who attends the retreat,
called InvoiceChargeRetreatFee. That gem has 4 fields in it -
ICRetreatFeeID, MainRegistrantID (a dropdown list box of peoples' names),
RegistrantTypeID (dropdown list), and DurationTypeID (ditto) - and no records
as yet, except my test data. One main registrant pays for himself/his
family, but can also opt to sponsor another individual; hence there could be
more than 1 retreat fee charge attributed to the same registrant.
I've based a data entry form, frmInvoiceChargeRetreatFee, on the
InvoiceChargeRetreatFee table; the registrar will use it as people arrive at
the desk to sign in. It has 3 combo boxes on it, where the registrar can
click on the person’s name, and then click their registrant type (family
type) and duration of stay. Once that’s done, I’d like the form to display
the correct fee, in a text box, looked up from tblScheduleRetreatFees.
I’ve tried setting the text box’s control source to
=DLookUp(tblScheduleRetreatFees!RetreatFee,[tblScheduleRetreatFees],tblScheduleRetreatFees!DurationTypeID=[Combo20]
And tblScheduleRetreatFees!RegistrantTypeID=[Combo16])
but this didn’t work. (I realize the Combo box numbers are high; it took a
while fiddling with the form and discarding the earlier, lower-numbered ones
to get the form roughly the way I wanted it.)
Have I got the DLookup code wrong? Or should I be trying to do this with
some other function?