Richard1941
Registered User.
- Local time
- Today, 00:37
- Joined
- Oct 29, 2012
- Messages
- 34
I have started a new project where I have tables of people, organization, and roles, as well as one that is supposed to contain the relationships among them. In all four tables there is an AutoNumber ID field as the primary key. The fourth table, the relationship table, has as foreign keys, the primary keys from the first three. My basic question is: Is there an easy way to do this?
More specifically, what I have tried that does not work is the following:
I have created a form with a combo box for each of the three main tables. The intent is for the user to select a person, an organization, and a role, and then click on a button control that will create the relationship in the relationship table. What I am trying to do in the code is, using an AfterUpdate event procedure for each of the combo boxes, open the appropriate table as a recordset, find the selected record, and set a form-local variable to the value of the ID field for that record. So far, I can't get past the first combo box. Here is the code I have written, including both the form variable declarations and the AfterUpdate code:
Option Compare Database
Dim personID As Integer
Dim orgID As Integer
Dim roleID As Integer
Dim rsPerson As Recordset
Dim rsOrg As Recordset
Dim rsRole As Recordset
Dim rsRel As Recordset
Private Sub Combo0_AfterUpdate()
Dim rs As Object
Dim db As Database
Set db = CurrentDb
Set rsPerson = db.OpenRecordset("qryPerson")
rsPerson.FindFirst "[LNFN] = '" & Me![Combo0] & "'"
If Not rsPerson.EOF Then Me.Bookmark = rsPerson.Bookmark
Set personID = [ID]
End Sub
The error message I get is Compile error: Object required, with the personID reference in the last statement highlighted. (qryPerson is a query based on tblPeople that combines first and last names into LNFN.)
Any ideas?
More specifically, what I have tried that does not work is the following:
I have created a form with a combo box for each of the three main tables. The intent is for the user to select a person, an organization, and a role, and then click on a button control that will create the relationship in the relationship table. What I am trying to do in the code is, using an AfterUpdate event procedure for each of the combo boxes, open the appropriate table as a recordset, find the selected record, and set a form-local variable to the value of the ID field for that record. So far, I can't get past the first combo box. Here is the code I have written, including both the form variable declarations and the AfterUpdate code:
Option Compare Database
Dim personID As Integer
Dim orgID As Integer
Dim roleID As Integer
Dim rsPerson As Recordset
Dim rsOrg As Recordset
Dim rsRole As Recordset
Dim rsRel As Recordset
Private Sub Combo0_AfterUpdate()
Dim rs As Object
Dim db As Database
Set db = CurrentDb
Set rsPerson = db.OpenRecordset("qryPerson")
rsPerson.FindFirst "[LNFN] = '" & Me![Combo0] & "'"
If Not rsPerson.EOF Then Me.Bookmark = rsPerson.Bookmark
Set personID = [ID]
End Sub
The error message I get is Compile error: Object required, with the personID reference in the last statement highlighted. (qryPerson is a query based on tblPeople that combines first and last names into LNFN.)
Any ideas?
