multiple subform images per main search form record

[carcher]

Hello. I am searching for a solution to a search form problem. I am not very
familiar with VBA coding, so please bear with me. I have an Access db with 2
tables...structures and images. They are joined (one to many) by StationID
with each StationID having multiple images associated with it.

I have a form that allows searching the structures table via a WHERE clause
and a command button to filter data entered in fields on the HEADER section
of the form (borrowed code from Allen Browne's help site). The filtered data
is displayed in the DETAIL section of the form.

My goal is to have the images for each StationID displayed with the filtered
detail results. I don't know how to get the image control to point to each
record displayed, and would really appreciate some detailed guidance.
tblImage (all text fields):
ImageID PK
ImageName
StationNo

structures (all text fields except Comments...Memo):
StationNo PK
StructureID
RiverStream
ItemNumber
SizeofPipeConduit
DateConstructed
Walkway
Account
OperatorType
Comments

Tables joined on StationNo with Structures.StationNo being the one side of
the relationship and tblImage.StationNo being the many.

Search form based on Structures table uses unbound text boxes and the
following where clause to query structures info:

Private Sub cmdFilter_Click()

Dim Whereclause As String

If Not IsNull(txtStationNo) Then
Whereclause = "[StationNo] Like '*" & txtStationNo & "*'"
End If

If Not IsNull(txtRiverStream) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[RiverStream] Like '*" & txtRiverStream & "*'"

End If

If Not IsNull(txtItemNumber) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[ItemNumber] Like '*" & txtItemNumber & "*'"

End If

If Not IsNull(txtStructureID) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[StructureID] Like '*" & txtStructureID & "*'"

End If


If Not IsNull(txtPipeSize) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[SizeofPipeConduit] Like '*" & txtPipeSize & "*'"

End If

If Not IsNull(txtDateConstructed1) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[DateConstructed] >= '" & txtDateConstructed1 &
"'"

End If

If Not IsNull(txtDateConstructed2) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[DateConstructed] <= '" & txtDateConstructed2 &
"'"

End If

If Not IsNull(txtWalkway) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Walkway] Like '*" & txtWalkway & "*'"

End If

If Not IsNull(txtAccount) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Account] Like '*" & txtAccount & "*'"

End If

If Not IsNull(txtTypeofOperator) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[OperatorType] Like '*" & txtTypeofOperator &
"*'"

End If

If Not IsNull(txtComments) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Comments] Like '*" & txtComments & "*'"

End If
Me.Filter = Whereclause
Me.FilterOn = True

End Sub


Private Sub cmdReset_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next
Me.FilterOn = False
End Sub



Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "You cannot add new structures to the search form.", vbInformation,
"Permission denied."

End Sub

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

I've added a subform to the search form. The subform is based on the
tblimage table and contains all field from the table. The only visible field
is the ImageName field...I've added an image control called ImageFrame to the
subform, and set the subform's property to continuous then dragged the
subform into my search form. When opening the search form, errors off on the
form_Current event saying it can't find the file. If I leave off the
subform's Form_Current event he subform correctly displays the imagename for
each image(s) for each filtered detail form record, yet the ImageFrame
displays the same image for each ImageName.

Code for subform tblImageTest:

Option Compare Database
Option Explicit

Public Function GetImagePath() As String
GetImagePath = GetDBPath & "images\"
End Function
Public Function GetDBPath() As String
GetDBPath = CurrentProject.Path & "\"
End Function

Private Sub Form_AfterUpdate()
Dim strNoImage As String

If Dir(GetImagePath & Me![ImageName]) <> "" Then
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
Else
strNoImage = "c:\windows\no image available.jpg"
Me.ImageFrame.Picture = strNoImage
End If

On Error Resume Next
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
End Sub

Private Sub Form_Current()
Dim strNoImage As String

If Dir(GetImagePath & Me![ImageName]) <> "" Then
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
Else
strNoImage = "c:\windows\no image available.jpg"
Me.ImageFrame.Picture = strNoImage
End If

On Error Resume Next
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
End Sub

I apologize if this is too much information, but I am in over my head.

Thank you.

 

[strive4peace]

Hi Carcher,

if the subform for the images is a continuous form and you are using
Access 2003 or lower, you cannot load DIFFERENT images in the same
unbound control for different records. Unless you are working with
Access 2007, the Image control is unbound ... only ONE image can be
displayed in that control, regardless if you have multiple records
showing. Access 2007 allows you to bind the image control to a
fieldname containing the path and filename.

sorry to be the bearer of bad news...

Warm Regards,
Crystal

*
have an awesome day
*

Hello. I am searching for a solution to a search form problem. I am not very
familiar with VBA coding, so please bear with me. I have an Access db with 2
tables...structures and images. They are joined (one to many) by StationID
with each StationID having multiple images associated with it.

I have a form that allows searching the structures table via a WHERE clause
and a command button to filter data entered in fields on the HEADER section
of the form (borrowed code from Allen Browne's help site). The filtered data
is displayed in the DETAIL section of the form.

My goal is to have the images for each StationID displayed with the filtered
detail results. I don't know how to get the image control to point to each
record displayed, and would really appreciate some detailed guidance.
tblImage (all text fields):
ImageID PK
ImageName
StationNo

structures (all text fields except Comments...Memo):
StationNo PK
StructureID
RiverStream
ItemNumber
SizeofPipeConduit
DateConstructed
Walkway
Account
OperatorType
Comments

Tables joined on StationNo with Structures.StationNo being the one side of
the relationship and tblImage.StationNo being the many.

Search form based on Structures table uses unbound text boxes and the
following where clause to query structures info:

Private Sub cmdFilter_Click()

Dim Whereclause As String

If Not IsNull(txtStationNo) Then
Whereclause = "[StationNo] Like '*" & txtStationNo & "*'"
End If

If Not IsNull(txtRiverStream) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[RiverStream] Like '*" & txtRiverStream & "*'"

End If

If Not IsNull(txtItemNumber) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[ItemNumber] Like '*" & txtItemNumber & "*'"

End If

If Not IsNull(txtStructureID) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[StructureID] Like '*" & txtStructureID & "*'"

End If


If Not IsNull(txtPipeSize) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[SizeofPipeConduit] Like '*" & txtPipeSize & "*'"

End If

If Not IsNull(txtDateConstructed1) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[DateConstructed] >= '" & txtDateConstructed1 &
"'"

End If

If Not IsNull(txtDateConstructed2) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[DateConstructed] <= '" & txtDateConstructed2 &
"'"

End If

If Not IsNull(txtWalkway) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Walkway] Like '*" & txtWalkway & "*'"

End If

If Not IsNull(txtAccount) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Account] Like '*" & txtAccount & "*'"

End If

If Not IsNull(txtTypeofOperator) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[OperatorType] Like '*" & txtTypeofOperator &
"*'"

End If

If Not IsNull(txtComments) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Comments] Like '*" & txtComments & "*'"

End If
Me.Filter = Whereclause
Me.FilterOn = True

End Sub


Private Sub cmdReset_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next
Me.FilterOn = False
End Sub



Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "You cannot add new structures to the search form.", vbInformation,
"Permission denied."

End Sub

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

I've added a subform to the search form. The subform is based on the
tblimage table and contains all field from the table. The only visible field
is the ImageName field...I've added an image control called ImageFrame to the
subform, and set the subform's property to continuous then dragged the
subform into my search form. When opening the search form, errors off on the
form_Current event saying it can't find the file. If I leave off the
subform's Form_Current event he subform correctly displays the imagename for
each image(s) for each filtered detail form record, yet the ImageFrame
displays the same image for each ImageName.

