[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

MS ACCESS - Button to set concatenate an image path

I need to create a button that takes some fields in tables and concatenates them into a file path, set it to a field and then refresh a form to show the image.  Please see the attached file.

form showing button needed.
0
gpchicago08
Asked:
gpchicago08
  • 11
  • 8
1 Solution
 
MacroShadowCommented:
Assuming the fields are not bound you can do something like this:
Sub Demo()
    strFileName = AddSlash(DLookup("Path_To_Emplyoee_Images", "ImagePathT")) & _
                  DLookup("LastName", "StaffT") & "_" & _
                  DLookup("FirstName", "StaffT") & ".jpg"
End Sub

Function AddSlash(strInput As String) As String
    AddSlash = IIf(Right(strInput, 1) = "\", strInput, strInput & "\")
End Function

Open in new window

0
 
MacroShadowCommented:
If the fields are bound then replace the dlookup with Me.NameOfControl.
0
 
gpchicago08Author Commented:
Great, Ok.  So after I get strFileName, how do I push it to the current ImagePath field in StaffT?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
gpchicago08Author Commented:
I'm getting a Runtime Error 2471.  It has to do with the singular record in the Path_To_Employee_Images field.  I tried adding this table to the query that this form is based on with no luck.  Is there a way to pull the info from ID 1 of this field in ImagePathT?
0
 
MacroShadowCommented:
<<So after I get strFileName, how do I push it to the current ImagePath field in StaffT? >>
That depends if your form is bound or not.
If it's bound:
Private Sub cmdFindImage_Click()
    Me.txtImagePath = AddSlash(DLookup("Path_To_Emplyoee_Images", "ImagePathT")) & _
                      DLookup("LastName", "StaffT") & "_" & _
                      DLookup("FirstName", "StaffT") & ".jpg"
End Sub
Function AddSlash(strInput As String) As String
    AddSlash = IIf(Right(strInput, 1) = "\", strInput, strInput & "\")
End Function

Open in new window


If it's not bound:
Either using a Sql statement
CurrentDb.Execute "INSERT INTO StaffT(ImagePath) VALUES (" & Me.txtImagePath & ")"

Open in new window

or a recordset
    Dim rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset("StaffT")

    With rs
        .AddNew
        !ImagePath = Me.txtImagePath
        .Update
        .Close
    End With

    Set rs = Nothing

Open in new window


<<I'm getting a Runtime Error 2471.>>
On which line does the error occur?
<< Is there a way to pull the info from ID 1 of this field in ImagePathT?>>
DLookup accepts 3 arguments:
1. The field to retrieve data from
2. The table containing the field
3. A condition, data retrieved where condition is true

For example:
DLookup("Path_To_Emplyoee_Images", "ImagePathT", "ID = 1")

Open in new window

0
 
gpchicago08Author Commented:
There was a typo... DLookup("Path_To_Emplyoee_Images", "ImagePathT", "ID = 1")

Now that I got it to work, how do I push this value into the fields on the form and in the table?

Thanks again!


DUH  - didn't read the posts above.  I will try them and report back!
0
 
gpchicago08Author Commented:
Ok.  I have this code.  It pumps a line into the txtImagePath box with some issues.
Private Sub btnFindImage_Click()
Me.txtImagePath = DLookup("Path_To_Employee_Images", "ImagePathT") & DLookup("LastName", "StaffT") & "_" & DLookup("FirstName", "StaffT") & ".jpg"
End Sub

Open in new window

1.  The name isn't the name of the current staff.  I'm assuming I have to add something to the DLookup commands to grab the current last and first names.
2.  The form doesn't push the new path to StaffT.  It just fills the form field.  If I click into another box, it fills the field in StaffT but doesn't refresh the image box.
3.  The form also doesn't refresh the image to show the image of the staff.
0
 
gpchicago08Author Commented:
I figured out the image issue.  Another typo.  Still can't get the correct name or the refresh.
0
 
gpchicago08Author Commented:
I got the name string figured out!  I used Me.txtLastName and Me.txtFirstName!!!
Yeah for me!

Now I just need the push of the final path to StaffT without me clicking somewhere else on the form.
0
 
gpchicago08Author Commented:
I tried this code and got an error
DoCmd.RunSQL "Update StaffT SET ImagePath = " & Me!txtImagePath & "  WHERE StaffID = Me!txtStaffID"

Open in new window

0
 
MacroShadowCommented:
DoCmd.RunSQL "Update StaffT SET ImagePath = " & Me!txtImagePath & "  WHERE StaffID = " & Me!txtStaffID

Open in new window

0
 
gpchicago08Author Commented:
Typo!  Geesh.
BUT...
Still getting Runtime Error 3075
Syntax error (missing operator) in query expression 'K:\DOCS\OFFICE\Last_First.jpg'
0
 
MacroShadowCommented:
Try:
DoCmd.RunSQL "Update StaffT SET ImagePath = " & Me!txtImagePath & " WHERE StaffID = '" & Me!txtStaffID & "'"

Open in new window

0
 
gpchicago08Author Commented:
Sorry.  Still not working.
I may be wrong but I think the error is occurring during the Me!txtImagePath portion of the command.  Not at the end.
0
 
MacroShadowCommented:
What is the image of txtImagePath at the time of the error?
0
 
gpchicago08Author Commented:
The image works fine.  The path is a valid path.  The only thing that doesn't happen is the updating of the table StaffT with the path text.

If I click into another box on the form, the table updates.

Is there a more simple way to update the table (refresh)?
0
 
MacroShadowCommented:
I'm confused. Previously you mentioned "Still getting Runtime Error 3075
Syntax error (missing operator) in query expression 'K:\DOCS\OFFICE\Last_First.jpg' ", now no error occurs???

The most simple way to update the table is using bound controls, then no code is necessary. The table will be updated once the focus is moved to another control.
0
 
gpchicago08Author Commented:
Error still occurs.
I do have bound controls so I guess I won't bother with this anymore.
0
 
MacroShadowCommented:
If your controls are bound there is no need for code.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now