Subform not loading 2

[Loktar]

Hello all,

I'm working on a form in Access 2000 with a subform in it. This subform is based off a query that uses a control in the main form to filter data. My problem is that when I load the main form, at first the subform doesn't have any records visible, although the labels still show up fine. When I click on the subform area, Poof!, all the records show up.

The only guess I have is that Access must load subforms first, then main forms, causing no records to be pulled up in my query the first time through, and then when I give the subform focus again, it finds new matches.

I've tried a lot of code (especially with .Refresh and .Requery) in many of the form and subform functions, but nothing is working. Is there anyway I can get the subform to automatically load those records when the main form is opened?

Thanks in advance!

 

[Rolliee]

I would re-do the form by makiing a new main form and add the subform again. They should show up.

rollie@bwsys.net

 

[TheAceMan1]

How are ya Loktar . . . .

You said: [blue]The only guess I have is that Access must load subforms first, then main forms . . . .[/blue], and your absolutely right. [purple]Forms are opened from the inner most subform out to the main form.[/purple]

Apparently your not using the [blue]Requery[/blue] method correctly. Try the following code in the [blue]On Load Event[/blue] of your main form:

[blue]Dim frm As Form
Set frm=Me![purple]YourSubFormName[/purple].Form
frm.[purple]Requery[/purple]
Set frm=Nothing[/blue]

Also, if the control on your main form (criteria for the subform query) changes data when you change record in the main form, put the code in the [blue]On Current Event[/blue] of your main form instead, and the subform will automatically synchronize . . . .

See Ya! . . . . . .

 

[Loktar]

Thanks for the tips... but none of them have worked so far. I haven't rebuilt the form yet, but I'm thinking about trying that soon.

Ace, I have a question for you... What is the On Current Event everyone is always talking about? I'm assuming that it is the Form_Current() function, but I'm not sure. I put the code you gave me in the _Current(), _AfterUpdate(), and _Load() of the main form... but nothing changed. I also tried with a frm.Repaint in there too.

And you were right that my subform will change with every record in the main form. Thanks for the tip about coding in the On Current Event. I'm sure that once this main problem is fixed, that code will work as well.

And just to make sure theres no confusion, the problem I'm still having is that my subforms records dont show up until I click on the subform. Before that they are just blank, with only the subform's labels showing.

Thanks again for the help so far!

 

[Loktar]

Another thought I just had is that I don't have any linked controls between the form and the subform. All the record lookup is done with a query. Could that be causing this?

Thanks again!

 

[TheAceMan1]

Loktar . . . . . . .

What is the [blue]On Current Event[/blue] everyone is always talking about?

This is a Form Event that [blue]occurs everytime you change record in a form (move from one record to another).[/blue] It allows you to do things when you move to another record (like synchronizing a subform, prompting for info, doinging calculations, ect. . . .). its one of the events thats great for automation.

I don't have any linked controls between the form and the subform. All the record lookup is done with a query.

This does'nt matter since your synchronizing with a control. I do query . . . . . [blue]just what type of control is it and where does it get its data?[/blue] Is it related to the underlying tables, is it a calculated control, is it bound/unbound?

Considering what you've tried so far, if the next few posts don't fix this, then I'll have to agree with [blue]Rolliee[/blue] . . . . . . [purple]Delete Form & subForm and Reconstruct . . . . . .[/purple]

See Ya! . . . . . .

 

[BSman]

Change the data source for the subform so it does not include the criteria based on the value in the main form. Then link the subform to the main form through whatever number of columns in the two source tables/queries that are necessary. One of the columns will correspond to the column behind the control in the main form that you had previously included as criteria for the subform's query.

 

[Loktar]

Well, I managed to find something that works. It does cause a slight flicker when it loads, but at this point, I think I'm ok with that

After a few hours of experimenting with .Requery calls I finally decided to make a "Fix It" button. Believe it or not, it actually worked!

By using the button I was able to come up with the following code in the main form to solve the problem:

Private Sub Form_Load()
    Me.SetFocus
    With Me.subfrm_AllItems
        .Requery
        .SetFocus
    End With
End Sub

Since the subform's records seemed to magically appear when it got focus, I had tried .SetFocus before, but with no luck. By using the button I realized that the main form had to get focus first (clicking on the button clued me in on that), THEN set focus on the form, but only after doing a .Requery...

