Adding Multiple Records with same information

Ernest Grogg
Ernest Grogg used Ask the Experts™
on
Hello,

I would like to see if I could add multiple records with some information that is the same throughout the table.

What I would like to do is have some fields that would repeat the information and fields that have a "through" would be unique...example below:

Field1: 1
through
Field2: 10
Field3: John
Field4: Doe
Field5: (111)123-1234
Field6: Blue Visitor

so it would be:  Enter the Batch of Cards Issued:  1   THROUGH    10
or someone could enter:                                         1   THROUGH   1

Field's 3-6 are the same that would repeat in the records.

so I would like it to enter separate records...if 1 through 10 (so it would be 10 records entered) or if the person entered the same info:  11  THROUGH 11 then there would only be 1 record entered
so the Table would be setup

for the multiple entries you would have multiple entries:
Field1: 1
Field2: John
Field3: Doe
Field4: 1111231234
Field5: Blue Visitor

Field1: 2
Field2: John
Field3: Doe
Field4: 1111231234
Field5: Blue Visitor

Field1: 3
Field2: John
Field3: Doe
Field4: 1111231234
Field5: Blue Visitor
.
ect...

so for the single record you would have 1 entry in the table:

Field1: 11
Field2: John
Field3: Doe
Field4: 1111231234
Field5: Blue Visitor

ect....

don't know if I could do this with VB and how could I do this?  this is also thinking ahead but the same concept would be if I wanted to return those cards, I would have a similar process that would enter a separate field that would show it returned and repeat it also....
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can do it in VB quite easily.

You need to have two text fields in your form which say "From"  to "To."

Now take the values of these two text fields in two different variables say "iStart" and "iEnd".

Now all you need is a for loop

A pseudo code is given for your reference.

Dim sSQL as string
For iI = 0 to iEnd - iStart + 1
      sSQL = "Insert into Table1(Field1,Field2,Field3...) values(" & iStart + iI & ",'John','Doe',...)"
     'Execute SQL here to insert the records.  
Next

Open in new window

Hamed NasrRetired IT Professional

Commented:
What is the purpose of the design of this table?

Looks like you might have a table with one record of the repeated info and a key value "foreignKey".
Then insert this key value in the current table.
PrimaryKey, FieldFrom, FieldTo, keyValue.

tbl1:
foreignKey, Field2: John, Field3: Doe, Field4: 1111231234, Field5: Blue Visitor
1, John, Doe, 1111231234, Blue Visitor

tbl2:
primaryKey, fieldFrom, fieldTo, foreignKey
1 , 1, 10, 1
2, 11, 11, 1
Ernest GroggSecurity Management InfoSec

Author

Commented:
sorry it took so long to respond...with the new year I was out and away.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ernest GroggSecurity Management InfoSec

Author

Commented:
hnasr,

Good thought, but to what I wanted having two tables would hinder what I have already...

Thanks though!
No worries Ernest. Let me know if you need any further help. And wish you a happy new year.
Ernest GroggSecurity Management InfoSec

Author

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial