Grid From 1

[PrimoSteve]

Hello All,

I have a subform that acts like a grid that displays 24 command button ( 4 Rows and 6 columns)

tblCmds
cmdID Short Text
cmdCaption Short Text
cmdPhotoUrl Short Text


Private Sub Form_Load()
On Error GoTo FormLoadErr:

Dim ctl As Control
Me.cTemp.SetFocus


For Each ctl In Me.Controls

If TypeOf ctl Is CommandButton Then

If ctl.Name Like "cmdOrder*" Then

ctl.Visible = True
ctl.Caption = DLookup("[cmdCaption]", "tblCmds", "[cmdID]='" & ctl.Name & "'")
If ctl.Caption = "Empty" Then ctl.Visible = False

End If

End If

Next ctl

DoCmd.Save

Exit Sub

FormLoadErr:
MsgBox ctl.Name & "-" & err.Description

End Sub


The following code above works fine to display the caption but when I add the following line of code to display the picture.

ctl.Picture = DLookup("[cmdPhotoUrl]", "tblCmds", "[cmdID]='" & ctl.Name & "'")

I get this error

"cmdOrder02-SampleDB can't open tje file
'C:\Users\Admin\Documents\Parts1.png'."

Any assistance would help in writing this code the correct way



For Each ctl In Me.Controls

If TypeOf ctl Is CommandButton Then

If ctl.Name Like "cmdOrder*" Then

ctl.Visible = True
ctl.Caption = DLookup("[cmdCaption]", "tblCmds", "[cmdID]='" & ctl.Name & "'")
ctl.Picture = DLookup("[cmdPhotoUrl]", "tblCmds", "[cmdID]='" & ctl.Name & "'")
If ctl.Caption = "Empty" Then ctl.Visible = False

End If

End If

Next ctl

DoCmd.Save


Regards
Primo

 

[strongm]

Command buttons only directly support bmp and ico file formats, I am afraid. Although a workaround is to use an image control as a proxie, and then transfer the picture from the proxie to the command button via the PictureData property.

 

[PrimoSteve]

Command buttons only directly support bmp and ico file formats, I am afraid. Although a workaround is to use an image control as a proxie, and then transfer the picture from the proxie to the command button via the PictureData property.

I've tried bmp file format but still to know avail

Primo

Command buttons only directly support bmp and ico file formats, I am afraid. Although a workaround is to use an image control as a proxie, and then transfer the picture from the proxie to the command button via the PictureData property.

Command buttons only directly support bmp and ico file formats, I am afraid. Although a workaround is to use an image control as a proxie, and then transfer the picture from the proxie to the command button via the PictureData property.

 

[PrimoSteve]

I've tried using the BMP file format but still no avail

Regards

 

[PrimoSteve]

Ive attached the file for viewing hope this might help

 

[dhookom]

Have you attempted to troubleshoot by writing similar code that sets the picture of a single command button with hard coded values? Isolating bit by bit is a great troubleshooting method. Don't add any complexity until you have success with the previous step.

 

[PrimoSteve]

Hi Duane, Ive been doing that exactly. I've managed to the pictures to show if the the link field for the photo is not null. But if the field is empty and null I get the error for the command button with the empty field e.g. cmdOrder2 which has no data

"cmdOrder02-SampleDB can't open tje file
'C:\Users\Admin\Documents\Parts1.png'."

 

[Andrzejek]