Code for subform tblImageTest:

Option Compare Database
Option Explicit

Public Function GetImagePath() As String
GetImagePath = GetDBPath & "images\"
End Function
Public Function GetDBPath() As String
GetDBPath = CurrentProject.Path & "\"
End Function

Private Sub Form_AfterUpdate()
Dim strNoImage As String

If Dir(GetImagePath & Me![ImageName]) <> "" Then
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
Else
strNoImage = "c:\windows\no image available.jpg"
Me.ImageFrame.Picture = strNoImage
End If

On Error Resume Next
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
End Sub

Private Sub Form_Current()
Dim strNoImage As String

If Dir(GetImagePath & Me![ImageName]) <> "" Then
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
Else
strNoImage = "c:\windows\no image available.jpg"
Me.ImageFrame.Picture = strNoImage
End If

On Error Resume Next
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
End Sub

I apologize if this is too much information, but I am in over my head.

Thank you.

 

[carcher]

Thank you so much Crystal. I'll quit beating my head against the wall now.
If no continuous form, then what are my options to display these images? I'm
lost.

Thank you for your prompt response,

Carol

Hi Carcher,

if the subform for the images is a continuous form and you are using
Access 2003 or lower, you cannot load DIFFERENT images in the same
unbound control for different records. Unless you are working with
Access 2007, the Image control is unbound ... only ONE image can be
displayed in that control, regardless if you have multiple records
showing. Access 2007 allows you to bind the image control to a
fieldname containing the path and filename.

sorry to be the bearer of bad news...

Warm Regards,
Crystal

*
have an awesome day
*

Hello. I am searching for a solution to a search form problem. I am not very
familiar with VBA coding, so please bear with me. I have an Access db with 2
tables...structures and images. They are joined (one to many) by StationID
with each StationID having multiple images associated with it.

I have a form that allows searching the structures table via a WHERE clause
and a command button to filter data entered in fields on the HEADER section
of the form (borrowed code from Allen Browne's help site). The filtered data
is displayed in the DETAIL section of the form.

My goal is to have the images for each StationID displayed with the filtered
detail results. I don't know how to get the image control to point to each
record displayed, and would really appreciate some detailed guidance.
tblImage (all text fields):
ImageID PK
ImageName
StationNo

structures (all text fields except Comments...Memo):
StationNo PK
StructureID
RiverStream
ItemNumber
SizeofPipeConduit
DateConstructed
Walkway
Account
OperatorType
Comments

Tables joined on StationNo with Structures.StationNo being the one side of
the relationship and tblImage.StationNo being the many.

Search form based on Structures table uses unbound text boxes and the
following where clause to query structures info:

Private Sub cmdFilter_Click()

Dim Whereclause As String

If Not IsNull(txtStationNo) Then
Whereclause = "[StationNo] Like '*" & txtStationNo & "*'"
End If

If Not IsNull(txtRiverStream) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[RiverStream] Like '*" & txtRiverStream & "*'"

End If

If Not IsNull(txtItemNumber) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[ItemNumber] Like '*" & txtItemNumber & "*'"

End If

If Not IsNull(txtStructureID) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[StructureID] Like '*" & txtStructureID & "*'"

End If


If Not IsNull(txtPipeSize) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[SizeofPipeConduit] Like '*" & txtPipeSize & "*'"

End If

If Not IsNull(txtDateConstructed1) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[DateConstructed] >= '" & txtDateConstructed1 &
"'"

End If

If Not IsNull(txtDateConstructed2) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[DateConstructed] <= '" & txtDateConstructed2 &
"'"

End If

If Not IsNull(txtWalkway) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Walkway] Like '*" & txtWalkway & "*'"

End If

If Not IsNull(txtAccount) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Account] Like '*" & txtAccount & "*'"

End If

If Not IsNull(txtTypeofOperator) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[OperatorType] Like '*" & txtTypeofOperator &
"*'"

End If

If Not IsNull(txtComments) Then
If Whereclause <> "" Then Whereclause = Whereclause & " AND "
Whereclause = Whereclause & "[Comments] Like '*" & txtComments & "*'"

End If
Me.Filter = Whereclause
Me.FilterOn = True

End Sub


Private Sub cmdReset_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next
Me.FilterOn = False
End Sub



Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "You cannot add new structures to the search form.", vbInformation,
"Permission denied."

End Sub

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

I've added a subform to the search form. The subform is based on the
tblimage table and contains all field from the table. The only visible field
is the ImageName field...I've added an image control called ImageFrame to the
subform, and set the subform's property to continuous then dragged the
subform into my search form. When opening the search form, errors off on the
form_Current event saying it can't find the file. If I leave off the
subform's Form_Current event he subform correctly displays the imagename for
each image(s) for each filtered detail form record, yet the ImageFrame
displays the same image for each ImageName.

Code for subform tblImageTest:

Option Compare Database
Option Explicit

Public Function GetImagePath() As String
GetImagePath = GetDBPath & "images\"
End Function
Public Function GetDBPath() As String
GetDBPath = CurrentProject.Path & "\"
End Function

Private Sub Form_AfterUpdate()
Dim strNoImage As String

If Dir(GetImagePath & Me![ImageName]) <> "" Then
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
Else
strNoImage = "c:\windows\no image available.jpg"
Me.ImageFrame.Picture = strNoImage
End If

On Error Resume Next
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
End Sub

Private Sub Form_Current()
Dim strNoImage As String

If Dir(GetImagePath & Me![ImageName]) <> "" Then
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
Else
strNoImage = "c:\windows\no image available.jpg"
Me.ImageFrame.Picture = strNoImage
End If

On Error Resume Next
Me![ImageFrame].Picture = GetImagePath & Me![ImageName]
End Sub

I apologize if this is too much information, but I am in over my head.

Thank you.

 

[strive4peace]

Highlight Record in Continuous Form
Customize Form/Report Design Toolbar
~~~

Hi Carol,

how about putting an Image control in the form footer (or header) and
show the image for the current record that the user is on?

Or you could put the image control on the main form -- still showing
just the image for the current record in the subform...

to make it more obvious which image is displayed, you can highlight the
current record in the continuous subform:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TO HIGHLIGHT THE CURRENT RECORD IN A CONTINUOUS FORM
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

put the following textbox control on your form (I usually put it in the
footer since there is often more unused space)

name --> RecordID_current
visible --> no

make the following textbox control in the detail section of your form:

name --> HighlightBox
left --> 0
top --> 0
width --> width of detail section
height --> height of detail section, like 0.2
enabled --> false
locked --> true
tabstop --> false

send this control to the back*
(instructions on customizing a toolbar follow)

in the design view of the form, select HighlightBox
since HighlightBox is behind everything, you may need to select it using
the object drop-down (1st icon on the formatting toolbar or combo in the
Properties window at the top)

conditional formatting
condition 1 --> Expression Is
--> [RecordID] = [RecordID_current]
change fill/back color to LIGHT YELLOW or light gray
or whatever color you want for your highlight

If my detail section background is White, I like to use light yellow for
a highlight

WHERE
RecordID is the controlname of your ID field control

then, in the form OnCurrent event, assign a value to the unbound
RecordID_current
being unbound, it will have the same value on every record

'~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Current()
Me.RecordID_current = IIf([NewRecord], 0, [RecordID])
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~

