Allowing combo box to accept blank entries (1 Viewer)

[Mezta1988]

Lord Almighty!

Thank you very much isladog, Genius... I can sleep well now, God! I love this forum.

 

[isladogs]

You're welcome. Sleep well.
Don't forget my advice to replace the attachment field used for the Photo.

 

[Mezta1988]

Thank you for all the informative advise, I learn so many things about access today. May god almighty keep you safe and shower you more blessings and your family. Thanks also to those who shared their thoughts regarding this post. Sorry if my english is not that good like you guys, until then have a nice day or night to everyone.

 

[isladogs]

Your English is very good. Better than some for whom English is their first language.

 

[Mezta1988]

Hi @isladog how are you sir,

I just want to tell that I followed your advise regarding the photo field on my employee Details form to change from attachment to text or short text. And I watched some videos in Youtube on how to do that and luckily I got managed to follow the tutorials and get it done for me. However, none of those tutorials show how to upload the image or delete the image using a button, it's like a manual thing like copying the file path of the folder and the name of the photo or file at the end.

Would you be so kind to share you thought and knowledge about this? on how I can add button so I can click and then it will open my folder explorer or the file path where I saved all the images.

Please......

 

[isladogs]

Hi

I'm actually not too sure what you're asking but assume you want to know how to link the photo to an individual person on the form based on the image path.

So I've attached a Contacts example database together with two sample photos (male/female) so you can try it out and use similar code in your own application. I did this almost 10 years ago partly based on an MS template & haven't looked at it for a while. I've tested the photo linkage and that does work but its possible other parts don't work

For this example only, the supplied photos need to stay in the Photos subfolder of wherever you save this file but you can choose any BMP, PNG or JPG file. I don't recommend JPG

I've already attached photos to 2 contacts - you can play around adding the sample photos / replacing with your own / removing photos

Hope I've understood what you wanted & that this helps you with the next steps

 

[Mezta1988]

Thank you very much for sharing some of your masterpiece, and I really appreciate every single help that you are extending to share your solutions. I'll check your file and I will try my best to learn all the structures and codes even though I have zero skills in vb or vba coding. But I'm sure this one won't be easy like a hot knife through butter haha. Have a lovely evening sir.

 

[isladogs]

Its no masterpiece but hopefully will help you.
The code may be a little tricky to understand. The most important parts are in cmdEditPhoto_Click event of the form frmContactDetails.

The same button can be used to add, change or remove a photo.
As you do so, it will update the file path of the related field in the Contacts table.

You should be able to find tutorials on the MS website to help you understand.
Also have a look at the MS Access Contacts template which IIRC has a video or help file with it. I think I may have taken the form from that and adapted it for my needs but it was a long time ago ….

 

[Mezta1988]

Hi,

I checked out the file that you posted, and I got confused with the frmContactDetail. The box which the photo appear, is that a form? Kindly please check the attachment.

 

[isladogs]

Yes - in that case its a subform containing an image control
The example came from a larger app where the same subform was used in several different forms. I often do that to save repeating code.

However you can just use an image control directly on your form if your prefer.
For example, see attached screenshot

 

[Mezta1988]

Alright, but let me try to do the first one you sent to me.
Thanks sir,

 

[isladogs]

No problem ... either way you need an image frame .... but please drop the sir!

 

[Mezta1988]

I'm sorry if I can't help my self not to address you using that salutation . Anyway, I have a question regarding the PhotoAvailable inside your table and when I checked your frmContactDetails it seems like that field is hidden. And one more thing I attached a JPG image of the code which I copied from the file that you sent to me and I'm getting an error everytime I click the button upload which I created inside my form.

The error message says "Label Not Defined"

 

[isladogs]

The ImagePath field is in the subform record source, not the main form.

If you are getting an error on the error handler, perhaps you didn't copy the entire code where the err_handler section is found

 

[Mezta1988]

Alright, I noticed also that you PhotoAvailable field in the table is set to yes/no but when I looked at the Lookup beside general tab is set to textbox, I'm trying to change mine but it's not changing. What I mean is, if I select the textbox it switch right away back to checkbox even though I removed the format and the default value on the General tab just like how it appears on your table.

Private Sub Uploader_Click()
'Add/edit/remove photo
On Error GoTo Err_Handler

'DISABLED for this example
 '   MsgBox "Feature has been disabled for this example", vbInformation, "Not Available"

'==========================================
'check if photo already assigned
strSelection = Nz(DLookup("Photo", "Person", "ID = " & Me.ID), "")

  If strSelection <> "" Then
        If MsgBox("Are you sure you want to remove the existing photo?", vbQuestion + vbYesNo, "No photo chosen") _
            = vbYes Then
           CurrentDb.Execute "UPDATE Person" & _
                " SET Person.Photo = Null, Person.PhotoAvailable = False" & _
                " WHERE (((Person.ID)=" & [Forms]![Person].[ID] & "));"
            Me.Requery
            Exit Sub
         Else
        '    'no code here
        End If
  End If
'==========================================
End Sub

'==========================================

'CR 28/08/2015 - Code rewritten to ensure compatibility with 64-bit Office
'add new photo

' Set options for the dialog box.
    Dim F As FileDialog
    Set F = Application.FileDialog(msoFileDialogFilePicker)
    F.Title = "Locate the photo file and click on 'Open'"
    
' Clear out the current filters, and add our own.
      F.Filters.Clear
      F.Filters.Add "Image files", "*.bmp; *.jpg; *.png"
      
' Set the start folder
        If Me.Photo <> "" Then
            F.InitialFileName = GetPathWithoutFilename(Me.Photo)
        Else
            F.InitialFileName = CurrentProject.Path & "\Photos"
        End If
    
' Call the Open dialog routine.
    F.Show

' Return the path and file name.
    strFilePath = F.SelectedItems(1)
    Debug.Print strFilePath
    
   ' Dim strSQL As String

'Update file path
   If strFilePath <> "" Then
       CurrentDb.Execute "UPDATE Person" & _
        " SET Person.Photo = '" & GetFilePath & "', Person.PhotoAvailable = True" & _
        " WHERE (((Person.ID)=" & [Forms]![Person].[ID] & "));"
  End If
      
   Me.Requery

Exit_Handler:
    Exit Sub

Err_Handler:
    'if err.number=5,user clicked cancel
    If Err.Number <> 5 Then
        MsgBox "Error " & Err.Number & " " & Err.Description
    End If
    Resume Exit_Handler

End Sub

the Person is the table and the Photo is the image path in my table.

 

[isladogs]

First of all, my apologies. I was wrong when I said the image path wasn't on the main form. Its a hidden control because there's no reason to see it ...or the other hidden controls.

It doesn't really matter whether you have a checkbox or textbox to display the PhotoAvailable field in the table. Both will work perfectly well. Choose whichever you prefer. Remember end users should never work on the tables. All interaction via the form.

Did you save your table after making changes?

BTW you can remove the line 'disabled for this example and the following line.
I meant to delete both but forgot

 

[Mezta1988]

I did not save the table after making some changes. I tried to run the VB code again but same error message is showing.

 

[isladogs]

Always save your table if you make design changes.
Try compiling your code to pin down your error. I don't know which 'label is undefined' in your database.

I added a couple of lines at the end of my last post about the same time you replied.

 

[Mezta1988]

I did not saved it because nothing has changed, like I said the checkbox as the default propoerty of YES/NO cannot be changed to text box. By thae way I did solve the issue for the label error, but unfortunately after I compile again I get another error.

 

[isladogs]

You can't save design changes if the table is in use. You need to close the form(s) first.
Good luck solving your error