Sequential Variables in a Loop

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I am trying to populate the fields in a form using a
For..Next loop to pull data from another table. The form
has six lines- for example, six lines with a date as the
first field, followed by other fields.
It seems like the easiest way (from my days of programming
in other languages) to fill in the form would be to use a
loop to select the records I need and then name each field
accordingly- such as Date(1), Date(2), Date(3).....
I tried doing it as (in simplified form here)
For I = 1 to 6
'code to select the appropriate record
Date(I)= selected record
Next I

Unfortunately, that does not produce the desired result
for naming the variable.

Any suggestions on how this can be done?

Thanks..>Dave
 
If you are multiple records, then would you not show these multiple records
in a listbox, or even some type of grid? (in ms-access, we can use a
listbox, or a continues form in place of a grid control).

hence, you can just stuff the sql right into the listbox, or the continues
form (likely a sub-form). No loop is needed, or even seems to make sense
here.

Do you need to display the data, or do you need to be able to edit that
data?

Can I correctly assume that each record is to be row of data displayed?

If yes, then take look at the following screen shot, on the left side of the
screen is a listbox (multi-column), on the right side, I used a sub-form. In
both cases, I did not "loop" to fill the data. (we don't do that anymore to
display data!).

http://www.attcanada.net/~kallal.msn/test/gs1.gif

Another example of using continues forms can be seen at:

Please feel free to clarify if the above is, or is not what you are looking
for.

If you are just displaying few records based on some condition, I would
start out building a listbox with the wizard..that is the least amount of
work. If you need to edit the data, then a continues form.
 
ALbert

Thanks for the response.

However, I don't think that will solve the problem. Let
me give you more detail about what we are trying to do and
see if that helps. It will either clarify things or make
it completely incomprehensible :)

I run a medical office and we are trying to set up a
custom database. Among other things, we need to fill out
insurance claim forms. The forms allow up to six
procedures per page. Each of these procedures needs to be
associated with up to 4 diagnosis codes. As long as the 6
procedures on the page have diagnosis codes that are all
within the same 4 codes (some procedures may have only 1,
others may have 2,3, or 4)they can print on one sheet. If
you need to add a 5th diagnosis, you have to start a new
form.

So- each record from the table of procedures has to be
evaluated to see how many diagnosis codes are associated
with that record AND whether they are unique codes or are
the same as codes used in the other records already on the
form. That is the main reason I am trying to do this with
a loop- When it goes to the next record it would loop
through the diagnoses (there may be 1 to 4 with each
record) and compare them to the diagnoses from the records
already on the form. The loop then decides two things:
1. Are there still 4 or less unique diagnosis codes on
the form
2. How to link each diagnosis for that record- For
example, the first record has Diagnosis1 and Diagnosis2.
It needs to have a field on the form that says "1,2" The
next record has the same first diagnosis as Diagnosis1 but
its Diagnosis2 is NOT the same as either one for the first
record. It needs to be set as Diagnosis3 on the form and
the field for that record is "1,3".

The 4 possible diagnosis codes are listed in separate
fields on the form and the procedure codes are linked to
those four by the field that reports "1,2,3,4".

(I have done this before in MUMPS and it was a lot
easier! If you have the diagnosis for each record
("Diagnoses") in a field where the diagnosis codes are
comma delimited,
For I = 1:4 Diag(I)=$P("Diagnoses",I,",") will give you
the separate codes and an equally simple statement will
check if any of them are the same as codes from other
records)


I suppose another option would be to create a temporary
table for the diagnosis codes with 2 columns and 4 rows.
Then populate the table with the codes as you go through
the records. It seems like it might work but I was hoping
there was a more elegant solution.

Thanks..Dave
 
Neil

Works beautifully!

THANKS

Dave
-----Original Message-----
Dave,

The answer to this question is to write the Date line as follows:

Me("Date" & I) = selected record

or

Forms!frmName("Date" & I)

HTH,

Neil.




.
Click to expand...
 
You are 100% correct if you can avoid a temp table, you should. however, I
think processing loop that builds up the values, and sets a grouping field
would enable the whole process to be sent to a report, and grouping field
would kick out a new page.

If you do need to parse out some values, then you can use the following
handy function:

Public Function mysplit(vData As Variant, intPos As Integer) As Variant

Dim vBuf As Variant
Dim intCount As Integer

If IsNull(vData) = True Then Exit Function
vBuf = Split(vData, ",")

intCount = UBound(vBuf, 1)

If intPos - 1 <= intCount Then
mysplit = vBuf(intPos - 1)
End If

End Function

Just paste the above in a standard module, and you have a nice parse
function like you had in mumps. I paste all of the above into all my
projects, and thus I always have a nice read to use parse routine.

So, for a given string, you can go:

strBuf = "a,b,c,d"
for i = 1 to 4
debug.print mysplit(strBuf,I)
next i

The results of the above of course would be output to the debug window, and
would be:
a
b
c
d

You can play, and test code in a module by using the debug window. In
fact,the debug window is really your command prompt.

Now, I do realize that the above may not be your problem, or question, but I
did want to show a nice handy function that you can paste into a module,a
and thus parse delimited data if you need to in the future. You can even
modify the above routine to accept a delimiter if you want.

The same goes for text boxes on a form. You can go:

for i = 1 to 4
debug.print me("diag" & i).value
next i

the above out output to the debug window the values in text boxes
diag1 diag2 diag3 diag4. Again, I wanted to show you can reference controls
on the screen via code.

If you need to build some "report" that kicks out a page when the max of 4
*different* diag is reached (or just the max of 6 procedures), then I would
write a code routine to send the data out to a temp table. The report writer
then can then work on this data. While you write out the data, you can use
hat group trick to tell the report writer when to move to the next page.
 
Back
Top