Intersection tables

[RHino302]

In Access, is there any way to do this?

Using only the spreadsheet view, no forms to input data.

You got two tables, that donate their primary key to an Intersection table to form the M:N relationship. I've got the Int table to use combo boxes. But I'd also like a way to input data into just the Int table, and have it automatically add a new record to the other tables. Sometimes I find it too cumbersome to use forms and sub-forms. It's even faster for me just to keep the 3 tables open and input data in one, the other, then make the selection in the Int table. But I'd like to enter a record in the Int table, and have it use the surrogate keys as primary keys for new records in the other tables if appliccable. And other fields would be null of course.

 

[dawnd3]

Hi there, I am not quite understanding your description of your table set up, but I think I can still help. If you create a form, using these tables, you can change the forms "Default view"(in the properties) to datasheet. This will look JUST like a table, however you can have all the fields you need (assuming tables are linked)including your combo boxes (the ones made in Form design as well as table design)Does this help? Dawn

http://www.calliopeconsulting.net

 

[RHino302]

Not really, but thanks for your reply. I'll try to explain it better. As is, say you have a table for student and class. Each student can have more than one class, each class more than one student. So you would have to, in essence, to add a new student, create a student in the student table, and create a class in the class table. (if the class did not exist already). Then you could select them both in the Intersection table to assign a class to a student. I would like a way to just put the data in the Intersection table. Put a student and a class in the same time and have this table create the data in the student and class tables as well.

 

[dawnd3]

Ok I think I got ya....
If you follow my above advice for the form, and then in the combo box you would use code in the "NotInList" event to add data that the user types in to the other two tables if it isn't in the table already. I actually got this code from this site fairly recently, but can't remember who to give credit too... I have altered it for my database as you will for yours, but it works beautifully. All you have to do here, is change some of the field names to your field names. This code assumes that the table is autonumbered. I altered the code from one that would allow you to add a new ID. If you need that code, try doing a search on this site, it is only a couple of weeks back. Dawn

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

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String

On Error GoTo Err_Combo26_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new task.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a task, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new task, open a recordset
' using the task table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tblTaskList", dbOpenDynaset)

' Create a new record.
Rs.AddNew

' Assign the NewData argument to the strtask field.
Rs![strTask] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded

End If

Exit_Combo26_NotInList:
Exit Sub
Err_Combo26_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue


End Sub

http://www.calliopeconsulting.net

 

[dawnd3]

by the way, this is for Access 2000, if you have 97, you will have to ask some of the experts on this site to change the code a bit. Dawn

http://www.calliopeconsulting.net

 

[RHino302]

Thanks. I do have 2000, but might be trying something similar on 97 also. I will try to get both going.

 

[dawnd3]

Great! Let me know how it goes. Dawn

http://www.calliopeconsulting.net