Custom Form to Collect Query Parameters

[andersonEE]

Hello experts. I'll try to keep this as straight-forward as possible.

Background
Trying to make a Daycare Billing Program. Tables (relevant to my question) include: Parents, Children, Rates, Debits.

Relationships: Many-to-Many between Parents and Children using a Parent_Child table in between, One-to-Many between Rates and Children, One-to-Many between Parents and Debits.

Rates table has fields for Name, Billing Cycle (monthly, weekly, etc.), and Amount.

The Debit table has fields for Parent_IDFK, Child, Amount, Date_Billed, and Description.

Question
I am trying to make a Query that uses a custom form to collect parameters. Right now it is set up so the user can select a Billing Cycle, then press ok and a query will run collecting all Children who have a (for example) "Monthly" billing cycle, and then append a record into the Debit Table for each of them. This way all Monthly clients can be billed at once.

My problem is the Date_Billed Field in the Debit table. I want the User to be able select a date on the custom form, and this date will be entered into Date_Billed column for each record. For instance, the database user bills all "monthly" clients and each of their debits is recorded on the first of the month. However, there is no "date" field in my query to append into the Debit Table. I tried to make an expression that just included all dates and my custom form parameter "chooses" the right one to append into the Debit table, but this did not work.



Any ideas on how to do this?

I am guessing that this kind of database (billing stuff) is done quite often. Is my idea on how to do mass billing appropriate or are there better/easier ways to do this (maybe based on date alone)?

Thanks for the help.

[pbaldy]

I would expect something like:

INSERT INTO DebitTable(AmountField, DateField, ...)
SELECT AmountField, Forms!FormName.ControlName, ...
FROM ClientTable
WHERE...

The form reference would pull the date entered by the user and insert it into the table with the other data.

[andersonEE]

This looks like exactly what I want to do; however, I have been trying to use the Query Design tool in Access 2007 because most of the tutorials on Microsoft's help site for queries use that. I'll try do do some reading on building my queries with SQL and see if I can get it working.

[pbaldy]

You can do it in design view. Just put the form reference in the Field spot. Access will alias it, or you can. Access will make it

Expr1: Forms!FormName.ControlName

You can make the Expr1 anything you want, though it really doesn't matter in this instance (it won't be visible to the user).

[andersonEE]

You can do it in design view. Just put the form reference in the Field spot. Access will alias it, or you can. Access will make it

Expr1: Forms!FormName.ControlName

You can make the Expr1 anything you want, though it really doesn't matter in this instance (it won't be visible to the user).

Perfect! Thanks pbaldy.

[pbaldy]

Happy to help!