Getting the current primary key value into another table

[syedalisajjad]

Hi all,



I need some help from you people. I have a form where I have a combo box and a list box. I make some selection in combo box and list box and data is saved to a table let's call it table1.

i have another table table2 where the primary key of table1 is stored (FK)

what i want is that after i make selections in combo box and list box and then, when i press the button combo box value and list box values are stored in table 1 and the primary key value of table 1 is automatically stored to table2, number of times new records are created in table1. (e.g. new records in table 1 have keys from 2....10) then table2 should contain (2,3,4,5,6,7,8,9,10) in the foreign key column and next time when record is entered in table1 then table2 fk column should continue from 11 onwards...

primary key of table1 is an autonumber field.

please help me with this....m totally new with this.....

thanks

[syedalisajjad]

please help

[khalid]

Hi Sajjad,

As far as I understand, you want to save the newly created ID of table1 to table2 whenever a new recored is entered in table1?

Is that the case?

Here is the code based on your earlier tables references?

Docmd.RunSQL " INSERT INTO tblTestResults ( tblComponentID ) SELECT Max([ID]) AS MaxID FROM tblComponent"

hth.

[Mclaren]

Please explain why you would want to do this ?

[syedalisajjad]

AOA,

Khalid bhai you are right. I have succeeded to some extent, i am able to get the primary key of table 1 by having a unique index on the foreign key column of table 2 but there is a problem.

let's suppose at first there are 10 records in table 1. when 11th record is entered in table1, the value in primary key of table 2 is 21......it goes through all the records in table1 but cannot save due to the unique constraint and save the value 21 is assigned to the primary key column of table 2.......and next time it will be 33 and so on......

what i want is that table2 primary key should increment one by one instead of going through all the records of table1.

thanks

[syedalisajjad]

@ Mclaren

i want to perform an automatic test......

first the user selects a vehicle from combo box, then he selects Test from the second combo box and then he selects the components he want to test from a multi select list box....

i have two tables where the data should be automatically inserted when the button is pressed.....so when the user presses the button, the selected value from the second combo box and the values selected from the listbox are inserted to the table1 (testresults), and in the second table the primary key of table1 is stored along with the value coming from vehicle combo box.....so i know which testresults belong to which vehicle......

[syedalisajjad]

@ Khalid bhai,

thanks for your code....i will try it tomorrow in office......

[khalid]

@ Khalid bhai,

thanks for your code....i will try it tomorrow in office......

I believe your database design is not good enough. The good databae design do lot of works behind the scene. The good normalization, Relationships, integrity of data do more instead of coding and calling/inserting the IDs of the tables to another table through VBA coding.

Would you please read Fundamentals of Relational Database Design?
http://office.microsoft.com/en-us/ac...001224247.aspx

Thanks,

[syedalisajjad]

AOA,

Khalid bhai,

thank you very much once again for your valuable suggestions but I have to do this....now your code is working fine as long as only one record is entered in table.A but the problem is when simultaneously two records are entered in tableA, it gets only the maxID from tableA to tableB and skips the first recordID.

Can you please tell me how to solve this problem?
JAZAKALLAH

[khalid]

AOA,

Khalid bhai,

thank you very much once again for your valuable suggestions but I have to do this....now your code is working fine as long as only one record is entered in table.A ...
JAZAKALLAH

Walikum Salam,
The code is doing exactly what it is shown. Get the MaxID of tblComponent and INSERT it INTO tblTestResults.

but the problem is when simultaneously two records are entered in tableA, it gets only the maxID from tableA to tableB and skips the first recordID.

This because the code is getting only the MaxID of the tableA and Insert it into tableB . I am not sure how you are entering the records to your tableB? are you using the INSERT recordset statement Or just using the underlying/source table of the form?
If you can attach a sample db, would be easy to analyse and work.

One approach is to get the MaxID of the records of tableA before saving the new records to tableB, then save only those records > greater then the MaxID already obtained, so all the records will be entered to tableB leaving the old records already exists tableA.

Private Sub Command5_Click()
Dim oldID As Long 'get the oldMaxID
Dim rst As DAO.Recordset ' declare recordset
    Set rst = CurrentDb.OpenRecordset("SELECT Max(tblComponent.ID) AS MaxID FROM tblComponent") ' open recordset
    oldID = rst!MaxID ' get the OldMaxID
    DoCmd.RunSQL " INSERT INTO tblTestResults ( tblComponentID ) SELECT tblComponent.ID FROM tblComponent WHERE ID >" & oldID ' Inset all the recorder greater (new) then oldMaxID
End Sub

Hope this solve your problem.

[syedalisajjad]

AOA,

Khalid bhai thanks a lot for helping.....can you give me your email address...i will send u the database on your email address....i cannot post it here....

thanks

[syedalisajjad]

AOA,

Please find the Sample db attached and have a look at sample form. What I want to achieve is that user selects a vehicle from the Vehicle Combo, then he selects a test from Tests Combo, then he selects components he want to test from the List box.......