With:
ctl.Picture = DLookup(...
what do you get from your DLookup? Is it just the name of the file, like MyPicture.bmp? Or full path with file name, like C:\SomeFolder\Images\MyPicture.bmp?

 

[strongm]

So, quick look at this - and DLOOKUP actually does the hard work of converting the underlying file to a supported type, so that is not the problem

So, have to ask, you are trying to open files in different user profiles. Does the account running the program have the correct access rights to do this?

 

[PrimoSteve]

Its the full path C:\SomeFolder\Images\MyPicture.bmp that's on the table field cmdPhotoUrl

tblCmds
cmdID Short Text
cmdCaption Short Text
cmdPhotoUrl Short Text

 

[PrimoSteve]

So, quick look at this - and DLOOKUP actually does the hard work of converting the underlying file to a supported type, so that is not the problem

So, have to ask, you are trying to open files in different user profiles. Does the account running the program have the correct access rights to do this?

Yes, the file can be open by different users but it shouldn't make a difference. I have attached the file so you can view it yourself

 

[OzFoxy]

I'm not sure what you want to achieve, but hope this helps.
I copied your 'butter.jpg' to my c:\ and renamed it to 'butter.bmp'
In tblCmds i changed the first 7 PhotoURL's to C:\butter.bmp
In the form load code I added Dim pic as variant
I modified the code as in the attached screensnip and got the result in the other screensnip. Hope this helps in some way.

 

[dhookom]

Many of your filenames in your table have trailing spaces.

 

[PrimoSteve]

I'm not sure what you want to achieve, but hope this helps.
I copied your 'butter.jpg' to my c:\ and renamed it to 'butter.bmp'
In tblCmds i changed the first 7 PhotoURL's to C:\butter.bmp
In the form load code I added Dim pic as variant
I modified the code as in the attached screensnip and got the result in the other screensnip. Hope this helps in some way.

 

[PrimoSteve]

@ OzFoxy - This worked perfect many thanks for your help and everyone's input, You can see attachment and idea of what I/m trying to achieve, still more work to do

 

[strongm]

Yes, the file can be open by different users but it shouldn't make a difference. I have attached the file so you can view it yourself

Primo, I'd already downloaded your example and tested it. it worked fine here (when I populated the table with files that actually existed on my PC; it's how I determined that dlookup was doing some interesting work in the background.

The only way I could get replicate your symptoms problem was if I changed the permissions,

 

[PrimoSteve]

Hi Strongman, I'll have look it's possible I haven't tested on another pc or network.

But I also have another issue which I think has to do with a Null field

I'm getting a "cmdOrder01-Type mismatch" error if the PhotoURL field is empty or Null
Any idea how do I code this to ignore the data field if it's empty/null or missing the linked photo address data

Private Sub Form_Load()
On Error GoTo FormLoadErr:

Dim ctl As Control
Dim pic As Variant
Me.cTemp.SetFocus



For Each ctl In Me.Controls
X = 1

If TypeOf ctl Is CommandButton Then

If ctl.Name Like "cmdOrder*" Then

ctl.Visible = True
ctl.Caption = DLookup("[cmdCaption]", "tblCmds", "[cmdID]='" & ctl.Name & "'")
pic = DLookup("[PhotoURL]", "tblCmds", "[cmdID]='" & ctl.Name & "'")
ctl.Picture = pic
If ctl.Caption = "Empty" Then ctl.Visible = False

X = X + 1
End If

End If

Next ctl

DoCmd.Save



Exit Sub

FormLoadErr:
MsgBox ctl.Name & "-" & err.Description

End Sub

 

[Andrzejek]

One way would be (and that would me my preferred way) to not allow NULL or empty PhotoURL field in tblCmds table.

Other way:

pic = DLookup("[PhotoURL]", "tblCmds", "[cmdID]='" & ctl.Name & "'")
If IsNull(pic) or pic & "" = "" Then
    'Do something
Else
    ctl.Picture = pic
End If

 

[PrimoSteve]

Hi Andrzejek,

Thanks! I've actually tried your way but I'm getting a compile error: Next without For

Private Sub Form_Load()
On Error GoTo FormLoadErr:

Dim ctl As Control
Me.cTemp.SetFocus
Dim pic As Variant


For Each ctl In Me.Controls


If TypeOf ctl Is CommandButton Then

If ctl.Name Like "cmdOrder*" Then

ctl.Visible = True
ctl.Caption = DLookup("[cmdCaption]", "tblCmds", "[cmdID]='" & ctl.Name & "'")
pic = DLookup("[PhotoURL]", "tblCmds", "[cmdID]='" & ctl.Name & "'")
If IsNull(pic) Or pic & "" = "" Then
MsgBox " No Image Available"
Else
ctl.Picture = pic
If ctl.Caption = "Empty" Then ctl.Visible = False


End If

End If

Next ctl

DoCmd.Save

Exit Sub

FormLoadErr:
MsgBox ctl.Name & "-" & err.Description

End Sub

 

[combo]

With indents you can easily find closing End If missing:

For Each ctl In Me.Controls
    If TypeOf ctl Is CommandButton Then
        If ctl.Name Like "cmdOrder*" Then
            ctl.Visible = True
            ctl.Caption = DLookup("[cmdCaption]", "tblCmds", "[cmdID]='" & ctl.Name & "'")
            pic = DLookup("[PhotoURL]", "tblCmds", "[cmdID]='" & ctl.Name & "'")
            If IsNull(pic) Or pic & "" = "" Then
                MsgBox " No Image Available"
            Else
                ctl.Picture = pic
                If ctl.Caption = "Empty" Then ctl.Visible = False
            End If
        End If
    ' ?????
Next ctl