Set Access Yes/No field record

Hi,
I use this code to create a set of records:
With CurrentDb.OpenRecordset("Part_Serial_Number")
        For i = 1 To Me.txtQuantity
            .AddNew

            ![Part_No] = Me.cboPart_Number
            ![Label_Date] = Me.txtDate_Created
            ![Serial_No] = Me.txtSerial_Number_Start
            ![Operator] = Me.cboOperator
            ![Work_Order] = Me.txtWork_Order
            ![Date_Code] = Me.txtDate_Code
            ![Notes] = Me.txtNotes
            If Me.cboPart_Number = "PS395" And Me.chkR = True Then
                ![R] = True
            End If
            .Update
            Me.txtSerial_Number_Start = Me.txtSerial_Number_Start + 1
        Next i
    End With

It all works except the IF statement.
The line:      ![R] = True
is executed, but the record is not updated in the table.

The field "R" is a Yes/No field.

Is there a problem with naming a field with a single letter as I have here?  "R"

Thanks,
Brooks
gbnortonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
That would not be the problem. Something else is going on.

Are you positive this expression ..

If Me.cboPart_Number = "PS395" And Me.chkR = True

actually evaluated to True?

mx
0
Jeffrey CoachmanMIS LiasonCommented:
Yes, ...
Check your spelling carefully, (P5395, PS39S, ...etc)
Also, verify if this should really be "AND" or "OR", ...etc
0
mbizupCommented:
>> the record is not updated in the table.

Are the other fields getting their values (ie: only "R" is not getting updated), or is the the entire record failing to get added?

>>The line:      ![R] = True
>>is executed

How are you determining that it is executed?


>> Me.cboPart_Number = "PS395"

What is the rowsource of your combo box?  If it contains multiple fields, such as including a Primary Key ID field, you may need to specify a column number (these start at zero).  Just as an example, this references the second column:

If Me.cboPart_Number.Column(1) = "PS395" And Me.chkR = True Then
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gbnortonAuthor Commented:
MX,
I used Stop in the code in the IF statement to verify it is being evaluated:
If Me.cboPart_Number = "PS395" And Me.chkR = True Then
                ![R] = True
                 Stop
End If

Boag2000,
It should be AND.

mbizup,
All of the other fields are being updated.  Only one field in the combo box.

Thanks,
Brooks
 
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
As a test only ... what happens if you do this:

            ![Notes] = Me.txtNotes
            ![R] = True
            .Update
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
In the VBA Editor set a breakpoint on this line:

>>             If Me.cboPart_Number = "PS395" And Me.chkR = True Then

You can do this in the VBA editor by clicking in the far left hand side on that line (a bold red dot will appear in the left margin if the break point is set).

That will make the code pause at that line.

When the code stops and takes you into the editor, hover the mouse over cboPartNumber and then over chkR.

Are the values as you expect them to be?

Also, if you don't have it already,place the following line of code at the top of your module, immediately below "Option Compare Database":

Option Explicit

Open in new window


That will ensure that you have all variables declared (which if they are not, weird stuff like this can happen)


0
mbizupCommented:
>> weird stuff like this can happen..

Clarifying, the weird stuff I'm thinking of is generally caused by typos in variable names, which Option Explicit provides a sanity check against.
0
mbizupCommented:
<I used Stop in the code in the IF statement to verify it is being evaluated>

If the program flow really is making it to the inside of your IF block and the R field still is not being updated, I'd be curious to see what the rest of your code looks like - particularly any error handling.

If this is a recently added field, try relinking your table.

0
gbnortonAuthor Commented:
I moved the ![R] = True out of the If statement.  It works there.  So I set the breakpoint where mbizup suggested.  chkR has a value of -1.
0
gbnortonAuthor Commented:
I meant me.chkR has a value of -1.
Thanks,
Brooks
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
So ... did you try the test I suggested @ http:#a36913397 ?

mx
0
mbizupCommented:
< chkR has a value of -1. >

What about Me.cboPart_Number?
0
gbnortonAuthor Commented:
Yes.  Putting the ![R] = True with the other assignment statements worked.  It is inside the If not working.
0
gbnortonAuthor Commented:
I found it works like this:
            If Me.cboPart_Number = "PS395" And Me.chkR = True Then
                ![R] = Me.chkR
            End If
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Pretty weird.  Try this:

If Trim(Me.cboPart_Number) = "PS395" And CBool(Me.chkR) = True Then
0
mbizupCommented:
Check the value of Me.cboPart_Number at your breakpoint, and also add this messagebox right after that line (it will make any leading/trailing spaces apparent if they are there):

msgbox "*" & Me.cboPart_Number & "*"


And if you havent already, remove the Stop command from the If-then block.
0
mbizupCommented:
Also, have you refreshed the link to your table as I suggested earlier?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yes ... are you 101% positive that your table does *not* contain True or -1 ?

mx
0
gbnortonAuthor Commented:
This morning it works as originally written.  I was using the breakpoints as suggested to verify the If statement.   The Me.cboPart_Number value was correct as was the Me.chkR.  The If statement executed properly.  The [R] field was updated correctly.  
My next step was to refresh the link, but that was not done yet.
Honestly, I don't know why it started working.  But thanks for helping work on it.  And as usual, I learned tips for troubleshooting.
Brooks
0
mbizupCommented:
Strange stuff!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are welcome.

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.