20 years and I still don't know this

I have subforms on my forms that I sometimes keep hidden (Visible=false) I do this so I can program changes to the values in various controls without the user having to know what is being changed,

Despite being a one to one relationship with the main form Access does allow the subform to move to a second record and allow data to be entered. To Stop this Scott gave me

If KeyCode = vbKeyPageUp Or KeyCode = vbKeyPageDown Then
    Me.{any OTHER field in your form}.SetFocus
End If
To place on every control (42 of them) in the subform. But when I make the subform visible the focus moves to some cmdbuttons even without tab stop and the PgUp PgDown buttons allow the form to move to a new record.

I tried [subform].Form![control].SetFocus on the button that makes the form visible to take the focus away from the buttons but it ignores it because making a form visible does not seem to cause an event to happen
Derek BrownMDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill RossProgrammerCommented:
Did you try setting the sub form property Allow Additions to No?

Regards,

Bill
0
Gustav BrockCIOCommented:
If it is a one-to-one relationship and you use the ID for both master- and linkfields, there will be only one record in the subform, except for a new record but I guess you have disabled that.

/gustav
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not quite sure what you're asking.

Do you not want your subforms to be able to move to new records? If so, just set the subform to disallow the addition of new records (i.e. AllowAdditions = False). You can toggle this back on as needed, if you need to add a new record via code.

Or are you having trouble with the focus on the form? If so, then AFTER you make the subform visible, just set the focus as needed:

Me.YourSubformControl.Visible = True
Me.SomeControlOnTheParentForm.SetFocus
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Derek BrownMDAuthor Commented:
Bill, That causes the form to go blank when no data .

Gustav, That's correct but the subform still allows data entry and does not report a problem until the focus moves to the main form. I guess in an mde that would just crash.

If you look at the screenshot there is a toggle control that will not let me add

If KeyCode = vbKeyPageUp Or KeyCode = vbKeyPageDown Then
    Me.{any OTHER field in your form}.SetFocus
End If

Because there is now KeyDown event with a toggle. So if you then click the existing depressed button the sub form moves to a new record.

I may be being extraordinarily finicky about this. Is anyone likely to click the toggle on the side that is already selected and then press the Page keys? It's just that it causes havock when they do.
Capture3.PNG
0
Bill RossProgrammerCommented:
Hi,

The subform will not be blank if you locate the ID on it.  Make it enabled and locked.

Regards,

Bill
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<If KeyCode = vbKeyPageUp Or KeyCode = vbKeyPageDown Then
    Me.{any OTHER field in your form}.SetFocus
End If
>>

  Set the forms KeyPreview  to true and in it's keydown event do:

If KeyCode = vbKeyPageUp Or KeyCode = vbKeyPageDown Then
    KeyCode = 0
End If

This effectively disables pg up/dn for the entire form.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Like LSM, I am confused at your request...,

<Despite being a one to one relationship with the main form Access does allow the subform to move to a second record and allow data to be entered. >
When?
Under what circumstances?

Lets be clear, when you open a standard main/subform, you can quite easily move the subform to another record....
So I don't understand why this very basic functionality is not working for you.?


As an aside, ...in looking at your form here, it seems that you may just be trying to do too much with this single form...

Perhaps I am not understanding something, ...so see if the other Expert's posts help first.

JeffCoachman
0
Derek BrownMDAuthor Commented:
Jeff, The reason for having a one to one relationship with the main form is not a conventional one. It is simply that I am running out of columns in my tables and queries. It is done simply so that I can have more field controls on the form.

The main reason for the form is of course to store data but equally to create a report with a scaled drawing of the product. I am not sure how I would create a drawing (which is a one page, one record report) if some of the data was in multi records from a subform.

Thanks for the code Jim. I have been looking for that for decades.
Bill good to see you back.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.