Search for record on subform (1 Viewer)

Kenln · Jul 20, 2010

Hi all,

I have a main form sub form. However I do not populate the subform until the main form is open. i.e. in the Form Load (of the Main Form) I set 'RecordSource' of the subform.

I would like to open the form and have the subform go to a specific record.

I have tried:

Code:

Dim rs As Object
Set rs = Me.sfrm_Common.Form.Recordset.Clone
rs.FindFirst "[dt_Date] = " & Trim(Str(DLookup("dt_Current", "tbl_Stored_Date")))
If Not rs.EOF Then Me.sfrm_Common.Form.Bookmark = rs.Bookmark

I checked and the

Code:

Not rs.EOF

is true.

Any ideas?

I appreciate any help you can give me with this,

Thank you,

Galaxiom · Jul 20, 2010

What actually happens when you run the code?

AccessBlaster · Jul 21, 2010

This is just a total guess

Code:

Dim rs As Object
Set rs = Me.sfrm_Common.Form.Recordset.Clone
rs.FindFirst "[dt_Date] = " & Trim(Str(DLookup("dt_Current", "tbl_Stored_Date")))
If Not rs.EOF Then Me.sfrm_Common.Form.Bookmark = rs.Bookmark

Code:

Dim rs As Object
Set rs = Forms![COLOR=red]MainForm[/COLOR]![COLOR=red]Subform[/COLOR].Form.Recordset.Clone
rs.FindFirst "[dt_Date] = " & Trim(Str(DLookup("dt_Current", "tbl_Stored_Date")))
If Not rs.EOF Then Forms![COLOR=red]MainForm[/COLOR]![COLOR=red]Subform[/COLOR].Form.Bookmark = rs.Bookmark

Richard

vbaInet · Jul 21, 2010

Two things I noticed:

1. The date you're using for FindFirst is a string, not a date.

2. When you do a FindFirst you perform a rs.NoMatch, not rs.EOF after that.

Kenln · Jul 21, 2010

Yeah! With your help I solved it. I was over looking the simple answer.
First, when the original code ran, nothing happened???

So here is what I did.

Code:

Dim rs As Object
Set rs = Me.sfrm_Common.Form.Recordset.Clone
rs.FindFirst [B][COLOR="Red"]"[/COLOR][/B][dt_Date] = DLookup([B][COLOR="red"]'[/COLOR][/B]dt_Current[COLOR="red"][B]'[/B][/COLOR], [B][COLOR="red"]'[/COLOR][/B]tbl_Stored_Date[COLOR="red"][B]'[/B][/COLOR])[COLOR="red"][B]"[/B][/COLOR]
If Not rs.EOF Then Me.sfrm_Common.Form.Bookmark = rs.Bookmark

Yep, the [dt_Date] was not a string.
Yep, using the Forms.Main_Form.Subform.Form is what helped me find it.

But proper use of quotes was my problem.
Sometimes I miss the obvious.

Thanks again,

Kenln · Jul 21, 2010

Don't know if I should start another thread for this but.

Next question: I know how to select a specific field in a record (after it is found). Does anyone know if I can and if so how do I select the entire row/record?

Thanks again,

vbaInet · Jul 21, 2010

Glad you found a way.

This would have been more appropriate:

Code:

Dim rs As [B][COLOR=Blue]DAO.Recordset[/COLOR][/B]
Set rs = Me.sfrm_Common.Form.[B][COLOR=Blue]RecordsetClone[/COLOR][/B]
rs.FindFirst "[dt_Date] = #" & Nz(DLookup("dt_Current", "tbl_Stored_Date"), "1/1/1800") & "#"
If Not rs[B].[COLOR=Blue]NoMatch[/COLOR][/B] Then 
    Me.sfrm_Common.Form.Bookmark = rs.Bookmark
end if
set rs = Nothing

I've highlighted only the not so obvious amendments. Also, like mentioned, you use NoMatch instead of EOF.

Kenln · Jul 21, 2010

I definately like your code example better. Much cleaner.

Thanks again,

Not if I can select an entire row/record I'm set.

vbaInet · Jul 21, 2010

To select the record just add this line after setting the bookmark:

Code:

DoCmd.RunCommand acCmdSelectRecord

Kenln · Jul 21, 2010

It already does that with the bookmark; i.e. go to a record.

However it's focus is the first text field. I can change this with the 'SetFocus' method and it works.

But what I am look for is: Along the left hand side of a form is the record select button (okay it isn't really a button but close enough). If the form is in DataSheet and you click the record select on the left hand side the entire row is highlighted.

Can I do this programmatically?

vbaInet · Jul 21, 2010

That is what that code line does, highlights that line. Have you tried it yet?

The bookmark property sets the cursor of the recordset to the chosen bookmark and in that case the first column gets focus.

Kenln · Jul 21, 2010

Hehe, yes I tried it and it too (DoCmd.RunCommand acCmdSelectRecord) gave gocus to the first column.

vbaInet · Jul 21, 2010

That's an odd behaviour. Let's see your finished code.

Also, is there any other code aside this one that is setting focus to the textbox or is affecting that field or control in anyway?

Kenln · Jul 23, 2010

The record is "selected".

I was expecting something along the lines of when I select a row with my mouse.

Thanks,

vbaInet · Jul 23, 2010

Can you post a stripped down version of your db?

Search for record on subform (1 Viewer)

Kenln

Registered User.

Galaxiom

Super Moderator

AccessBlaster

Registered User.

vbaInet

AWF VIP

Kenln

Registered User.

Kenln

Registered User.

vbaInet

AWF VIP

Kenln

Registered User.

vbaInet

AWF VIP

Kenln

Registered User.

vbaInet

AWF VIP

Kenln

Registered User.

vbaInet

AWF VIP

Kenln

Registered User.

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread