Need Auto query on form Open to stop!

[Caitlin11]

Hello everyone,

I have a question that seems like it should be fairly easy. First some background info:

1. I'm modifying an mdb that was written by a different programmer.
2. I"m using Access '97

I have a form that has 3 list boxes - policy #, Acct # and name. It has a subform under it that shows the corresponding data when a query is run. The idea is for the user to open the form - type in a name, policy# or acct. #, press <enter> and the corresponding records should show up in the subform below. This part DOES work. The problem is that when the user initially opens the form - it automatically queries all recrods and shows them in the subform below. Does anyone know possible reasons this would happen? I need to stop this from happening!

Thank you in advance for any help that you can give me!

Caitlin

 

[jdjewell]

Most likely...

...this is happening, because the subform has a query or table specified as the control source. I know of one way to get around this off the top of my head:

Don't specify a control source in the form properties, thereby unbinding all of the controls in the subform. Then, in the button_click event, you would do something like:

dim db as database
dim rs as recordset

set db = CurrentDB
set rs = CurrentDB.OpenRecordset(YourSelectStatement)

text1 = rs.fields("text1")
text2 = rs.fields("text2")
...so on and so forth.

Other than that, you would need to alter something in the form_load event of the subform, and I don't know what the best way would be off the top of my head. You could set all of the values for all of the fields in your subForm equal to "" in the onLoad, and you wouldn't have to change anything else.

Hope this helps some.

 

[Mile-O]

You could also just remove the subform's SourceObject and add it via code when you do the query.

 

[Caitlin11]

Thank you. I'll try both of your suggestions and see which one I can get to work.

Caitlin