How to insert attachment in form with VBA (1 Viewer)

[Jerme24]

Hi, recently I just joined a company and I need to do a DB for my company. I am totally new to MS Access. I created a simple form and I want to add image in the form. May I know how do i do that in vba? Below is my current code for saving record in the form. I cannot upload my file

Private Sub btn_save_Click()

Dim sMissingValues As String
Dim ack As Integer

Dim db As Database
Set db = CurrentDb

Dim strAddDriverInfo As String
Dim strAddVehicleInfo As String

sMissingValues = ""

If Len(Me.txtDriverName & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Driver name"
If Len(Me.txtDriverID & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Driver ID"
If Len(Me.txtDriverContact & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Driver contact"
If Len(Me.txtDriverEmail & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Driver email"

If Len(Me.txtVehicleMake & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Vehicle Make"
If Len(Me.txtVehicleModel & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Vehicle Model"
If Len(Me.txtVehicleNo & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Vehicle No"

If sMissingValues <> "" Then
    MsgBox prompt:="Please update the following mandatory fields:" & vbCrLf & sMissingValues, buttons:=vbCritical, title:="Missing Information"
    
Else
ack = MsgBox("Are you sure you want to save this driver record?", vbYesNo)

If ack = vbYes Then
    DoCmd.SetWarnings False

strAddDriverInfo = "insert into tbl_driver_info ([driver name], driver_id, driver_contact, driver_email, date_created, driver_photo) VALUES ('" & Me.[txtDriverName] & "', '" & Me.[txtDriverID] & "', '" & Me.[txtDriverContact] & "', '" & Me.[txtDriverEmail] & "', NOW(), '" & Me.driverPhoto & "' )"
strAddVehicleInfo = "insert into tbl_vehicle_info (driver_id, vehicle_make, vehicle_model, vehicle_no) VALUES ('" & Me.[txtDriverID] & "', '" & Me.[txtVehicleMake] & "', '" & Me.[txtVehicleModel] & "', '" & Me.[txtVehicleNo] & "' )"

    DoCmd.runsql strAddDriverInfo
    DoCmd.runsql strAddVehicleInfo
    
    MsgBox prompt:="Driver Record Successfully Added", buttons:=vbInformation, title:="Record Saved"
    
    With Me
    .txtDriverName.Value = ""
    .txtDriverID.Value = ""
    .txtDriverContact.Value = ""
    .txtDriverEmail.Value = ""
    .txtVehicleMake.Value = ""
    .txtVehicleModel.Value = ""
    .txtVehicleNo.Value = ""
    .driverPhoto = ""
    End With
    
    DoCmd.SetWarnings True
    
    Else
    MsgBox prompt:="Driver Record not Save", buttons:=vbInformation, title:="Record not Save"
    
End If

End If

End Sub

 

[moke123]

depends what you mean by "I want to add image in the form"
Do you want to show a picture that will change with every record? Or a background image or Logo?
If your talking about an attachment field , most of us would tell you not to use them and use a linked image in an image control instead.

One thing you want to be careful about is using "+" when concatenating strings. If you encounter a null value you'll get an unexpected result. You should use an amphersand "&" .

An easy way to validate controls on a form is to loop through the form controls using the tag property.
Even better is that you can use one procedure for most any form.

Something like:

Public Function ValidateForm(frm As Form, TagCharacter As String) As Boolean
'validated controls must have a label. Ok to use a hidden label if needed.

    Dim ctl As Control
    Dim flg As Boolean
    Dim strOut As String

    flg = True

    For Each ctl In frm.Controls

        If ctl.Tag = TagCharacter Then
            If Nz(ctl.value, "") = "" Then
                flg = False
                ctl.BorderColor = vbRed
                strOut = strOut & Space(10) & "* " & ctl.Controls.Item(0).Caption & vbNewLine
            Else
                ctl.BorderColor = vbBlack
            End If

        End If
    Next

    If flg = False Then
        MsgBox "The following field(s) must be completed:" & vbNewLine & strOut
    End If

    ValidateForm = flg

End Function

 

[Jerme24]

What I meant by "I want to add image in the form", is I actually wanted to add an image on the form when filling in the details. And that is what you mentioned, an attachment field.

How to go about doing linked image in an image control and tag property?

Sorry, Im totally new to Access

 

[Jerme24]

I added this to my beforeupdate. It is able to validate. But after I submitted the form and tried to close it, it will prompt the validation message. Where should i put an end to the validation?

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strError As String
Dim ctl As Control
Dim blnIsValid As Boolean

strError = "Validation failed for the following reasons: " & vbNewLine
blnIsValid = True

For Each ctl In Me.Controls
    With ctl
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionButton, acOptionGroup, acToggleButton
                If Nz(.Tag) = "Required" And Nz(.Value) = "" Then
                    blnIsValid = False
                    strError = strError & "   - " & ctl.Name & " is required" & vbNewLine
                End If
        End Select
    End With
Next ctl

If blnIsValid = False Then
    'Form data is NOT valid!
    Cancel = True 'Cancel update
    MsgBox strError, vbInformation
End If

Set ctl = Nothing


End Sub

 

[jdraw]

See this tutorial/video from Richard Rost.

 

[theDBguy]

Hi. Check out the LoadFromFile method.

Field2.LoadFromFile method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

 

[mike60smart]

Hi
You have 2 tables and you had not set any referential integrity between them.

Your Form was based on both tables which is a No No in this case.

I set up a true relationship between each of the tables and then created a Main Form based on the Driver Details
with a subform based on the Vehicle.

See the attached.

 

[moke123]

Heres Mikes example with a picture added and validation on the drivers name and number.
There are many ways to do it. In this example I am only saving the name of the photo in the drivers table. all the photos are stored in an image folder in the same folder as the database . There is also a button to get the photo name.

 

[Pat Hartman]

Moke's way is best when working with a Jet/ACE BE. images tend to bloat an Access BE very quickly. I only embed images that have limited values. For example, I have a drawing log application for a steel fabrication company that has several sub companies (4 at the moment). So, I store the logos for all the companies in the database BUT, for the drawings of which there could ultimately be 100's of thousands. the app stores the path only and when the user clicks on the show drawing button, it uses the FollowHyperlink method to open the image of the drawing itself. I don't load these into an image control as I do the logo because I want the drawings to be fullscreen.

 

[Jerme24]

Hi
You have 2 tables and you had not set any referential integrity between them.

Your Form was based on both tables which is a No No in this case.

I set up a true relationship between each of the tables and then created a Main Form based on the Driver Details
with a subform based on the Vehicle.

See the attached.

Thanks. I will take a look and explore.

 

[Jerme24]

Heres Mikes example with a picture added and validation on the drivers name and number.
There are many ways to do it. In this example I am only saving the name of the photo in the drivers table. all the photos are stored in an image folder in the same folder as the database . There is also a button to get the photo name.

Thanks. I will take a look and explore as well.