Complicated question....reference a text box?

[Myriad_Rocker]

Hey everyone, thanks for taking a look at this! I really do appreciate it.

Here's the situation...

I have one form that contains customer information. They have a customer id and a contract effective date.

I have another form with payment information that has customer id and contract effective date. This table keeps track of their payments in the contract cycle by their effective date. Since a customer can have multiple contracts, they can have multiple effective dates, but their customer id never changes.

So, from the customer information form, I want to click a button to go to the payment screen...but when that payment screen comes up, I want it to populate information based on what was on the other form. ie customer id and contract effective date.

I was trying to do the record source for the form something like the following:

SELECT tblPMT.CustomerID, tblPMT.ContractEffDt, tblPMT.AmtPaid FROM tblPMT WHERE tblPMT.CustomerID = & txtCustID.text & AND tblPMT.ContractEffDt = & txtEffDate.text &

Once upon a time, this would have been easy for me. But since getting out of college, I haven't refreshed myself....can I reference a text box on a form in an SQL statement? If so, am I doing it correctly? If not, then how should I do this?

 

[billyr]

Make Access do the work.

Create a stored query on the payments table with the id and date criteria referencing their controls on the customer form. (=forms!customerForm!txtID). You can use the query as record source for your pmt form. If that's not desirable, view the query in "SQL View" and copy and paste the SQL statement as needed (You can then delete the stored query).

 

[Myriad_Rocker]

Thanks very much...I got it to work perfectly using:

[forms]![PullFromText]![txt_pulltest]

I put this in the criteria for the query and put that SQL into the record source of the form.

 

[Rich]

You can also use the Where condition of the Open Form method