How write values to a 2nd table

I have a form bound to a specific table but when a checkbox is checked (after update event), and want certain values to be written to a 2nd table.  Here is my code but it is not working.  Can someone offer a suggestion?

Private Sub chkbx10WindEp_AfterUpdate()

    If Me.chkbx10WindEp = True Then
        Me.txt10WindEpQty = Me.txtQty
        Me.txt10WindEp = "#10 Window Envelope"
       
        Dim rs As dao.Recordset
        Set rs = CurrentDb.OpenRecordset("tblEnvelopeInventory")
        rs.addnew
        rs!JobN = Me.txtJobN
        rs!CompanyName = Me.txtCompanyName
        rs!EnvelopeStyle = Me.txt10WindEp
        rs!Qty = "-" & Me.txtQty
        rs!TranxDate = Date
        rs.update
        Set rs = Nothing
    End If

End Sub
SteveL13Asked:
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.

Rey Obrero (Capricorn1)Commented:
what is not working? be specific
0
SteveL13Author Commented:
The values are not being written to tblEnvelopeInventory.  Sorry I didn't clarify.
0
Rey Obrero (Capricorn1)Commented:
try this codes, i commented some  lines for testing
open table after running the codes

btw, do you have an autonumber field in that table?

Private Sub chkbx10WindEp_AfterUpdate()

    If Me.chkbx10WindEp = True Then
        Me.txt10WindEpQty = Me.txtQty
        Me.txt10WindEp = "#10 Window Envelope"
       
        Dim rs As dao.Recordset
        Set rs = CurrentDb.OpenRecordset("tblEnvelopeInventory")
        rs.addnew
        rs!JobN = Me.txtJobN
        rs!CompanyName = Me.txtCompanyName
'        rs!EnvelopeStyle = Me.txt10WindEp
'        rs!Qty = "-" & Me.txtQty
'        rs!TranxDate = Date
        rs.update
        rs.close
        Set rs = Nothing
    End If

End Sub
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.

SteveL13Author Commented:
Yes, there is an autonumber field.  
0
SteveL13Author Commented:
And the code is still not working correctly.  I removed the autonumber field for now.
0
Rey Obrero (Capricorn1)Commented:
< I removed the autonumber field for now.>
do not remove the autonumber field..
what is the name of the autonumber field?
0
SteveL13Author Commented:
I put it back.  The name of the autonumber field is RecID and is the primary key.
0
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the db?
0
SteveL13Author Commented:
I would but its so proprietary I can't.
0
Rey Obrero (Capricorn1)Commented:
ok... try this, see if you get the message


Private Sub chkbx10WindEp_AfterUpdate()

    If Me.chkbx10WindEp = True Then

msgbox "Adding record to tblEnvelopeInventory"


        Me.txt10WindEpQty = Me.txtQty
        Me.txt10WindEp = "#10 Window Envelope"
       
        Dim rs As dao.Recordset
        Set rs = CurrentDb.OpenRecordset("tblEnvelopeInventory")
        rs.addnew
        rs!JobN = Me.txtJobN
        rs!CompanyName = Me.txtCompanyName
'        rs!EnvelopeStyle = Me.txt10WindEp
'        rs!Qty = "-" & Me.txtQty
'        rs!TranxDate = Date
        rs.update
        rs.close
        Set rs = Nothing
    End If

End Sub
0
SteveL13Author Commented:
I am not getting the message box.
0
Rey Obrero (Capricorn1)Commented:
in the design view of the form, select the checkbox then hit F4
select the event tab, select [Event Procedure] in the afterupdate event.
then click on the (...) see if you will be taken to to the codes we are working

test your codes
0
SteveL13Author Commented:
Yes... that took me to the code we've been working on.
0
Rey Obrero (Capricorn1)Commented:
test it now...
0
SteveL13Author Commented:
Not working.
0
Rey Obrero (Capricorn1)Commented:
are you getting the message?
0
Rey Obrero (Capricorn1)Commented:
and make sure that Me.chkbx10WindEp = True
0
Rey Obrero (Capricorn1)Commented:
from your VBA window do a DEBUG > Compile
correct any errors raised

do a compact and repair of the db.
0
SteveL13Author Commented:
I get an error on...      Dim rs As dao.Recordset

User defined type not defined.

????
0
Rey Obrero (Capricorn1)Commented:
tools > references

see if you have referenced Microsoft DAO x.x object library

also look for reference with MISSING prefix
* uncheck this and select the available version of the same library name.
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
SteveL13Author Commented:
Well, that was it.  The Microsoft DAO object library wasn't there.  Thank you!
0
SteveL13Author Commented:
I did not mean to accept my own comment.  Please do not close.
0
SteveL13Author Commented:
Well, that was it.  The Microsoft DAO object library wasn't there.  Thank you!
0
Rey Obrero (Capricorn1)Commented:
you are requesting to accept your own post as the solution to the problem?
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.