updating data in subforms

[zhelyan]

Hi,
I know this has been discussed many times before but I am a beginner and can't figure out what is wrong.
Have a look at the attached database. I have made a quick draft of the form I need.
I need to be able to add/remove and edit the categories and skills names for each company in the 2 subforms /skill and category/

I do not know if this is the way to do it nor I pretend I have set up the tables and relations properly.

Any help will be appreciated

----
had the wrong version uploaded, just attached the correct one

 

[sbooth]

zhelyan,
I am not totally sure what you are trying to do, but see the attached db and let me know if this is what you are after.

I made a few changes to your tables. I added pk's to table_has_skill and table_in_category. Also, you had fields where you were doing your lookup at the table level. It is not a good idea to do this. So, I changed those fields.

On the subforms, I created the 2 subforms. I used the combo box wizard to create the combo boxes and I stored the value in the union table. I deleted the old subforms and added the new subforms linking them with the company pk.

 

[zhelyan]

zhelyan,
I am not totally sure what you are trying to do, but see the attached db and let me know if this is what you are after.

I made a few changes to your tables. I added pk's to table_has_skill and table_in_category. Also, you had fields wher
e you were doing your lookup at the table level. It is not a good idea to do this. So, I changed those fields.

On the subforms, I created the 2 subforms. I used the combo box wizard to create the combo boxes and I stored the value in the union table. I deleted the old subforms and added the new subforms linking them with the company pk.

Thank you for the reply.
What I am trying to do is to be able to modify the content in the 2 sub forms on the fly - this is - to add, remove and edit the values from within the main form.
This though proved to be error prone.
I have attached what I have come up so far.

I did have a few mistakes in the DB I posted in my previus post.

 

[sbooth]

The form needs to be prettied up, but is the form in the db that I attached what you are wanting?

 

[zhelyan]

The form needs to be prettied up, but is the form in the db that I attached what you are wanting?

pretty much yes.
But I also want to be able to add NEW /not existing in the tables/ categories and skills directly from within the main form - without having to edit the tables directy as I am doing now with the 2 buttons at the bottom of each subform.

 

[sbooth]

Okay, the form does allow you to add to either categories or skills. Try going to the last line in either subform and either start typing or select an item from the drop down. Then, navigate to another company and come back. It should be there. If you look in the table, it will have added the item(s) in the table.

 

[wiklendt]

do you mean you want to type in a new, say skill, in the combo of your subform and have the data entered automatically? i do similar things in my databases. here is the code i use for one of them. it goes in the "on not in list" event of the drop-down.

Private Sub Antibiotic_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_Antibiotic_NotInList

Dim intAnswer As Integer
Dim strSQL As String

intAnswer = MsgBox("The antibiotic " & Chr(34) & NewData & _
    Chr(34) & " is not currently listed." & vbCrLf & _
    "Would you like to add it to the list now?" _
    , vbQuestion + vbYesNo, "Specimen Database")
    
If intAnswer = vbYes Then

    strSQL = "INSERT INTO tblAntibiotics([Antibiotic])" & _
             "VALUES ('" & NewData & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "New antibiotic added.", vbInformation, "Specimen Databse"
    Response = acDataErrAdded

Else
    MsgBox "Please choose an antibiotic from the available list." _
            , vbInformation, "Specimen Database"
    Response = acDataErrContinue
End If

Exit_Antibiotic_NotInList:
    Exit Sub

Err_Antibiotic_NotInList:
    MsgBox Err.Description
    Resume Exit_Antibiotic_NotInList

End Sub

the "Chr(34)" produces double-quotation marks.