Form with datasheet AND single record view: Add a record ONLY in single record view ? (1 Viewer)

I've got:

A main form with two sub-forms
One sub form is a datasheet view of a query
Other sub form is a single record view of the highlighted record

A master/child relationship which updates the single record view by means of a text box on the single sub, which is 'fed' by the ID of the current highlighted record.

I can edit the single record in the single sub form and changes are reflected in the datasheet view, which is what I want.

Is this possible:

Add a new record in the single record sub form, WITHOUT enabling AllowAdditions in the datasheet view?

I don't want any editing or addition of records in the datasheet view.

I can't find a way to do it without going to unbound fields and a heap of code and taking control over a lot of Access' functionality.

Set the Allow Additions property of each subform as required

NEVER use queries as subforms. Always use Forms. It is only with a form that you have control over what gets saved and when.

Since they are two separate subforms, you would just set AllowAdditions to no on the DS one. Once these are separate subforms, I would not allow modifications in the DS view form. The user would need to make all changes in the single view form. That means you only need validation code in one form and not both.

It might be my terminology that's confusing. Both subs are forms, one has a RecordSource which is a SELECT query. The intention is that a WhereCondition will filter to just records for a single customer account.

The single sub form has tblDelAddresses as RecordSource.

The main form has a text box txtDelAddrID with control source as follows: =[sfrmDelAddrDsheetV4].[Form]![DelAddrID]

Single sub form has Master: txtDelAddrID
Single sub form Child: DelAddrID

This all works, datasheet is out of bounds for input but I can edit in the single form and DS gets updated, all good.

The master child link is preventing adding a new record (I think)

I found that the only way I could add a record is to set the datasheet form AllowAdditions = true. Then I saw some odd behaviour (but which can be explained I think by the link and the text box).

The datasheet was enabled. Focus went to the datasheet. I could enter a new record in the datasheet.
None of this is what I want.

The single form was enabled also, but if I entered just a single character I got an error (can't remember precise wording, something preventing the record from being updated, I think it was the textbox).

But once the single character had been entered, I could complete editing in the single form (of course that's not what I want).

Is it to do with DelAddrID being assigned on first keypress ? I'm thinking that the textbox DelAddrID is preventing Access from initialising a new record ?

Suggest you upload a stripped down copy of your db as its very difficult to follow your explanation though you've tried hard to explain it step by step

If that's not possible, some screenshots may help

OK. Run frmDelAddressesV4. Click Add New Address. DS is AllowAdditions = true.

Move to Company Name and enter a character. Breaks.

But. Comment out the line
Forms!frmDelAddressesV4!sfrmDelAddrDsheetV4.Form.AllowAdditions = True
DS is disabled, focus goes to single form, breaks on single character

Just tried this on a tablet so I've not looked at the code.
Added a new record without problems in the top section until I reached county when it didn't respond
Added another record and it stopped responding at address2.
Is that what you mean?

No errors occurred. The only message was telling me which button I had clicked cmdAdd_click

BTW are you aware there are two records with a single dot .

Thanks for trying it Colin.

The msgbox is to come out. I agree I can add a new record in the datasheet but I don't want that, I want the datasheet disabled for editing and new records and only use the bottom single form part.

To be honest I've done it with my V3 with unbound fields on the bottom half and using Recordsets to update the datasheet with edits or new records, but there's just so much code it was getting silly. The final straw came when I realised I had to add code for limiting the number of characters in the unbound fields before they get written. There's got to be an Access way of doing this (I hope)

Sorry should have read the thread title in full.

As the datasheet is at the top that is the obvious place to go....especially as the focus is on that subform.

Why do you have the datasheet if you want it deactivated?

Suggest you move the single form to the top and then do one of the following to the datasheet subform.
1. Disable it
2. Lock it
3. Hide it
4. Remove it completely

If you don't need it at all, do the final one. Otherwise, you can add a button to enable/unlock/show the subform as appropriate

For example in the attached update, I've have moved your single form controls to the main form, removed that subform and hidden the datasheet by default
I've slightly simplified your code but most of it I've left alone.
I'm sure you could significantly simplify it further - e.g. unbound fields - why?

It's intuitive when used in context. You start an order, pick a customer account and this populates the order form invoice address. If you need a delivery address, click the Delivery Address button which opens the Delivery Address form. This is a list of delivery addresses previously used for this customer, so it may be in the datasheet list, double-click it and the form closes and the delivery address is added to the order.

But if this is a new delivery address, we want to get user input, in the bottom half of the form, then add it to the table and datasheet list, then close the form as before.

So it only works for me as I've designed it, I want to see a list of previously used delivery addresses or have the option to add a new one - but not in the datasheet which isn't at all user friendly for input. It makes sense to present the datasheet first, that's where the user will be looking, have we already raised an order with this delivery address ? Only if it's not in the list will the user need to add a new one.

What I haven't been able to resolve is keeping the datasheet locked down whilst allowing the bottom half to accept input and save a new record.

As already stated, in my view this is more complicated than it needs to be

I don't agree with your reasoning & the acrobatics you're jumping through with your code is part of why I think that ... but its your database & not mine

Did you look at my modified version?

If as I suggest you move the single form to the main form (whether at the top or bottom), you can lock the datasheet form (but keep it visible if you wish) when you click the Add New button.

I did look at your revised form, thanks for taking the trouble. Unfortunately it doesn't function as I want. Without the DS view, the form has no purpose, there's no list to make a selection from. When the DS is made visible, the single form doesn't update to reflect the current selection, I want both forms to be in sync, and I want to be able to add a new record from the single form (per thread title).

Anyway, new day, new look, and it's working with a trivial change. I had a bound ID control on the single entry form which I thought was necessary, but wasn't. That was throwing an error ('You can't assign a value to this object') when I tried to add a new record and I began to wonder if what I was trying to do wasn't possible without a lot of work. So once that was fixed it was just as bob fitz said, just set the Allow Additions property of each form.

So it's all driven by Access with no database code behind the scenes. Thanks all for contributions.