where [RecordID] is the controlname corresponding to your long integer
unique ID.

If you are using a datasheet, I believe you will need to set up
conditional formatting for each control as you can't use a control that
spans the whole line as you can in a continuous form

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CUSTOMIZING YOUR FORM/REPORT DESIGN TOOLBAR
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

when you are in the design view of a form or report, RIGHT-CLICK in a
gray area to the right of your menu bar or an icon bar

choose "Customize" from the shortcut menu

in the dialog box...

choose the *Commands* tab

Category --> *Form/Report Design*

slide these icons to the Form Design toolbar:
(they are located about 3 "screens" down in the Commands list)

Align Right -- must have >1 control selected to be enabled
Align Left -- "
Align Top -- "
Align Bottom -- "
Bring To Front
Send To Back

If you don't know what these icons do, while the customize window is
open, click on the DESCRIPTION command button for each one to see what
is does.

to be efficient, the Align button are a MUST for form and report design

the Bring To Front and Send To Back are nice to have too -- since
everything is "layered" and this gives you control on the order

~~~~~~~

It is also a good idea to open the property sheet
(from the menu --> View, Properties)

on the Format tab, you can set values such as width, height, left, top, ...

~~~~~~~

in order to align, you have to have more than one control selected

to select multiple controls:

1. click and drag an imaginary line (or rectangle) touching every
control you want to select and then let go of the mouse

OR

2. click (and drag if desired) in the vertical ruler to select
everything on that extended horizontal line (or rectangle)

OR

3. click (and drag if desired) in the horizontal ruler to select
everything on that extended vertical line (or rectangle)

OR

4. click the first object and then SHIFT-CLICK objects to toggle their
selection status

*****************
there are a few tricks I use, though, to make formatting faster...

1. double-click a control handle to do a best-fit

2. adjust width and height using property sheet. sometimes I drag to
get a relative size, then adjust because I like things to be exact

3. line top and left using buttons you can add to toolbars (I sometimes
use property sheet to adjust these as well)

4. select several controls at once by clicking and dragging either in
vertical ruler or horizontal ruler -- anything that touches the extended
line/rectanglar region will be selected

5. click in a ruler to get all controls touched by the extended line --
also great for reducing margins

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Access Basics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
for better understanding of the basics of Access, print and read this:

Access Basics
http://www.accessmvp.com/Strive4Peace/Index.htm
8-part free tutorial that covers essentials in Access


Warm Regards,
Crystal

*
have an awesome day
*

 

[carcher]

Hi, Crystal.

Thanks for coming back to my problem. I can show the image for one
imagename per main record, but some main form records (stationID)have as many
as 5 images per record(could be more for future stationIDs). Is there a way
to show each image for each record? Maybe using multiple image frames and
some procedure to fill them for records that have multiple images? I'm
sorry...I am ignorant as to the capabilities as well as the implementation.

Highlight Record in Continuous Form
Customize Form/Report Design Toolbar
~~~

Hi Carol,

how about putting an Image control in the form footer (or header) and
show the image for the current record that the user is on?

Or you could put the image control on the main form -- still showing
just the image for the current record in the subform...

to make it more obvious which image is displayed, you can highlight the
current record in the continuous subform:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TO HIGHLIGHT THE CURRENT RECORD IN A CONTINUOUS FORM
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

put the following textbox control on your form (I usually put it in the
footer since there is often more unused space)

name --> RecordID_current
visible --> no

make the following textbox control in the detail section of your form:

name --> HighlightBox
left --> 0
top --> 0
width --> width of detail section
height --> height of detail section, like 0.2
enabled --> false
locked --> true
tabstop --> false

send this control to the back*
(instructions on customizing a toolbar follow)

in the design view of the form, select HighlightBox
since HighlightBox is behind everything, you may need to select it using
the object drop-down (1st icon on the formatting toolbar or combo in the
Properties window at the top)

conditional formatting
condition 1 --> Expression Is
--> [RecordID] = [RecordID_current]
change fill/back color to LIGHT YELLOW or light gray
or whatever color you want for your highlight

If my detail section background is White, I like to use light yellow for
a highlight

WHERE
RecordID is the controlname of your ID field control

then, in the form OnCurrent event, assign a value to the unbound
RecordID_current
being unbound, it will have the same value on every record

'~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Current()
Me.RecordID_current = IIf([NewRecord], 0, [RecordID])
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~

where [RecordID] is the controlname corresponding to your long integer
unique ID.

If you are using a datasheet, I believe you will need to set up
conditional formatting for each control as you can't use a control that
spans the whole line as you can in a continuous form

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CUSTOMIZING YOUR FORM/REPORT DESIGN TOOLBAR
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

when you are in the design view of a form or report, RIGHT-CLICK in a
gray area to the right of your menu bar or an icon bar

choose "Customize" from the shortcut menu

in the dialog box...

choose the *Commands* tab

Category --> *Form/Report Design*

slide these icons to the Form Design toolbar:
(they are located about 3 "screens" down in the Commands list)

Align Right -- must have >1 control selected to be enabled
Align Left -- "
Align Top -- "
Align Bottom -- "
Bring To Front
Send To Back

If you don't know what these icons do, while the customize window is
open, click on the DESCRIPTION command button for each one to see what
is does.

to be efficient, the Align button are a MUST for form and report design

the Bring To Front and Send To Back are nice to have too -- since
everything is "layered" and this gives you control on the order

~~~~~~~

It is also a good idea to open the property sheet
(from the menu --> View, Properties)

on the Format tab, you can set values such as width, height, left, top, ...

~~~~~~~

in order to align, you have to have more than one control selected

to select multiple controls:

1. click and drag an imaginary line (or rectangle) touching every
control you want to select and then let go of the mouse

OR

2. click (and drag if desired) in the vertical ruler to select
everything on that extended horizontal line (or rectangle)

OR

3. click (and drag if desired) in the horizontal ruler to select
everything on that extended vertical line (or rectangle)

OR

4. click the first object and then SHIFT-CLICK objects to toggle their
selection status

*****************
there are a few tricks I use, though, to make formatting faster...

1. double-click a control handle to do a best-fit

2. adjust width and height using property sheet. sometimes I drag to
get a relative size, then adjust because I like things to be exact

3. line top and left using buttons you can add to toolbars (I sometimes
use property sheet to adjust these as well)

4. select several controls at once by clicking and dragging either in
vertical ruler or horizontal ruler -- anything that touches the extended
line/rectanglar region will be selected

5. click in a ruler to get all controls touched by the extended line --
also great for reducing margins

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Access Basics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
for better understanding of the basics of Access, print and read this:

Access Basics
http://www.accessmvp.com/Strive4Peace/Index.htm
8-part free tutorial that covers essentials in Access


Warm Regards,
Crystal

*
have an awesome day
*

Thank you so much Crystal. I'll quit beating my head against the wall now.
If no continuous form, then what are my options to display these images? I'm
lost.

Thank you for your prompt response,

Carol

 

[strive4peace]

Show Multiple Images for Continuous Subform with Filename
~~~~~~

Hi Carol,

you're welcome

"Is there a way to show each image for each record? Maybe using
multiple image frames and some procedure to fill them for records that
have multiple images?"

yes, try this:

make 5 Images controls on your main form with Names -->
Image1
Image2
Image3
Image4
Image5

for each:
SizeMode --> Zoom

Make 5 textbox controls under each image with Names -->
ImageName1
ImageName2
ImageName3
ImageName4
ImageName5

