Tab Forms and Subform questions

[harleyhead]

I know what I want to accomplish, but am uncertain that it can be done.

I want to use a tab control on a form. Each page of the tab control would contain the same subform. Since the subform is based on the same query, I want to be able to dynamically change the criteria as the user selects a given tab. Can this be done and how?

 

[thornmastr]

Of course you can do this. What you are going to do is replace the record source of the sub form on the fly.

The easiest way to accomplish this task is at the top of the main forms code module, ie the module which is global for the life of the form you are going to declare a string to hold the record source, ie,

Dim gstrSQL as string.

Now, let’s say you want to get all records from table x where the transaction date is equal to the transaction date on the main form, and the subform is sftab2. So in the event associated with the tab control you want you would put the following code.

gstrSQL = “SELECT * FROM TBLX WHERE TRANSACTIONDATE = #” & _
me.transactiondate.value & “#”
docmd.openform “sftab2”
forms!sftab2.recordsource = gstrSQL

Now, if by some chance you get a slight screen shimmering from the sub form, you can eliminate that by including the achidden constant in the docmd.openform

and then after you set the record source to the sub form, make the sub form visible.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[harleyhead]

Robert, Thank you.

I have everything working great except one thing. So, I have a tab control, with a subform on top of it. On the On Change event of the Tab Control, a text box on the main form display the tabctl + 1. The number displayed is correct. Following the setting of the text box, a macro is run that does a requery to change the contents of the subform. The query associated with the subform has it's criteria based on the value in the (to be hidden) text box. The data for the selected page is correct.

The subform is a simple data sheet, with one column as a combo box. The problem is the contents of the combo box changes only after the first time the On Change event occurs. So when you select another tab, the data represented in the datasheet is correct, but the combo box still has the contents of the first selection.

What do I have to do to get the combo box to change with each tab selection?

 

[thornmastr]

on the on click even of the tab control requery the combobox box.

I think that's what you are asking. If not, say it again but slower, or explain it again. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com