Attachment not inserting into table in db problem

[cuddles]

The records all insert into DB but the attachment does not.



HELP!!!!

Option Compare Database
Option Explicit

Private Sub Add_Click()
Dim yrID As Integer, mthid As Integer, measId As Integer, comLvlId As Integer, contId As Integer, stid As Integer
Dim prodId As Integer, lobId As Integer, busId As Integer, progId As Integer, comId As Integer, freqId As Integer
Dim memTgt As Variant, memRch As Variant, memConv As Variant, PROGENTDT As Variant, ts As Date
Dim rstQuality As DAO.Recordset
Dim rsChild As DAO.Recordset
Dim lngQualID As Long, st As String

   Set rstQuality = CurrentDb.OpenRecordset("QualMain")
   rstQuality.Index = "PrimaryKey"
   
If IsNull(prognm.Value) Or prognm.Value = "" Then
msgbox "Please select the program name."
prognm.SetFocus
Exit Sub
Else
progId = prognm.Column(1)
End If

If IsNull(stcd.Value) Or stcd.Value = "" Then
msgbox "Please select the state."
stcd.SetFocus
Exit Sub
Else
stid = stcd.Column(1)
End If

If IsNull(contact.Value) Or contact.Value = "" Then
msgbox "Please select the program owner name."
contact.SetFocus
Exit Sub
Else
contId = contact.Column(1)
End If

If IsNull(YR.Value) Or YR.Value = "" Then
msgbox "Please select the program year."
YR.SetFocus
Exit Sub
Else
If YR.Value = "2013" Then
yrID = 1
Else
yrID = YR.Value - 2013 + 1
End If
End If

If IsNull(MTH.Value) Or MTH.Value = "" Then
msgbox "Please select the program month."
MTH.SetFocus
Exit Sub
Else
mthid = MTH.Column(1)
End If

If IsNull(FREQ.Value) Or FREQ.Value = "" Then
msgbox "Please select the Frequency of Intervention."
FREQ.SetFocus
Exit Sub
Else
freqId = FREQ.Column(1)
End If

If IsNull(BUSUNIT.Value) Or BUSUNIT.Value = "" Then
msgbox "Please select the business unit."
BUSUNIT.SetFocus
Exit Sub
Else
busId = BUSUNIT.Column(1)
End If

If IsNull(LOB.Value) Or LOB.Value = "" Then
msgbox "Please select the line of business."
LOB.SetFocus
Exit Sub
Else
lobId = LOB.Column(1)
End If

If IsNull(prodnm.Value) Or prodnm.Value = "" Then
msgbox "Please select the plan/product."
prodnm.SetFocus
Exit Sub
Else
prodId = prodnm.Column(1)
End If

If IsNull(hedismeasure.Value) Or hedismeasure.Value = "" Then
msgbox "Please select the HEDIS Measure."
hedismeasure.SetFocus
Exit Sub
Else
measId = hedismeasure.Column(1)
End If

If IsNull(commlvl.Value) Or commlvl.Value = "" Then
msgbox "Please select customer, Provider or Member."
commlvl.SetFocus
Exit Sub
Else
comLvlId = commlvl.Column(1)
End If

If IsNull(COMMTYPE.Value) Or COMMTYPE.Value = "" Then
msgbox "Please select the communication type."
COMMTYPE.SetFocus
Exit Sub
Else
comId = COMMTYPE.Column(1)
End If

If Not IsNull(progdt.Value) Or progdt.Value <> "" Then
PROGENTDT = progdt.Value
Else
PROGENTDT = ""
End If

CurrentDb.Execute "INSERT INTO QualMain" & "(YR_ID,MTH_ID,MEASURES_ID,COMM_LVL_ID,CONTACT_ID,ST_ID,PROD_ID,LOB_ID,BUS_ID,PROG_ID,COMM_ID,FREQ_ID,PROG_ENTDT) Values" & "('" & yrID & "'" & "," & "'" & mthid & "'" & "," & "'" & measId & "'" & "," & "'" & comLvlId & "'" & "," & "'" & contId & "'" & "," & "'" & stid & "'" & "," & "'" & prodId & "'" & "," & "'" & lobId & "'" & "," & "'" & busId & "'" & "," & "'" & progId & "'" & "," & "'" & comId & "'" & "," & "'" & freqId & "'" & "," & "'" & PROGENTDT & "');"

If Not IsNull(txtPath.Value) Or txtPath.Value <> "" Then
st = txtPath.Value
lngQualID = DMax("[QID]", "[QualMain]")
rstQuality.Seek "=", lngQualID
rstQuality.Edit
Set rsChild = rstQuality.Fields("Attachment").Value
rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile st
rsChild.Update
rstQuality.Update
End If

msgbox "Record successfully added."

End Sub
   'THIS GETS THE ATTACHMENT FROM THE MACHINE AND PUTS IT IN THE LSTATTACH TEXTBOX