make a combo on your main form with Name -->
cboImageStartNum

put this code behind the main form:

'~~~~~~~~~~~~~~~~~~~~
Private Function ShowImages()

'written by Crystal, 4-6-08
' strive4peace2006 at yahoo.com

'ASSUMPTIONS
' there is a function to return Path to Image Files called -->
' GetImagePath
' subform has control with filename for Image called -->
' ImageName
' there are Image controls on the form named
' Image1... Image5
' there are textbox controls on the form named
' ImageName1... ImageName5

'set up Error Handler
On Error GoTo Proc_Err

Dim i As Integer _
, ImageNum As Integer _
, NumImageRecords As Long _
, NumImages As Integer _
, strFilename As String

'this is how many Images/ImageName controls you have on your form
NumImages = 5

NumImageRecords = Me.subform_controlname.Form.Recordset.RecordCount

If NumImageRecords = 0 Then
ImageNum = 1
GoTo UnusedNotVisible
End If

If IsNull(Me.cboImageStartNum) Then Me.cboImageStartNum = 1

With Me.subform_controlname.Form.RecordsetClone
.MoveFirst
.Move (Me.cboImageStartNum - 1)
ImageNum = 1

Do While Not .EOF

If ImageNum > NumImages Then GoTo Proc_Exit

strFilename = GetImagePath & .ImageName
If Len(Dir(strFilename)) > 0 Then
Me("Image" & ImageNum).Visible = True
Me("Image" & ImageNum).Picture = strFilename
Me("ImageName" & ImageNum) = .ImageName
ImageNum = ImageNum + 1
End If
.MoveNext
Loop

End With

UnusedNotVisible:

For i = ImageNum To NumImages
Me("Image" & i).Visible = False
Me("ImageName" & i).Visible = False
Next i

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

Resume Proc_Exit

'if you want to single-step code to find error,
' CTRL-Break at MsgBox
' then set this to be the next statement
Resume

End Function

'~~~~~~~~

Private Function Set_cboImageStartNum()

'set RowSource for cboImageStartNum

Dim i As Integer _
, s As String _
, NumImages As Integer _
, NumImageRecords As Long

'this is how many Images/ImageName controls you have on your form
NumImages = 5

Me.cboImageStartNum = 1

NumImageRecords = Me.subform_controlname.Form.Recordset.RecordCount

If NumImageRecords = 0 Then
Me.cboImageStartNum.Visible = False
Exit Function
Else
Me.cboImageStartNum.Visible = True
End If

If NumImageRecords < (NumImages + 1) Then
Me.cboImageStartNum.RowSource = "1"
Me.cboImageStartNum.Requery
Exit Function
End If

s = 1
For i = 2 To (NumImageRecords - NumImages + 1)
s = s & ";" & i
Next i
Me.cboImageStartNum.RowSource = s
Me.cboImageStartNum.Requery

End Function

'~~~~~~~~

Private Function UpdateImageStartNumAndShowImages()
Set_cboImageStartNum
ShowImages
End Function

'~~~~~~~~~~~~~~~~~~~~

WHERE
subform_controlname is the Name property of your subform control

on the Current event of your main form and after you change subform filter:
=UpdateImageStartNumAndShowImages()

on the AfterUpdate evnet of cboImageStartNum -->
=ShowImages()


Warm Regards,
Crystal

*
have an awesome day
*

Hi, Crystal.

Thanks for coming back to my problem. I can show the image for one
imagename per main record, but some main form records (stationID)have as many
as 5 images per record(could be more for future stationIDs). Is there a way
to show each image for each record? Maybe using multiple image frames and
some procedure to fill them for records that have multiple images? I'm
sorry...I am ignorant as to the capabilities as well as the implementation.

Highlight Record in Continuous Form
Customize Form/Report Design Toolbar
~~~

Hi Carol,

how about putting an Image control in the form footer (or header) and
show the image for the current record that the user is on?

Or you could put the image control on the main form -- still showing
just the image for the current record in the subform...

to make it more obvious which image is displayed, you can highlight the
current record in the continuous subform:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TO HIGHLIGHT THE CURRENT RECORD IN A CONTINUOUS FORM
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

put the following textbox control on your form (I usually put it in the
footer since there is often more unused space)

name --> RecordID_current
visible --> no

make the following textbox control in the detail section of your form:

name --> HighlightBox
left --> 0
top --> 0
width --> width of detail section
height --> height of detail section, like 0.2
enabled --> false
locked --> true
tabstop --> false

send this control to the back*
(instructions on customizing a toolbar follow)

in the design view of the form, select HighlightBox
since HighlightBox is behind everything, you may need to select it using
the object drop-down (1st icon on the formatting toolbar or combo in the
Properties window at the top)

conditional formatting
condition 1 --> Expression Is
--> [RecordID] = [RecordID_current]
change fill/back color to LIGHT YELLOW or light gray
or whatever color you want for your highlight

If my detail section background is White, I like to use light yellow for
a highlight

WHERE
RecordID is the controlname of your ID field control

then, in the form OnCurrent event, assign a value to the unbound
RecordID_current
being unbound, it will have the same value on every record

'~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Current()
Me.RecordID_current = IIf([NewRecord], 0, [RecordID])
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~

where [RecordID] is the controlname corresponding to your long integer
unique ID.

If you are using a datasheet, I believe you will need to set up
conditional formatting for each control as you can't use a control that
spans the whole line as you can in a continuous form

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CUSTOMIZING YOUR FORM/REPORT DESIGN TOOLBAR
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

when you are in the design view of a form or report, RIGHT-CLICK in a
gray area to the right of your menu bar or an icon bar

choose "Customize" from the shortcut menu

in the dialog box...

choose the *Commands* tab

Category --> *Form/Report Design*

slide these icons to the Form Design toolbar:
(they are located about 3 "screens" down in the Commands list)

Align Right -- must have >1 control selected to be enabled
Align Left -- "
Align Top -- "
Align Bottom -- "
Bring To Front
Send To Back

If you don't know what these icons do, while the customize window is
open, click on the DESCRIPTION command button for each one to see what
is does.

to be efficient, the Align button are a MUST for form and report design

the Bring To Front and Send To Back are nice to have too -- since
everything is "layered" and this gives you control on the order

~~~~~~~

It is also a good idea to open the property sheet
(from the menu --> View, Properties)

on the Format tab, you can set values such as width, height, left, top, ...

~~~~~~~

in order to align, you have to have more than one control selected

to select multiple controls:

1. click and drag an imaginary line (or rectangle) touching every
control you want to select and then let go of the mouse

OR

2. click (and drag if desired) in the vertical ruler to select
everything on that extended horizontal line (or rectangle)

OR

3. click (and drag if desired) in the horizontal ruler to select
everything on that extended vertical line (or rectangle)

OR

4. click the first object and then SHIFT-CLICK objects to toggle their
selection status

*****************
there are a few tricks I use, though, to make formatting faster...

1. double-click a control handle to do a best-fit

2. adjust width and height using property sheet. sometimes I drag to
get a relative size, then adjust because I like things to be exact

3. line top and left using buttons you can add to toolbars (I sometimes
use property sheet to adjust these as well)

4. select several controls at once by clicking and dragging either in
vertical ruler or horizontal ruler -- anything that touches the extended
line/rectanglar region will be selected

5. click in a ruler to get all controls touched by the extended line --
also great for reducing margins

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Access Basics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
for better understanding of the basics of Access, print and read this:

