Value saved is default not user input

[Paul B.]

Can someone please tell me what is wrong with the following:


Private Sub Disposition_AfterUpdate( )

If [Disposition] = "Out of Service" Then

[VehStatus] = "Not In Service"

Else

[VehStatus] = "In Service"

End If

DoCmd.RunSQL "UPDATE tblVehicles SET tblVehicles.VehStatus _
= [VehStatus]" & "WHERE ...criteria...."

End Sub

My code actually has other DoCmd.RunSQL lines in it that work fine, so I
know that part is okay.

My concern is that the default value of the textbox [VehStatus] is using
DLast to show the current value in the table and that the code above is not
holding the value set by the IF statements.

How would I fix this?

Cheers

 

[MacDermott]

Your code is updating the value which is displayed on your form.
This value is not saved back to the underlying table until the record is
saved.
This can be done in many ways; here are 2 ways to do it programmatically:
Me.Dirty=False
DoCmd.RunCmd acCmdSaveRecord

HTH

 

[Paul B.]

The code is updating the value of the form, that is fine, but when I run the
SQL Update, it does not take the value from the field as set by the IF
statement, but rather the default value.

The form is looking at the last record for the Vehicle as the default value,
when the user changes the disposition, the IF statement updates the field
[VehStatus].

Cheers

Your code is updating the value which is displayed on your form.
This value is not saved back to the underlying table until the record is
saved.
This can be done in many ways; here are 2 ways to do it programmatically:
Me.Dirty=False
DoCmd.RunCmd acCmdSaveRecord

HTH

Can someone please tell me what is wrong with the following:


Private Sub Disposition_AfterUpdate( )

If [Disposition] = "Out of Service" Then

[VehStatus] = "Not In Service"

Else

[VehStatus] = "In Service"

End If

DoCmd.RunSQL "UPDATE tblVehicles SET tblVehicles.VehStatus _
= [VehStatus]" & "WHERE ...criteria...."

End Sub

My code actually has other DoCmd.RunSQL lines in it that work fine, so I
know that part is okay.

My concern is that the default value of the textbox [VehStatus] is using
DLast to show the current value in the table and that the code above is not
holding the value set by the IF statements.

How would I fix this?

Cheers

 

[MacDermott]

Perhaps your SQL should look like this:

DoCmd.RunSQL "UPDATE tblVehicles SET tblVehicles.VehStatus _
= '" & [VehStatus] & "' WHERE ...criteria...."

The code is updating the value of the form, that is fine, but when I run the
SQL Update, it does not take the value from the field as set by the IF
statement, but rather the default value.

The form is looking at the last record for the Vehicle as the default value,
when the user changes the disposition, the IF statement updates the field
[VehStatus].

Cheers

Your code is updating the value which is displayed on your form.
This value is not saved back to the underlying table until the record is
saved.
This can be done in many ways; here are 2 ways to do it programmatically:
Me.Dirty=False
DoCmd.RunCmd acCmdSaveRecord

HTH

Can someone please tell me what is wrong with the following:


Private Sub Disposition_AfterUpdate( )

If [Disposition] = "Out of Service" Then

[VehStatus] = "Not In Service"

Else

[VehStatus] = "In Service"

End If

DoCmd.RunSQL "UPDATE tblVehicles SET tblVehicles.VehStatus _
= [VehStatus]" & "WHERE ...criteria...."

End Sub

My code actually has other DoCmd.RunSQL lines in it that work fine, so I
know that part is okay.

My concern is that the default value of the textbox [VehStatus] is using
DLast to show the current value in the table and that the code above

is
not

holding the value set by the IF statements.

How would I fix this?

Cheers

 

[Marshall Barton]

Ahh, Turtle's post finally caught my eye ;-)

I don't know if this the core problem, but both you guys are
leaving the line continuation inside the quotes.

DoCmd.RunSQL "UPDATE tblVehicles " _
& " SET tblVehicles.VehStatus = '" & Me.VehStatus _
& "' WHERE ...criteria...."
--
Marsh
MVP [MS Access]

Perhaps your SQL should look like this:

DoCmd.RunSQL "UPDATE tblVehicles SET tblVehicles.VehStatus _
= '" & [VehStatus] & "' WHERE ...criteria...."

The code is updating the value of the form, that is fine, but when I run the
SQL Update, it does not take the value from the field as set by the IF
statement, but rather the default value.

The form is looking at the last record for the Vehicle as the default value,
when the user changes the disposition, the IF statement updates the field
[VehStatus].

Can someone please tell me what is wrong with the following:

Private Sub Disposition_AfterUpdate( )
If [Disposition] = "Out of Service" Then
[VehStatus] = "Not In Service"
Else
[VehStatus] = "In Service"
End If

DoCmd.RunSQL "UPDATE tblVehicles SET tblVehicles.VehStatus _
= [VehStatus]" & "WHERE ...criteria...."

 

[Paul B.]

Thank you!!!

For the life of me, I'll never figure out these quotes, without your help
and this forum, I would be forever in the land of lost quotes!

Cheers

Ahh, Turtle's post finally caught my eye ;-)

I don't know if this the core problem, but both you guys are
leaving the line continuation inside the quotes.

DoCmd.RunSQL "UPDATE tblVehicles " _
& " SET tblVehicles.VehStatus = '" & Me.VehStatus _
& "' WHERE ...criteria...."
--
Marsh
MVP [MS Access]

Perhaps your SQL should look like this:

DoCmd.RunSQL "UPDATE tblVehicles SET tblVehicles.VehStatus _
= '" & [VehStatus] & "' WHERE ...criteria...."

The code is updating the value of the form, that is fine, but when I run the
SQL Update, it does not take the value from the field as set by the IF
statement, but rather the default value.

The form is looking at the last record for the Vehicle as the default value,
when the user changes the disposition, the IF statement updates the field
[VehStatus].

Can someone please tell me what is wrong with the following:

Private Sub Disposition_AfterUpdate( )
If [Disposition] = "Out of Service" Then
[VehStatus] = "Not In Service"
Else
[VehStatus] = "In Service"
End If

DoCmd.RunSQL "UPDATE tblVehicles SET tblVehicles.VehStatus _
= [VehStatus]" & "WHERE ...criteria...."