Dynamically creating Subfrms based on number in recordset

  • Thread starter Thread starter Michael Banks
  • Start date Start date
M

Michael Banks

Hello,

I am having problems trying to create a subform for each record in a
given recordset.

I am able to count up the number in a given recordset but I can't seem
to be able to create a subfrm for each record.

My subfrm contains a picture and a check box. The code I have working
right now is as follows:

Private Sub cboGLN_AfterUpdate()
Dim i As Long
Dim k As String
Dim subfrmPhotoName As String
Dim subfrm As Form
Dim rsCount As Integer
Dim subfrmTop As Long
Dim subfrmLeft As String

Me!txtRecordSetCount = ""
Set rs = Me.subfrmPhoto.Form.RecordsetClone
Me![txtRecordSetCount] = rs.RecordCount
subfrmLeft = "0.2083"
Debug.Print "subfrmleft is " & subfrmLeft

While rsCount <= rs.RecordCount
Debug.Print "rsCount is " & rsCount
Debug.Print "subfrmTop is " & subfrmTop
Debug.Print "subfrmPhotoName is " & subfrmPhotoName

If rsCount = 0 Then
rsCount = 1
subfrmPhotoName = "subfrmPhoto1"
Else
rsCount = rsCount + 1
subfrmPhotoName = "subfrmPhoto" & rsCount
End If

If subfrmTop = 0 Then
subfrmTop = 1
Else
subfrmTop = subfrmTop + 3
End If

k = DLookup("Location", "dbo_tblFormData", "GLN = '" &
cboGLN & "'")

i = InStr(1, k, cboGLN)
i = i + 13

Dim j As String
j = left(k, i)

' subfrmPhotoName.RecordSource = "SELECT * from dbo_tblFormData
where [Location] like '" & j & "*'"
' CreateControl frmphoto, acImage, acDetail, cblGLN, txtLocation,
subfrmLeft, subfrmTop, 3, 2.25

Wend
End Sub

In the table "dbo_tblFormData" I have fields "GLN" and "Location". The
location is the specific network location of the picture file i.e.

"S:\GLN_Images\AmeritechAudit\068168_421874_2001_12_19_11_35_14.jpg"
and contained within that Location is the GLN. I use the DLookup to
find the GLN within the lovation. What I would like to do is to create
an image on each subfrm. Currently, without using any of this code I
can set the subfrm as a single form and can use the navigation buttons
to cycle through the pictures. So somewhere Access is able to determine
that I have many pictures for each GLN, however if I set the subfrom to
a continuous form I will just get the first picture repeated over and
over. There must be a way to change that property. Can anyone offer up
any thoughts?

Thanks for your help, sorry for the lenghly confusing explanation.

I can clarify if need be
Mike
 
Hello,

I am having problems trying to create a subform for each record in a
given recordset.
Click to expand...

YOW!

Suppose there were 4226 records in the recordset. You'ld crash!

And even without that problem, your database will bloat very rapidly
as you keep changing the form's design.

I can't imagine why you would want to create *MULTIPLE SUBFORMS* for
this purpose; wouldn't it be better to have a single Continuous
subform so that you could see the desired records all together?
 
That would be just fine, but I can't get the continuous subform to
display more then the first record over and over again. I have to use
the Navigator Buttons to go to the second record. You are very correct
in how big the db would get. Fortunately, at most I only have maximum of
25 records for a recordset, most are 2-5.

Any idea on how to get the second, third, etc. record to show up on a
continous form without using the navigator buttons?

Thanks for your response.
Mike
 
Any idea on how to get the second, third, etc. record to show up on a
continous form without using the navigator buttons?
Click to expand...

With the scrollbar; with the Tab key; with the Enter key; with the
PageDown key; with a command button using DoCmd.GoToRecord acNext;
probably a dozen otehr ways.
 
Each of the file names includes the GLN (location of each pole), then the
year, month, day, hr, min, sec the picture was taken. This picture I
included is the continous form with three image frames in it. Therefor
the two pictures that actually show up are the repeating pictures. I have
to goto the 2nd record by navigating to the second record, I want it so
that the 2nd picture shows up in the 2nd image frame and I can't seem to
accomplish this.
Click to expand...

If the image controls are unbound, there's really only one of them:
the Continuous Form has only one instance of each control, displayed
repeatedly. I can't think of any good way to do this in a continous
Form - you may indeed need to do as you originally suggested, or else
use the CreateControl method to create as many image controls as
needed on a single Form.
 
Back
Top