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]

CurrentAttachment 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

[lonelykeyboard]

PS i have rs.close and db.close i did not copy and paste it on accident

[zmbd]

- - Sounds like we have some housekeeping to take careof in the code. If you will follow the first few steps in the following link (and please read the rest) we can start getting thru your code: > Before Posting (VBA or SQL) Code
You may need to repeat the "compile" step several times as the compiler will stop on the first error found. You then need to fix that error and then re-run the compile until you nolonger receive an error.

- - You note that you have a db.close in your code, I do not see where you opened the database... you've only set the pointer Set db = CurrentDb a rule of thumb is to close ONLY those things that you've opened to to release ONLY those things that you have set. So in your case you need somthing like:

'(...)
rs.close 'close the record set so that you wont lose stuff.
Set rs = Nothing 'release the pointer to the recordset.
Set db = Nothing 'release the pointer to the database.
'(...)
 

Also, IMHO we've moved offtopic to the OP starting with post #3. I Rabbit answered your original question in post#2. We can split the thread at that point if you will suggest a title.

[lonelykeyboard]

Get back to you on Monday Rabbit, Thanks the work week is over..

Take care and thanks for the help.

LonelyKeyboard