Form won't refresh

[BroncoMarc]

I have a form based on a query that changes based on combo boxes on the form.

When I changed the selection in the combo box the data in the rest of the form wasn't changing based on the query. I tried putting me.refresh and me.requery in the After Update event of the combo box.

I thought my query was wrong until I tried running the query by itself (with the form open and a selection in the combo box). The query works fine.

I have a box on the form that shows a count of the records that match the current combo box selection. That works... but even if it says 3 records match, I can still see all 2500 records in the form.


I tried copying everything from the form except the combo boxes to a sub form and that works. It updates just like it's supposed to. But, I don't want to mess with a sub form.. I want the main form to work.

Any idea what's going on?

Thanks,
- Marc

 

[MikeAngelastro]

Try adding the combobox value to the query supporting the form as a criterion for the field it represents. You do this by right-clicking the criterion part of the QBE grid and selecting build and following the instructions. Then on the form do a requery on the after update event of the combobox.

 

[BroncoMarc]

That is what I am already doing. The problem is it does not update the data on the form when I do a requery.

The query works fine when run on it's own. But the data in the form does not change when I change the selection in the combo box.


Below is a snip of code showing the refresh routine. I've tried me.refresh and me.requery (What's the difference?) and neither work.

==================================

Private Sub EraseFilters()
cboSearchForStatus = Null
cboSearchForProjNum = Null
cboSearchForGroupLeader = Null
cboSearchForAssigned = Null
cboSearchForCustomer = Null
cboSearchForMarket = Null
cboSearchForSubmitter = Null
cboSearchForProdCode = Null
End Sub

Private Sub RequeryData()
Me.Refresh
TxtTotalRec.Value = DCount("[ProjNum]", "$MainTableQuery")
End Sub

Private Sub cboSearchForStatus_AfterUpdate()
txtFilterName = cboSearchForStatus.Value
EraseFilters
cboSearchForStatus.Value = txtFilterName
RequeryData
End Sub

Private Sub SearchForGroupLeader_AfterUpdate()
txtFilterName = cboSearchForGroupLeader.Value
EraseFilters
cboSearchForGroupLeader.Value = txtFilterName
RequeryData
End Sub

===================================

Sample from query:

- Marc

 

[BroncoMarc]

[No message]

 

[Sharky II]

i'm getting the same problem - Me.Requery doesn't work.

any info appreciated.

 

[MikeAngelastro]

After you update the value in the combo box, run the query separately with the form still open. If the query works but the form does not update, your form may be corrupt. If this is the case try recreating the form.

 

[BroncoMarc]

I tried creating another form and it does the same thing.


This thing is being a HUGE P.I.T.A.! Every workaround I try has it's own flaw that doesn't work.

Summary: Basically, I have a main form with several combo boxes that limit what the query shows and what shows in the form. There are several tabbed pages on the form with different parts of the records on each page.

If I make the data part of the main form, when I change the combo boxes to limit the records, the data is never updated. It still shows every record. If I run the query on it's own it works fine, so my query is good.

Workaround - make a subform for each tabbed page. Hey it works! When I change a combo box the data below is limited to the query. Great.. the first tab works.. lets try the 2nd... Hmm.. now nothing is linked! If I set the first tab to record 10, the 2nd tab is still on record #1.

Ok, so I need to link the forms togather. The main form and all subforms are based onthe same query, so I just link ProjectNum in the main form to ProjectNum in each subform. I test it and they all seem to be linked now. So, lets test the combo boxes... (This part is great..) I select something from the combo box and the entire subform disappers!!! I clear it out and it comes back!


So, right now I had one tab with the data on the main form that ignores the query. I have a 2nd tab with the data on an unlinked subform that works with the query, but isn't on the same record as any other tabs. I have a 3rd tab with the data on a linked subform that disappears anytime something is chosen in a combo box.

Can someone help me before I go insane??

- Frusterated.

 

[WayneRyan]

Marc,

Is there any chance that you could post your db here?

Compact, ZIP and post.

Wayne

 

[Pat Hartman]

1. Help explains the difference:
Refreshing records only updates the data that already exists in your datasheet or form. It doesn't reorder records, display new records, or remove deleted records and records that no longer meet specified criteria. To perform those actions, requery the records.

2. You can't use filters on subforms.

3. Are you sure that this works - TxtTotalRec.Value = DCount("[ProjNum]", "$MainTableQuery") I would change it to:
TxtTotalRec.Value = DCount("[ProjNum]", "[$MainTableQuery]") - VBA does not work properly when object names include spaces or special characters.

4. Get in the habit of using queries that contain ONLY the columns necessary for a form. Therefore, EVERY subform will need a separate query.

5. Use the mainform to locate the record that you want and then set the master/child links of the subforms. Access will then sync your forms for you without any code.

 

[BroncoMarc]

Wayne..
I'll have to load up some fictional data to post it here.. Right now it's using data from an SQL server and has about 2500 records. I was worried that it would be too large to post here (It was around 5 megs), but I copied the tables locally, then deleted 99% of the records and compacted it and zipped it. Now it's 162k !



Pat..

#3 - I copied that from a database that I'm recreating. It seems to work. It gives an accurate record count. I'll make the change you suggested.

#4 - Is this an optimization thing? Will it speed it up?

I'll try the suggestions you gave in #4 and #5 today and let you know how it works.

- Marc

 

[BroncoMarc]

I broke up my queries so there are seperate queries for the main form and all sub forms. I also linked all the subforms back to the main form. Now I'm back to the original problem. The data doesn't update when I change the combo boxes.

Then I tried changing Me.Refresh to Me.Requery and it started working!

Hopefully that's all that was wrong.

One more question (Hopefully!)... What does it mean when I open the form and it says "This recordset is not updatable" along the bottom? Is it something to do with the data being on and SQL server?

Thanks!
- Marc

 

[Pat Hartman]

It means just what it says. Whether the data is in an Access table or linked ODBC table doesn't matter as far as updatablility is concerned. One reason that a linked table cannot be updated is if it does not have a unique primary key.

Take a look at these:
INFO: Troubleshooting Errors That May Occur When You Update Data in Queries and in Forms
ACC97: Can't Update Recordset After Refreshing Linked Table
ACC: Creating Virtual Indexes with SQL Data-Definition Queries