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

Multple record entry

P: 68
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
Jul 16 '09 #1
Share this Question
Share on Google+
5 Replies

Expert 5K+
P: 8,692
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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstTest As DAO.Recordset
  3. Dim intTestCounter As Integer
  5. Set MyDB = CurrentDb
  6. Set rstTest = MyDB.OpenRecordset("Table1", dbOpenDynaset, dbAppendOnly)
  8. With rstTest
  9.   For intTestCounter = 1 To 10
  10.       .AddNew
  11.         ![Field1] = "Yada"
  12.         ![Field2] = "YadaYada"
  13.         ![Field3] = "YadaYadaYada"
  14.         ![Field4] = (intTestCounter ^ 2)
  15.       .Update
  16.   Next
  17. End With
  19. rstTest.Close
  20. Set rstTest = Nothing
Table1 after code execution:
Expand|Select|Wrap|Line Numbers
  1. Field1    Field2      Field3         Field4
  2. Yada      YadaYada    YadaYadaYada      1
  3. Yada      YadaYada    YadaYadaYada      4
  4. Yada      YadaYada    YadaYadaYada      9
  5. Yada      YadaYada    YadaYadaYada     16
  6. Yada      YadaYada    YadaYadaYada     25
  7. Yada      YadaYada    YadaYadaYada     36
  8. Yada      YadaYada    YadaYadaYada     49
  9. Yada      YadaYada    YadaYadaYada     64
  10. Yada      YadaYada    YadaYadaYada     81
  11. Yada      YadaYada    YadaYadaYada    100
P.S. - It appears as though you may want to take a look at the following Article.
Jul 16 '09 #2

P: 68
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?
Jul 17 '09 #3

Expert 100+
P: 266
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,
Jul 17 '09 #4

P: 68
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
Jul 22 '09 #6

Post your reply

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