insert a set of records to table based on two numbers from fields on form

[Ryno Bower]

Good day.
I need help on the following.

I have a table NewBookEntry with fields NumberFrom and NumberTo and a few other fields as well. I created a form from this table also called NewBookEntry.

I want to fill in numbers on the form for example from 100 to 150. Then i want to insert the 50 records to a table called DetailHeader and into field RefId. It should insert 50 records showing 100 101 102...
I want to be able to do this with any given numbers in the form and let it create the records.

[twinnyfo]

Ryno,

First, I have to ask about the reason for copying the records. If you have the records already, what purpose do you have for making a duplication of the data? There may be a valid reason, but that is not apparent from the question.

However, what you wish to do is very possible. You could do it very simply with an INSERT Query or via a recordset in VBA. Either should work equally well--it just depends on how comfortable you are with creating either method.

Let us know how you would like to proceed.

[Ryno Bower]

Hi. Thanks for your reply.
I wan to make a register for all our books we use at work. We use numbered books. Example a request for credit book. Each book has 50 pages. Like 14501 to 14550. Next book 14551 to 14600 etc. So each time a new book is issued i want to be able to create the new book in the database. I use a form to fill this in like what book it is, the two fields to fill in the from 14501 and another to fill in 14550. Now the thing i want to do is as soon as i click to create and save this info the code must write the fifty records in another table naming the records from the first number to the last number. So there will be fifty records with the sequence of numbers.

Then as each number is used in the book i will create a form to go to the specified record and i will fill in the info i need.

If this is not clear enough please let me know and ill try and explain a different way.

Thanks

[twinnyfo]

Ryno,

I think I am kind of following you. But, I must ask, if you create this Credit book, will 50 pages always be used? Keep in mind, I am still not quite following what this DB is about. I'm trying to figure that out. My initial thought is that there is no need to really create 50 new records. Just create a new record for each book whenever you use it, incrementing the page number by one.

So, for example, you may have a Credit Book that has the first "page" as 14501. You edit that record. Then you have another book that has "page" number 103. You edit that page. next, you go back to the Credit Book, and then you create a new record for page 14502. And so on. Keep in mind that unless there is a specific reason why each "book" must have a certain number of "pages" then it is a waste of resources until you actually need that record. Make sense?

However, you can do it the way you are asking. I'm just trying to guide you to good DB principles first, but the ultimate decisions on DB design are up to you!

[Ryno Bower]

Ok. I understand your point. But i would prefer doing it the way i wanted to although it might not be the 'right' way. I will explain why.
but first. All our books follow in sequence and are never the same. The numbers may vary depending on which batch we get from our head office. The reason why i want to let it create the number of records according to the numbers i filled in on the form is that when the data is captured the user do not have to fill in the document number as they may make a mistake as to when it is already there they can just choose the number they will be using. I know this is silly and its difficult for me to explain exactly. But yes, this is the way i want to go. Thank you though for your advice. I fully understand your view. All i want is that if i put in the from field 12000 and to field 12050 on the form it must create the number of records in a table with the numbers following in sequence. So in this case 50 records. If i choose to put 12000 to 13000 a 1000 records should be created with the numbers 12000 to 13000 all in sequence.
Thanks for helping.

[twinnyfo]

I will have to get back to you in the morning. But this is very doable. I'll need to know about your tables and how they are related first. So, if you can put some of that together for me, I will try to guide you through tomorrow.

[Ryno Bower]

Thank you. I am just going to give you the necessary info regarding this. I am not going to list all the fields of the tables as it will become too long. So i have the following tables.
TblNewBookEntry and TblDocumentHeaderInfo

TblNewBookEntry has the following fields.
Book (primary field)
NumberFrom
NumberTo

TblDocumentHeaderInfo has the following.
DocumentID (primary field, not autonumber)
Supplier
Reason
Book
Date etc....

These two tables are related one to many on Book field.

So, whatever numbers i punch into the from field and the to field it must create the number of records in sequence into the DocumentID field whether its 2 records or 1000. Depending on the given numbers. So if its 100 and 200. A 100 records must be created in sequence from 100 to 200. Or if its 1250 to 1300. 50 records must be created in sequence from 1250 1251 1252 up to 1300.
hope this is clear.

[Rabbit]

Just so you are aware, 100-200 is comprised of 101 numbers. And 1250-1300 is comprised of 51 numbers. Unless you want to skip the first number.

[Ryno Bower]

You are right.. clever one.
No. As long as it creates the records from a certain number to a certain number. In my case then 49 records. Or whatever numbers i choose. It should start at the first number and end at the other number.

[twinnyfo]

Ryno,

Where do you want these sequential numbers to reside? I don't see a corresponding field in TblDocumentHeaderInfo....

[Ryno Bower]

Hello. It should reside in the DocumentID field in the table.

[twinnyfo]

Structurally speaking, that is not where it should go. What happens when you have two different books with the same page numbers? DocumentID should be a unique identifier that is always different and identifies a particular record--regardless what is in the record.

[Ryno Bower]

No. I was going to manipulate the code to insert a prefix before the numbers. If you can you may help me to write the code as follows.
When the new book is entered from the TblNewBookEntry table then to let the code write the records using the BookID in front followed by the numbers.
Like BookID is RFC and then the numbers. It would look like this then. RFC14501 RFC 14502 etc. The same as when another book is enetered as GRCC. It should be GRCC4501 GRCC4502 etc.
The numbering of a certain book will never be the same. So it would work for a primary key as records would not be duplicated.

[twinnyfo]

Ryno,

Got it. Makes sense. Not the most traditional method of design, but it will work (and hopefully) relatively easy to do.

It did take us a while to figure out exactly what was going on before we move forward.

First, I should ask how comfortable you are with the MS Access VB programming environment. If I must start from scratch, then we can do that, but hopefully, you have some familiarity with VBA and grasp certain concepts already. That will make it much easier for all.

[Ryno Bower]

Yes for sure. I do have some knowledge about vba that i have learnt along the way. Pretty much the basics actually. I struggle with the sql statements though and also writing some procedures sometimes. Im not always sure how to start and call back some procedures. Thats why i came here for help.

[twinnyfo]

No problem! Ill be glad to guide you along.

A can tell you right away that one of your challenges is going to be keeping people from "fat-fingering" the page numbers (including you). Because you are creating records that correspond to your page numbers, you will always have to perform a validation on all of the data whenever you want to add a book and its corresponding pages.

This is not necessarily a big deal, but in terms on practice, it is something that you will have to be aware of.

Additionally, when people are going to a particular page, I struggle with know how they will know that they are on the "right page". That is something for you to manage and monitor and figure out how to deal with it. It can be done programmatically, but is outside the scope of this thread--just be aware of it.

So, you said you have created the form that you want to use, but I want to go back to DB structure.

You say:

TblNewBookEntry has the following fields.
Book (primary field)
NumberFrom
NumberTo

But that "table" structure looks more like what you would want to have on your form (and only your form) and not actually stored.

You should, however, have table for your Books:

Field     Description
BookID    PK, AutoNumber
BookCode  I would assume you would incorporate this into the PK of TblDocumentHeaderInfo
BookName  Name of the Book
BookDesc  A more detailed description
etc.      Only information that deals specifically with each book.

You can then still use this Table as a FK to TblDocumentHeaderInfo.

Then your form would be very simple: A Combo Box, which gathers a list of your books. Two other text boxes (one for the beginning page, one for the ending page) and a command button for executing the addition of your pages.

Your homework assignment: Accomplish those tasks and we will start again soon.

For tonight, I think that will be all--time to spend time with my family.

Grace and peace!

[Ryno Bower]

That's right. I do have a table like this. And a form with a combo box to choose the book from and the page number. Now i need to have a code to do the procedure when i click the command button.

Enjoy family time.

[twinnyfo]

OK,

So here is a general outline of what you will want to do:

1. User Opens the Form
2. User Selects the Type of Book from the Combo Box
3. User Clicks "Add Book" (or something meaningful)
4. Your Form validates the data entries

   If the data is valid, Append the records
   If the data is invalid, go back and give further instructions

And then... I don't know where you want to go after that, but this is the general concept.

I can see some challenges with how you use this form:

The reason why i want to let it create the number of records according to the numbers i filled in on the form is that when the data is captured the user do not have to fill in the document number as they may make a mistake as to when it is already there they can just choose the number they will be using.

As mentioned before, if the user always goes to a new record, there is no chance to make a mistake, because it is always a new record. My question for implementation will be, how does the user know "which page" they will be going to? These are all details outside the scope of this thread, but things you should think about.

Back to our form.....

On the Command Button that the user clicks once they have filled in the requested data, you will build a procedure in the OnClick Event of that button.

So, back to our outline, your Procedure will look like this:

Private Sub cmdAddBook_Click()
On Error GoTo EH
 
    'Your validation code here
 
    'Your appending code here
 
    'Whatever else code here
 
    Exit Sub
EH:
    MsgBox "There was an error adding the Book!  " & _
        "Contact your DBA!", vbOkOnly, "Error!"
    Exit Sub
End Sub

We can take each of these steps one at a time. We want to make sure each step works correctly before we move on to the next.

Our aim is for you to try to work through some of this yourself.... If you have no clue whatsoever where to start, we can also help with that. However, if I simply write the code for you, I figure that you have learned anything. however, by trying different things, you will understand the coding process better and be better able to modify this code for projects in the future.

Standing by for further assistance.....