Forms adding to tables help

[rob_andy]

On a form I've got a query that uses 2 tables linked by a order ID, Payment and Order. The form is meant to add a payment into the payment table and then set a field 'HasPayment' in the order table to true. On the form_load() I've had do the following:

DoCmd.GoToRecord , , acNewRec

So that when the form is used it automatically adds a new payment row. However because of the query it also adds a new record to the order table. This means that it sets HasPayment in this new row to True. I need it to look at the order that the payment is linked to by order ID and set HasPayment for that order. If you can help I'd be most appreciative. Cheers R

I also tried the following:

CurrentDb.Execute = "UPDATE Order Set HasPayment = True WHERE OrderID = " & OrderID.Value & ";"

However, when I try and run I get the error 'Argument not optional'

Help!!!

 

[guybrush]

It sounds like you are trying to kill 2 birds with one stone. This may be a case where 2 stones are better than 1.

First, I have some questions...

How do your users get to the "payment" form? Do they link to it from the Orders form?

If So then you should pass the orderID to the payment form, so it will know which order to apply the payment to (or simply leave the order form opened when the payment form pops up).

The payment form should be bound to the payment table (don't mess with the query that grabs the HasPayment field). Set the dataEntry=Yes property on the form to only allow new payments. Then allow the user to type in the payment information.

(I am assuming that part of the payment form includes the OrderID. You could either have them look it up with a combo box, or used the value that was passed from the orders form.)

When they hit "submit" (or whatever), then validate the data that they entered. If it is all good, you should THEN update the HasPayment field, and close the payment form.

Depending upon how you opened the payment form, will dictate how you update the HasPayment field.

If you left it opened in the background, then you can simply use
Forms("OrderForm").HasPayment=-1

If it is closed, you could run an update query, that accepts the
orderID as criteria, and update the HasPayment field.

Or, you could use ADO or DAO to programatically open a recordset
and update the data.

You decide

Now, after that 21 year explanation, you may have more questions. I hope this at least gets you to the next step.

 

[rob_andy]

Problem solved

Thanks for the help, I had the payment form set to open a new record when it loaded, because it was linked to the order form as well it was creating a new order and setting the has payment in the new order. So I chaged the way I was doing things and instead of using the form I created an sql update statement. It works, I'm happy, thanks R