Want to show all entries when the value in combo is null

[bbannock]

hey everyone,

I've got a quick question...I'm sure this is not too hard to do, but i'm hitting a wall. I have a form/subform set up which looks up entries based on department ID and Account. Right now you need to enter an account number for any data to be shown in the subform, however I want the subform to show all of the data for the Department ID selected when there is no value for account. So basically I want the form to ignore the account box when no value is selected. Thanks alot in advance.

Brandon

 

[BSman]

Only link the subform by the department ID, but NOT the Account number. This way everything will be displayed for the selected department until a specific account is selected. There are improvements you can make to this mode of operation, but this should get you started.

 

[bbannock]

I only linked the form and subform by dept ID...now I get all of the data for each department, however nothing happens when I type an account number into the combo box. Anything else I could try.

Thanks,
Brandon

 

[BSman]

After the user selects the account number, you have to include a filter statement for the subform. It gets tricky to refer to the subform properly from the main form, but the process you want is something like this. I've just taken the simple example of writing it as if I were applying it to the main form:


me.FilterOn = True
me.Filter = "[AccountNbr] = '" & me.cboAccountNbr & "'"
me.Requery


I've written this assuming that Account number is a text value. If it's a number, drop the two single quotes ('), which must surround text value. And I've assumed that AccountNbr is the name of your column in the data source for the form (or subform in your case).

Your reference to the subform might be something like this:

me.SubForm1.Forms!MySubForm.FilterOn = True

That's probably the wrong syntax, but experiment.

 

[bbannock]

I'm sorry, I'm pretty new to using code in access...most of what I have came from using the wizards and some other simple methods. Do I put this code in the code in which my query is based on? I'm really not sure where to put either of these codes. I'm sorry to be a bother, I just don't understand.

thanks
Brandon

 

[BSman]

In the properties for the control where the user selects the account number, for the property AfterUpdate you create code (click on the ...).