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!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
Triacona

Triacona

Technical User
Jun 11, 2009
462
GB
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
 
Sort by date Sort by votes
Is your issue that the UpdateNo isn't defaulted to the correct UpdateNo for new records? I would expect this is the case since you don't have any code or other that sets the default value of the UpdateNo in the attachments form.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Hey Duane! [smile]
Thanks very mucgh for your help![bigsmile]
In the attachments form, what do I do code wise?
Do I loose the linking code on the button?
Do I set the default value in the properties of the UpdateNo to the linked value, for example "[UpdateNo]=" & Me![UpdateNo]?

Thanks again for your help[smile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
or
Code: 
[forms]![CallsLogged]![UpdatesSubform].[form]![UpdateNo]
Doesn't seem to work if I put it in the default value??
Thanks :)

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
I have put
Code: 
[Forms]![CallsLogged]![Updates Subform].[Form]![UpdateNo]
into the default value for UpdateNo on the Attachments Form.
This has worked with the button code as such...
Code: 
Private Sub OpenAttachments_Click()
On Error GoTo Err_OpenAttachments_Click

    Dim stDocName As String
        stDocName = "Attachments"

    Dim stLinkCriteria As String
        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

The only problem now is the images are not refreshing.
I have a browse for image button which then links a picture to that record in the Attachments Form.
I will investigate further...
Any ideas would always be much appreciated [smile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
The picture path displays but the picture reverts back to the blank picture, when changing records.

Here is my code for the picture and Browse Button.
Code: 
Private Sub Form_Current()

    Dim path As String
    
        FileExists
        
End Sub

Sub FileExists()

    Dim fso
    Dim path As String
    
    Set fso = CreateObject("Scripting.FileSystemObject")

        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"
        Else
            Me.PropIm.Picture = path ' MsgBox path & " has been located.", vbInformation, "File Found"
        End If
    

End Sub

Private Sub BrowseForImage_Click()
On Error GoTo Err_BrowseForImage_Click

    Dim fdg As FileDialog, vrtSelectedItem As Variant
    Dim strSelectedFile As String


    Set fdg = Application.FileDialog(msoFileDialogFilePicker)

        With fdg
            .AllowMultiSelect = False
            .InitialView = msoFileDialogViewDetails
                If .Show = -1 Then
                    For Each vrtSelectedItem In .SelectedItems    'onby be 1
                        strSelectedFile = vrtSelectedItem
                    Next vrtSelectedItem
                    Me![AttachPath] = strSelectedFile
                    Me.PropIm.Picture = strSelectedFile
                Else     'The user pressed Cancel.
                End If
        End With

    Set fd = Nothing

Exit_BrowseForImage_Click:
    Exit Sub

Err_BrowseForImage_Click:
    MsgBox Err.Description
    Resume Exit_BrowseForImage_Click



    
End Sub
I have no idea why the image is not displaying, it works for my other program, but within a subform....?
It seems this form is causing the issue..?
Any help to clarify the problem here would be great! [bigsmile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Yay it now links with the code below, change in red![bigsmile]
Code: 
Private Sub Form_Current()

    Dim path As String
    
        FileExists
        
End Sub

Sub FileExists()

    Dim fso
    Dim path As String
    
     [COLOR=#CC0000]  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[/color]        
    Set fso = CreateObject("Scripting.FileSystemObject")

        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"
        Else
            Me.PropIm.Picture = path ' MsgBox path & " has been located.", vbInformation, "File Found"
        End If
    


End Sub
Private Sub BrowseForImage_Click()
On Error GoTo Err_BrowseForImage_Click

    Dim fdg As FileDialog, vrtSelectedItem As Variant
    Dim strSelectedFile As String


    Set fdg = Application.FileDialog(msoFileDialogFilePicker)
  
        With fdg
            .AllowMultiSelect = False
            .InitialView = msoFileDialogViewDetails
                If .Show = -1 Then
                    For Each vrtSelectedItem In .SelectedItems    'onby be 1
                        strSelectedFile = vrtSelectedItem
                    Next vrtSelectedItem
                    Me![AttachPath] = strSelectedFile
                    Me.PropIm.Picture = strSelectedFile
                Else     'The user pressed Cancel.
                End If
        End With
  
    Set fd = Nothing

Exit_BrowseForImage_Click:
    Exit Sub

Err_BrowseForImage_Click:
    MsgBox Err.Description
    Resume Exit_BrowseForImage_Click
    
End Sub

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Hi Duane,

Here is an interesting one...
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 Right(path, 3) = ".jpg" Or ".bmp" Then
   Me.PropIm.Picture = path ' MsgBox path & " has been located.", vbInformation, "File Found"
End If

I want the picture to display only if it is a bmp or jpg...how would I do that in the above code?

Thanks for all your help[bigsmile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
or
Code: 
ElseIf Right(path, 3) In(".jpg",".bmp") Then
Thanks :)

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
ElseIf Right(path, 4) = ".jpg" Or Right(path, 4) = ".bmp" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Hey PHV,
Thanks very much, that works brilliantly!
[smile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
I have another problem...

Code: 
If Not fso.FileExists(path) Then
            [COLOR=#A40000]If Right(path, 4) = ".doc" Or Right(path, 4) = ".rtf" Or Right(path, 4) = ".docx" Or Right(path, 4) = ".xls" Or Right(path, 4) = ".xlsx" Or Right(path, 4) = ".pdf" Then
                Me.PropIm.Picture = "F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\BLANK.jpg"
            End If[/color]
        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 ' MsgBox path & " has been located.", vbInformation, "File Found"
        End If
The code highlighted red does not seem to work...I can't figure out why.
Basically I want the code to produce a blank image in PropIm if the file types are the ones listed in the red code.
The image does not change to the blank image when it reaches the file types highlighted in red...
Any help would be greatly appreciated...[smile]
Thanks [bigsmile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Dear All,

I've figured it out thanks [smile]
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 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 ' MsgBox path & " has been located.", vbInformation, "File Found"
[COLOR=#A40000]ElseIf Right(path, 4) = ".doc" Or Right(path, 4) = ".rtf" Or Right(path, 4) = ".docx" Or Right(path, 4) = ".xls" Or Right(path, 4) = ".xlsx" Or Right(path, 4) = ".pdf" Then
   Me.PropIm.Picture = "F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\BLANK.jpg"[/color]
End If
Thanks for all your guys help![2thumbsup]
Keep up the excellent work!

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
I probably wouldn't hard code the extensions since it might leave you maintaining code rather than data. Consider creating a small table of these values with a column storing if the file type is an image or not. Then use DLookup() or DCount().

Data belongs in your tables, not in your code.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Hi Duane! [smile]
I have created a table FileTypes.
FileTypes has the following fields:
FileNo (Autonumber)
FileExtension (Text)
FileType (Text)
Example data as follows:
FileNo FileExtension FileType
1 .jpg Image
2 .xls Excel
etc...
How would I use the DlookupFunction to find the appropriate value for the following senario?
Code: 
    Dim fso
    Dim path As String
    
        If IsNull([AttachPath]) Then
            path = "F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\BLANK.jpg"
        Else
            path = [AttachPath] ' change to match the file w/Path
        End If
        
    Set fso = CreateObject("Scripting.FileSystemObject")
    
        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 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 ' MsgBox path & " has been located.", vbInformation, "File Found"
        ElseIf Right(path, 4) = ".doc" Or Right(path, 4) = ".rtf" Or Right(path, 4) = ".docx" Then
            Me.PropIm.Picture = "F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\WORD.bmp"
        ElseIf Right(path, 4) = ".xls" Or Right(path, 4) = ".xlsx" Then
            Me.PropIm.Picture = "F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\XLS.bmp"
        ElseIf Right(path, 4) = ".pdf" Then
            Me.PropIm.Picture = "F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\PDF.bmp"
        End If
I want an image to display for the different file types, unless it is an image, in which case I want the image displayed.
I also had another thought:
Code: 
 path = MsoFileType.msoFileTypeExcelWorkbooks
and do this for each file type so msoFileTypeWord and then there is pdf???
Thanks again for your help! [bigsmile]


Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Hi again Duane,

I get a Type mismatch error for the following:
Code: 
ElseIf path = MsoFileType.msoFileTypeExcelWorkbooks
One is a string (path) and the other a number MsoFileType.msoFileTypeExcelWorkbooks which has the value of 4.
Is there a way to make MsoFileType.msoFileTypeExcelWorkbooks output a string extension or filetype?
Or should it be used as a lookup using path? MsoFileType.msoFileTypeExcelWorkbooks(path)?
Thanks [smile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
e.g
Code: 
 elseif MsoFileType.msoFileTypeExcelWorkbooks(path) then
This gives me an error of
Compile Error: said:
Expected array
Click to expand...
Thanks :)

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
or
Code: 
Set fs = CreateObject("Scripting.FileSystemObject")
ElseIf fs.LookIn = path And fs.FileType = msoFileTypeExcelWorkbooks Then


Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
I you code about 3 posts back, try something like:
Code: 
ElseIf DCount("*","FileTypes","FileExtension ='" & Right(path, 4) & "'") >0 Then
            Me.PropIm.Picture = path ' MsgBox path & " has been located.", vbInformation, "File Found"
I would also store the file "F:\PLANNING&REGULATION\Shared Documents\Access Reports\Calls Logged\v1.3\Screenshots or Attachements\PDF.bmp" with the record where the extension is PDF.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Hi Duane [smile]
Thanks for all your help!
I have substituted the above code into the xls ElseIf it shows the xls picture but not the pdf one...
How exactly does the above code work?
From my understanding of it your telling it to count all the records in the table FileType where the file extension equals the last 4 characters of the path i.e. the file extension, if that count is greater than 0 output the specified picture.
How do you specify each file type? so I can set different images for each file type?

Thanks [bigsmile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

PrimoSteve
  • Locked
  • Question
Cannot add Image to Command Button?
Replies
9
Views
696
PrimoSteve
PrimoSteve
breezett93
  • Locked
  • Question
Function that find all controls on a form and puts their tab index in numerical order
Replies
19
Views
777
dhookom
dhookom
Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
681
Kennelbloke
Kennelbloke
TrekBiker
  • Locked
  • Question
Form Data Not Updating
Replies
6
Views
550
dhookom
dhookom
I
  • Locked
  • Question
How to get 2 codes to work together.
Replies
6
Views
465
hermanlaksko
hermanlaksko

Part and Inventory Search

Sponsor

Back
Top