Multple record entry

[KingKen]

I have a situation whereby I have to enter many records in a db that only have one field that changes. For example software installed on a computer would give you many different rows of data with just a single variant in the software field.

I want to know if it is possible to have my form setup in such a way that it takes one instance of constant data and various instances of a variant field but for each variant create a new record and add it to the table

For example it would take the machine name entered only once by the user and add it with every software that the user selects. I am thinking here of a subform in datasheet view that allows u to enter multple records. All records must be entered in the same table

thanks experts

[ADezii]

@KingKen
This would be a simple matter using DAO and a Looping Structure. Let's assume that your Table Name is Table1, and that it consists of 4 Fields named Field1, Field2, Field3, and Field4. The following code will Append 10 Records to Table1 keeping Field1, Field2, and Field3 constant while making Field4 variable.

Dim MyDB As DAO.Database
Dim rstTest As DAO.Recordset
Dim intTestCounter As Integer
 
Set MyDB = CurrentDb
Set rstTest = MyDB.OpenRecordset("Table1", dbOpenDynaset, dbAppendOnly)
 
With rstTest
  For intTestCounter = 1 To 10
      .AddNew
        ![Field1] = "Yada"
        ![Field2] = "YadaYada"
        ![Field3] = "YadaYadaYada"
        ![Field4] = (intTestCounter ^ 2)
      .Update
  Next
End With
 
rstTest.Close
Set rstTest = Nothing

Table1 after code execution:

Field1    Field2      Field3         Field4
Yada      YadaYada    YadaYadaYada      1
Yada      YadaYada    YadaYadaYada      4
Yada      YadaYada    YadaYadaYada      9
Yada      YadaYada    YadaYadaYada     16
Yada      YadaYada    YadaYadaYada     25
Yada      YadaYada    YadaYadaYada     36
Yada      YadaYada    YadaYadaYada     49
Yada      YadaYada    YadaYadaYada     64
Yada      YadaYada    YadaYadaYada     81
Yada      YadaYada    YadaYadaYada    100
 

P.S. - It appears as though you may want to take a look at the following Article.

[KingKen]

I should have mentioned that I am working in microsoft Access 2003. All the forms are in access. Would this approach work there too?

I can't see the link that you are pointing me to please repost.

How about the subform aproach can it work in Access? if so how?

[ajalwaysus]

Yes, this can be done in Access 2003.

As to using a subform, you can, by using Master - Child linking between the Parent form and subform, and only link on the constant fields.
Display all your constant fields on your parent form, and only those that change on the subform. This way you can add as many records as you want on the subform and it will automatically inherit the constants from the parent form. This will work even if you have more than one filed you wish to change.

Should work,
AJ

[ADezii]

@KingKen
http://bytes.com/topic/access/insigh...ble-structures

[KingKen]

Ok this solution did not work. I created the subform and linkes the fields but when i try to enter records it tells me that it cannot add values because it would create duplicate values in the index, primary key, or relationship.

Is there anyway that I could tweek this solution to allow the entry of many records by just changing the one record that needs adjusting