Access Basics
http://www.accessmvp.com/Strive4Peace/Index.htm
8-part free tutorial that covers essentials in Access


Warm Regards,
Crystal

*
have an awesome day
*

Thank you so much Crystal. I'll quit beating my head against the wall now.
If no continuous form, then what are my options to display these images? I'm
lost.

Thank you for your prompt response,

Carol

:

Hi Carcher,

if the subform for the images is a continuous form and you are using
Access 2003 or lower, you cannot load DIFFERENT images in the same
unbound control for different records. Unless you are working with
Access 2007, the Image control is unbound ... only ONE image can be
displayed in that control, regardless if you have multiple records
showing. Access 2007 allows you to bind the image control to a
fieldname containing the path and filename.

sorry to be the bearer of bad news...

Warm Regards,
Crystal

*
have an awesome day
*

 

[strive4peace]

Hi Caol,

ps

oops, forgot something...

add this statement in ShowImages under
Me("ImageName" & ImageNum) = .ImageName
-->

Me("ImageName" & ImageNum).Visible = True



Warm Regards,
Crystal

*
have an awesome day
*

Show Multiple Images for Continuous Subform with Filename
~~~~~~

Hi Carol,

you're welcome

"Is there a way to show each image for each record? Maybe using
multiple image frames and some procedure to fill them for records that
have multiple images?"

yes, try this:

make 5 Images controls on your main form with Names -->
Image1
Image2
Image3
Image4
Image5

for each:
SizeMode --> Zoom

Make 5 textbox controls under each image with Names -->
ImageName1
ImageName2
ImageName3
ImageName4
ImageName5

make a combo on your main form with Name -->
cboImageStartNum

put this code behind the main form:

'~~~~~~~~~~~~~~~~~~~~
Private Function ShowImages()

'written by Crystal, 4-6-08
' strive4peace2006 at yahoo.com

'ASSUMPTIONS
' there is a function to return Path to Image Files called -->
' GetImagePath
' subform has control with filename for Image called -->
' ImageName
' there are Image controls on the form named
' Image1... Image5
' there are textbox controls on the form named
' ImageName1... ImageName5

'set up Error Handler
On Error GoTo Proc_Err

Dim i As Integer _
, ImageNum As Integer _
, NumImageRecords As Long _
, NumImages As Integer _
, strFilename As String

'this is how many Images/ImageName controls you have on your form
NumImages = 5

NumImageRecords = Me.subform_controlname.Form.Recordset.RecordCount

If NumImageRecords = 0 Then
ImageNum = 1
GoTo UnusedNotVisible
End If

If IsNull(Me.cboImageStartNum) Then Me.cboImageStartNum = 1

With Me.subform_controlname.Form.RecordsetClone
.MoveFirst
.Move (Me.cboImageStartNum - 1)
ImageNum = 1

Do While Not .EOF

If ImageNum > NumImages Then GoTo Proc_Exit

strFilename = GetImagePath & .ImageName
If Len(Dir(strFilename)) > 0 Then
Me("Image" & ImageNum).Visible = True
Me("Image" & ImageNum).Picture = strFilename
Me("ImageName" & ImageNum) = .ImageName
ImageNum = ImageNum + 1
End If
.MoveNext
Loop

End With

UnusedNotVisible:

For i = ImageNum To NumImages
Me("Image" & i).Visible = False
Me("ImageName" & i).Visible = False
Next i

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

Resume Proc_Exit

'if you want to single-step code to find error,
' CTRL-Break at MsgBox
' then set this to be the next statement
Resume

End Function

'~~~~~~~~

Private Function Set_cboImageStartNum()

'set RowSource for cboImageStartNum

Dim i As Integer _
, s As String _
, NumImages As Integer _
, NumImageRecords As Long

'this is how many Images/ImageName controls you have on your form
NumImages = 5

Me.cboImageStartNum = 1

NumImageRecords = Me.subform_controlname.Form.Recordset.RecordCount

If NumImageRecords = 0 Then
Me.cboImageStartNum.Visible = False
Exit Function
Else
Me.cboImageStartNum.Visible = True
End If

If NumImageRecords < (NumImages + 1) Then
Me.cboImageStartNum.RowSource = "1"
Me.cboImageStartNum.Requery
Exit Function
End If

s = 1
For i = 2 To (NumImageRecords - NumImages + 1)
s = s & ";" & i
Next i
Me.cboImageStartNum.RowSource = s
Me.cboImageStartNum.Requery

End Function

'~~~~~~~~

Private Function UpdateImageStartNumAndShowImages()
Set_cboImageStartNum
ShowImages
End Function

'~~~~~~~~~~~~~~~~~~~~

WHERE
subform_controlname is the Name property of your subform control

on the Current event of your main form and after you change subform filter:
=UpdateImageStartNumAndShowImages()

on the AfterUpdate evnet of cboImageStartNum -->
=ShowImages()


Warm Regards,
Crystal

*
have an awesome day
*

Hi, Crystal.

Thanks for coming back to my problem. I can show the image for one
imagename per main record, but some main form records (stationID)have
as many as 5 images per record(could be more for future stationIDs).
Is there a way to show each image for each record? Maybe using
multiple image frames and some procedure to fill them for records that
have multiple images? I'm sorry...I am ignorant as to the
capabilities as well as the implementation.

Highlight Record in Continuous Form
Customize Form/Report Design Toolbar
~~~

Hi Carol,

how about putting an Image control in the form footer (or header) and
show the image for the current record that the user is on?

Or you could put the image control on the main form -- still showing
just the image for the current record in the subform...

to make it more obvious which image is displayed, you can highlight
the current record in the continuous subform:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TO HIGHLIGHT THE CURRENT RECORD IN A CONTINUOUS FORM
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

put the following textbox control on your form (I usually put it in
the footer since there is often more unused space)

name --> RecordID_current
visible --> no

make the following textbox control in the detail section of your form:

name --> HighlightBox
left --> 0
top --> 0
width --> width of detail section
height --> height of detail section, like 0.2
enabled --> false
locked --> true
tabstop --> false

send this control to the back*
(instructions on customizing a toolbar follow)

in the design view of the form, select HighlightBox
since HighlightBox is behind everything, you may need to select it
using the object drop-down (1st icon on the formatting toolbar or
combo in the Properties window at the top)

conditional formatting
condition 1 --> Expression Is
--> [RecordID] = [RecordID_current]
change fill/back color to LIGHT YELLOW or light gray
or whatever color you want for your highlight

If my detail section background is White, I like to use light yellow
for a highlight

WHERE
RecordID is the controlname of your ID field control

then, in the form OnCurrent event, assign a value to the unbound
RecordID_current
being unbound, it will have the same value on every record

'~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Current()
Me.RecordID_current = IIf([NewRecord], 0, [RecordID])
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~

where [RecordID] is the controlname corresponding to your long
integer unique ID.

If you are using a datasheet, I believe you will need to set up
conditional formatting for each control as you can't use a control
that spans the whole line as you can in a continuous form

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CUSTOMIZING YOUR FORM/REPORT DESIGN TOOLBAR
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

when you are in the design view of a form or report, RIGHT-CLICK in a
gray area to the right of your menu bar or an icon bar

choose "Customize" from the shortcut menu

in the dialog box...

choose the *Commands* tab

Category --> *Form/Report Design*

slide these icons to the Form Design toolbar:
(they are located about 3 "screens" down in the Commands list)

