Proper Use of SetFocus?

[rpeare]

here's the code I'm using when I open a particular database.

Dim db As Database
Dim rst_Courts As Recordset

Set db = CurrentDb

Set rst_Courts = db.OpenRecordset("tbl_courts")
If rst_Courts.RecordCount = 0 Then
    DoCmd.OpenForm "frm_Edit_Courts"
    Forms!frm_edit_courts!lbl_Warning.Visible = True
End If

Set db = Nothing

Basically I want a certain from to be opened and data entry to be forced before anything else in the database can happen (this would be a startup database for a local drug court)

I want the form 'frm_edit_courts' to be opened, which this is doing just fine and also activating a banner that tells them setup needs to be done before they can progress.

What is happening, however, is that when I do the record count then open the form if the recordcount is 0 is that the form is opening behind my main form.

I do not want to use modal/pop up restrictions on this database and though modal seems to work in concept it doesn't perform the way I want it to.

So my question is this what is the correct syntax to set the focus to the form frm_edit_courts, or am I thinking the wrong way.

I've tried

forms!frm_edit_courts.setfocus
forms!frm_edit_courts!<fieldname>.setfocus
forms.frm_edit_courts.setfocus
forms.frm_edit_courts.<fieldname>.setfocus

I've tried running the same code above strictly as code for the ON OPEN event of the main form as well and had the same results (form opens behind the main form)

[rpeare]

I've just tried this on the ON OPEN event of my main form as well, same result, form opens in the background.

I've also removed all coding from the form frm_edit_Courts so it doesn't appear to be related to the ON OPEN or other events I have on that secondary form.

If DCount("*", "Tbl_Courts") = 0 Then
    DoCmd.OpenForm "frm_edit_courts"
End If

[ItsMe]

You can try. Going off of memeory here

forms!frm_edit_courts.Forms.setfocus
forms!frm_edit_courts![fieldname].setfocus

Your If Then statement is checking for 0 records. So this code will have to execute somewhere else.

Another thing is you can use popup without the modal feature. I don't see how popup would confilct. However, popup and form.setfocus used together seem redundant.

[rpeare]

I tried something similar to that already as well to the same effect, I even put in debug.print messages between individual lines of code and it's progressing through my commands just fine just doesn't seem to execute them. I'm not even getting any error messages

Just for the sake of ruling out a problem with my form frm_edit_courts I created a brand new form with one control on it named fld_court_name. There is no code whatsoever attached to this form and ran this:

Dim frm As Form
Dim fld As TextBox

If DCount("*", "Tbl_Courts") = 0 Then
    DoCmd.OpenForm "frm_edit_courts"
End If

If SysCmd(acSysCmdGetObjectState, acForm, "Frm_Edit_courts") <> 0 Then
    Debug.Print "IN HERE"
    Forms!frm_edit_courts.Form.SetFocus
    Debug.Print "focus should be set to form frm_edit_courts"
    Forms!frm_edit_courts!fld_Court_Name.SetFocus
    Debug.Print "focus should be set to form frm_edit_courts field fld_court_name"
End If

this code is printing all three debug.print messages, I am getting no errors but the form is still opening in the background.

I know pop up would work but I really do not want to do that for this database I just can't figure out why the setfocus isn't working, nor can I figure out why docmd.openform is behaving differently when it's inside a set of code (in this case my record count check) vs. just being a simple docmd.openform that's no conditional.

[rpeare]

Now this is extremely strange...

I put in msgbox prompts between the commands

in each case the frm_edit_courts is on top until the end of the script when the frm_main is brought to the front again.

There are no other events on the form frm_main other than the ON OPEN event so I'm not sure why it seems to be correctly opening the form I want then shoving it into the background again.

[ItsMe]

Sounds like something is going on with form main. Check yo see if you have its properties on popup = yes

I tested the following code in a click event. Worked fine.

DoCmd.OpenForm "frmTestForm", acNormal
Forms!frmTestForm.Form.SetFocus
Forms!frmTestForm.[Text2].SetFocus

[rpeare]

I thought there might be a setting wrong with the main from as well and created 2 new forms from scratch and put in the same record count code and the same effect happened.

pop up and modal are both set to no and I'm currently looking at other settings that may be affecting it but so far I'm coming up blank.

[rpeare]

I created a completely blank database in both 2003 and 2007. Did a record count function before opening the form in each and had the same result on both bound and unbound forms. The 'main' form always ends up on top instead of the secondary form.

I'm including a quick example database. Two main forms, one unbound secondary form and one bound secondary form. The behavior is identical.

SetFocusExample.zip

This is not a simple open form which, based on what you typed, I think you are doing, if I do a simple 'docmd.openform "formname"' without doing the record check it works fine. In fact I have a button on the main form that performs that function. My problem is that I want the database to automatically go to that form if it is opened and finds no records in a specific table. When I perform that record check and open the form within a section of code that's where I'm having the difficulty of the 'main' form returning to the top after the code has run.

[ItsMe]

I will take a look at it. Give me a little time.

[ItsMe]

I was having trouble understanding your posts. Now I understand your posts and can't understand the behaviour of the DB. I have tried a few different things, but to no avail. I will have another look later. Definately weird.

[rpeare]

yeah I can not figure it out, the only solution appears to be to make form you are opening a pop up but I don't understand why the database does not interpret the openform command the same way regardless of how you get there.

[ItsMe]

I tried some more things. I created a function, I used the form's current event, I used the other form's current event, I called the function from the other form. Nothing seemed to matter.

Bottom line, the main form has to finish loading. Unless there is a way for it to be focused on a record within a recordset, it will not be finished loading. Perhaps you can place the other form in a subform container on the main form and use the .Visible command. Other than that, I don't have any suggestions.

[rpeare]

I think I've given up and just made it a pop up, I've thought about making it a tab control form for the entire application as well because I'll need this functionality for other parts of the database and nothing I've tried has worked either.

[ItsMe]

If the main form is bound to a recordset you could try something like Docmd acNext. Just thinking outloud. Guessing on a way to let it finish loading. Maybe if it is a bound form it would be as simple as set focus on someting that is tab count 2 or 3 and using the GotFocus for that control's event.

Don't know how important this is to you but....

[rpeare]

I tried it on a bound form with the same result. If I step through it the form opens, goes to the appropriate field, then as soon as it hits the 'end sub' it reverts back to the main form.