now when he presses the save button the data should be stored to two tables, TestResults table and VehicleTestInformation table. I am able to store the records in TestResults table with your help but still no success in VehicleTestInformation table

please have a look at it and help me out........

Thanks

[khalid]

now when he presses the save button the data should be stored to two tables, TestResults table and VehicleTestInformation table. I am able to store the records in TestResults table with your help but still no success in VehicleTestInformation table

What exactly you want to store in each table? What is the purpose of storing the data to both the tables simultaneously?

I have downloaded the db but again I am using Access-2010 and your db is created in Access-2003, anyhow this shouldn't be the matter.

You have two tables you want to store data:
1) TestResults
2) VehicleTestInformation (need to know what information you want to store)

What information should go to each table? seems that you want to store the TestResultFK ID, and VehicleFK ID to VehicleTestInformation table???

here is a question?
If you want to store the TestResultFK, it will be incremented ID of your TestResults table along with the VehicleFK ID (repeated ID of a vehicle for each entry) - What is the purpose of doing so???

[syedalisajjad]

AOA,

Khalid bhai,

Main purpose is the automation of testing process

There are two types of components which are to be tested.

1. Components which are tested independently (i.e. components not installed inside a vehicle)
2. Components which are tested inside a vehicle.

>TestResults table stores the results of both types of components.

>You are right VehicleTestInformation will contain TestResultsFK and VehicleFK,

>this is because if i want to see which components were tested inside which vehicle or which test results belong to which vehicle, i will be able to get it through this
table........

>There are two different forms, one for the simple component tests and one for the Components inside a vehicle (SampleForm in attached SampleDb)

>Now suppose first the user starts simple component test (i.e. components not inside a vehicle) and he selects 10 components to test against a particular test and
when he presses the button TestResults table will contain 10 records with ID from 1 to 10.

>Then user starts Vehicle Test (Components inside a vehicle).....he selects a vehicle from the combobox, selects test from combo box and selects components from list box
Let's suppose he selects 10 components to test against a particular vehicle, and when he presses the button TestResults will contain 10 more records with ID from
11 to 20, and at the same time VehicleTestInformation should also contain 10 records.

--------------------------------------------------
VehicleTestInformationID TestResultsFK VehicleFK
--------------------------------------------------
1 |11 | 1
2 |12 | 1
3 |13 | 1
4 |14 | 1
5 |15 | 1
6 |16 | 1
7 |17 | 1
8 |18 | 1
9 |19 | 1
10 |20 | 1

[khalid]

Okay here is the solution:

1) First create a yes/no field in TestResults (table) and name it SaveToVTest, set its default value to true (-1) so it will be true for each new entry.

2) Add this line of code before you enter list items to TestResults table:

Code:
'clear all the checks from TestResults (SaveToVTest field)
DoCmd.RunSQL ("UPDATE TestResults SET TestResults.SaveToVTest = 0;")

This will clear all checks before Inserting new entry to TestResults table.

3) Add this line of code to Append/Insert the newly added record to VehicleTestInformation table.

Code:
strSQL = "INSERT INTO VehicleTestInformation ( TestResultFK, VehicleFK )" & _
" SELECT TestResults.TestResultID, TestResults.TestFK " & _
" FROM TestResults " & _
" WHERE (((TestResults.SaveToVTest)=True));"
DoCmd.RunSQL (strSQL)

This code will append all the records WHERE (((TestResults.SaveToVTest)=True))

your full code is here:

Private Sub butSave_Click()
Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant
  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("TestResults", dbOpenDynaset, dbAppendOnly)
 
 Dim CBx As ComboBox, DL As String
   
   Set CBx = Me![cboVehicle]
   DL = vbNewLine & vbNewLine
   
   If CBx.ListIndex = -1 Then 'No selection made!!
      MsgBox "'Vehicle' is a required field!" & DL & _
             "You'll have to go back and make a selection ...", _
             vbInformation + vbOKOnly, _
             "Missing Data Error! . . ."
      CBx.SetFocus
      Cancel = True
      Exit Sub
   End If
   
   Set CBx = Nothing
  
  'make sure a selection has been made
  If Me.listComponents.ItemsSelected.Count = 0 Then
   MsgBox "Must select at least 1 component variant you wish to Test"
   Exit Sub
  End If
 
  'clear all the checks from TestResults (SaveToVTest field)
  DoCmd.RunSQL ("UPDATE TestResults SET TestResults.SaveToVTest = 0;")
  'add selected value(s) to table test results
  Set ctl = Me.listComponents
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!ComponentFK = ctl.ItemData(varItem)
    rs!TestFK = Me.cboTest
    rs!Result = "Open"
    rs.Update
  Next varItem

 'now append the TestResults data to VehicleTestInformation table
 strSQL = "INSERT INTO VehicleTestInformation ( TestResultFK, VehicleFK )" & _
          " SELECT TestResults.TestResultID, TestResults.TestFK " & _
          " FROM TestResults " & _
          " WHERE (((TestResults.SaveToVTest)=True));"
 DoCmd.RunSQL (strSQL)
    
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
End Select
    
End Sub

Hope this would solve the problem.