Align Right -- must have >1 control selected to be enabled
Align Left -- "
Align Top -- "
Align Bottom -- "
Bring To Front
Send To Back

If you don't know what these icons do, while the customize window is
open, click on the DESCRIPTION command button for each one to see
what is does.

to be efficient, the Align button are a MUST for form and report design

the Bring To Front and Send To Back are nice to have too -- since
everything is "layered" and this gives you control on the order

~~~~~~~

It is also a good idea to open the property sheet
(from the menu --> View, Properties)

on the Format tab, you can set values such as width, height, left,
top, ...

~~~~~~~

in order to align, you have to have more than one control selected

to select multiple controls:

1. click and drag an imaginary line (or rectangle) touching every
control you want to select and then let go of the mouse

OR

2. click (and drag if desired) in the vertical ruler to select
everything on that extended horizontal line (or rectangle)

OR

3. click (and drag if desired) in the horizontal ruler to select
everything on that extended vertical line (or rectangle)

OR

4. click the first object and then SHIFT-CLICK objects to toggle
their selection status

*****************
there are a few tricks I use, though, to make formatting faster...

1. double-click a control handle to do a best-fit

2. adjust width and height using property sheet. sometimes I drag to
get a relative size, then adjust because I like things to be exact

3. line top and left using buttons you can add to toolbars (I
sometimes use property sheet to adjust these as well)

4. select several controls at once by clicking and dragging either in
vertical ruler or horizontal ruler -- anything that touches the
extended line/rectanglar region will be selected

5. click in a ruler to get all controls touched by the extended line
-- also great for reducing margins

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Access Basics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
for better understanding of the basics of Access, print and read this:

Access Basics
http://www.accessmvp.com/Strive4Peace/Index.htm
8-part free tutorial that covers essentials in Access


Warm Regards,
Crystal

*
have an awesome day
*


carcher wrote:
Thank you so much Crystal. I'll quit beating my head against the
wall now. If no continuous form, then what are my options to
display these images? I'm lost.

Thank you for your prompt response,

Carol

:

Hi Carcher,

if the subform for the images is a continuous form and you are
using Access 2003 or lower, you cannot load DIFFERENT images in the
same unbound control for different records. Unless you are working
with Access 2007, the Image control is unbound ... only ONE image
can be displayed in that control, regardless if you have multiple
records showing. Access 2007 allows you to bind the image control
to a fieldname containing the path and filename.

sorry to be the bearer of bad news...

Warm Regards,
Crystal

*
have an awesome day
*

 

[carcher]

Hello, Crystal.

So sorry to be taking up so much of your time, but I your help is much
appreciated.

