Add n records a table depending on an data entry?

[jlnewbie]

I want to add n records to a table depending on data entry from a form. For instance.

How many records would you like to enter? - User enters 3 in [Myfield} of Record N -Table 1

Table 2 then should now have 3 records related to Record N.
Table 2 will be filled later.

I've looked for plenty of help options but nothing? Is there a function, query type or event procedure that will do this. Thank You

 

[cmmrfrds]

Here is one way of doing it.
In your event or button event add the following code and then build the function.

Dim indx as integer, Nkey as integer, returnOkay as String
indx = Me.[MyField] ' this is number of records to add
Nkey = Me.[YourKey] ' this is the foreign key from table 1
returnOkay = MyAddFunction(indx, Nkey)

Function MyAddFunction(indx as integer, Nkey as integer) as String
Dim RSMT As DAO.Recordset, indx1 as integer
Set RSMT = CurrentDb.OpenRecordset("You Table 2", dbOpenDynaset)
'-- Add a new record in the Table 2, indx number of times
For indx1 = 1 to indx
RSMT.AddNew
RSMT!table1keyInTable2 = Nkey
RSMT.Update
Next indx1
RSMT.Close
MyAddFunction = "AOkay"
End Function

 

[jlnewbie]

I've built the function and where do put the event On Exit?
I'm a newbie to vba. What is the {YourKey]foreign key?
Thanks again

 

[cmmrfrds]

For the event. You could use the lost focus event of MyField, then tabbing out it would be activated. Or add a button to your form and put it behind the onclick event.

In a normalized database you will have many relationships between tables of one to many. In your case, table 1 is the one side and table two is the many side of the relationship. The primary key in table 1 will be a foreign key in table 2, which is how you establish the relationship between the tables. Table 2 will also have its own primary key -- possibly autonumber.

Hope this helps, I need to take my son to hockey now.
Jerry

 

[jlnewbie]

cmmrfrds:
I tried your function and event procedure. Re-written for my db.
Here it is. My problem now is I get error break in the function at RSMT!ConcreteLogKeyInCylinderTestData = Nkey. May you see something I don't! Thanks

Private Sub NumCylinders_LostFocus()

Dim indx As Integer, Nkey As Integer, returnAOkay As String
indx = Me.[NumCylinders] ' this is number of records to add
Nkey = Me.[LabNumber] ' this is the foreign key from table 1
returnAOkay = MyAddFunction(indx, Nkey)

End Sub

Function MyAddFunction(indx As Integer, Nkey As Integer) As String
Dim RSMT As DAO.Recordset, indx1 As Integer
Set RSMT = CurrentDb.OpenRecordset("CylinderTestData", dbOpenDynaset)
'-- Add a new record in the Table 2, indx number of times
For indx1 = 1 To indx
RSMT.AddNew
RSMT!ConcreteLogKeyInCylinderTestData = Nkey
RSMT.Update
Next indx1
RSMT.Close
MyAddFunction = "AOkay"
End Function

 

[cmmrfrds]

Right before the For indx1 statement add the following.

Debug.print " the Nkey = "; Nkey

Then after the error do a Control G. This will bring up the debug window and you can see what is in the field Nkey. Maybe it doesn't have the value you expect and this is a good way to debug it. How is the field ConcreteLogKeyInCylinderTestData defined in your table? Integer? Long? other?? Otherwise, from what I can see the code looks good.

Jerry

 

[cmmrfrds]

Another thing to look for is do you have other fields in the record that require input. Check the field definitions and validation rules. If a required field is not entered this will cause an error. Do you have the reference library Microsoft DAO 3.6 Object Library selected under Tools then References on the toolbar menu?

If you continure to have problems paste the exact error message in a post.

Jerry

 

[jnairn]

It will trash the function if the ConcreteKey.... field is set as autonumber, or if it is a primary/Foreign Key, has referential integrity set to the Primary table set to Enforce Integrity.

The Validation rules noted in the previous post would not come into effect until the Update statement.

If it is a related key field to the primary table, the addnew has to come from the primary table.

 

[MichaelRed]

jlopez et-al,

This is slightly ouside of the box, but why should the user need (or even be able to) specify how many related records should be creatred? When the User needs to 'Add' a record the system/process should be set up to do the 'add' when the information which supports the addition has been entered. If you want to add records to the 'many' table, just set up the dataentry form to have an 'add related' command button which will cary forward the record pointer information from the 'one' table.


MichaelRed
There is never time to do it right but there is always time to do it over

 

[jlnewbie]

MichealRed:
The reason I need user to add to 'many' Table is that most of the time (90%) there should be only 5 records add to the many table but in some instances there might 6 or 7, maybe more. We've had several instances where there should of been 7 records entered but only 4 were entered or vice versa.(Get the picture). Using the addnew method automatically should set the records. This way the records although not completely fill will have already been there.
Having the records there should also make data entry to the many table that much easier. You open it and see all of the ones that have data and those that don't.
Can the "addrelated" command button do that? I try anything that works.