Private Sub cmdAdd_Click()                   
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
With dlgOpen
 .AllowMultiSelect = False
 .InitialFileName = "Z:\" 'Initial Path when explorer is opened
 .Show
     If .SelectedItems.Count = 0 Then
       msgbox ("No file Selected") 'No file selected
     Else
        Me.lstAttach.AddItem .SelectedItems(1)  'sets textbox on the form to the path selected
        
     End If
  End With

End Sub

[atuhacek]

Try declaring a variant and set it with

me.dlgOpen.AddItem (your variant)

them assign that to your list

http://support.microsoft.com/kb/824272

[June7]

Are you saving a lot of large attachment objects into db? These can really eat up Access 2GB file size limit.

[cuddles]

Not at this time we are not. We had a deadline to create this in 58 days. This entire giant application and have millions of records so far and they want attachments so the data entry form can be used to add new and the data upate form to update and add attachments to both. They will be 1 page .pdf files at this time. We are migrating to SQL Server but had to meet the deadlines in place of this build and only have 1 person to do the planning of the DB, layouts and all, the design of forms and front-end apps. The functionality, the supporting documentation. on and on it goes. That person is ME. That is why I have been so stressed working 14 hour days 7 days a week on a language I have not used since 2006. I know another language inside and out and THOUGHT that was what I would be coding in. Anyway, I have 21 forms now, 16 reports and now they want the attachments to work which that will be migrated to SQL Server in the next month or so. But to meet the deadline they need this to work now so they can say ok, we met the deadline of the build and now so people can use it have to mirgrate it. Cannot use it as is cuz we have no web server or way to get users to the application. We can put it on everyones machine but that does not help with the links to the tables. We cannot sharepoint it cuz 5000 limitation. We put it on a shared drive but then you have 1 person at a time opening it and using the SD, slower than molasses. It is slow as it is when I compact on my local machine due to the size. Takes about 7 mins to compact and repair. And the filtering through the listboxes to get results, wow. about 3 minutes. This is because it is searching through millions of records. So anyway, I will look at that link and see what I can do. Thanks

[cuddles]

UGH. Well that did not work with my current code.

[ItsMe]

If you are migrating to SQL server why attachment field?

[atuhacek]

This should work just comment out your function and paste this in. It would probably be better to use hyperlinks to point to the files. But if your set on attachments try this.

Function cmdAdd_Click() As VariantDim

Dim objDialog As Object


Set objDialog = Application.FileDialog(3)


Dim var As String


With objDialog
    .AllowMultiSelect = False
    .InitialFileName = "Z:\" 'Initial Path when explorer is opened
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "No file selected."
    Else
        var = .SelectedItems(1)
        Me.lstAttach.AddItem (var)
    End If
End With


Set objDialog = Nothing




End Function

[cuddles]

In order to meet the demands of the deadline on the 30th of June. Mgmnt wants what Mgmnt wants. So, it has to work like they wanted it to almost 58 days ago. We don't have the SQL Server space yet or approval. That is just the goal. In the meantime they want this done. I cannot figure it out. Time to post it on freelancer.com for some help.

[atuhacek]

Are you trying to get the path of a file or the file itself? Then what are you doing with it when you get it? Returning it to a table or control.

[cuddles]

NO there is a textbox for the attachment. I have Add and Remove buttons. So they can add and remove different files. Then I have once their data entry is complete an add button to add the record. It is the add record that does not pick up the file that is in the textbox.

[atuhacek]

Ok. For clarity. You are using the file dialog to save a value in lstAttach (what ever that is) then you are using you add record to send txtPath (what ever that is) to your table.

How is the value going from lstAttach to txtPath?

If Not IsNull(txtPath.Value) Or txtPath.Value <> "" Then


st = txtPath.Value
lngQualID = DMax("[QID]", "[QualMain]")
rstQuality.Seek "=", lngQualID
rstQuality.Edit


Set rsChild = rstQuality.Fields("Attachment").Value
rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile st
rsChild.Update
rstQuality.Update


End If

[cuddles]

Actually it is not. It is: (I posted this b4 I found that particular error and fixed it after the post, but that is not causing the problem. It still does not attach but says the new record added was successful)

If Not IsNull(lstAttach.Value) Or lstAttach.Value <> "" Then


st = lstAttach.Value
lngQualID = DMax("[QID]", "[QualMain]")
rstQuality.Seek "=", lngQualID
rstQuality.Edit


Set rsChild = rstQuality.Fields("Attachment").Value
rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile st
rsChild.Update
rstQuality.Update


End If

[atuhacek]

Have you debugged the st to verify it is getting the fully qualified path of your file.

[cuddles]

how do you debug an ST?

[atuhacek]

Put a breakpoint at the line


st = lstAttach.Value

Then run you code. Step past it to see what value is being stored into your variable.