Solved Object variable or with block variable not set

[MaryamF]

I get this error message :
Object variable or with block variable not set

on this :
Private Sub CmndAddTenant_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim TSSN As String
Dim TFN As String
Dim TDOB As Date
Dim msg As Integer
Dim TF As String
Dim TMS As String
Dim TNC As Double
Dim TBUID As String
Dim TOD As String
Dim SD1 As String
Dim SD2 As String
Dim SD3 As String
Dim FI As Image
Dim SI As Image
Dim TI As Image


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TblTenants")

TSSN = Me.TenantSSN.Value
TFN = Me.TenantName.Value
TDOB = Me.TenantDOB.Value
TF = Me.TenantPhone.Value
TMS = Me.TenantMStatus.Value
TNC = Me.TenantNChildren.Value
TBUID = Me.BUID.Value
TOD = Me.TenantOcupationD.Value
SD1 = Me.SuportDoc1.Value
SD2 = Me.SuportDoc2.Value
SD3 = Me.SuportDoc3.Value
FI = Me.ImagFirstDocument.Value
SI = Me.ImagSecondDocument.Value
TI = Me.ImagThirdDocument.Value

With rst
.AddNew
!BUID = TBUID ' Add data.
!SSN = TSSN
!TenantStatus = TMS
!TenantChildren = TNC
!TenantDocumentInfo1 = SD1
!TenantDocumentInfoImage1 = FI
!TenantDocumentInfo2 = SD2
!TenantDocumentInfoImage2 = SI
!TenantDocumentInfo3 = SD3
!TenantDocumentInfoImage3 = TI
!OccupationDate = TOD

.Update
.Bookmark = .LastModified
End With

rst.Close


End Sub

 

[tvanstiphout]

On which line, exactly?
You can hit Ctrl+Break when the error occurs, and the debugger should show you the line it happened on.

There is a lot of busy work going on in your code, but that is not the current problem.

 

[CJ_London]

You use save with addnew, not update

 

[tvanstiphout]

You use save with addnew, not update

No, that's not right. See this.

 

[arnelgp]

why are you Declaring an Image?
if it is an Attachment, that is not the way to do it.
you need to open the Attachment field as a Recordset
and on that recordset, add LoadFromFile the image.

 

[The_Doc_Man]

Two observations.

First, for any control that actually HAS a value, the .Value property is the default so never has to be explicitly named.

Second, using the intermediate variables to hold each field is perfectly legal ... but twice the amount of work.

Instead of

...
TSSN = Me.TenantSSN.Value
...
With rst
.AddNew
...
!SSN = TSSN
...

You COULD have done this:

...
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TblTenants")
With rst
.AddNew
!SSN = me.TenantSSN
...

Lots less typing.

Also, when you are editing VBA code, you can see the correct menu bar to set Tools >> Options >> General and set "Break on unhandled error" (a toggle button), which would cause the code to stop and highlight the line it didn't like.[/CODE]

 

[MaryamF]

why are you Declaring an Image?
if it is an Attachment, that is not the way to do it.
you need to open the Attachment field as a Recordset
and on that recordset, add LoadFromFile the image.

Thank you.
Yes, Declaring an Image , was causing the error...
It's still not clear to me , how to add an uploaded image to the table...

 

[theDBguy]

It's still not clear to me , how to add an uploaded image to the table...

As @arnelgp said, if you're using an Attachment field, take a look here.

Work with attachments in DAO

Office VBA reference topic

learn.microsoft.com