file attachment on MS Access 2007

[ddtpmyra]

Need to know how to do the vba coding on attaching the file

code
Private Sub Command_Click()
Dim rs1 As Recordset
Set rs1 = CurrentDb.OpenRecordset("cmr_filestorage")
rs1.AddNew
Me.author = rs1.Fields("author")
Me.requestor = rs1.Fields("requestor")
Me.deadline = rs1.Fields("deadline")
Me.category = rs1.Fields("category")
Me.descripton = rs1.Fields("description")
Me.file = rs1.Fields("file")

rs1.Update
rs1.Close

MsgBox "New record added"
End Sub

Problem
I can do the file upload to the user interface but when I hit the button to save the new records error message "Object does'nt support this method" and it's pointing to:
Me.file = rs1.Fields("file")

Question
Somebody can show me how to handle this error im kinda new on ms 2007

thanks,
DM

[DonRayner]

Need to know how to do the vba coding on attaching the file

code
Private Sub Command_Click()
Dim rs1 As Recordset
Set rs1 = CurrentDb.OpenRecordset("cmr_filestorage")
rs1.AddNew
Me.author = rs1.Fields("author")
Me.requestor = rs1.Fields("requestor")
Me.deadline = rs1.Fields("deadline")
Me.category = rs1.Fields("category")
Me.descripton = rs1.Fields("description")
Me.file = rs1.Fields("file")

rs1.Update
rs1.Close

MsgBox "New record added"
End Sub

Problem
I can do the file upload to the user interface but when I hit the button to save the new records error message "Object does'nt support this method" and it's pointing to:
Me.file = rs1.Fields("file")

Question
Somebody can show me how to handle this error im kinda new on ms 2007

thanks,
DM

If you're adding a new record to the table you need to change these lines

Me.author = rs1.Fields("author")....should be rs1.fields("author") = me.author.value
Me.requestor = rs1.Fields("requestor")...... same change as above
Me.deadline = rs1.Fields("deadline")......etc
Me.category = rs1.Fields("category").....etc
Me.descripton = rs1.Fields("description")......etc
Me.file = rs1.Fields("file")....etc

[NeoPa]

As Don says, you're moving the data the wrong way. You need to move it from the form (Me.) to the record buffer (rs1.).

Having said all that, this is really a quite unnecessarily complicated way to handle something that Access forms do for you anyway, without any work required.

Have a look at some of the wizards.

[ddtpmyra]

Everythings works fine except when I'm saving the file attachment
I tried this but does'nt work

rs.Fields("file") = Me.file.Filename.Value
rs.Fields("file") = Me.file.FileType.Value
rs.Fields("file") = Me.file.FileURL.Value

I need more help on this please.

[NeoPa]

...
I need more help on this please.

I suggest you pay some more attention to what's already posted then. Simply ignoring responses is not likely to win you too much help.

[ddtpmyra]

NeoPa,

It's the same problem on file attachment if you look on my first post.

I can do the file upload to the user interface but when I hit the button to save the new records error message "Object does'nt support this method" and it's pointing to:
Me.file = rs1.Fields("file")

[DonRayner]

Everythings works fine except when I'm saving the file attachment
I tried this but does'nt work

rs.Fields("file") = Me.file.Filename.Value
rs.Fields("file") = Me.file.FileType.Value
rs.Fields("file") = Me.file.FileURL.Value

I need more help on this please.

It needs to be "rs.Fields("file") = me.file.value". Also make sure that you don't have a typo for the field name in your table or in the control name. Add a msgbox "msgbox me.file.value" at the beginning of your sub to see what value you're trying to add.

Try this code and see how it works

Private Sub Command_Click()
On error goto ErrorPoint
 
Dim db1 as dao.databae, rs1 As dao.Recordset
Set db1 = CurrentDb
set rs1 = db1.OpenRecordset("cmr_filestorage")
 
rs1.AddNew
 
rs1!author = me.author.value
rs1!requestor = me.requestor.value
rs1!deadline = me.deadline.value
rs1!category = me.category.value
rs1!description = me.description.value
rs1!file = me.file.value
 
rs1.Update
rs1.Close
 
set rs1 = nothing
set db1 = nothing
 
MsgBox "New record added"
ExitPoint:
Exit Sub
 
ErrorPoint:
msgbox err.descripton & "  Error# " & err.number
Resume ExitPoint
 
End Sub
 

You will need to make sure that you Microsoft DAO referanced for this code to work.

[NeoPa]

NeoPa,

It's the same problem on file attachment if you look on my first post.

I wasn't asking for the question to be repeated. I was merely wondering at the fact that you don't seem to have noticed some of the comments already posted.

This whole issue is only a problem because you are trying to bark yourself, when Access makes a perfectly good dog. Check out post #3.

Post #2 answers your problem if you insist on continuing to try to code, what is already built into the functionality of Access forms, yourself.