Full Version: Creating a Form to Update Multiple Tables
UtterAccess Forums > Microsoft® Access > Access Forms
fish521
Hello,
Sorry if this has been posted previously but I could not locate a thread on it anywhere. What I have is a 3 tables that are set up like this:
tblCategory
CatID PK
Category
tblActivity
ActivityCode PK
Activity
tblCategoryActivity
CatActID PK
CatID FK
ActivityCode FK
What I need to do is create a form that will allow me to select a Category from a combo box, which will then populate a list box with the corresponding Activities (I have already done all this on the form) but where I can't seem to get past is I would like to be able to select the Activity and delete it if necessary or add a new Activity to the list.
Any help or direction to a thread speaking towards this would be great.
Thanks,
fkegley
The simplest would be to use a text box into which the user can type what he wants to add, then a command button to add the value to the Row Source of the list box via an Append query.
Delete button could also be placed on the form, that would only become active if the user selected something in the list box. Then when the button is clicked, the value could be deleted from the Row Source of the list box via a delete query.
In each case, you would want to then requery the row source of the list box.
Doug Steele
I'll assume that the combo box is named cboCategory and that it has CatID as its first column (whether or not it's visible) and Category as its second column.
imilarly, I'll assume that the list box is named lstActivity, and that it has ActivityCode as its first column, and Activity as its second column.
I'll further assume that CatID is a numeric field, and ActivityCode is a text field.
To delete the activity, you could either double-click on the entry in the list box, or select it and click on a button to delete. In either case, the code would be something like:
CODE
Dim strSQL As String
  
  If MsgBox("Do you want to delete activity " & Me.lstActivity & " from " & _
    "category " & Me.cboCategory & "?", vbYesNo) = vbYes Then
  
    strSQL = "DELETE FROM tblCategoryActivity " & _
      "WHERE CatID = " & Me.cboCategory.Column(0) & _
      " AND ActivityCode ='" & Me.lstActivity.Column(0) & "'"
    CurrentDb.Execute strSQL, dbFailOnError
  
  End If

Adding strictly using a list box is a little trickier. What you could do is have two list boxes: one that has those activities associated with the category, and one that has those activities not associated with the category. You can put buttons between the two list boxes, one to move the selected item from the left hand box to the right hand box, and one to move the selected item from the right hand box to the left hand box. Depending on how you've set the list boxes up, one would be deleting an entry from tblCategoryActivity (as above), which the other would be adding an entry to tblCategoryActivity.
Don't forget to requery the list box/boxes after the code runs.
fish521
Doug,
Otried putting the following code on a command button to go with the 2 list box idea that you gave me. I am getting an error on the last line. Should I be wording this code differently
Private Sub cmdNewActivity_Click()
Dim strSQl As String
If MsgBox("Do you want to add this Activity" & Me.lstActivity & " from " & _
"category" & Me.cboCategory & "?", vbYesNo) = vbYes Then

strSQl = "INSERT INTO tblCategoryActivity" & _
"WHERE CatID = " & Me.cboCategory.Column(0) & _
"And ActivityCode = '" & Me.lstActivity.Column(0) & "'"
CurrentDb.Execute strSQl, dbFailOnError

End If

End Sub
Doug Steele
What error are you getting?
fish521
Syntax error in INSERT INTO statement
Doug Steele
You appear to have removed the spaces I had between tblCategoryActivity and the keyword WHERE and in front of the keyword And.
fish521
I've got those in there, but it still gives the same error and is highlighting that last line
urrentDb.Execute strSQl, dbFailOnError
Doug Steele
Immediately before CurrentDb.Execute strSQl, dbFailOnError, put a line of code
ebug.Print strSQL
Run the code. It will still fail, but once it does, I want you to go to the Immediate window (Ctrl-G) and copy what's printed there.
fish521
This is what it showed
INSERT INTO tblCategoryActivity WHERE CatID = AND ActivityCode = ''
Doug Steele
Okay, it's not picking up anything from cboCategory nor lstActivity.
Are you sure those are the correct names of the controls on the form?
Is something selected in both the combo box and the list box?
Is the MultiSelect property of the list box lstActivity set to something other than None?
fish521
Those are the correct names on the form, I'm selecting one option from the combo box and then one from the list box. The MultiSelect property is set to None. I've attached the database with the form, tables and query that I am using for this. Maybe there is something there that I am just not catching.
Thanks for all of your help
Doug Steele
Sorry, I don't download other people's databases.
oes the message box that prompts to ask whether to proceed have the correct information in it?
Are you sure that Column(0) is the correct column to use from the two controls?
fish521
The message box states this
o you want to add this Activity2 to Category17?
The 2 is the correct Activity Code for that activity and the Category ID of 17 is correct also.
Doug Steele
In that case, try:
!--c1-->
CODE
Private Sub cmdNewActivity_Click()
Dim strSQl As String
  
  If MsgBox("Do you want to add this Activity" & Me.lstActivity & " from " & _
    "category" & Me.cboCategory & "?", vbYesNo) = vbYes Then
  
    strSQl = "INSERT INTO tblCategoryActivity" & _
      "WHERE CatID = " & Me.cboCategory & _
      " AND ActivityCode = '" & Me.lstActivity & "'"
    CurrentDb.Execute strSQl, dbFailOnError
  
  End If
  
End Sub

However, is the ActivityCode actually a text field? If it's numeric, you need
CODE
Private Sub cmdNewActivity_Click()
Dim strSQl As String
  
  If MsgBox("Do you want to add this Activity" & Me.lstActivity & " from " & _
    "category" & Me.cboCategory & "?", vbYesNo) = vbYes Then
  
    strSQl = "INSERT INTO tblCategoryActivity" & _
      "WHERE CatID = " & Me.cboCategory & _
      " AND ActivityCode = " & Me.lstActivity
    CurrentDb.Execute strSQl, dbFailOnError
  
  End If
  
End Sub
fish521
Do I need to add the primary key information into this code, also do I need to specify the locations of where the CatID and ActivityCode are going to be placed in tblCategoryActivity such as
strSQl = "INSERT INTO tblCategoryActivity (CatActID, CatID, ActivityCode)" & _
" WHERE CatID = " & Me.cboCategory & _
" AND ActivityCode = " & Me.lstActivity
" AND CatActID = " & AutoNumber
For something like this
fish521
Oh and sorry the ActivityCode is numeric
Doug Steele
I don't know what I was thinking. That's not a valid INSERT INTO statement I gave you!
!--c1-->
CODE
Private Sub cmdNewActivity_Click()
Dim strSQl As String
  
  If MsgBox("Do you want to add this Activity " & Me.lstActivity & " from " & _
    "category " & Me.cboCategory & "?", vbYesNo) = vbYes Then
  
    strSQl = "INSERT INTO tblCategoryActivity (CatID, ActivityCode) " & _
       "VALUES (" & Me.cboCategory & ", " & Me.lstActivity & ")"
    CurrentDb.Execute strSQl, dbFailOnError
  End If
  
End Sub

You do not include Autonumber fields in the list of fields, nor in the list of values.
Sorry about the confusion (but then that wasn't the only cause for the earlier problems)
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.