How to open an empty subform within a main form?

[surotkin]

Hello everyone,

I have a main unbound form to set search criteria. The main form has a bound subform. The subform is used to display a search results based on a query that uses search criteria specified in the main form.

The subform is bound to an empty recordset, so user gets an empty form when he/she opens it.
It works fine for the first run. However, after user closes the form and opens it again, the main form brings the whole table (approx.200,000 records) nevertheless its subform is based on an empty recordset

SELECT tblAgents.*
FROM tblAgents
WHERE (((tblAgents.AgentID)=0));

The subform is empty if I run it as stand alone form, but it gives me the whole table (approx.200,000 records) if I run it within the main form.

Does anybody have any ideas?

surotkin

 

[ChanceD2]

1. Open the main form in design view.
2. Click on the subForm and view the properties.
3. Look at the following fields in the properties window:
Link Child Fields
Link Master Fields
4. If there is anything in them, it would cause the behavior that you are talking about. (write down what is there) then you can try to remove the entered data.

see if that helps???

HTH

C-D2

 

[surotkin]

Hi ChanceD2,
sorry for late response.

No, there is nothing in [Link Child Fields] and [Link Master Fields] properties.

It looks that MS Access stores the last query used by property [Record Source] even though the property [Record Source] of the form is empty by the time when I open the form (I assign the property [Record Source] dynamically when user selects the search criteria).

This behavior of MS Access confuses me, since it gives me not empty recordset when I open the form which has not defined (it is empty) property [Record Source].

Any ideas how to force MS Access to open the form with empty recordset?

Thanks.
surotkin

 

[MajP]

one way to do that is to make a query with the correct fields but have a where statement that returns no records. Such as where 1 =2, true = false etc.

SELECT tblValue.autoID, tblValue.dblValue
FROM tblValue
WHERE (1=2)

However, what you are describing should not be happening unless you are saving the form somehow. If the recordsource is empty, it should stay empty even after dynamically building the recordsource.

 

[surotkin]

Hi MajP,
exactly,

If the recordsource is empty, it should stay empty even after dynamically building the recordsource.

I think it should stay, however not all the time.
I tried to rebuild the form.
It worked for the first 8-9 times, as it suppose to work.
Then it started to return not empty recordset, though the recordset property is empty, when I open the form.

I believe I found a solution.
I save the form with recordset property assigned to a table which is always empty (I never use this table). Then I set recordset property after user selects a search criteria.
It works for now.

Thanks.
surotkin

 

[TheAceMan1]

How are ya surotkin . . .

I'm sure you realize programming around this problem is not the answer (sounds like corruption to me), and I'm betting the problem will raise its head when you design other subforms.

[blue]Have you tried importing everything into a new DB?[/blue]

BTW: what access version/service pak?

See Ya! . . . . . .

 

[surotkin]

Hi TheAceMan1,
sorry for delay.

I am using MS ACCESS 2003 SP2.

I have not imported everything into a new DB (lots of work to do to set permissions for each object for each User Group) yet. However, if the problem raise again then I will try.

Thanks.
surotkin