URGENT: Use a query to populate table based on form parms

[demunchkin]

Hi,

I'm working with a database that will be used to capture employee number and amount spent either in the pharmacy or in the restaurant. There are two separate forms: one for the pharmacy and one for the restaurant. An entity (pharmacy or restaurant) is auto-attached to the form based on which employee is logged in. In the end, a text file will be edited and exported with all necessary data (for payroll deduction). Here is my problem: what I need to do is use a query place a code in a table on each record based on which entity it came from per the form.

For example: employee 123456789 comes into pharmacy (entity code 107) and purchases $15.25 worth of medication.
Employee 987654321 goes to the restaurant (entity code 112) and purchases $5.03 worth of food.

I need to send that info to a table and populate the [entity] column with a code based on the text. I need it to do this without asking me for the parameter name.

123456789 107 1525
987654321 112 503

I apologize if this is confusing. Too many cooks were in this pot in building this database. I will try to explain better if there are questions.

Any help would be extremely appreciated.

Thanks,
Devin

 

[scriverb]

These would be seperate Update action queries. With the little information you are giving me I will make a few assumptions. Let's work on the Pharmacy query first. Use the following SQL and name this query(qryUpdPharTrans)
UPDATE YourTableName A SET A.EmployeeID = Me![EmpID], A.Entity = 107, A.TransactionAmt = Me![TransAmt];
You will have to modify this to reflect your table name and the names of the corresponding fields. Also the Me![EmpID] should be modified to the name of the form control that has the employee id in it at the time of the query run. Me![TransAmt] should be modified to the name of the form control that has the transaction amt in it.
The Restaurant query would be identical but would have 112 as the value assigned to A.Entity. Name this one (qryUpdRestTrans)
UPDATE YourTableName A SET A.EmployeeID = Me![EmpID], A.Entity = 112, A.TransactionAmt = Me![TransAmt];

Whereever you determine to run these queries the following code should be used to trigger the execution:
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdPharTrans"
DoCmd.SetWarnings True
or
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdRestTrans"
DoCmd.SetWarnings True

Let me if you need more assistance.
Bob Scriver

 

[demunchkin]

Bob,

That's very close to what I was looking for. Thank you.

Devin