recordset with DO LOOP

[Guest]

I guess I just don’t understand recordsets yet.
I’m working on a form with a combo box that looks up customer names from a
query that I use to identify the equipment that the customer has at his
location.
I would like to set up X amount of textboxes on this form and have then
become visible to display the equipment description.
The code would look a little like this, I think

Private Sub Combo0_AfterUpdate()

Dim rst As Recordset
Dim I As Integer
rst.MoveFirst
' use I to number text boxes txtName1, txtName2....
I = 1
Do
Me.txtName(I).Visible
Me.txtName(I).Value = rst
rst.MoveNext
I = I + 1
Loop Until rst.EOF

End Sub

Any help would be greatly appreciated

 

[stebain]

I guess I just don't understand recordsets yet.
I'm working on a form with a combo box that looks up customer names from a
query that I use to identify the equipment that the customer has at his
location.
I would like to set up X amount of textboxes on this form and have then
become visible to display the equipment description.
The code would look a little like this, I think

Private Sub Combo0_AfterUpdate()

Dim rst As Recordset
Dim I As Integer
rst.MoveFirst
' use I to number text boxes txtName1, txtName2....
I = 1
Do
Me.txtName(I).Visible
Me.txtName(I).Value = rst
rst.MoveNext
I = I + 1
Loop Until rst.EOF

End Sub

Any help would be greatly appreciated

It would look a bit like that.
(I'm assuming X is the recordcount)

Are you wanting to create these txtName forms on the fly?
txtName1 is different than txtName(I), by the way.

Anyway...

Private Sub Combo0_AfterUpdate() 'Any reason you use AfterUpdate instead of
some other option?
Dim rst As Recordset
Dim I As Integer
Dim customerNameValue as string

customerNameValue = Combo0.Text

'Somewhere in here, I am assuming that you have opened a recordset into
rst... unless, that is what you are asking above.
'Something like Set rst = CurrentDb.OpenRecordset("SELECT EquipmentName
FROM Equipment WHERE CustomerName=""" & customerNameValue & """"

rst.MoveFirst

'Is there a reason you are adding these on the fly, rather than using a
listbox or a subform?
While not rst.eof do
'make the text field visible
'populate the textfield
txtName(I).SetFocus
txtName(I).Text = rst.Fields("EquipmentName").Value & vbNullString 'just
in case it is empty, forces a handled case
rst.movenext
Wend
end sub


Something tells me I am not answering the right question.

 

[Marshall Barton]

I guess I just don’t understand recordsets yet.
I’m working on a form with a combo box that looks up customer names from a
query that I use to identify the equipment that the customer has at his
location.
I would like to set up X amount of textboxes on this form and have then
become visible to display the equipment description.
The code would look a little like this, I think

Private Sub Combo0_AfterUpdate()

Dim rst As Recordset
Dim I As Integer
rst.MoveFirst
' use I to number text boxes txtName1, txtName2....
I = 1
Do
Me.txtName(I).Visible
Me.txtName(I).Value = rst
rst.MoveNext
I = I + 1
Loop Until rst.EOF

End Sub

There's no help for that code ;-)

You should create a continuous (or datasheet) form to
display the data. No funky text box manipulation, no
recordset, just a simple subform.

 

[Guest]

--
thank You

It would look a bit like that.
(I'm assuming X is the recordcount)

Are you wanting to create these txtName forms on the fly?
txtName1 is different than txtName(I), by the way.

Anyway...

Private Sub Combo0_AfterUpdate() 'Any reason you use AfterUpdate instead of
some other option?
Dim rst As Recordset
Dim I As Integer
Dim customerNameValue as string

customerNameValue = Combo0.Text

'Somewhere in here, I am assuming that you have opened a recordset into
rst... unless, that is what you are asking above.
'Something like Set rst = CurrentDb.OpenRecordset("SELECT EquipmentName
FROM Equipment WHERE CustomerName=""" & customerNameValue & """"

rst.MoveFirst

'Is there a reason you are adding these on the fly, rather than using a
listbox or a subform?
While not rst.eof do
'make the text field visible
'populate the textfield
txtName(I).SetFocus
txtName(I).Text = rst.Fields("EquipmentName").Value & vbNullString 'just
in case it is empty, forces a handled case
rst.movenext
Wend
end sub


Something tells me I am not answering the right question.

1. txtname on the fly? Not really the form will load with the txtname just
not visible, I think you can have about 700 text boxes on a form, right?? I’m
only going to use about 150 but I would like to make them visiable as needed
so I used the DO LOOP
Do

Me.txtName(I).Visible
Me.txtName(I).Value = rst
rst.MoveNext
I = I + 1
Loop Until rst.EOF

or do I need to make txtName a string and add (I) to it ??

2. AfterUpdate? I guess after I choose a other name in the combo box I need
to clear all txtName and set visible=false and start all over again

Perhaps a subform is the better way, but I would like to get it to work this
way. Each week I print off reports for all my customers (collection tickets)
this report has the customer name and current list of equipment I fill in the
gross money and the percentage for each piece of equipment and on the bottom
I add up the totals the customer gets . I then got a form I use to input the
income from each customer all I do is just input the totals that I added up
on the report ticket. I’m would like to make text boxes on the form to become
visible and display the description of the equipment. In other words I would
like my income form to look like my report I use so I can keep a running
record of the income each piece of equipment makes.

I just don’t understand the rules for recordset yet. If I could find some
good examples of code I could figure this out.

 

[Guest]

Hi Gus,

Marshall is absolutely right; forget messing around with individual discrete
text boxes and opt for a continuous form. Anyway it sounds as though your
requirement is for more than a single text box: from your description you
need to enter amounts against each and every equipment line so there will be
at a minimum the equipment description and an amount field. Moreover a
continous form will automatically have all the scrolling mechanisms for long
lists - you indicate a maximum of 150 lines. I'm sure you can get the
continuous form to look approximately like your report. I suggest you
inhibit the addition of new records in the form design to prevent that empty
line at the end of the list, etc. You can put a running total in the form
footer, etc., etc.

It's your decision whether to implement the form as a sub form or a
stand-alone linked form.

Now we come to the slightly more tricky part: how to create the recordset
for the continous form. Most (all?) data forms are bound to recordsets that
in turn sooner or later are based on data in tables. ( I have often longed
for a way of building a recordset directly from a collection of objects and
then have a form display the results. The only way I know is to create a
table from my collection and then bind the form to that table. If anyone
reading this post knows of another way please let me know.) So I think you
are in the business of creating a table for the continous form. I suggest
that if the design is static you do not delete/recreate the table each time
but simply empty it and then refill it, that way the design persists from use
to use.

Ok so what's the rough sequence of events? I see nothing wrong with using
the After Update event of your Combo Box. When this occurs: empty your
table; refill the table with the equipment records for this customer; requery
the continous form/sub form. You can then enter your billing period data,
etc. At the end you will have captured all this on your table and you can
accumulate, post to your accounts and whatever else takes your fancy before
moving onto the next customer.

I hope this gives you a flavour. If you're not too proficient with Access
and VBA it can be a little daunting at first. So shout if you want more
details or help.

Regards,

Rod