Link to home
Start Free TrialLog in
Avatar of gbnorton
gbnortonFlag for United States of America

asked on

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
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

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
Yes, ...
Check your spelling carefully, (P5395, PS39S, ...etc)
Also, verify if this should really be "AND" or "OR", ...etc
>> 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
Avatar of gbnorton

ASKER

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
 
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>> 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.
<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.

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.
I meant me.chkR has a value of -1.
Thanks,
Brooks
So ... did you try the test I suggested @ http:#a36913397 ?

mx
< chkR has a value of -1. >

What about Me.cboPart_Number?
Yes.  Putting the ![R] = True with the other assignment statements worked.  It is inside the If not working.
I found it works like this:
            If Me.cboPart_Number = "PS395" And Me.chkR = True Then
                ![R] = Me.chkR
            End If
Pretty weird.  Try this:

If Trim(Me.cboPart_Number) = "PS395" And CBool(Me.chkR) = True Then
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.
Also, have you refreshed the link to your table as I suggested earlier?
Yes ... are you 101% positive that your table does *not* contain True or -1 ?

mx
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
Strange stuff!