How to create a command button that opens another form with data based on that form 2

Triacona · Sep 26, 2012

Dear All,
I'm having problems with a command button linking a form to another.

I have a form CallsLogged.
Which uses the table CallsLogged.
CallsLogged table has the Primary Key of Reference
It has 3 Subforms.

One of the subforms is UpdatesSubform.
Which uses the table Updates.
Updates table has the Primary Key of UpdateNo
The Updates table links to the CallsLogged table via Reference

The relationship between the CallsLogged table and the Updates table is One Call to Many Updates; I have enabled referential integrity.
This works great.

The problem I am having is that instead of creating a subform on the subform updates, no screen space, I want to create a button that will open a form I have already created.

The form is called Attachments
The form Attachments uses the table Attachments
The table Attachments Primary Key is AttachNo
It also has the field UpdateNo

It links to the Updates table via UpdateNo to Updates tables UpdateNo. This creates a One Update with Many Attachments relationship; I have enabled referential integrity.
I want the form to have the UpdateNo field filled in automatically, in accordance with the Update forms UpdateNo.

I have used the button wizzard and used it to display specific information UpdateNo <=> UpdateNo.
This doesn't autofill in the UpdateNo and in fact it is blank or zero with the AttachNo still on Autonumber.

I have also checked when the form Attachments is running after clicking the button.
The Attachments form shows in the Properties: Filter [UpdateNo]=63
And next to the navigation buttons (Filtered)
But still no joy.
here is the code:

Code:

Private Sub OpenAttachments_Click()
On Error GoTo Err_OpenAttachments_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Attachments"
    
    stLinkCriteria = "[UpdateNo]=" & Me![UpdateNo]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenAttachments_Click:
    Exit Sub

Err_OpenAttachments_Click:
    MsgBox Err.Description
    Resume Exit_OpenAttachments_Click


End Sub

I have even tried to create a subform on the UpdatesSubform and then use a button to link to that and that does not work, also used a normal open form button, didn't work.
The Attachment table is empty of course as no attachments have been added yet.

Any help would be greatly appreciated [smile]

Thank you,

Kind regards

Triacona

dhookom · Oct 1, 2012

Triacona said:
How do you specify each file type? so I can set different images for each file type?

Not sure what you mean. I think you just enter the file types and image file names into the table you created.

Duane
Hook'D on Access
MS Access MVP

Triacona · Oct 1, 2012

Hi Duane!

Thanks for your help.
What I mean is the file type will have a specific image attached to it.
I.e. xls with have a picture of an excel icon, found in the F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\ folder.

So would I store the path for those specific file types in the the table?
How would the code above then Identifiy the correct image?

Thank you,

Kind regards

Triacona

Triacona · Oct 1, 2012

I have also tried:

Code:

   If DLookup(FileExtension, FileType, "FileExtension" = Right(path, 4)) Then
     Me.PropIm.Picture = DLookup(FileImagePath, FileType, FileExtension = Right(path, 4))
   End If

This either doesn't work or when I try to change the formating gives me the following error:
[quote: Run-time error '2428'] you Enterd an invalid argument in a domain aggergate function.
So based on the extension I want a particular image to display, but only for certain types.
So in my FileType table, I have the following data:
FileNo FileExtension FileType FileImagePath
1 .bmp Image NULL
2 .gif Image NULL
3 .jpg Image NULL
4 .png Image NULL
5 .tif Image NULL
6 .wmf Image NULL
7 .doc Word F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\WORD.bmp
8 .rtf Word F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\WORD.bmp
9 .docx Word F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\WORD.bmp
10 .xls Excel F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\XLS.bmp
11 .xlsx Excel F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\XLS.bmp
13 .pdf PDF F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\PDF.bmp

So as you can see I want to check the path and check the FileExtension in relation to right(path,4) and then match it to the data in the table and output the correct image.
So If right(path,4) = FileExtension goto placeholder with correct extension and output the correct image to Me.PropIm.Picture, but using FileImagePath.

Thanks for your help [smile]

Thank you,

Kind regards

Triacona

Triacona · Oct 1, 2012

I have also tried:

Code:

  If Right(path, 4) = DLookup(FileExtension, FileType, Right(path, 4)) Then
    Me.PropIm.Picture = DLookup(FileImagePath, FileType)
  End If

but with the same error:

Run-time error '2428' said:
you Enterd an invalid argument in a domain aggergate function

Thank you,

Kind regards

Triacona

dhookom · Oct 1, 2012

What happened when you used the syntax that I suggested?

Duane
Hook'D on Access
MS Access MVP

Triacona · Oct 1, 2012

Hey Duane

as in y previous post it just shows the xls picture...so I don't know if that is in cache?
it doesn't show the PDF one....

I have substituted the above code into the xls ElseIf it shows the xls picture but not the pdf one...

Thank you,

Kind regards

Triacona

Triacona · Oct 1, 2012

I have just tried:

Code:

If DCount("*", "FileType", "FileExtension ='" & Right(path, 4) & "'") > 0 Then
  Me.PropIm.Picture = DLookup("FileImagePath", "FileType")
End If

The above seems to work within the table but for an xls and pdf file it has an error:

Run-time error '2220' said:
Uniform Calls Logged Database v1.5 can't open the file 'NULL'

