Save data into join table from two main tables

[Nautlilus]

Hi, This must be a very common scenario for a lot of situations but i cant seem to find a simple solution for it.
I have two tables with item data and status text and then a join table with just the primary keys from these two tables.

Table1 (PK1, Item, item_desc, Item_location, Item_Properties, .....)
Table2 (PK2, StatusText)
JoinTable (PK1, PK2)

I have a form where users can add new item and at this point, i want to show them theavilable status and on selecting the correct status, a new row should be inserted into JoinTable with PK1 of new item and PK2 of status selected.. What the best way of achieving this?

 

[LittleSmudge]

I do it with a combo box and a recordset


On the form have a combo cboSelectStatus that displays all of the available status

Then in the cboSelectStatus.AfterUpdate

Private Sub cboSelectStatus.AfterUpdate()
Dim rst As ADODB.Recordset
etc..
etc..


rst.OPEN "SELECT * FROM JOINTABLE " _
       & "WHERE PK1 = " & txtPK1 & " " _
       & "AND PK2 = " & cboSelectStatus

If rst.EOF Then
    rst.AddNew
    rst!PK1 = txtPK1
    rst!PK2 = cboSelectStatus
    rst.Update
Else
    If MsgBox("That link already exists. Do you want to remove it ?",vbYesNo) = vbYes Then
        rst.Delete
        rst.Update
End If
rst.Close
Set rst = Nothing
End Sub

Where txtPK1 is a text box control on the main form that is bound to the PK1 field in Table1


You can 'display' the results of these links in a read only subForm that is bound to a RecordSource of

"SELECT Table2.StatusText FROM JoinTable INNER JOIN Table2 ON JoinTable.PK2 = Table2.PK2"

If you do you will need to add a line right at the end of the combo AfterUpdate code above to the effect of

Me!subFormControlName.Form.Requery

so that the form's display changes every time the combo box causes the JoinTable's contents to change.



'ope-that-'elps.








G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Nautlilus]

Thanks.. I am doing something similar.. Actually i ended up using a subform where PK1 and PK2 are combo boxes with PK2 as lookup to the Statustext from Table2. The subform is joined to master fields on PK1=PK1. I can use the dropdown in subform to change status text and it saves the new combination of PK1 and PK2 into JOINTABLE. Actually alogn with statustext, there is one more field in JOINTABLE that i want to store, shipping status which is just two std text fields. I am using that also as a combo box on subform.

But i liked your solution better. I will give it a try.