Individual Text boxes having own "rowsource"

[corner40]

hi everyone
I have a form with approximately 60 text boxes on it. 30 of the textboxes I'd like to display a number from a table based on the date selected from a combo box at the top of the form and also a productid...the other 30 are going to work in a similar fashion but display differeny information from a different source.
Initially I was thinking I'd just set the rowsource of each textbox and have a refresh/requery command button to display the info as needed. Silly me...there is no rowsource property for text boxes. So I tried a dlookup in an expression builder...never been a fan of expression builder...didn't work anyhow. I would really like to use rowsource somehow I think...I would be easiest...I don't really want to hard code all of the textboxes rowsources in vb as that would seem to be a hassle and a bit of a nightmare for upkeep.
Any other suggestions would be most appreciated. If this doesn't make sense, please ask questions as I really need an answer sooner then later.
thanks everyone
jeremy

 

[FancyPrairie]

If the values for each of the text boxes is coming from a table, then set the RecordSource of your form to a query (i.e. Select * From ... )

And then set the control source of each text box to the appropriate field in the table.

In the AfterUpdate event of the combo box, include something like this:

Me.Filter = "DateFieldInTable=#" & [YourComboBox].Value & "#"
Me.FilterOn = True

 

[corner40]

Hi fancy
thanks for the suggestion. The only problem is that each text box is also going to have a productid as criteria. The productid isn't going to be entered from anywhere or by anyone. It needs to kind of be statically assigned to each textbox.
thanks though...I'm terrible for remembering the filteron property so you may have helped me in another instance.
thanks
Jeremy

 

[FancyPrairie]

If I understand the first part correctly, you have 30 text boxes on your form that need to be updated with values coming from a table (from 30 fields within the table????). And each text box has a product ID assigned to it (where is this product id coming from??? can you store the product ID in the tag property or each of the text boxes???)

If the 30 text boxes updated by 30 fields in table, then I would suggest a naming convention to make it easier. For example, the first text box might be named "Text1" and the field in the table that updates Text1 would be named "Field1". Now when you create a loop to update the values, you loop would look something like this;

For i = 1 to 30
me("Text" & i).value = rst("Field" & i).value
next i

 

[corner40]

the part I can't get past is how to make each text box have a different productid assigned to it. They aren't in any consecutive order.

here's a little more into it to give you an idea of what I'm doing:
I have a table classmax(maxid, maxdate, productid, classmaxnum)

I have a form with a combo box with all available dates(cboDate)
the form also has 60 other unbound text boxes.

What I am trying to show is what the classmaxnum is based on the date. Each textbox is representing a different productid. Classmaxnum is the maximum number of kids that can be booked in a lesson for that particular product on that particular date. The other text boxes I haven't mentioned much yet will be displaying the number of kids already booked for that product on that particular date. The calculated number is simply a count of the number of productid's on the given date in the "activitiesbooked" table.

for example:

cbodate = dec 1 04

class name(productid) classmaxnum booked
9:00am level 1 (70) 10 6
11:00am level 1 (723) 8 5
etc

and then else where on the form level 2 and so on

do you still suggest dlookup. The other route to take I guess, although very ugly, is the create a query for each text box and just set the control source. But that doesn't excite me in the least.

any help would be great. The reason i'm only asking for help with half of the text boxes is that if I get those to work I'm sure I can get the actuals to work no problem.
thanks again for the help
Jeremy

 

[corner40]

oops...sorry ignore the dlookup part...

 

[PHV]

[tt]class name(productid) classmaxnum booked
9:00am level 1 (70) 10 6
11:00am level 1 (723) 8 5[/tt]
I you know how to get this result with a query you may consider a listbox with this query as rowsource.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[corner40]

Hi PHV
That is actually a really great idea that i hadn't thought of. I kinda said to myself "ohhh...obviously" when I first looked at it, but then I remembered that they are going to need to be able to adjust the classmaxnum. For example, if a class is getting close to full, the will bring in another instructor and need to adjust the classmaxnum to a larger size to accomidate more kids.
but thanks, that was a great suggestion. I may still use it and then have to have like a contiuous form with all the info to be adjusted there. I'd just rather find a way to help them do it all in one place instead of having to jump between forms.
thanks again
Jeremy