VBA and SQL code to add information to two tables

I have a piece of code that is supposed to take information entered into a text box, check a table to see if it has been added to that table yet, if not, add it, if so, skip that step. Then the item should be chosen from the list (activity table) should be added to another table (StaffActivities) with information that is in the form.

I have a piece of code that a friend helped me with, which worked in another database. I am having errors with the very last DoCmd statement. It is giving me errors already and I haven't been able to test the code.

I am attaching the database so you can see it in context. The form is frm_act1TO and the button I have written the code behind is btnTest.

Here is the code:

Private Sub btnTest_Click()
Dim strSql As String
Dim db As Database
Dim rst As DAO.Recordset
Dim LngItem As Long


Dim rowc As Interger

With Me.txtactdesc
    For rowc = 0 To .ListCount - 1
    
    strSql = "Select * From Activities Where ActDesc = '" & .Column(0, rowc) & "'"
    
Set rst = db.OpenRecordset(strSql)

    If (rst.BOF And rst.EOF) Then
    
    DoCmd.RunSQL "Insert Into Activities (actdesc) Values ('" & Column(0, rowc) & "'"
    
rst.Close

End If

strSql = "Select ActID from Activities Where ActDesc = '" & .Column(0, rowc) & "'"

Set rst = db.OpenRecordset(strSql)

DoCmd.RunSQL "Insert Into StaffActivities (ActDate, ActID, STOID, TOID)"
                Values "(actDate, '"rst.Fields(0).value &"'), cmbsto, cmbTO"
                
                
           Next rowc
           
End With
                
                  
    
End Sub

Open in new window


The other thing I have done is not include the StaffID field in the list of fields from the StaffActivity table. I think that I may take that field out, as it seems redundant and may be very difficult for me to get programed into this whole thing.

I am also concerned about using SQL code in this. I have been going through and switching a lot of the code behind these forms to strickly VBA. I have heard that it isn't good to use both or switch between the two.

Thank you for any help or advice you can give!
For-Use-on-Utter-Access.accdb
MeginAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Gustav BrockCIOCommented:
Skip all the SQL stuff and use DAO.
This is how:
Private Sub btnTest_Click()

    Dim db      As DAO.Database
    Dim rst     As DAO.Recordset
    Dim strSql  As String
    Dim varDesc As Variant
    Dim lngID   As Long

    varDesc = Me.txtactdesc.Value
    
    strSql = "Select * From Activities Where ActDesc = '" & varDesc & "'"
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSql)
    
    If rst.RecordCount = 0 Then
        rst.AddNew
            rst!ActDesc.Value = varDesc
            rst!ActTypeID.Value = Me!lsttype
            rst!STOID.Value = Me!cmbSTO.Value
            rst!TOID.Value = Me!cmbTO.Value
        rst.Update
        rst.Bookmark = rst.LastModified
    End If
    lngID = rst!ActID.Value

    strSql = "Select Top 1 * From StaffActivities"
    Set rst = db.OpenRecordset(strSql)
    
    rst.AddNew
        rst!ActDate.Value = Nz(Me!ActDate.Value, Date)
        rst!ActID.Value = lngID
        rst!STOID.Value = Me!cmbSTO.Value
        rst!TOID.Value = Me!cmbTO.Value
        rst!StaffID.Value = Me!cmbStaff.Value
    rst.Update
    rst.Close
    
    Set rst = Nothing
    Set db = Nothing
                
End Sub

Open in new window

You will need some error handling for missing entries like staff.

/gustav
NotFromUtterAccess.accdb
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
MeginAuthor Commented:
Holy  Cow!!!!  This totally works!!!!!

I don't know how to thank you enough. I have been working on this forever. I am soooo excited. I wish I could give you 10,000 points for answering this!
0
Gustav BrockCIOCommented:
That's very kind of you. Enjoy!

/gustav
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.