Subform snubs new record input

[kupe]

One form, two subforms in a new db. The three work well together, except when it comes to a new record.

When I want to input details of a new record, the main form and a subform work ok, but the third subform won't accept anything.

Any guidance, please, gurus?

 

[Obsidian]

I've been having the same problem, (which is why I'm here,) & I've no idea how to prevent or fix it.
I've found selecting "Records" > "Remove Filter/Sort" followed by "Insert" > "New Record" inserts a new record that can be edited. However, if the subform for entering details loses focus the record once again gets snubbed.
I tried to get something working by using

RunCommand acCmdRemoveFilterSort
RunCommand acCmdRecordsGoToNew​

in cmdAdd_Click but this didn't work at all.

Also, I've found that it seems only the keyboard input is snubbed - I've been able to select an item from a combo and this updates the record, (I know this as a value is generated for an autonumber field).

 

[ansentry]

Here is a small sample, that I quickly put to-gether, it appears to work fine for me.

Have a look you may find something different to your one.


For my own info, please advise the outcome.

 

[kupe]

Thanks, Obsidian, and Ansentry.

Thanks very much for the example, Ansentry. A quick comparison is that property Record Locks is set slightly differently. I changed mine to yours, but that didn't improve things.

Your forms go straight to tables and mine through queries, but I can't see that would cause the fault.

The main error message I get is 'You cannot add or change a record because a related record is required in table tblEmployees'.

I'll continue to search through your example. Many thanks. (I will report back. If I resolve it, you may well hear the cheer.)

 

[ansentry]

kupe,

Thank for the quick reply, Just a quck thought (before I go to bed : 12:41 am)

have a look at your relationships and compare them with mine.

I have just change the sample. The forms are now linked by queries and they work fine.

If you can't work it out, take out any sensitive data and post it here and I or others will have a look.

Good Night..............zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

 

[kupe]

Good morning John
I felt in the end I should follow your example, so I have changed round the relationships in the db till it resembles yours.

Sadly, the error remains where it was, only perhaps the fault is a little more clearly defined. Now the error says that the new entry cannot be saved because a 'related record' is required in the (middle) table.

So the subform that is working is the equivalent of your tblVehicles, and the one that won't save is the equivalent of your tblService History.

(In the basic ways, my db is like yours, with one-to-manys in the same way, same key and data types.)

 

[ansentry]

kupe,

as I said last night;

If you can't work it out, take out any sensitive data and post it here and I or others will have a look.

 

[kupe]

Yes, I noted that, and was most grateful, but unfortunately that's not simple. If I can think of a quick way, though, I will. Many thanks.

Hope you had a good night. It's sweltering on this other side of the globe. Cheers.

 

[kupe]

Hi John
Here's a chopped down version. Be most grateful if you could look. All the best
paul

 

[ansentry]

Here it is, fixed.

I have made changes to your PK(Primary Keys and FK (Foreign Keys). I have changed their names and Delted the combo lookup that you had in the tables for the keys

The reason I made the changes to the names is so that you can follow the links between the tables. The combo lookup for key beacuse I can't see what they were ment to do.

Have a look at the word doc that I have attached you will see how the second subform must link.

Finally, it is your database and you can undo my name changes etc.



Good Luck.

 

[kupe]

That is so brilliant of you, John. Many, many thanks. I'd been pouring over it for hours but couldn't find that fault. I do appreciate very much your excellent work. And, no, your names will remain as a tribute to you.
Gratefully paul

 

[kupe]

An update: When a new record begins, only a few fields are used, and each of these are on the main form. The result is that when the db is closed then opened, these records don't appear.

However, if any field in one of the subforms has some sort of entry, then it works fine.

An error message still appears when a subform is entered for the first time with a new record. "Macro or function set to BeforeUpdate or ValidationRule ..."

Click OK, go back one record, and on the return everything works fine.

Ring any bells with you, John?

All the best

 

[goldmedallist]

That happens to me too, but I think there would be a need to refresh the form for it to take effect. I tried using the refresh button and it works in some cases. I am not sure whether it works everytime though

On a slightly seperate note, how do you prevent the data from being recorded into the underlying tables? I tried inputting into the forms and then closed the form, but realised that all those data that I have typed in are already effected into the table. Is there a way to disable it such that it saves only when the user wants to?

 

[ansentry]

Paul,

I have just had a look at the sample I returned to you (0605) and it works fine.

Remember you have 3 forms;

Form1 = FrmMatrix
Form2 = FrmWorkers
Form3 = FrmTransfers

Before you can enter into form3 you must have a record in form2 and before you can enter into form2 you must have a record in form1.

The reason that yours can be hard to follow is;

1) You have a main form (frmMatrix) and you have a subform for it that's called FrmWorkers. You have the relationship as 1- Many but you only ever have 1-1. The way that you have your forms set up you cannot do what can be done in mine, that is have one record in the main form that has many related records in the subform.

2) What you really have is one big table, maybe you should have 1-1 relationships in lieu of 1-Many.

Have a look at the new sample, I have not make any code or links changes just make it a bit easier to read (for me anyway). Have a look at the instructions down the bottom, follow them and it will work fine.


Have a look under help for 1-1 relationships.

 

[ansentry]

Paul,

Sorry here is the attachment.

 

[kupe]

Thanks very much for the advice and the new zip, John. I have it and will be looking into it asap. Very much obliged to you. Will report back ...