VBA won't change focus to control on another form.

I have a pair of linked forms, Form1 & Form2, each based on a seperate table, Tbl1 & Tbl2, respectivley, in which Tbl1 has a One-to-Many relationship to Tbl2. Both forms are viewed as Continuous Forms. So, when a control is selected in Form1, corresponding to a record in Tbl1, the appropriate set of records from Tbl2 are displayed in Form2. In adition to the records from the underlying tables on each form, a New Record is displayed on each form. (Ideally, I would like the New Record NOT to be displayed, but have not found a way to do this.) Currently, if I click the new record in Form2 and there is more than just a New Record on Form2, the control changes to a specific control on the first record in the list, which is the correct behavior. However, when some records on Form1 are selected, there are no corresponding records from Tbl2, so only a New Record is displayed on Form2. In this case, whenever any control on Form2 is clicked, instead of changing the focus to the specific control the current record, which is the new and first and only record, I would like the focus to change back to Form1. In other words, never allow a new record on the form to keep the focus, even if it is the only record on the form.

Set recClone = frmForm.RecordsetClone()
If frmForm.NewRecord Then
MsgBox "You cannot create a new record using this form."
If recClone.RecordCount = 0 Then
Form1.[Desired Control].SetFocus
Else
DoCmd.GoToRecord , "", acFirst
cboContact.SetFocus
End If
Else
MsgBox Chr$(34) & "Contact Full Name" & Chr$(34) & _
" is the only field that can be edited " & _
"using this form."
cboContact.SetFocus
End If

This means, whenver there is only a new record displayed on Form2 (RecordCount=0), the focus should go to a specific control on Form1 (Form1.cboContact.SetFocus), otherwise, the control should go to the first record displayed on Form2. However, when this code is executed, if there is only a new record on Form2, the focus simply remains on the control that was clicked on the new record on Form2 and seems to ignore the fact that I have specified Form1. I have also tried, simply, Form1.SetFocus. That didn't work, either. Once I have clicked Form2, the focus remains there, regardless of what is specified in the SetFocus command.

The idea is not to permit users from adding any records using either form and only to enable them to edit the record using the "DesiredControl" for any record on either form. Again, ideally, I would rather NOT EVEN have the new record display on either form, but, short of this, if Form2 has no records corresponding to the record selected in Form1, showing only a new record, I would like the focus to go back to Form1 if a control is selected on Form2.

I will also submit this to the Visual Basic section and award the points to whoever, in either section, offers the best solution.

if you don't want to allow users to add records on either form... just set the allowadditions property to false on those two forms... then users can't add data using those forms... (they can ALWAYS link

Form1 & Form2 are linked forms, but Form2 is NOT a subform of Form1. Access wouldn't let me have both views set to "Continuous", in which several records are viewed at once, similar to a datasheet view, but with a header, footer, & the ability to add controls, if one was a subform of the other. This forced me to use linked forms.

Nevertheless, I tried your solution without success. While typing code, I noticed that after typing "Me.Parent" then typing another ".", I didn't get a quick select list after the last period. Upon executing code, I get a message saying I've used an invalid reference to the Parent Property.

if you don't want to allow users to add records on either form... just set the allowadditions property to false on those two forms... then users can't add data using those forms... (they can ALWAYS link to the table if they have sufficient access knowlege, but that's a different matter)

I have the "AllowAdditions" property set to "False", but I still want the focus to return to Form1 if only a new record is displayed on Form2 when it is clicked. Furthermore, it would be really nice if the new record was never displayed on either form. Any suggestions for this?

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

ok. i'm lost as to why you are seeing the "new record" if you have allow additions set to false... are you running an update query that inputs a blank record or is the query returning a blank record that "looks like" a new record?

sounds odd...

are you changing recordsource's, using filters, or relying on access to link the forms in order to display the "relevant" data. it seems like you are using access if i read your question right... have you thought about changing that?

I don't know why the new record is displayed, but it is. It won't actually allow me to enter any data, but it still shows up. After selecting an item from the combobox, it displays the message saying I am not allowed to add a record using this form, which I programmed. Clicking "OK", I get a run-time error, 3205, saying I can't add a record to the table because a related record is required in the parent table. Clicking "End", the item selected is displayed in the combobox field, despite the previous message. However, upon selecting a different record on the parent form, Form1, I get another message saying I cannot add a record to because a related record is required in the parent table. This last message does not indicate a run-time error, as did the previous one. Both of the last two messages are generated by the system, not explicitly by my code as with the first one. If I press <Esc>, the newly created record dissappears & I can move onto another record. So, it does control the data as I wish, but I want the user interface to be more intuitive.

Reading my last entty, I see I need to clarify a couple of things. When I select an item from the combobox on the new and only record on Form2, I get the run-time error, click "End" and the item is shown as selected in the combobox, and it is displayed as if in a record. At this point, it is no longer the only record shown, for a new record is shown below it. At this point, if I click a different record on Form1, I get the second "cannot add record" message. I must return the focus to the combobox on Form2, then press <Esc> to get this record to disappear from Form2 and leave only a new record displayed. At this point, I can move to another record on Form1.

NOTE: In the second to last sentence, "move onto another record" means move onto another record on the parent form, Form1. There is, at this point, only one record, a new record, displayed on Form2.

Okay, I feel very very silly. I just checked the AllowAdditions property again & discovered, althought I thought I had set it to "No", I had actually set the property below it, DataEntry, to "No". Suddenly, none of this is necessary. Sorry to bother everyone.

I'll mark it up to being a neophite. You can mark it up to my being a dufus.

Thanks.

0

Featured Post

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved. W…

Introduction
The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…

As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…

Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…