Attachment not inserting into table in db problem

[cuddles]

Well if I did this right, I added breakpoint and then ran it and in this watch area below it says value: expression not defined in the context

[atuhacek]

When it gets to the breakpoint you will have to go to the debug menu and step into (or press F8) to go one line past the variable then hover over the variable. it should show you the string in it. If not you can open the locals window under View. this will show all variables and there value. make sure it has the full file path.

If not then we have to figure out what add or subtract.

[ItsMe]

I cannot understand your original code. I am having a hard time understanding the workflow. Make sure you are dealing with a field of type Attachment at the table layer. Perhaps you should use DAO to Append instead of SQL and INSERT INTO.

It has been a while since I have goofed around with attachment fields. I believe the solution for you lies with a DAO.Recordset2. Perhaps have rstQuality append one record and use rstChild for the Attachment field. You could call a separate Sub procedure from the rstQuality (within the same module) that uses another Recordset instance of the Recordset2 class (rstChild) to add the attachment. I mention it because I have a working example here. It may be of help to you. Been a while since I looked at it but I believe it is relevant.


.

[cuddles]

Thanks all for the input. I cannot recode the entire thing. I will just have the freelancer.com person do what I need to do. A person over there did this data entry form for me and at the time there was no attachment. I tried to add that in and messed it up.

[June7]

Refer to link at bottom of my post for debugging guidelines.

I am still confused as to what you are trying to do. From the code it looks like you want to embed file into Attachment type field of table. This is not a good idea for 2 reasons:

1. uses up Access file size limit

2. will not migrate to SQL Server database

If you just want to save path string to a text field you are over-complicating the code.


Why is code need to save record? Is form bound to table?

[ItsMe]

I understand you are desperate for a solution but I suggest you read June's post #20

[cuddles]

I understand that. but VPs want what they want. thus the reason for 58 days from concept to deploy and using Access instead of what I said from day 1, SQL Server. But it was going to take too long to get what I need and it did. been here since the 2nd week of April and just got my software for SQL Server 3wks ago. So, they are trying to get something done fast by the seat of their pants using a person that does not know VBA very well. That being said............this is the program...........Data Entry form built by someone else. employed someone cuz I am clueless. They did all the coding up to attachment. I Googled to figure out attachment stuff and tried to look at everything possible. Even bought 250 worth of worthless Access books. Even Access for dummies and that is like basic basic Access................so.........now we are up to almost the 30th and we have to have end product. Cannot be deployed cuz no way to split DB. But they want to see that it is done.

so, I have 2 bugs. 1 is an export but that is another story. The other is this. The data entry form works fine for placing the attachment in the lstAttach textbox. it puts it in there are a C:\blah\blah\blah. I can click on it and even remove it with the remove button. So, all my data entry fields are filled in, selected and the attachment is in that lstAttach box but does not look like attachment to me looks like link. Then when I click the add record button, it will say the add was successful and the record is there but the attachment field does not go from 0 to 1. It does on my data update form and yes I tried to use the attachment code from that but got all sorts of errors and just cried myself to sleep last night. I am so incredibly stressed over this entire thing that I have lost 12lbs since I started this job, so tired I am ready to just fall apart and eyes burn to the point where I can hardly see anymore. I have never in my years of programming and not with VBA, I use other tools, been in this situation. My old org always had plans and you did not even begin a build until you had some sort of design layout and documentation. But as I say, this is a seat of your pants project with very little thought put into it and all they care about is an end result. I told them flat out the mirgration to SQL and if we have to rebuild forms I will need at least 6 months cuz I am not doing this again.

[cuddles]

This is why I will more than likely just have the person who did the data entry form just fix that little piece for me since he is familiar. I hate outsourcing but it is what it is.

[June7]

Regardless of whether or not the migration to SQL Server takes place, I suggest the simplest approach may be to just save path string in text field and leave the files external instead of embedding in attachment field. But that does present new questions.

Are you now trying to load multiple files into one Attachment field? An Attachment type field is a multi-value field and is really why it will not migrate to other databases. Access is the only one with multi-value field structure. If VPs want to migrate to SQL will face issue of getting those Attachment type fields converted to a normalized structure and storing path string into a text field. Might as well do it now than later. Instead of multiple files into a single multi-value Attachment field, use a related table with a record for each file. I guess an alternative might be code that extracts each embedded file and save into an SQL blob field if and when the need arises, but that is beyond my experience.

Another question would be how to structure the path string. If the path string uses drive letter then every user must have the same drive mapped on their computer. I recommend UNC path. This is a path that uses server name in place of drive letter and Windows drive mapping is not needed. Either way, it is not even necessary to save the full path into field. Constructing full path to external file can be done in code, just depends on the file hierarchy (are all files in the same path or scattered everywhere).

[cuddles]

I got it working. Here is the solution at the bottom of that code after the insert:

Dim i As Long
If lstAttach.ListCount > 0 Then
lngQualID = DMax("[QID]", "[QualMain]")
rstQuality.Seek "=", lngQualID
rstQuality.Edit
Set rsChild = rstQuality.Fields("Attachment").Value
For i = 0 To lstAttach.ListCount - 1
st = lstAttach.Column(0, i)
rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile st
rsChild.Update
Next
rstQuality.Update
End If
msgbox "Record successfully added."
End Sub