Prevent overwrite of existing records in a subform

[Gingertrees]

(Why hasn't Access fixed this? It's been a problem since the beginning of MS Office!!!)
I'm tracking calls to customers. So I've got a form including customer info (name, address, etc), and a subform within it for recording call data (date, caller, result code [1-10], notes, etc). The top form is successfully locked down, but subform records keep being overwritten. This seems to be because the 1st call record is always on top.

My needs:
When I look up consumer Jane Smith, and she has 23 call records, I want it to show me a new (blank) call record to be added.

DIFFICULTY:
the existing 23 call records need to be able to be browsed, so straight "data entry" mode isn't an option. In rare cases, there may be a mistake that will need to be edited in an old call.

DIFFICULTY 2:
The call subform includes text fields as well as dropdowns (list boxes) and date fields. These should not be editing in the vast majority of cases (EXCEPT for the occasional typing error that needs correction).

Can you help me?

 

[jackd]

Sounds like a Customer -->CustomerCalls 1 to Many standard form subform set up.
(You haven't said whether or not the same Customer could be called more than once on the same date. I am assuming NO.

tblCustomer
CustomerId autonumber PK
other Customer fields

tblCustomerCalls
CustCallId autonumber PK
CustId Number FK to Customer
CallDate DateTime
other Call to this Customer related info


CustId + CallDate would form a unique composite index to prevent duplicates

It sounds like your subform should have some logic to Add a new record
Click add and it quickly inserts a new Call record for this Customer and adds today's Date. You want to immediately move to this record to add fields.

It sounds that someone can get into the existing record and just overtype whatever is there.

It should be possible to open the CustCalls table as a Snapshop recordset (READ ONLY) by default.
If the Call is really for a new record, click an add button and Open the CustCall recordset as dynaset.

Some combination of these should prevent unintended overwriting of records.

 

[strive4peace]

I am assuming you are using LinkMasterFields and LinkChildFields to synchronize the records on your subform.

On the CURRENT event of your mainform, put code to go to a new record on the subform

   with me.subform_controlname.form
      if not .NewRecord then .AddNew
      .controlname_firstcontrol.SetFocus
   end with

 

[jackd]

Nice.!!!

 

[strive4peace]

thanks, Jack

oh! there is a mistake ...

   with me.subform_controlname.form
      if not .NewRecord then .Recordset.AddNew
      .controlname_firstcontrol.SetFocus
   end with

 

[Gingertrees]

thanks!

 

[strive4peace]

you're welcome happy to help