[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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
0
gbnorton
Asked:
gbnorton
  • 8
  • 6
  • 6
  • +1
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 CoachmanCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 MVP, Access and Data Platform)Commented:
As a test only ... what happens if you do this:

            ![Notes] = Me.txtNotes
            ![R] = True
            .Update
0
 
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
You are welcome.

mx
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 8
  • 6
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now