MS Access Need help with if then statement

Problem:
I receive the following error on form open:
You can't assign a value to this object (Me.Status)

Field types:
Me.ti_cancel = True/False field
Me.Status = Value list


Code: (Located on form open)
Private Sub Form_Open(Cancel As Integer)
If (Me.ti_cancel) = False Then
        Me.Status = "Cancelled"
       
          End If
End Sub

Open in new window

DJPr0Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try using the load event

Private Sub Form_load()
If (Me.ti_cancel) = False Then
        Me.Status.value = "Cancelled"
       
          End If
End Sub
0
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

capricorn1 has your answer here....

As a note, you may want to add this same code to other events of the form (or other controls) in case the ti_cancel is changed on the fly.

JeffCoachman
0
DJPr0Author Commented:
No errors and not working after moving to "on load"

Additional info:

This form consists of 2 tables with a relationship - each field (Me.ti_cancel & Me.Status) are located in different tables.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Rey Obrero (Capricorn1)Commented:
are this two controls Me.ti_cancel & Me.Status bound ?

what is the recordsource of the form?

does the recordsource includes both tables?
0
DJPr0Author Commented:
are this two controls Me.ti_cancel & Me.Status bound ?
Me.ti_cancel yes, to a SQL server
Me.Status not bound, it is a value list with 4 options including "Cancelled"

what is the recordsource of the form?
One SQL table and one Access table with a relationship

does the recordsource includes both tables? Yes
Record source:
SELECT dbo_dd_taskinfo.*, dbo_dd_taskinfo.ti_type, dbo_dd_taskinfo.ti_class, Matrix_SQL.Status, Matrix_SQL.[P 703], Matrix_SQL.[P 704], Matrix_SQL.[P 705], Matrix_SQL.[P 706], Matrix_SQL.[T 802], Matrix_SQL.[T 803], Matrix_SQL.[T 804], Matrix_SQL.[T 805], Matrix_SQL.[T 806], Matrix_SQL.[T 807], Matrix_SQL.[T 800], Matrix_SQL.[T 801] FROM dbo_dd_taskinfo LEFT JOIN Matrix_SQL ON dbo_dd_taskinfo.ti_id=Matrix_SQL.TaskInfoID WHERE (((dbo_dd_taskinfo.ti_type)="P") AND ((dbo_dd_taskinfo.ti_class)="703"));


Just noticed the Yes/No field has this for data:
"0" and "-1"
The field ti_cancel is from a SQL server - and I can't convert to text on the Access linked table.
0
Rey Obrero (Capricorn1)Commented:
what is ".Status" a combo or listbox ?
what are the values listed, post them in order..

also try this

Private Sub Form_load()
If (Me.ti_cancel) = False Then

msgbox "ti_cancel is " & Me.ti_cancel
        Me.Status.value = "Cancelled"
 
else
msgbox "ti_cancel is " & Me.ti_cancel
     
          End If
End Sub


try that and see what message will be displayed..
\

.
0
DJPr0Author Commented:
I receive:
ti_cancel is 0

Sorry, I think the problem is, I'm displaying this form in datasheet view which displays all the records - your code above does work for the first record.

How can adjust the code to loop threw all the records on form load?

Tried the code below and received the following error:
Cannot update. Database or object is read-only.

Private Sub Form_Load()
          
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM dbo_dd_taskinfo,Matrix_SQL")

rst.MoveLast
rst.MoveFirst

Do Until rst.EOF
  rst.Edit
  If ti_cancel = True Then
    Status.Value = "Cancelled"
  
  End If
   
Loop

Set rst = Nothing
End Sub

Open in new window

0
Rey Obrero (Capricorn1)Commented:
place this codes in the load event of the form

with me.recordset
       .movefirst
       do until .eof
          if !ti_cancel=0 then me.Status.value="Cancelled"
       .movenext
     loop
     .movefirst
end with
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DJPr0Author Commented:
Worked great after minor adjustments:

Private Sub Form_Load()
         
       With Me.Recordset
       .MoveFirst
       Do Until .EOF
          If Me.ti_cancel = True Then Me.Status.Value = "Cancelled"
       .MoveNext
     Loop
     .MoveFirst
End With
End Sub
0
DJPr0Author Commented:
Thanks capricorn1!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.