So it seems it is getting values from the table just not the right values.
I deleted the filetypes that are images, and then instated the following code:

Code:

    If Not fso.FileExists(path) Then
            Me.PropIm.Picture = "F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\BLANK.jpg"
        
        ElseIf fso.FileExists(path) Then
            If DCount("*", "FileType", "FileExtension ='" & Right(path, 4) & "'") > 0 Then
                Me.PropIm.Picture = DLookup("FileImagePath", "FileType")
            End If
        ElseIf Right(path, 4) = ".jpg" Or Right(path, 4) = ".bmp" Or Right(path, 4) = ".png" Or Right(path, 4) = ".wmf" Or Right(path, 4) = ".tif" Or Right(path, 4) = ".gif" Then
            Me.PropIm.Picture = path
        
        End If

This only brings out the first image, word, the first record in the table.
How do I make it find the right extension and link that to the right path in the table?
Thanks you [smile]

Thank you,

Kind regards

Triacona

dhookom · Oct 1, 2012

The arguments of the DLookup() functions are string literals or variables.

[CODE vba]
If Not IsNull DLookup("FileExtension", "FileType", "FileExtension ='" & Right(Me.Path, 4) & "'") Then
Me.PropIm.Picture = DLookup("FileImagePath", "FileType","FileExtension ='" & Right(Me.Path, 4) & "'")
End If
[/code]

Please learn how to debug. Open the immediate window (press Ctrl+G) and enter

Code:

? DLookup("FileExtension", "FileType", "FileExtension ='.bmp'") 
? DLookup("FileExtension", "FileType", "FileExtension ='.xls'") 
? DLookup("FileImagePath", "FileType", "FileExtension ='.xls'")

You also need to be careful since you have added some file extensions using 3 characters and others using 4 (xls and xlsx).

Duane
Hook'D on Access
MS Access MVP

Triacona · Oct 1, 2012

Hi Duane

Thanks for all your valueable help [bigsmile]

I have taken on board what you have said..
I opened the immediate window and pasted in the code you have...how do I then debug? I am quite new to debugging...
usually just move or edit the code and check my logic and hope it works.
But debugging sounds really useful.
Any pointers there would be great! [smile]

On the code you have given me, I correct a syntax error, which is what VB was saying, so now I have:

Code:

If Not IsNull(DLookup("FileExtension", "FileType", "FileExtension ='" & Right(Me.path, 4) & "'")) Then
            Me.PropIm.Picture = DLookup("FileImagePath", "FileType", "FileExtension ='" & Right(Me.path, 4) & "'")
        End If

VB Highlights:

Code:

If Not IsNull(DLookup("FileExtension", "FileType", "FileExtension ='" & Right(Me[COLOR=#EF2929][b].path[/b][/color], 4) & "'")) Then
            Me.PropIm.Picture = DLookup("FileImagePath", "FileType", "FileExtension ='" & Right(Me.path, 4) & "'")
        End If

And says

Compile Error said:
Method or data member not found.

Any further help would be greatly appreciated [2thumbsup]

Thanks!

Thank you,

Kind regards

Triacona

Triacona · Oct 1, 2012

Hi Duane,
I have solved the problem with the compile error:

Code:

If Not IsNull(DLookup("FileExtension", "FileType", "FileExtension ='" & Right([b]path[/b], 4) & "'")) Then
            Me.PropIm.Picture = DLookup("FileImagePath", "FileType", "FileExtension ='" & Right([b]path[/b], 4) & "'")
        End If

Both me.path had to be changed to path, now it works brilliantly!
if I were to include the image extensions in the FileType table, how would I reference the path in the table?
So if I filled in FileImagePath for .bmp for example, to path. the thought being path in this case with be the variable path??
if not, how would I get around that?
Thank you again for all your help! have a star! [2thumbsup]

Thank you,

Kind regards

Triacona

dhookom · Oct 1, 2012

Path is possibly a reserved word. Check Allen Browne's list.

Don't you have the actual path to the image files in your table stored in the [Path] field?

Duane
Hook'D on Access
MS Access MVP

Triacona · Oct 2, 2012

Hey there Duane [smile]

The path is a variable string.
The actual field stored in my table Attachment is AttachPath.
here is an exerpt of the code:

Code:

 Dim path As String
    
       If IsNull([AttachPath]) Then
            path = "\\wbc-fp-1\users\etienne\data\My Pictures\Houses\BLANK.jpg"
        Else
            path = [AttachPath] ' change to match the file w/Path
        End If

Thanks

Thank you,

Kind regards

Triacona

dhookom · Oct 2, 2012

I don't understand what you need if anything. Just explain what your form's record source is and what you want to happen.

Duane
Hook'D on Access
MS Access MVP

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to create a command button that opens another form with data based on that form 2

Triacona

Technical User

dhookom

Programmer

Triacona

Technical User

Triacona

Technical User

Triacona

Technical User

dhookom

Programmer

Triacona

Technical User

Triacona

Technical User

dhookom

Programmer

Triacona

Technical User

Triacona

Technical User

dhookom

Programmer

Triacona

Technical User

dhookom

Programmer

Similar threads

Part and Inventory Search

Sponsor