D J
asked on
MS Access Need help with if then statement
Problem:
I receive the following error on form open:
Field types:
Me.ti_cancel = True/False field
Me.Status = Value list
Code: (Located on form open)
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
<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
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
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.
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?
what is the recordsource of the form?
does the recordsource includes both tables?
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=Matr ix_SQL.Tas kInfoID 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.
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=Matr
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..
\
.
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..
\
.
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:
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks capricorn1!
Private Sub Form_load()
If (Me.ti_cancel) = False Then
Me.Status.value = "Cancelled"
End If
End Sub