Access 2007 locking fields

[Jane Alford]

I apologise in advance if my previous question has got itself held up in a queue, but there's been no sign of it for over 3 hours now...

I have a MAJOR problem, that I'm sure has a simple solution, but I've been hitting my head against this particular "brick" wall too often and I need to find a solution.

I create a form, with a combination of text boxes and combo boxes. All the Add/Edit/Delete options for the form and fields are set to Yes. There's no record locking there's no select from list only option set etc.

My problem is, is that something is locking the form. The form pre-fills the fields with the first record from the query; dead straight forward, there's nothing on the query to lock the record; the form is then locked, none of the fields can be edited, none of the combobox drop down lists allow selection.

What is locking up the form?

The only way I've got around this in the past is to completely start the form again from scratch. I'm too far down the development route to waste time doing that AGAIN. So please is there anyone out there who knows the foibles of Access 2007 Forms who might know what's going on.

I've tried unlocking EVERYTHING (all controls/form/detail etc) using VBA on form_open, but that hasn't helped either.

It just seems as if once the form is locked, NOTHING can unlock it again...

[TheSmileyCoder]

A few things spring to my mind:
When you open the forms recordsource as a query, are you then able to manipulate the fields?
If NO, then the problem is in your query design (Post SQL for further assistance)

If yes, you can edit records directly in the query, then the problem is likely in the form. Try to answer the questions below:
Are you using the form as a main form or as an embedded subform?
Is the form set to only only allow new records? I.e. is DateEntry Only set to yes?
Is the form Continues, Single, or split?

With smiles, TheSmileyOne

[Jane Alford]

Apparently because one of the queries embedded three levels down uses UNION, the query is ReadOnly.

I need to use the UNION to create a single record from a lookup on 3 different tables that each contain address fields (I know, but the client wants to keep historical data on the records), so the UNION select was the perfect way to go about this...

[NeoPa]

I'm afraid that simply will not work Jane. Smiley is right on the button of course. See Reasons for a Query to be Non-Updatable for more on this.

[Jane Alford]

My problem is, is that as long as the query attached to the FORM is updateable, I see no reason why the queries attached to comboboxes should be updateable. That is not logical at all. Why is Access trying to UPDATE the lists attached to a comboBox?

[NeoPa]

Clearly I misunderstood what you were trying to say. There is a big difference between the query the form's bound to (The form's RecordSource) and any other queries that may be used within ControlSources of any of your controls (bound or unbound). Referring to queries embedded three levels down as using UNION can only be a reference to the bound RecordSource query. If you're now saying that wasn't what you meant then perhaps a few steps back and trying again from a point we all recognise would be a good idea.

Is the RecordSource query updatable or not?

[Jane Alford]

Sorry. The form is bound to an updatable table. The comboBoxes are bound to queries that reference queries that are UNION statements.

[NeoPa]

Jane Alford:
The comboBoxes are bound to queries that reference queries that are UNION statements.

I assume you mean these controls are populated by the said queries? They must surely be bound to fields from the RecordSource driving the form.

Your earlier instincts were correct. The population of these controls does not affect the updatability of your form.

The problem is, that if that isn't the problem, then something else must surely be! Check out Smiley's post for other ideas and provide us with some more information about your form and what you're doing. Remember, except for the obvious (where we can guess what you're doing without even being told sometimes) we can only answer from the information you provide.