Expand|Select|Wrap|Line Numbers
- SELECT tblrcmtask.id, tblrcmtask.rcmtask,tblrcmtaskoptions.id, tblrcmtaskoptions.rcm_id, tblrcmtaskoptions.rcmtaskoptions FROM tblrcmtask INNER JOIN tblrcmtaskoptions ON tblrcmtask.id=tblrcmtaskoptions.rcm_id
Columns tblrcmtask.id and tblrcmtaskoptions.id are the primary keys of the tables tblrcmtask and tblrcmtaskoptions respectively.
I do not understand how do I create new ID in both the tables while the user adds new entries.The user can add only tblrcmtaskoptions.rcmtaskoptions and tblrcmtask.rcmtask in the form.Also, there are multiple rows in the table tblrcmtaskoptions for each tblrcmtask.id.
I want the user to be able to add new rows in the table tblrcmtaskoptions for an existing tblrcmtask.id
I tried using dropdowns for these two but I am facing problem while creating the new ID as Maximum of the ID + 1.
Here's the code:
Expand|Select|Wrap|Line Numbers
- Private Sub Form_Current()
- If Me.AllowAdditions = True And IsNull(Me.txtRCMTASKID) Then
- Dim MyRecords As DAO.Recordset
- Dim Myfield As DAO.Fields
- SQL = "SELECT Max(tblRCMTASK.ID) AS MaxOf_RCMTASKID FROM tblRCMTASK;"
- Set MyRecords = dbTHIS.OpenRecordset(SQL)
- Set Myfield = MyRecords.Fields
- Me.txtRCMTASKID = Myfield("MaxOf_RCMTASKID") + 1
- Me.txtRCMTASKID.DefaultValue = Myfield("MaxOf_RCMTASKID") + 1
- MyRecords.Close
- End If
- If Me.AllowAdditions = True And IsNull(Me.txtRCMOPTIONSID) Then
- Dim MyRecords1 As DAO.Recordset
- Dim Myfield1 As DAO.Fields
- SQL = "SELECT Max(tblRCMTASKOPTIONS.ID) AS MaxOf_RCMOPTIONSID FROM tblRCMTASK;"
- Set MyRecords = dbTHIS.OpenRecordset(SQL)
- Set Myfield1 = MyRecords1.Fields
- Me.txtRCMOPTIONSID = Myfield1("MaxOf_RCMOPTIONSID") + 1
- Me.txtRCMOPTIONSID.DefaultValue = Myfield("MaxOf_RCMOPTIONSID") + 1
- MyRecords1.Close
- End If
- Me.txtRCM_ID = Me.txtRCMTASKID
- End Sub