Help Storing Attachments to Specific Records

[lonelykeyboard]

The database I have created needs a feature in which certain projects can be attached with certain specified PDF's.

I have tried creating the following loop in order to search through a table column (table is named attachments) the column (which is titled project number and is just right of ID or in column(1)by a generated project number on my form (pnumber.value)

If a match exists between my table [Attachments](Field(1))and the generated number (pnumber.value) in my table column that it will edit the attachments column (called attachments - field 6) and update the attachments based on those attached on my form.

If there is not a match I would like it to create a new record with the project number details and associated attachments generated adn saved on the form.

PS (TorF.value is a textbox I generated to tell me if a record exists in the table on the form load to help with troubleshooting and potentially to use as simplyfying the switch / If else statements)

Thank you all very much for your help and I appreciate the time you have taken out to read the post,

LonelyKeyboard

Private Sub Command1_Click() '(button Update Project)

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim PNum As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Attachments", dbOpenDynaset)

PNum = PNumber.Value

With rs
Do Until .EOF
If .Fields(1) = PNum Then
.Edit
.Fields(6) = Attachments.CurrentAttachment
.Update
Else: If .Fields(1) <> PNum Then .AddNew
.Fields(1) = PNum
.Fields(2) = CWO.Value
.Fields(3) = MWO.Value
.Fields(4) = PIR.Value
.Fields(5) = Description.Value
.Fields(6) = Attachments.CurrentAttachment
.MoveNext
End If
Loop
End With

rs.Close
db.Close


MsgBox ("You have successfully added the attachment")
End Sub


Private Sub Command3_Click()
DoCmd.OpenForm ("Main Menu")
DoCmd.Close acForm, ("Attachments")
End Sub


Private Sub Form_Load()  

'All Code below this point works flawlessly, it is posted here as a reference of the logic attached to the form 

PNumber.Value = [Forms]![Assemble Attachments]![List19].Column(0)
CWO.Value = [Forms]![Assemble Attachments]![List19].Column(1)
MWO.Value = [Forms]![Assemble Attachments]![List19].Column(2)
PIR.Value = [Forms]![Assemble Attachments]![List19].Column(3)
Description.Value = [Forms]![Assemble Attachments]![List19].Column(4)

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim PNum As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Attachments", dbOpenDynaset)

PNum = PNumber.Value

With rs
Do Until .EOF
If .Fields(1) = PNum Then TorF.Value = True Else: If .Fields(1) <> PNum Then TorF.Value = False
.MoveNext
Loop
End With
End Sub

[Rabbit]

Your recordset should not return record. It should just return the record you're looking for. Then you can use the record count to let you know if there is a match or not.

Basically something like this:

Set rs = db.OpenRecordset("select * from someTable where idField = " & id, dbOpenDynaset)

If rs.RecordCount > 0 Then
   ' do something
Else
   ' do something else
End If

[lonelykeyboard]

I am getting an error in the following line:

.Fields(6) = Attachments.CurrentAttachment

Method 'Value' of object 'Field2' failed

I have no clue how to handle this either

[lonelykeyboard]

Also this is generating an error

Set rs = db.OpenRecordset("Select * From Attachments Where Project = " & PNumber.Value, dbOpenDynaset)

[lonelykeyboard]

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim PNum As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * From Attachments Where Project = " & PNumber.Value, dbOpenDynaset)
'error generated on the above line

PNum = PNumber.Value


With rs
Do Until .EOF
If rs.RecordCount > 0 Then
.Edit
.Fields(6) = Attachments.CurrentAttachment
.Update
Else:
.AddNew
.Fields(1) = PNum
.Fields(2) = CWO.Value
.Fields(3) = MWO.Value
.Fields(4) = PIR.Value
.Fields(5) = Description.Value
.Fields(6) = Attachments.CurrentAttachment
.MoveNext
End If
Loop
End With

rs.Close
db.Close

[Rabbit]

CurrentAttachme nt merely returns the index of the file that is active in an attachment object. It does not represent the file itself. To actually copy the binary data of a file into an attachment field, you need to use the LoadFromFile method of the field object.

An example of that can be found here: http://blogs.office.com/b/microsoft-...cess-2007.aspx

As far as your second error. You haven't told us what the error message is so there's not much I can use to figure out what's happening. If I had to guess, I would say the query is written incorrectly, ie a table name was mispelled or a field name was mispelled or you used the wrong data type.

[lonelykeyboard]

The Error was posted in message three,

It says "Method 'Value' of object 'Field2' failed"

[lonelykeyboard]

Is it trying to compose an array? That is what I think of when I see the Field2 there

Thanks for the help Rabbit

[Rabbit]

That's your first error message. I already answered that in the first paragraph of my previous post along with a link to sample code.

I was referring to your second error with the query that you never provided the error message for.

[lonelykeyboard]

Data type mismatch in criteria expression

[lonelykeyboard]

Sorry about that Rabbit

[Rabbit]

That's what I thought it would be. You just need to match up your data types in your where clause. I'm guessing it's a text and not a numeric data type. Just surround the value in single quotes.

[lonelykeyboard]

I have tried both single and double quotes in my where statement without a good result.

Here is my original code

Set rs = db.OpenRecordset("Select * From Attachments Where Project = " & PNumber.Value, dbOpenDynaset)

I have placed single quotes before Pnumber and after.value

I have also tried declaring a variable and placing the number in the variable

so for instance,

Dim Pnum as string
Pnum = 'Pnumber.value'

All of these scenarios either generate syntax or the data type mismatch errors.

I'm sorry If I am not following what you are saying

[Rabbit]

Set rs = db.OpenRecordset("Select * From Attachments Where Project = '" & PNumber.Value & "'", dbOpenDynaset)

[lonelykeyboard]

Is there a reason my Else statement would not be working?

Private Sub Command1_Click()

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim PNum As String
Set db = CurrentDb

PNum = PNumber.Value

Set rs = db.OpenRecordset("Select * From Attachments Where Project = '" & PNumber.Value & "'", dbOpenDynaset)



With rs
Do Until .EOF
If rs.RecordCount > 0 Then
.Edit
.Fields(6) = Attachments.CurrentAttachment
.Update
Else:
.AddNew
.Fields(1) = PNum
.Fields(2) = CWO.Value
.Fields(3) = MWO.Value
.Fields(4) = PIR.Value
.Fields(5) = Description.Value
.Fields(6) = Attachments.CurrentAttachment
.MoveNext
End If
Loop
End With

I tested it with some stored records I have in the database and the "true" statement works but the else statement will not add the value. The code window initiates the code and runs it without error, I know that I have to change the Fields(6) but the initial data should store IE Fields 1-5 I would think

Thanks again