473,387 Members | 1,863 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

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

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
1 2235
ariful alam
185 100+
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

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

Similar topics

0
by: Rupe | last post by:
I have a web site on a remote, shared host with a bunch of individual Access databases. I want to switch my databases from Access to MS SQL and have some questions. I update some of my Access...
16
by: sm_hall | last post by:
Hi, I manage and develop an Access 2003/SQL 2000 based system for a real estate company. It's quite extensive and has a level of Windows/Office integration. My question is this: what are the...
6
by: John | last post by:
Hi We have an access app (front-end+backend) running on the company network. I am trying to setup replication for laptop users who go into field and need the data synched between their laptops...
0
by: Typehigh | last post by:
I have a routine that replicates a table with an existing table (used as a template) in SQL Server. The desired new table name is passed to the routine along with the template table name. The code...
2
by: Phokojoe | last post by:
I have an access database which uses 2 forms to enter data in 1 table. The other form is a subform and is continuos whereas the other (Primary) is not continuos. It works fine in an access...
3
by: M O J O | last post by:
Hi, I have an application where I've implemented a global hotkey, so no matter what other application is in front (have focus), my app will react when the key combination is pressed. This works...
6
by: BrianDP | last post by:
I have a table that has always been in my back-end of this application. The table is getting quite large, and, on top of that, we lose records out of this table. They just dissappear! No rhyme or...
0
by: johnsmith011276 | last post by:
I have linked a table to from Oracle to Access via ODBC. I have set up all the proper privilages; however, I am having an issue inserting data. I can edit the data already in the table. I can...
3
by: adjo | last post by:
I have used Access frontend with SqlServer backend combination for a number of years. In the easy 'oldfashioned' way using DAO and odbc linked tables. Works fine within limits. Now I'm facing the...
11
by: Gwindor | last post by:
I have been doing programming in Access for about 10 years. I am now being asked to make an Access front end for data that is stored in SQL Server. My impression is that the only way to do this...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.