Entering Field Value with Code Doesn't Trigger New Record with AutoNumber

In trying to respond to another thread, I have run into something that is confounding me (or maybe I'm just getting dense).

We have a subform. One field has an event on DblClick to launch a search form. When the user identifies the target, he/she clicks a button on the subform. This pushes the appropriate value into a field on the original subform using VBA code and closes the search form. This all works fine.

The behaviour that is driving me bugging is when the user clicks on a new record (i.e. new line) on the subform, we would like to automatically generate the next record (E.g. when you type in a field of a record with autonumber in datasheet mode, Access automatically generates the next record). Currently this doesn't happen - Access generates the PK for the record being modified, but doesn't generate the view of the next record.

What really confuses me is that I have created similar looking example in which this works just fine. I can't figure out which of the differences between the two samples is causing this behaving.

Also, typing information into the field on the subform does cause the next record to be generated. It is just doing this via code that works in one case but not another.

I have narrowed it down to the actual subform. Even as a standalone form the form exhibits the same behaviour.

For reference, the original thread is
http://www.access-programmers.co.uk/forums/showthread.php?t=99457

Any suggestions?

-grommit

In the popup movie picker form, behind the button "Pick", use the name of the control instead of the name of the field...
I saw the behavior you spoke of, and the above change fixed it.
I don't know why the NewRecord command is in there.

Thanks, Sergeant! That's a clean, easy fix.

Any insight as to why the control name would be required in some instances, but not all?

The NewRecord command was an attempt by the original author to force the generation of the next record. I agree - it is not needed.

- g

gromit said:

Any insight as to why the control name would be required in some instances, but not all?- g

Click to expand...

I am not an Access scientist, just a monkey that has been shaved down, taught to speak and use a keyboard.
But if I had to venture a guess...
You have clicked into the new record row to begin adding a record, then the form pops up and you try to affect the underlying recordset, subverting the new row that you were originally adding (held in memory until you move focus from that row, right?) and this played on the forms 'synchronicity'.

Any insight as to why the control name would be required in some instances, but not all?

Click to expand...

When the control and the bound field have different names, you must use the control's Name property when working with control properties. When the wizards build forms/reports, they assign the bound ControlSource name to the control's Name property so it isn't always apparent that the names are not interchangeable. When you add controls to a form/report, they get added with a name such as Text256 or whatever. I ALWAYS change the Name property of controls to be txtRealName for text fields, cboRealName for combos, etc. That way, it is always clear in my code whether I am referring to the control or the data field within.

I'm the original author totally aware of the fact NewRec did nothing and was just a lousy try that ought to be deleted after giving no result.

Thanks a lot Sergeant. I'm kind a newbie to Access, could you explain in a sentence or two why this code works and previous did not?

This is the first time I've quoted myself...

Sergeant said:

I am not an Access scientist, just a monkey that has been shaved down, taught to speak and use a keyboard.
But if I had to venture a guess...
You have clicked into the new record row to begin adding a record, then the form pops up and you try to affect the underlying recordset, subverting the new row that you were originally adding (held in memory until you move focus from that row, right?) and this played on the forms 'synchronicity'.

Click to expand...

You should refer to the control name when adding values programatically, not the underlying recordset's field name. The form is there for a reason, it is a very good interface to a table or query...let it work for you.