Still probs with my code

B

Biba

Hi,
Can someone pls tell me what's wrong with this code?

Private Sub Frame41_AfterUpdate()
If Me.Frame41 = True Then
Me.DATE_IN = Format(Date, "yyyymmdd")
Else
Me.DATE_IN = vbNullString
End If
End Sub

DATE_IN is a nvarchar field and the option group (Frame41) has two radials,
yes = 1 and no = 0.
Access' VB stops on DATE_IN.

TIA,
Biba
 
B

BruceM

For one thing an option groups contains an integer value; I do not think it
can be evaluated to True or False. For that you need a Yes/No field. If
the value of the True option is 1 and of the False option 2 you could try:
If Me.Frame41 = 1 Then
etc.
By the way, the values are -1 and 0 for True and False in a Yes/No field.
Option values, on the other hand, are up to you. I used the values 1 and 2
by way of example.
For another thing, why not just make DATE_IN a date field? I'm not sure
what you mean by a nvarchar field.
 
B

Biba

I cannot change the type of field because the table was designed by someone
else. The error msg is: Compilation error. Method or data member not found.

Thank you.
 
D

Douglas J. Steele

The error message implies that the field DATE_IN isn't in the underlying
recordset of the form, or else its name is something else.
 
B

Biba

This simple code is driving me nuts.

In a continuous form I have the following shown fields:

Account_No

Cost

DATE_IN

Then I use an option set with two radials Yes and No.

The idea is that when the Y is marked, a 1 is written in an int field PAID
and today's date (yyyymmdd) is written in the DATE_IN field.

Perhaps there's a better way to do it?
 
D

Douglas J. Steele

So if DATE_IN is indeed the correct name of the field, the other possibility
is that there's a problem with your References collection, and so one (or
both) of Date or Format isn't being properly recognized.

Does it work if you change the line to

Me.DATE_IN = VBA.Format(VBA.Date, "yyyymmdd")

If so, then it's definitely a problem with your References.

Open any code module, then select Tools | References from the menu bar.
Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile, under the Debug menu), go
back in and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)
 
B

BruceM

I wonder about the point I raised about evaluating an option group value
(Frame41) to True. I realize this doesn't address the apparent References
problem, but I would think it is going to be an issue at some point.
 
D

Douglas J. Steele

Yes and no.

While it may be a misuse of an option group, to Access, 0 is False, non-zero
is True, so that shouldn't cause any problems.

Of course, I don't understand why he's using an option group when a check
box would suffice.
 
B

BruceM

Thanks for clearing that up.

Douglas J. Steele said:
Yes and no.

While it may be a misuse of an option group, to Access, 0 is False,
non-zero is True, so that shouldn't cause any problems.

Of course, I don't understand why he's using an option group when a check
box would suffice.
Click to expand...
 
B

Biba

A ckbox will store a -1 while I need to be a 1. Will try what you suggested
and let you know, Thank you.
 
D

Douglas J. Steele

Why do you need 1? Are you using SQL Server, which stores Booleans as a bit
field (and hence only 0 and 1)? If so, I thought Access took care of that
translation for you automatically.
 
B

Biba

Yes, I'm using SQL2k.

Douglas J. Steele said:
Why do you need 1? Are you using SQL Server, which stores Booleans as a
bit field (and hence only 0 and 1)? If so, I thought Access took care of
that translation for you automatically.
Click to expand...
 
D

Douglas J. Steele

So what happens if you use a checkbox? (Sorry, it's been too long since I've
experimented, and I don't have a SQL Server database handy...)
 
B

Biba

a -1 is stored.

Douglas J. Steele said:
So what happens if you use a checkbox? (Sorry, it's been too long since
I've experimented, and I don't have a SQL Server database handy...)
Click to expand...
 
D

Douglas J. Steele

Okay.

What about the other issue? Does your code work after the instructions I
gave you?
 
B

Biba

No but I just fixed it. I erased the form and created a new one and worked.
Maybe some happened with the VB which made no sense. Thank you much for
helping.
 
Top