Setfocus to first record in a subform

[parry]

Hi, I have a subform and want to set focus to the first record in the form using VBA. When I use DoCmd.OpenForm Subform_Filter_data the subform opens a separate window instead of focusing on the subform within the main form. The main form is open at the time.

If I try Forms!Subform_Filter_data!Code.SetFocus I get an error, because the form isnt open.

Any ideas how to reference the subform?

cheers
Parry
This message was edited by parry on 2003-01-09 18:49

 

[Russell Hauf]

If you have inserted a subform onto a form, then it should be open when the main form is open. Are you using Access 2000? If so, you can use something like:

Me.Subform.Setfocus

where subform is the name of the subform OBJECT on your form, not necessarily what the name of the subform is.

HTH,

Russell

 

[parry]

Thanks for the reply Russell. I found this link here http://www.programmingmsaccess.com/TheBook/A2002Excerpts/ProgrammingMainandSubFormControls.htm where it mentions that because the subform is a control on the main form you cannot access the controls on the subform directly like i have been trying.

Im still a bit stuck but getting closer. What I want to do is for the user to enter a number in a text box thn click a command button. Under the click event I want to search for the number in the code field on the subform.

Heres the code I have so far. Its finally getting focus to the subform but not doing a search and finding the code number. It just sits on the first record - no error appears.

Private Sub Command52_Click()
On Error GoTo Err_Command52_Click
Dim SearchBox
Dim Result
Dim FormName As String
Dim Frm1 As Form
Dim Ctl1 As Control

'Set focus to search box
    Me.txtGotoRecord.SetFocus

'Initialize values
    SearchBox = Me.txtGotoRecord
    FormName = "Subform_Filter_data"
    Set Frm1 = Forms("FormSearch")
    Set Ctl1 = Frm1.Subform_Filter_data
        
'first check if a valid number was entered
    If txtGotoRecord.Value = "" Or IsNumeric(txtGotoRecord.Value) = False Then
        MsgBox "Please enter a number and try again", vbExclamation, "Code Number Required"
        txtGotoRecord.Value = ""
        txtGotoRecord.SetFocus
        Exit Sub
    End If

'Set focus to the Code field in the subform
    Ctl1!Code.SetFocus

'Look for the number
    DoCmd.FindRecord SearchBox, acAnywhere, False, acSearchAll, True, acCurrent, True
    
'Determine search result
    If Ctl1!Code.Value <> SearchBox Then
    MsgBox "The Record you searched for was not found", vbInformation, "Project Record Not Found"
    Me.txtGotoRecord.SetFocus
    Exit Sub
    End If

Exit_Command52_Click:
    Exit Sub

Err_Command52_Click:
    MsgBox Err.Description
    Resume Exit_Command52_Click

End Sub

What am I doing wrong?

 

[Russell Hauf]

This is very similar to this http://mrexcel.com/board/viewtopic.php?topic=34837&forum=14&10 thread. I prefer to filter the main form based on results of the subform's record source. Take a look - it actually works very well (what I posted was right off of a sample database that I created).

-Russell

 

[parry]

Thanks Russell, I actually saw that thread but I dont want to do a filter. The main form has several combo boxes to filter the form by various criteria. The boss now just wants those filtered records to remain but set focus to a particular record within those filtered records. Some people are never happy

The form is in data view if that matters. It shouldnt as Ive tried the normal Find control in the toolbar and it found the record when I had focus on the subform and correct field.

I have to do a find record or something similar. It must be the following line thats wrong ...
DoCmd.FindRecord SearchBox, acAnywhere, False, acSearchAll, True, acCurrent, True

Any other suggestions?

 

[Russell Hauf]

Sorry, I started a response last night but got busy and never hit "submit". Let me take a look at it - I want to try it from scratch as your code is giving me a headache (no fault of yours - I think it is the small font!).
Meanwhile, maybe someone else will step in and save the day...

-Russell

 

[Russell Hauf]

Try something like this. Basically, the only thing you need is to set the focus to the subform first (Ctl1.SetFocus), but I made some other changes too...eliminating some unnecessary variables and also putting your "check" block before the initialize block (why initialize what you're not going to use, right?).

Hope it helps.

<pre>
Private Sub Command52_Click()
On Error GoTo Err_Command52_Click
Dim SearchBox
Dim Result
Dim FormName As String
Dim Frm1 As Form
Dim Ctl1 As Control

'Set focus to search box
Me.txtGotoRecord.SetFocus

'first check if a valid number was entered
If txtGotoRecord.Value = "" Or IsNumeric(txtGotoRecord.Value) = False Then
MsgBox "Please enter a number and try again", vbExclamation, "Code Number Required"
txtGotoRecord.Value = ""
txtGotoRecord.SetFocus
GoTo Exit_Command52_Click
End If

'Initialize values
SearchBox = Me.txtGotoRecord
Set Ctl1 = Me.Subform_Filter_data

'Set focus to the Code field in the subform
Ctl1.SetFocus
Ctl1!Code.SetFocus

'Look for the number
DoCmd.FindRecord SearchBox, acAnywhere, False, acSearchAll, True, acCurrent, True

'Determine search result
If Ctl1!Code.Value <> SearchBox Then
MsgBox "The Record you searched for was not found", vbInformation, "Project Record Not Found"
Me.txtGotoRecord.SetFocus
Exit Sub
End If

Exit_Command52_Click:
Exit Sub

Err_Command52_Click:
MsgBox Err.Description
Resume Exit_Command52_Click

End Sub</pre>

-Russell

 

[parry]

You da man!! Thanks very much Russell. I've spent ages (3+ hrs) trying to work it out, from trying seek to making a recordset and trying Movefirst/next to loop through etc all to no avail. And the answers so simple too.

I definetly owe you a beer for this one

cheers
Parry