process multiple entries on a form with one module

[lewie]

I have a form where people enter new records. They can enter maybe 10 or 20 at a time. I have a button that creates a unique number using entered information. I can make it work for the open entry on the form but if they entered 10 new records it only works on the current form.
How do i reference the other 9 entries that were made on the form or do i have to go to the place they are stored to reference them. I guess what am I saying is there a way to sequence through the entries that are located on a form.
Thanks
Lewie

 

[JunglesMcBeef]

G'day,

Are your records being stored directly into a table? or are you storing them in an array first?

 

[lewie]

currently they go through a make table, to a master table and then are appended to another table. I am trying to streamline the process. There is this sequence for about 40 different types of tapes. So the db pretty large just to assign a unique number to each tape. lets say there are types cc, lf, and mcd. when a new one of each kind comes in each will get loaded in a maketable then appended to a master table of the same kind so like cc entry will go into a make table then be appended to the master cc table to get the next cc number. then that is appended to another main master table. Then to assign the unique numers a macro with over 200 queries is run. A set of 5 queries for each type.
This is run on the master table searching for all empty id fields, which are then filled in using update queries which add the type and the unique number for each type so you get cc1 cc2 cc3 or lf1 lf2 lf3. i took the 200 queries and wrote a case so only the 5 required would be run. I then used a variable so I only need a macro with 5 querys instead of 200. but now i guess i have to run this and automatically update after each entry instead of doing it after everything is loaded in the table. and then if i can put variables in the queries i think i can elimatate 195 make tables and queries. Then if i can have one table track all the last used numbers for each type i can elimate 195 master tables(ie lf master table). then maybe my 36 meg db will shrink and become more usuable as mutiuser over the wan. Anyway do you see what i am working towards doing.

 

[JunglesMcBeef]

I think I see where you want to go with this, but things still aren't clear. So what you are saying is you want whatever the user enters on the form to be put into one master table, effectively having one table for your whole database? Whatever the case, I'm thinking you should probably be handling the user data on the form through code, and check the last used unique number/id before adding any data to a table. Based on your last post, and in response to your first question, unless you are using VBA for the processing of data in your db, I can't think of an easy way to do what you are asking. But if you are using VBA or want to use it... the possibilities are endless.

 

[lewie]

I am new to programming in this context but I think I would like to start. I think I can load vb.net at home if I have to. I have a wrox book Access 2002 VBA and I think I would like to start small and build since as of now I have no critical deadlines. Can I access fields in tables using VBA.
Thanks Lewie

 

[JunglesMcBeef]

Ok, accessing fields in a table through VBA is relatively simple (assuming you already have some sort of programming knowledge). All that has to be done, is create a connection to the database and set it to an object. Then you create a recordset object (set the active connection to the connection object you just created), which will be the object that will do all the manipulation of the fields in the table. You can add new records, create new tables, edit existing records and tables (updating when processing of records is complete), and a whole lot more. A lot of it will be outlined in the book you mentioned. My advice is to keep posting on this site though whenever in doubt of something. I'll definitely help you out if you need, what you have detailed above sounds accomplishable, but like I said I'm still a little sketchy on some of the details.

 

[lewie]

Should i use DAO or ADO since there are supposedly phasing out DAO.
Lewie

 

[JunglesMcBeef]

You are right, DAO is in maintenance mode so ADO is definitely the way to go