troubleshooting Question

VBA and SQL code to add information to two tables

Avatar of Megin
Megin asked on
Microsoft AccessMicrosoft Applications
3 Comments1 Solution1098 ViewsLast Modified:
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

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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros