Link to home
Start Free TrialLog in
Avatar of D J
D JFlag for United States of America

asked on

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

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try using the load event

Private Sub Form_load()
If (Me.ti_cancel) = False Then
        Me.Status.value = "Cancelled"
       
          End If
End Sub
<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
Avatar of D J

ASKER

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.
are this two controls Me.ti_cancel & Me.Status bound ?

what is the recordsource of the form?

does the recordsource includes both tables?
Avatar of D J

ASKER

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.
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..
\

.
Avatar of D J

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of D J

ASKER

Thanks capricorn1!