By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Add new entries to sql table form MS ACCESS front end.

P: 99
I have two tables which can be represented by this query (I have made this query the Recordsource of the form):
Expand|Select|Wrap|Line Numbers
  1. SELECT tblrcmtask.id, tblrcmtask.rcmtask,tblrcmtaskoptions.id, tblrcmtaskoptions.rcm_id, tblrcmtaskoptions.rcmtaskoptions FROM tblrcmtask INNER JOIN tblrcmtaskoptions ON tblrcmtask.id=tblrcmtaskoptions.rcm_id
I want the user to be able to add new entries into these table via a form in access 2007.
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
  1. Private Sub Form_Current()
  2. If Me.AllowAdditions = True And IsNull(Me.txtRCMTASKID) Then
  3. Dim MyRecords As DAO.Recordset
  4. Dim Myfield As DAO.Fields
  5. SQL = "SELECT Max(tblRCMTASK.ID) AS MaxOf_RCMTASKID FROM tblRCMTASK;"
  6.    Set MyRecords = dbTHIS.OpenRecordset(SQL)
  7.    Set Myfield = MyRecords.Fields
  8.    Me.txtRCMTASKID = Myfield("MaxOf_RCMTASKID") + 1
  9.    Me.txtRCMTASKID.DefaultValue = Myfield("MaxOf_RCMTASKID") + 1
  10.    MyRecords.Close
  11. End If
  12. If Me.AllowAdditions = True And IsNull(Me.txtRCMOPTIONSID) Then
  13. Dim MyRecords1 As DAO.Recordset
  14. Dim Myfield1 As DAO.Fields
  15. SQL = "SELECT Max(tblRCMTASKOPTIONS.ID) AS MaxOf_RCMOPTIONSID FROM tblRCMTASK;"
  16.    Set MyRecords = dbTHIS.OpenRecordset(SQL)
  17.    Set Myfield1 = MyRecords1.Fields
  18.    Me.txtRCMOPTIONSID = Myfield1("MaxOf_RCMOPTIONSID") + 1
  19.    Me.txtRCMOPTIONSID.DefaultValue = Myfield("MaxOf_RCMOPTIONSID") + 1
  20.    MyRecords1.Close
  21. End If
  22. Me.txtRCM_ID = Me.txtRCMTASKID
  23. End Sub
How do I do this?
Jul 6 '12 #1
Share this Question
Share on Google+
1 Reply


ariful alam
100+
P: 185
If 2nd table is the sub table of 1st table, then why you need to use primary key in the second table? if, there is no primary key in the second table, then it is possible easily to create a new id for the 1st table using access query option in a form. and add new row in the 2nd table using sub form technique for specific record in 1st table.
Jul 6 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.