Updating fields with VBA code

[Guest]

In a form I have a table field that I want to set programatically from the
Current event depending on the state of a button that I use as a toggle. The
code line goes like this:

If <bla..bla> Then
Me.approved.Value = 1
Else
Me.approved.Value = 0
End If

The 'approved' field is of datatype 'bit'. This starts some funny behavor in
Access (2003). Either I get an error message telling that the value I try to
enter is of wrong lenght/value or the field is not updated at all.

Can anyone give me some advice on this?

/Leif S.

 

[Brendan Reynolds]

Try changing the 1 to -1. Yes, I know a bit field won't actually store a
negative value, but JET sees the bit field as a Boolean ('Yes/No') field.

 

[Guest]

Brendan;
Thank U for responding. I forgot to mention that my Access app is running
against an MS SQL 2000 server database. I tried the -1 option but it
generated a data type error.

/Leif S.

 

[Brendan Reynolds]

I guessed it was a SQL Server table, Leif, as JET doesn't have a bit data
type. But is this an MDB with ODBC-linked tables, or is it an ADP?

In an MDB, I just tried a test with the 'Products' table from the SQL Server
version of Northwind, which has a bit field called 'Discontinued'. Either 1
or -1 work for me. Here's my test code ...

Private Sub Command20_Click()

' If Me.Discontinued.Value = 0 Then
' Me.Discontinued.Value = -1
' Else
' Me.Discontinued.Value = 0
' End If

If Me.Discontinued.Value = 0 Then
Me.Discontinued.Value = 1
Else
Me.Discontinued.Value = 0
End If

End Sub

Are you sure that it is when you attempt to update the value that the error
is raised? Could it possibly be the part of your code that tests the
condition that is raising the error?

If <bla..bla> Then '<----- could the error lie here?
Me.approved.Value = 1
Else
Me.approved.Value = 0
End If