Trying to find a record on a sub form using VBA

[lsemmens]

I have a database that catalogues my stamp collection. My problem is that I want to return to the last record I had displayed when last I Closed Access down. I have set a marker in the "Country" table, called LPOS, and a marker in a related table "StampCatalogue", called MPOS. Both are set to "Y" on close, and reset to "N" when the record is subsequently opened and found

I use the Load event on the Main Form to automatically return me to the last country (MPOS) that I had loaded. I then want to return to the last stamp I had displayed for that country (LPOS). I cannot automate this part of the function. The code will run as a stand alone but not as part of the initial Load event.

By using the following code (I have removed other, irrelevant, sections of code for clarity) I can automatically find the country

Private Sub Form_Load()
' set the field to search on
DoCmd.GoToControl "LPos" 'Absolute address is Forms!Country!LPos
DoCmd.FindRecord "Y", acEntire, True, acSearchAll, True, acCurrent, True
' clear flag
Me!LPos.Text = "N"

But I run into trouble when I then want to find the data in the subform
I've tried all sorts but can only execute the following as a seperate routine after the first one has completed

' find record in subform
Forms!Country!StampCatalogueSubform!Mpos.SetFocus
DoCmd.FindRecord "Y", acEntire, True, acSearchAll, True, acCurrent, True
Forms!Country!StampCatalogueSubform!Mpos.Text = "N"

How can I make the search on the subform run as part of the initial load event? I have:
a) included it as part of the Code
b) called it as a sub routine from the load event
c)placed it in the Load event of the SubForm
d)I've even tried the Activate event to no avail.

Help! Please, thanks in advance, Leigh

 

[mdmilner]

My guess is, the subform loads as an object on the main form, technically, after it's completely done. Attempting to manipulate the subform during the load event of the main form can't be done because it isn't there yet.

I would also think that putting the second set of code into the subform's Load or Activate event would do the trick. Could you be more descriptive about any error messages you're getting?

I haven't peered closely at your form references syntax - might be something in there.

Mike

 

[lsemmens]

Thanks Mike
I get no error message it just does not find the record on load
I can run the code after the country is found as a separate routine eg
click() event on a command button I just want to incorporate it into the startup (I'm lazy I guess)
I've tried using the load event of the subform and it won't work there either. I suspect it has something to do with the fact that the subform is filtered on the main form i.e. only records from the ctaht country displayed

 

[mdmilner]

What if instead of filtering the subform you changed the subforms recordsource each time the main form selected value changes? You'd have to have the subform do a Me.Requery each time.

 

[lsemmens]

The subform is where all the action happens. Each time I select a new record, code is executed to load two different .JPG's using a "Me.PictureBox.Picture = varName" statement
it also sets the current location into variables for parsing to another subform which records stamps actually on hand so...

the relationship is
table Country StampCatalogue OnHand
field Country >> Country
relnship 1 many
field OnHand >> OnHand
relnship 1 many

along with many lookup tables that are accessed via combo boxes
I was probably incorrect in saying that the form was filtered as the table is part of a one to many relationship being one Country!country to many StampCatalogue!Country

If it would help, I can try and strip the Data out of the app and post the entire Database here. It would require a couple of days work though as the data and associated pictures now runs to several Gig (I hit Access' 2G limit early in the piece when I embedded all images in the Database, took me some time to work out what had happened Access does not report this error very well "out of range" message if I remember) the actual Database itself now runs at 114Mb and growing.
While I have commented the VB code, there is very little explanation as to what does what on screen and no help file at all, I just haven't written it yet.

If all this is too hard, it's not a major issue as I'm just trying to save a mouse click, Just lazy I guess!
Thanks again
Leigh

 

[lsemmens]

Formatting was a bit out here so I'll try again

table ______ Country ____ StampCatalogue ___ OnHand
field _______ Country _______ Country
Relnship _____ one _________ many
field ______________________ OnHand _______ On Hand
Relnship ___________________ One ____________ Many

 

[mdmilner]

I think we're back to my original guess.
The main table loads onto the main form and sets the focus as it loads.

The subform is going to want to display only the related records to the record now in focus...it's going to need to do that first, before you can attempt to set the focus within the related records showing on the subform.

So, what if you put your code within the subforms FOCUS event?
I don't think loading the subform triggers the FOCUS event, only moving onto the subform does that. At least, that's my current guess. Have to see if I can duplicate your sample quickly and test.

Mike

Forms!Country!StampCatalogueSubform!Mpos.SetFocus 
DoCmd.FindRecord "Y", acEntire, True, acSearchAll, True, acCurrent, True 
Forms!Country!StampCatalogueSubform!Mpos.Text = "N"

 

[lsemmens]

Thanks Mike, sorry I took so long to get back, I've been off line for a few days. I'll try the Focus event. My concern isthough, that every time I move to a new record the Focus event will want to activate, which cpould be a major bottleneck