Am I asking the impossible? Open form find record insert item?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a MasterCard main form with a MasterCard Details subform. I would
like to create a drop down that would open another subform
(subfrmExpenseDetails) and search for two (or three) parameters (the purchase
date and the supplier( and $amount)) and insert the found record
(ExpenseReportNumber) into that field. Is that possible? If so, can someone
help me with the code? Thanks.
 
Why are you even opening the other form? It sounds like you need to write a
function or sub. It isn't clear what tables and fields are involved, so,
its difficult to provide sample code. DLookup may be a possibility or you
may need to write code to open a recordset filtered to the appropriate
ExpenseReportNumber.
 
I have a table with Receipts

tblReceipts - from a field in this report it selects from four different
reports

1.Expense Report
2. Purchase Order
3. Travel Expense Report
4. Check Request Report

If the Expense Report or Travel Expense Report were selected (this means the
person has to be reimbursed for these expenses) then I would need to match
the items in the Expense Report and Travel Expense Report to these different
tables:

tblMasterCard
sibfrmMasterCardDetails
AND
tblReimbursement Check

Unfortunately, I am not sure how to do this. I imagine that I would have to
manually go in and select from a dropdown box, the ExpenseReportID and Travel
ExpenseReportID, right on the frmMasterCardDetails? If I have to do this, I
need to see the information from these tables in order to select the right
ExpenseReportID.

Help? I am not sure that I have made myself clear?
 
OK....from what I understand...you have various expense reports that require
you to match unreimbursed expenses found in a credit card statement? It
seems inappropriate that a credit card would be associated with a Check
Request...but we'll move on.

The form you are designing for expense reports should include a subform
which would list charged expense items. For any new records in the subform,
you would have a drop down list filtered to contain a list of the
unreimbursed expenses. The record ID, date, supplier, and amount should be
columns in the drop down. I imagine there is some relationship between
sibfrmMasterCardDetails and tblReimbursementCheck, which would indicate
whether or not an expense has already been reimbursed, and this relationship
should serve as one method of filtering for the correct expense? The record
ID from sibfrmMasterCardDetails would be stored in the expense report detail
record (tblReceipts?) indicating that the expense has been reconciled to a
particular expense report.

You don't need to open any other form, nor do you need to write code!
 
For clarification, only the Expense Report and Travel Expense Report Receipts
are reimbursed. These are paid by my boss' MasterCard.

The Check Request would only come about if a vendor sent a bill to us and
the parent company (which is in CA) would pay the bill, I would then do a
Check Request for that expense to them.

Purchase Orders are generated when an expense is charged to our company
account, for instance, for office supplies.

That is why I decided to have a tblReceipts. There is a dropdown box on the
tblReceipts that would open the appropriate form, i.e., Expense, Purchase
Order, etc. I thought that because everything generated from a Receipt that
would be the way to go?

You are correct that there is a frmExpenseReport (with the expense report
number, date I generate the expense report) and a frmExpenseReportDetails
(Expense date, supplier, amount, etc.)

This is where I start going around in circles and not understanding how to
set up the relationships, forms, etc. I have a frmMasterCardDetails and
somehow I do have to tie this in with the frmReimbursementCheck and
frmReimbursementDetails. The only way to do that is to have a drop down
field on the frmMasterCardDetails where I would select the
ReimbursementCheckID when it has been paid?

Also, I am not sure how to tie the Receipt/ExpenseReport into the
MasterCardDetails/ReimbursementCheck. If it would help, I can e-mail you a
relationship printout out or anything else that would help. My e-mail is
(e-mail address removed).

I guess this is just beyond my abilities at the moment. But, I am trying to
learn! I appreciate all the advice you can provide.

Thanks.
 
Let's focus on expense reports for now...

You should probably have...

tblExpenseReport
Which would contain various field for the expense report, i.e.,
submitted by, date, etc.

tblExpenseReport_ChargeDetail

which would contain
ID
ER_ID (Expense Report ID)
MC_CD_ID (Master Card Charge Detail ID)

tblCC
which would contain various fields for the card, i.e., issued to, exp
date, card type, etc.

tblCC_ChargeDetail (sibfrmMasterCardDetails?)
which would contain various fields for the card charges, i.e., supplier,
date, item, etc.


tblExpenseReport and tblExpenseReport_ChargeDetail are related

tblMasterCard and tblMasterCard_ChargeDetail are related.

tblExpenseReport_ChargeDetail and tblMasterCard_ChargeDetail are also
related.

Hope this clarifies how your relationships should look. How you plan to
enter CC charges, i.e., from a bank statement or from the expense report,
will determine whether you need to pop up a form to enter the charge
details.
 
Back
Top