Q. How do I update a record with a PK copied from a subform record?

[Jim Jones]

Hi,

I have a subform "Service".

I need to create a new table: Details and it's subsequent pop up
form.
I can't make a subform for what I want to do because I have no room.

Whenever I create a new service ticket, from now on, I'll need to
open the "Details"
form to list the details of service.

I finally got the field to match the pop up form, with the following
syntax which works perfectly,

=[Forms]![CustomerData]![CustomerCarData]![Service].[Form]![ServiceTicket]

but when I go to update the record, I get
the following:

You can't add or change a record because a related record is
required in table "Service"

which is total nonsense, because the record with that
service ticket number IS RIGHT THERE, in the Service subform from
where I'm getting
it in the first place !

If this isn't an Access bug, I'd like to know how to do what I need
to,
which is update the rest of the record, and save the record
to the Details table, which IS the "many side" of the service ticket
number.


Can anyone help?

Thanks,
Jim

 

[John Viescas]

One option: Use a Tab control so that you can put multiple subforms on your
form - each one on a different tab.

But your current technique can also work. Although you're successfully
filtering the "pop up" form with the ServiceTicket, your popup form doesn't
have a clue about this value. Subforms work because Access supplies the
linking value via Link Master / Link Child properties of the subform
control. It his case, you'll need to have code in the pop up form either
grab the ServiceTicket value from the other form or have the code that opens
the pop up pass it the ServiceTicket in OpenArgs. The pop up form can set
the value in the form's BeforeUpdate event just before Access saves a new
row.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)