Form With Join Creates Two New Records

[TastyWheat]

I have a form that uses the following join as its control source:

SELECT * FROM [Purchase Order]
LEFT JOIN [Work Orders]
ON [Purchase Order].WorkOrderID = [Work Orders].WorkOrderID;

I also enclosed a small picture of the relationship between the two tables. This is the problem I'm getting when using my purchase order form.

What I Want:
When I type in the "Work Order #" it will lookup the most recent work order with the same number and put its ID into the purchase order record.

What I'm Getting:
When I type in the "Work Order #" a brand new work order is created and the "Work Order ID" field gets the next number in the sequence.

Make note that I'm not using code for any of this. This is happening all on its own. I think the join is messing things up here. Any ideas?

 

[boblarson]

You can't type a work order number into a bound field on your form and expect it to find something. If you do that, it just starts a new record. You need to put an UNBOUND control where you can type in the number and then either have a button to click to find the work order, or set code on the after update event of the text box to do a DLOOKUP and have the recordset move to the correct record. You can also create a combo box that would have all work order numbers listed and that way you can select an existing one and on the after update of the combo box do the same thing as the text box.

 

[TastyWheat]

It looks a bit odd on my form but using a combo box really does the trick. I can bound a column and then hide it (by making its width 0") so they think they're saving some other value. I think using the combo box method is more reliable than putting a procedure in the AfterUpdate or BeforeUpdate event.