Dynamic listing in a form

  • Thread starter Thread starter Alp Bekisoglu
  • Start date Start date
A

Alp Bekisoglu

Hi Experts,

Got stuc again! I'm trying to display the results of a VBA for/next loop in
a form as parsed into fields (textboxes).

I do get everything I want and display it in a MsgBox. Also the same with
Debug.Print. I couldn't figure out/remember how to parse this into a form
designed as a continuous form.

All I get for the time is the very last output of the loop. Just need to be
able to get the previous ones displayed as individual lines on the form.

The form has 5 textboxex in which I would like to display the values for
x | Date | Description | Amount | Balance

Code is:
Dim aylik As Single, Toplam As Single, bakiye As Single
Dim adet As Long, gun As Date
Dim izah As String
bakiye = Me.tpybl
adet = Me.inst_no
aylik = Me.inst_amount
gun = date
For x = 1 To adet
If Len(x) = 1 Then
A = " " & x
Else
A = x
End If
izah = A & ". installment"
bakiye = bakiye - aylik
ara = Day(gun)
ara1 = Month(gun) + x
ara3 = year(gun)
Me.tarih = DateSerial(ara3, ara1, ara)
Me.aciklama = izah
Me.odeme = aylik
Me.Kalan = bakiye
Debug.Print izah & " "; aylik & " "; bakiye
g = "|" & Me.tarih & "|" & izah & "|" & aylik & "|" & bakiye &
vbCrLf
ResTxt = ResTxt & g
Next x
head = "|" & "__Date__" & "|" & "Description " & "|" & "Amount" & "|" &
"Balance" & vbCrLf
MsgBox head & ResTxt, vbOKOnly, "Calculation Results"

Thanks in advance,

Alp
 
Alp,

To display these values in a recordset, you must write them to a recordset.
If you know what the format is going to be every time you do this, then just
create the form and leave it unpopulated. Then, when you enter this set of
code, open that recordset and write the values you have calculated in your
code below to the recordset. Then open the form that uses this as a
recordset. Depending on how frequently you use this information, and how
quickly it changes, you might want to consider deleting the records from
your table every time the form closes, or at least every time the
application closes.

HTH,
Dale
 
Hi Alp

This is the way unbound controls behave in a continuous form. Any property
you set, including the Value, applies to every instance of the control, so
they will all show the last value you set.

To make different values display in your continuous textboxes, you must
either bind the textbox to a field in your recordsource, or you must make
the controlsource of the textbox a calculated expression based on one of the
other fields in your record.
 
Hi Dale and Graham,

Thanks for both your advices. Actually this was the initial stage of my next
question the "date change on SQL" on which we did correspond with Graham.

Well, while waiting for an answer, I decided to create a table to use as a
dumpster each time this form is used and then it (logically) started to
work. I somehow thought I could achieve the same without actually creating a
table. But anyway, on each use the table is populated and if the user
decides to keep it, all lines are copied into another table for keeping.
Othewise code uses the RunSQL DELETE * on close.

This actually started off with a need for a payment distribution/allocation
of a basic sum over installments and I wanted to give the user an option to
choose (and the see the resulting schedule) on different number of
installments. In the end if one is liked, there is the option to keep it.

But thanks to both of you for taking the time to share your knowledge with
me.

Sincerely,

Alp
 
Alp,

You may not be oput of the proverbial woods yet! In effect you are using a temp
table which you fill and later delete. This operation quickly bloats a database.
Keep an eye on your file size! You may need to compact frequently.
 
Hi Alp

Further to what PC Datasheet said, I would recommend you put temporary
tables in a separate database and include them in your main front-end as
linked tables. This is for two reasons:

1. The database bloat that PCD mentioned. Not only does database bloat lead
to huge mdb files, but it significantly increases the chance of corruption.

2. If you have a multi-user environment, your users will want to have their
own copies of the temporary tables. In this case, they will (presumably)
have their own copies of the front-end mdb and will share the back-end
containing the data. Your temporary table(s) can be created in a separate
mdb on the local drive and linked to the local copy of the front-end.
 
Ok, now the second set of thanks.
PC D, that was why I did not want to use a temp table which gets written,
erased, written, erased,.... I do know about the bloating and would really
rather use a dataset that does not get actually written unless accepted by
the user. Any suggestions? I did try (as indicated in my initial post, I can
get all I want and display it as a message) but just couldn't manage to
display it on a form.
Graham, yes, I was just thinking of that (kicking them off to an external
mdb) which I will definitely do. Unless PC D comes up with a life saver,
that is.

Thanks again.

Alp
 
Back
Top