I'm doing something wrong, and I don't know what. I copied the functions
and controls into the main form (which is the search form for structures
data). I don't know what to do with the combobox...
Typed =UpdateImageStartNumAndShowImages() into the main form's current
event, and keep getting syntax error...moved it to After_Update on form
(since this is the form where filtered data shows (filter button is on header
of main form along with unbound search textboxes...filtered data fields are
in detail of main form), and I can use the form, the subform still shows
correct data, but the main form's imagename controls show #Name?

Typed on the AfterUpdate evnet of cboImageStartNum -->
=ShowImages()

I don't think it's firing off, but it shows up in red when viewing code.

There is a public function to getimagepath, but the code to populate the
image control with imagepath & imagename is located in the subform (which is
based on the tblImage table).

Something else that is confusing me is that the subform control itself (not
the subform name located inside it) is named tblImage...just like the table
that holds the image data.

Hope I haven't muddied the water too much. Thank you again.

Carol

Hi Caol,

ps

oops, forgot something...

add this statement in ShowImages under
Me("ImageName" & ImageNum) = .ImageName
-->

Me("ImageName" & ImageNum).Visible = True



Warm Regards,
Crystal

*
have an awesome day
*

Show Multiple Images for Continuous Subform with Filename
~~~~~~

Hi Carol,

you're welcome

"Is there a way to show each image for each record? Maybe using
multiple image frames and some procedure to fill them for records that
have multiple images?"

yes, try this:

make 5 Images controls on your main form with Names -->
Image1
Image2
Image3
Image4
Image5

for each:
SizeMode --> Zoom

Make 5 textbox controls under each image with Names -->
ImageName1
ImageName2
ImageName3
ImageName4
ImageName5

make a combo on your main form with Name -->
cboImageStartNum

put this code behind the main form:

'~~~~~~~~~~~~~~~~~~~~
Private Function ShowImages()

'written by Crystal, 4-6-08
' strive4peace2006 at yahoo.com

'ASSUMPTIONS
' there is a function to return Path to Image Files called -->
' GetImagePath
' subform has control with filename for Image called -->
' ImageName
' there are Image controls on the form named
' Image1... Image5
' there are textbox controls on the form named
' ImageName1... ImageName5

'set up Error Handler
On Error GoTo Proc_Err

Dim i As Integer _
, ImageNum As Integer _
, NumImageRecords As Long _
, NumImages As Integer _
, strFilename As String

'this is how many Images/ImageName controls you have on your form
NumImages = 5

NumImageRecords = Me.subform_controlname.Form.Recordset.RecordCount

If NumImageRecords = 0 Then
ImageNum = 1
GoTo UnusedNotVisible
End If

If IsNull(Me.cboImageStartNum) Then Me.cboImageStartNum = 1

With Me.subform_controlname.Form.RecordsetClone
.MoveFirst
.Move (Me.cboImageStartNum - 1)
ImageNum = 1

Do While Not .EOF

If ImageNum > NumImages Then GoTo Proc_Exit

strFilename = GetImagePath & .ImageName
If Len(Dir(strFilename)) > 0 Then
Me("Image" & ImageNum).Visible = True
Me("Image" & ImageNum).Picture = strFilename
Me("ImageName" & ImageNum) = .ImageName
ImageNum = ImageNum + 1
End If
.MoveNext
Loop

End With

UnusedNotVisible:

For i = ImageNum To NumImages
Me("Image" & i).Visible = False
Me("ImageName" & i).Visible = False
Next i

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

Resume Proc_Exit

'if you want to single-step code to find error,
' CTRL-Break at MsgBox
' then set this to be the next statement
Resume

End Function

'~~~~~~~~

Private Function Set_cboImageStartNum()

'set RowSource for cboImageStartNum

Dim i As Integer _
, s As String _
, NumImages As Integer _
, NumImageRecords As Long

'this is how many Images/ImageName controls you have on your form
NumImages = 5

Me.cboImageStartNum = 1

NumImageRecords = Me.subform_controlname.Form.Recordset.RecordCount

If NumImageRecords = 0 Then
Me.cboImageStartNum.Visible = False
Exit Function
Else
Me.cboImageStartNum.Visible = True
End If

If NumImageRecords < (NumImages + 1) Then
Me.cboImageStartNum.RowSource = "1"
Me.cboImageStartNum.Requery
Exit Function
End If

s = 1
For i = 2 To (NumImageRecords - NumImages + 1)
s = s & ";" & i
Next i
Me.cboImageStartNum.RowSource = s
Me.cboImageStartNum.Requery

End Function

'~~~~~~~~

Private Function UpdateImageStartNumAndShowImages()
Set_cboImageStartNum
ShowImages
End Function

'~~~~~~~~~~~~~~~~~~~~

WHERE
subform_controlname is the Name property of your subform control

on the Current event of your main form and after you change subform filter:
=UpdateImageStartNumAndShowImages()

on the AfterUpdate evnet of cboImageStartNum -->
=ShowImages()


Warm Regards,
Crystal

*
have an awesome day
*

Hi, Crystal.

Thanks for coming back to my problem. I can show the image for one
imagename per main record, but some main form records (stationID)have
as many as 5 images per record(could be more for future stationIDs).
Is there a way to show each image for each record? Maybe using
multiple image frames and some procedure to fill them for records that
have multiple images? I'm sorry...I am ignorant as to the
capabilities as well as the implementation.

:

Highlight Record in Continuous Form
Customize Form/Report Design Toolbar
~~~

Hi Carol,

how about putting an Image control in the form footer (or header) and
show the image for the current record that the user is on?

Or you could put the image control on the main form -- still showing
just the image for the current record in the subform...

to make it more obvious which image is displayed, you can highlight
the current record in the continuous subform:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TO HIGHLIGHT THE CURRENT RECORD IN A CONTINUOUS FORM
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

put the following textbox control on your form (I usually put it in
the footer since there is often more unused space)

name --> RecordID_current
visible --> no

make the following textbox control in the detail section of your form:

name --> HighlightBox
left --> 0
top --> 0
width --> width of detail section
height --> height of detail section, like 0.2
enabled --> false
locked --> true
tabstop --> false

send this control to the back*
(instructions on customizing a toolbar follow)

in the design view of the form, select HighlightBox
since HighlightBox is behind everything, you may need to select it
using the object drop-down (1st icon on the formatting toolbar or
combo in the Properties window at the top)

conditional formatting
condition 1 --> Expression Is
--> [RecordID] = [RecordID_current]
change fill/back color to LIGHT YELLOW or light gray
or whatever color you want for your highlight

If my detail section background is White, I like to use light yellow
for a highlight

WHERE
RecordID is the controlname of your ID field control

then, in the form OnCurrent event, assign a value to the unbound
RecordID_current
being unbound, it will have the same value on every record

'~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Current()
Me.RecordID_current = IIf([NewRecord], 0, [RecordID])
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~

where [RecordID] is the controlname corresponding to your long
integer unique ID.

If you are using a datasheet, I believe you will need to set up
conditional formatting for each control as you can't use a control
that spans the whole line as you can in a continuous form

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CUSTOMIZING YOUR FORM/REPORT DESIGN TOOLBAR
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

when you are in the design view of a form or report, RIGHT-CLICK in a
gray area to the right of your menu bar or an icon bar

 

[carcher]

....oops...I had copied main forms ImageName1...ImageName5 controls from
subform, so they were bound to tblImage table's ImageName field instead of
unbound. When I deleted the recordsource, they show up empty now. I hope
that is what is intended.

 

[strive4peace]

Hi Carol,

"Typed on the AfterUpdate evnet of cboImageStartNum -->
=ShowImages()"

"I don't think it's firing off, but it shows up in red when viewing code."

=ShowImages()
gets typed into the PROPERTY itself, NOT in the code ... read this and
pay close attention to the section on Code Behind Forms (CBF)

Access Basics (on Allen Browne's site)
http://www.allenbrowne.com/casu-22.html
8-part free tutorial that covers essentials in Access

Allen has a wealth of information on his site; after you get to the
bottom of this link, click on 'Index of Tips'

~~~

always compile your code before testing it...


'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~
"There is a public function to getimagepath, but the code to populate
the image control with imagepath & imagename is located in the subform
(which is based on the tblImage table)"

*** How to Create a Standard (General) Module ***

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen
-- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it. You can have
several procedures (Subs and Functions) in a module, which gives you a
way to categorize them ... ie: basic procedures that would be useful in
any database; procedures that are specific to a particular database;
procedures for converting data; etc

~~~
"the subform control itself (not the subform name located inside it) is
named tblImage"

YOU should always choose a good name when controls are made (you will
learn that when you read the Access Basics document) -- you probably
used a wizard to make the subform control and that is what you put into
it -- you should not, btw, allow users to directly manipulate a table,
that should be done with a form or subform -- so you should make a form
based on tblImage and use that as a subform (read the section on
Mainform/subforms)

~~~

"I had copied main forms ImageName1...ImageName5 controls from
subform, so they were bound to tblImage table's ImageName field instead
of unbound. When I deleted the recordsource, they show up empty now. I
hope that is what is intended."

do not use the wizards to make your controls; turn on the toolbox and
set the properties yourself. The Access Basics document tells you the
common properties to set for a textbox control and what each one is for.
I should have specified that the *ControlSource* of these controls
should be blank ... and I did tell you that they should be on the main
form (sorry, I know it is a little overwhelming)

Warm Regards,
Crystal

*
have an awesome day
*

 

[carcher]

Crystal,

You rock! I appreciate your patience as well as your expertise. Many
thanks for all that you do here...it's great encouragement for beginners like
myself. I look forward to completing the reading material that you pointed
me to...it really sheds some light. Thanks again.

I hope YOU have an awesome day!

Carol

 

[carcher]

Hello, again Crystal,

I have one more question...I'm sorry...I was just so consumed with the
multiple image problem for so long that I forgot to ask. Is it possible to
add something to this form to selectively enlarge and/or print the images
returned on the form? For example, if there were 4 images on a certain
record, and I wanted to enlarge and print 2 of them, is there a click event
or a hyperlink or something that I could use? I have a Report for the
results of the form that prints the filtered data and ALL images associated
with that record, but I was asked if the images could be selected
individually.

Many thanks,

Carol

 

[strive4peace]

Hi Carol,

you're welcome

thank you for your kind words, it is folks like you that make me happy
to help!

When you get done reading Access Basics (about 10 times <smile> -- each
time, more will sink in) ... post each statement that is confusing and
we will explain them.

VBA is quite easy to learn, it makes sense once you get past the mental
barrier, have some foundation, and know how to effectively look stuff up
in Help

Warm Regards,
Crystal

*
have an awesome day
*

 

[strive4peace]

hi Carol,

"Is it possible to add something to this form to selectively enlarge
and/or print the images returned on the form? "

this is 1a2-part answer

1. too see enarged images on the screen, you can use a double-click event

if you process a double-click event for the images that are rendered on
the form, you need to make another set of 5 controls: ImageID1...
ImageID5 (they do not have to be visible) and write the image IDs --
then you can test right(me.activecontrol.name,1) to get the image number
and use me("ImageID" & ImageNumber) to refer to the respective ImageID
control
(ok, I know you are saying this sounds like Greek -- there is a lot
written in this paragraph -- read it a few times, it will start to make
better sense)

In the subform itself, you can process a double-click event on each
control to open another form. You can use the Where Clause of the
Openform action to limit the record.

syntax:
doCmd.OpenForm FormName, View, FilterName, WhereCondition, DataMode,
WindowMode, OpenArgs

for instance:
docmd.OpenForm "frmBigImage" _
, ,,"ImageID=" & me.ImageID_controlname,,acDialog

acDialog means that the popup form needs to be closed before you can
click on any other form

~~

the pop-up form would be based on your Images table and be a single-form
view showing an enlarged image.

some form properties you might want to specify on the popup form would be:

AllowEdits --> No
AllowAdditions --> No
AllowDeletions --> No

PopUp --> Yes
Modal --> Yes

2. for printing a report with the image ... make a report that shows the
enlarged image and filter it analgous to how you filter the popup form

doCmd.OpenReport ReportName, View, FilterName, WhereCondition,
WindowMode, OpenArgs

ie:
doCmd.OpenReport "rptBigImage", acViewPreview, , _
"ImageID=" & me.ImageID_controlname

~~~

Hi Carol, I know we have done a core dump on you today ... give it at
least a night or two of sleep to let things sink in ...

Do not ever feel we are bothered when you ask questions, that is why we
are here, we want to help you

"I have a Report for the results of the form that prints the filtered
data and ALL images associated with that record"

you could further filter the report using the method I gave you above
<smile>

*****
You can find some detailed instructions for building a WhereCondition
for the OpenReport action in this thread (you need do no more than one
condition though):

http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1

newsgroup: microsoft.public.access
subject: Re: Query Report Not showing any print preview outputs
date: Apr 5, 2008 5:47 AM EST
initial post: Apr 3, 2008 10:52 pm EST

Warm Regards,
Crystal

*
have an awesome day
*

 

[carcher]

Thanks once again, Crystal...I'll try it out today.

Many thanks,

Carol

 

[strive4peace]

you're welcome, Carol

Warm Regards,
Crystal

*
have an awesome day
*

 

[carcher]

Hello, Crystal...it's me again.

Thanks for pointing me to the links to the tutorial and to the report code.
I have read them both...great info that I needed to know (lots of stuff that
you miss when you learn Access just by using it). I found a couple of VBA
online primers that I've been looking at today, as I am in way over my head
at this point.

I am ashamed to ask for further instruction, but the more I read, the more I
confuse myself. I'm still trying to make the double-click event of the Image
controls on my form enlarge the images. You've explained how it should be
done, but you were right, it does sound like Greek to me.

I have added the textbox controls ImageID1 through ImageID5 on the main
form...but I don't have a clue what to do with them. I started out just
trying to use the 'ShowImages' function to also fill these ImageID controls,
but I am very confused and need your direction, please.

Hopelessly,

Carol

 

[strive4peace]

Hi Carol,

glad you liked the links

the most important thing when you are learning is to clear your mind of
your current application ... I know it is easier to say than it is to do ...

I will try to get a sample db put together for you (I started it and
will see if I can't get it finished enough for you to look at) ... got a
lot to do tonight, going out of town for a week

BUT -- in order to understand any sample, you will need to really
submerse yourself with VBA -- it does not do you much good to get a
bunch of code you don't know how to modify ...

Warm Regards,
Crystal

*
have an awesome day
*

 

[carcher]

I really appreciate your help and patience. I apologize for the
inconvenience, and I do want to learn all that I can...I'm just having
trouble getting over this hurdle. I'm continuing to read all that I can find
and trying to apply it, but it is overwhelming right now.

Thank you again,

carol

 

[strive4peace]

steps to document a database
~~~

Hi Carol,

sned me email and I will send you the sample database to show images
from a continuous subform ... sorry I don't have much time right now to
explain it.

*** Steps for Documenting a database ***

Make a PowerPoint presentation and choose blank slide (no placeholders)

Even if you have never used PowerPoint, it is not hard. A presentation
is a collection of slides. I like to use PowerPoint because the default
is landscape, which fits a screen nicely.

To create a new presentation, press CTRL-N (New). To create a new slide,
press CTRL-M (Mew slide meow!). To save, press CTRL-S and pay attention
to where it goes!

switch between application you are documenting and PowerPoint (ppt)

to copy screen to Clipboard, press PrintScreen key on your keyboard

to paste contents of Clipboard, press CTRL-V

put screen dumps on separate slides

Document ScreenDumps of:

: design view of forms
(my hidden controls are always colored dark)

: form view of forms

: design view of reports

: report view of reports

: design view of each query and paste SQL in textbox on the slide (add
line breaks in SQL so that each RESERVED word starts a new line. Format
text as ARIAL 8 point. Becoming familiar with SQL (Standard Query
Language) is very valuable.

: output of each query (just one screen is enough)

: relationships with tables expanded so you can see all the fields
(include all tables, even those without links)

Save and Print ppt presentation

Print all code -- I like to copy it into Word and format it for easier
readability, and build a Table of Contents (Insert, Index and Tables...)
using the Heading style tags (Heading1 ALT-CTRL-1 for the name of the
module, Heading2 ALT-CTRL-2 for the sub/function declaration, Heading3
alt-ctrl-3 for whatever). FYI, all paragraphs are called "Normal" unless
you change their style name -- a GREAT feature of Word that not too many
people know about.

get a marker and label all controls with the Name property so when you
read through the code, you can see what it is referring to. It is also
helpful to label the ControlSource if it is different -- I like to Name
my controls the same as the ControlSource to avoid confusion.

Get a lined piece of paper for each form and write each form name at the
top and underline

click on each control in design view of form, whenever there is an
event, note it on your paper along with the sub/function that it runs.

ie:
form BeforeUpdate = UpdateMe()

It will take you a few hours to make a good reference like this and then
a few hours to understand what is happening, but you'll save lots of
time for yourself because you will take a whole different approach to
setting up databases.

*** steps to document VBA code using Word ***

Create a Word Document

File, Page Setup
top = 0.5
bottom = 0.5
left = 0.75
right=0.5
header = 0.3

Format, Style...
choose Normal
click Modify...
Format command button
Font... Arial, 8

OK, OK, Close

save your word doc in the directory with the database you are documenting

then, switch to your Access database

1. open the design view of each module
2. selected all code and copy (CTRL-A, CTRL-C)
3. switched to word
4. type in module name
5. format as Heading1 (CTRL-ALT-1)
6. press ENTER to create new line
7. paste code (CTRL-V)
8. press ENTER to create new line
9. repeat process from step 1 till done

once all the code is in word,

in replace dialog box, click More

search --> ^pPrivate (space)
replace --> ^pPrivate (space)
--> Heading2

^p is code for paragraph marker (Special command button)

Then, Replace All

also do for:
^pFunction (space)
^pSub (space)

to add Heading2 to what you replace with:

1. make sure you are in the ReplaceTo box
2. drop Format (from the "More" part at the bottom of the dialog box)
3. choose Style...
4. choose Heading2

***
add Header to document
View, Header and Footer

Database Name - documented [current date] for db modified [db mod date]
- {PageNo}/{Pages}

where
info in brackets is to be inserted by you
{PageNo} and {Pages} are variable icons on the Header/Footer toolbar for
the page number and the number of pages

I put header in italics and right -align

***
To create Table of Contents

1. go to end of document (CTRL-END)
2. create new line (ENTER)
3. create page break (CTRL-ENTER)
4. Type "Table of Contents" ENTER ENTER
5. from the menu: Insert, Index and Tables... Table of Contents tab
6. OK

format :

TOC1
font --> Arial 12 bold
Paragraph Space Above = 6 points

TOC2
font --> Arial 10 bold
Paragraph Space Above = 1 point

***

enhance documentation

It would be good to enhance the documentation with:

screen shot of design view and form/preview for each object. Save shots as
JPG. Insert picture into documentation between the module name and the code.

If you want to take this one step further, turn on the drawing toolbar
and label all controls (except labels, unless they have code) with their
NAME and CONTROLSOURCE (if equation, or is different than name).

***

To update the Table of Contents

click on any TOC line
press F9 to Update

***

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If anyone else wants a copy of the database, email me with this as the
subject:

Show Images from Continuous Subform

I will post it on the Internet sometime, probably here:

http://www.accessmvp.com


Warm Regards,
Crystal
strive4peace2006 at yahoo.com


*
have an awesome day
*