I really have no idea why this works, but for now I'll accept that fact and move on I guess it's just some weird bug/"feature" in Access. Thanks a ton for your help. It really helped in getting me pointed in the right direction!

One last thing, when you responded about the On Current Event comment I made, is that the Form_Current() function that does that? I'm confused since everyone refers to it by saying "On Current" but there is no Form_OnCurrent() function.

Thanks again for your help and speedy replies!

 

[Loktar]

BSMan,

Change the data source for the subform so it does not include the criteria based on the value in the main form.

I would if there was an easy way around this. The problem is that I was using parts of string values stored in the main forms table and comparing them to parts of string values in the subform's table. There was no easy way of getting the two to match up, and due to the way our data is gathered, there wasn't an easy solution to reformatting that data.

 

[BSman]

Base the form and subform on queries that include calculations of the substring for each table. Even though you mayt display the substring, you can still use the "dummy" column in each query to link the main form and the subform.

 

[TheAceMan1]

Loktar . . . . .

First, to answer your question. There is no form [blue]On Current Function![/blue] Whats being talked about is the forms [blue]On Current Event[/blue]. In form design view, open the properties window and click the [blue]Event Tab[/blue]. You'll see the [blue]On Current[/blue] event right at the top. Put the cursor on that line & press F1 to read all about it.

While the cursor in on the line, click the three elipse button that appears to go to the [blue]On Current[/blue] event routine in VBA. I hope this is where you placed the code!.

Now, with your latest code, your curing the problem, not fixing it! There is something wrong . . . . . and its not a bug! For all my years programming Access, this is not normal behavior, even if somethings wrong.

Taking into account there's info we don't know yet, you need to apprise us if you want to continue with resolution or accept your cure?

And, oh yeah . . . . . you never answered my question about that all depending control:

[blue]just what type of control is it and where does it get its data?[/blue] Is it related to the underlying tables, is it a calculated control, is it bound/unbound?

See Ya! . . . . . .

 

[Loktar]

I'm still working on getting a "real" solution. Here's a more detailed breakdown of the situation. [Warning, long explanation ahead ]

In one table (linked to an excel file, of which i have no control over format etc.) there is an [ItemType] field that contains item information dealing with the inventory. An example would be abc-def--INV123. the --INV123 means its a part of inventory group INV123 and it shares its photo, description, etc. with other items in that group. This also means that [Item] INV123 is the "master" for that group. The record for INV123 holds the description for the group (memo field) and INV123.bmp is the groups photo name.

From the menu form in the db, when a user searches for an item by its # ([Item] field), it pops up a textbox to get the #, and then needs to pull up frmGroupedItems with the appropriate info. This is the form I've been having problems with. frmGroupedItems has a subform subfrmAllItems that is a continuous form listing various info about each item. frmGroupedItems has info for the group in it.

If the item searched for is not part of a group (doesn't have --..... at the end of its [ItemType] field) it will just pull up 1 record in the subform. If it is part of a group, it pulls up all the records for the group in the subform and uses the "master" items info in the main part of the form to give the group info. Right now I have it reload the form as if it was being searched for the main item.

My problem is that the info that "link" the tables have so many different cases to think about (is it part of a group? If so, is it the "master" or not?) and these all have different queries that need to get done. On top of that, they are only parts of strings found in the fields.

I'm still pretty new to Access and VB code, and I'm sure my C/C++/Java background is muddying my thought process

Thanks, once again, for all your help and your interest in helping me solve this problem!

 

[Loktar]

Ah, ok. Now the On Current makes sense. I don't know why it didn't click before.

Also, I've pretty much got this thing wrapped up. I started it over using new forms, and did what BSman was saying about using queries to get the data in the right format first. I used the code I had before to generate a dummy column in a query for the group number, which access seems to like a lot better than me trying to jam values into forms using requeries and reloading forms

It took a few more queries than I was originally thinking, but in the end it runs smoothly and doesn't feel hacked together.

Thanks a ton for all your help. The project is complete and I feel like I've learned a lot today. Couldn't ask for much more than that! Stars all around!

 

[TheAceMan1]

Loktar . . . . .

Great! ;-)

I'm sure [blue]BSman[/blue] is pleased . . . . as am I.

Don't forget to log what you've learned . . . . . I'd be lost without personal logs, and they've have saved my life so many times, logging has become automatic . . . . quite an impressive library I have!

[purple]Good hinting in your future designs! . . . . . . .[/purple]

See Ya! . . . . . .