Solved

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

Posted on 2002-06-12
9
268 Views
Last Modified: 2008-02-01
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.

The code for click event for each control on Form2 is as follows:

Private Sub [Control Name]_Click()
    If Not Form1.NewRecord Then _
        Call pubNoEdits(Me, Me.cboContactName)
End Sub

The called procedure is as follows:

Sub pubNoEdits(frmForm As Form, cboContact As ComboBox)
   
    Dim recClone As Recordset
   
    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

End Sub

The key lines are:

        If recClone.RecordCount = 0 Then
            Form1.[Desired Control].SetFocus
        Else
            DoCmd.GoToRecord , "", acFirst
            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.
0
Comment
Question by:Menes
9 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 7074733
is form2 a subform on form1? if so, the following code should set the focus back to the main form.

me.parent.cboX.Setfocus

where me and parent are vba reserved words for the active form and parent form.

replace cboX with your control name


0
 

Author Comment

by:Menes
ID: 7074767
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.

-MEM
0
 
LVL 8

Accepted Solution

by:
dovholuk earned 500 total points
ID: 7075222
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)

make sense or did i miss the meaning of the question?

dovholuk
0
 

Author Comment

by:Menes
ID: 7075307
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?
0
Highfive + Dolby Voice = No More Audio Complaints!

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.

 
LVL 8

Expert Comment

by:dovholuk
ID: 7075489
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?

just wondering...

dovholuk
0
 
LVL 4

Expert Comment

by:Nevaar
ID: 7075687
Have you stepped thru the logic in the pubNoEdits subroutine to see where it goes astray?
0
 

Author Comment

by:Menes
ID: 7077126
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.

Thanks,

-MEM
0
 

Author Comment

by:Menes
ID: 7077139
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.

Hope this helps.

-MEM
0
 

Author Comment

by:Menes
ID: 7077160
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

How to run any project with ease

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

Join & Write a Comment

Suggested Solutions

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…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now