Add/Append multiple records at one time to a table

I receive shipments of research medication. Depending on the study each "kit" can have a different number of medication bottles ranging from typically 1 to 12. Each kit has a unique number and within the kit each bottle has a unique number. One or more bottles from a single or multiple kits may be dispensed to a research person.

Here is reason I am looking for a better solution: the person brings back the bottle/s and I need to put it back into the kit it came from. Since the bottle does not state which kit it came from I had to do extra work to track this info down.

I built a very simple table=tblContents with two fields: KitNum and BottleNum which has made it easy to find the kit based on the bottle when the person returned it. The problem is entering the data. Today I got in 10 boxes with 12 bottles each which means 120 entries.

What I would like to do is enter the KitNum once and then have multiple fields on the same form, so in todays case this would append 12 records at one time to tblContents. If one box had only 6 bottles then it would append six records. The number of bottle fields would either have to be able to expand or not add a record if the field was Null.

I tried some Cartesian product queries, but got not no where.

Any thoughts would be appreciated.


Post your answer or comment

comments powered by Disqus
Hi there, I have some troubles in one of my forms, in fact what i'm about to explain could be impossible to realize. Let me illustrate with the image on this link to show you the form : ht tp://hpics.li/017d706 (ht and tp seperatet to post the link in my 1st post^^)
My database has a few tables like customer, project, task, user, hours etc..
Uses have to log their working hours on a specific task of a project.
What they currently can do is add a log one at the time. What I would like is to create a form that allows the user to add multiple logs at once and to modefy existing ones.
These logs are savec in the table Hours (IDEntry, Entry date, hours, task, User, comment)
Now, to the form shown in the link above:
The User selects a Month in the combo box (usually the current month). The entry date labels automatically updates according to the month. What the form should do is to load the already existing records in the correct boxes. The user can then modefy them or add new ones in the empty boxes.
What I managed to do so far is to get the info in the boxes with a DLOOKUP function in the control source of every textbox. This is not the solution because the DLOOKUP does not allow to modefy data.

I also tried to bing the textboxes to a query record with the entry date label as criteria and that doesn't work either.

Now I am wondering if that is even possible to realize. Could someone point me in the right direction please, that would be great.

Thank you

My database has 5 joined tables and I need to add multiple records at one time rather than individually. All I can find on Google is how to add multiple rows to one table, but I need to be able to add multiple records to multiple tables while maintaining data integrity.

I have the table with all of the the records i need to add. Can I somehow tell my db to add the selected fields from "tblNew" to their corresponding fields in tables 1,2,3,4?

I have a database that has an equipment table. Several of the pieces of equipment are the same, i.e. hospital beds. The question from one of my users is.... is there a way to add a comment to several pieces of equipment at the same time. For instance, I would need to search for hospital bed, then enter a comment to insert on multiple records at one time. Does this make sense? Any ideas?

I need to create a database where a user can create several new records at once.

I work for a police department and they need to have a database for traffic summons. They need to be able to have one user enter several summons numbers at one time, that are assigned to one officer.

I have two tables "Summons" and "Officer" I have entered all the officer's names into the "Officer" table.

I then created a form with a combo box linked to the "Officer" table. I then created two unbound text boxes for a start summons number and an end summons number to be assigned to a particular officer.

What do I name the two unbound text boxes? Also, what code can I use that would allow a user to enter a start summons number and an end summons number that will be linked to a particular officer. Can anyone help? Thanks

I have three reports APart1, AExSig, and APart2. Each report consist of two pages that I need to print double sided and top to bottom. Working with macros I have managed to get all three reports to open at one time and the print screen to open for each, however I then have to click properties and select double sided top to bottom to print for each. is there a way to make this all one action either through a macro or code? Thanks for the help.

Sorry, not sure if this should be in the vba section, or the queries section, but I think it's more code-related so I've put it here.

I have a database that contains linked tables from multiple access databases. At certain points, code will trigger that is meant to copy a record from one table to another (using docmd.runsql "INSERT INTO..."), both of these tables are in two separate databases. Every so often, the query will fail and, although no errors are shown, the record isn't inserted into the destionation table. Unfortunately, the code then goes on to delete the record from the source table, so I haven't been able to test if it's something about the actual record that's the query to fail. I've now temporarily removed the delete query, so if it goes wrong again, I'll be able to try and insert the data myself and see what (if anything) goes wrong.

So, questions:
1. This is a multi-user database (everyone has their own local copy of the front end linked to multiple back end databases), is it likely that this is failing because of a record locking issue, or is it something else? (I've checked the destination table and there are no required fields, so even if there was some missing data that shouldn't be the cause).

2. Am I going about this the best way? In order to copy records from a table in one database to a table in another, should I link them in a central db and use runSQL from there, or would I be better off linking the tables within the databases and then executing the query in one of those (and would it be better to execute in the source or the destination)?

Sorry if this doesn't make much sense, I can usually fix most things, but as this doesn't always go wrong I need to try and work out what the specific set of circumstances are that make it go wrong and I was just hoping some of you might have some ideas.

Thanks.

How do I print multiple records on one page when using the columnar report? For example:

Record 1 Record 2 Record 3
Field 1 value value value
Field 2 value value value
Field 3 value value value

It seems that I can only do one record per page with columns.

Thanks.

I am looking for the best way to save multiple selections into one field on a table.

I'll try to make this succinct:
My company has a relatively simple but functional contact list built in Access. There is actually only one table; it holds all of the data for every customer, and has the labels you anticipate, like Name, Address, Email, etc.

We are a used equipment company, and want to be able to label the products and services each custmer supplies, in order to send them the right updates. The problem is that each customer usually does more than one thing.

I have a proposed list of about 20 or so choices I would like to be able our people to identify.

What is the best method for me to add these, so that 1) our people can see all the options and select multiples; 2) have their selections saved to the current table; and 3) have that data be searchable.

I appreciate any help or advice anyone has to offer.

Hi,

Im importing a table with VB, i would then like to add a column to that table so i can populate it.

Is there a way of which i can achieve this? I just need to add one column to a table called tblBase called ID.

Thank you.

dont know how common this is but i am being tasked with finding a way to add multiple records at once.... for instance, my DB keeps track of military airspace that goes active and cold throughout the day. right now i have it set up where the user selects an airspace and fills in the data, date, time, estimated time going cold etc....

thier request is that they have the ability to select multiple airspaces and only enter the times and dates once for all of them.

Right now there is a table for airspace definition and another table for events (this one tracks the actual times)

example

__ W123 , date hot , time hot , date cold , time cold
__ W234 , date hot , time hot , date cold , time cold
etc...

the W### is the name of the airspace

any ideas would be greatly appreciated

Ricky

Hello -

I am new to forums and not sure how to start this thread - so please be patient. I have however, up to this point, thought myself to be fairly proficient with Access, that is until this issue.

I found a thread (Add Multiple Records on One Form 8/13/10) from William McKinely that is similar to my problem. He was able to get an answer from PBaldy - but it was a little short on details.

I am looking for the details of PBaldy's solution to create / run an append query to pre-populate a table in order to create / show a form for data entry with multiple new records for easy update.

Since I did not know if was appropriate to contact them directly, I thought I would post my request for more information in a new thread.

thanks

hello,

i'm working with access and now i have a question if it is possible to save multiple records at once.

i want to save labels. these will be saved in the table when you press the button and i have a seperate button for generating the labels from the table to a pdf file.

now i'm having a problem:

saving and generating the labels works perfect.
now i have a textbox on my form where the user kan submit the amount of labels he wants to save, because sometimes it's possible that one label needs to be printed more than one time on the pdf file.

i've tried some things but i can't seem to make it work

is it possible to do this through a for loop, that the for loop checks the amount that is submitted in the textbox and that he loops until the value is null.

thanx in advance

ilse

I have searched and searched using various key words but cannot find the answer to my question. I have three tables: tblClient (for basic client information), tblDischarge (for client discharge information), and tblAppointments (for appointments that are added to an outlook calender). All three tables are linked using the IDNumber from the Client table. I have a form where a user inputs a discharge date for the client. Once that date has been added I need to add 5 records to the Appointments table for different followup times based on that discharge date (such as one month, two months, three months, etc.). I have the code working to add an individual appointment to outlook but not 5 different ones at one time. So the question I have is how to add the 5 records based on the one discharge date?

I hope this makes sense. Thanks

Louise

Hi everyone, Hope I explain myself correctly, but here goes. I have a form that a user enters a date and clicks ok. I then want to add 10 more records at once. The best way to explain this is using a soccer team scenario. If, everyday, I want to assess a players progress I have 2 tables related. One main table has the date as key and the second table has the date and player name as concatenated key. Once I create the
date in the main table I want to automatically create the whole team for that date - somehow appending to the second table in one go?

Good morning, I have been trying to make a database and have run into a dilly of a pickle. I'm attaching the form to this entry so that interested persons can look at the problem if I am unable to explain clearly enough. The form controls of frmPtCharacteristics work fine until you attempt to add new data and then everything stops working. Entries can't be created, I am unable to search between records with the combo box, and i get an error prompt when I move to the next form because it tries to close the original form and can not save it.
I have a form whose underlying table is related to several others in a one-to-many fashion with updated fields and join property 2 (include all records from 'one' table in many table).
When I enter data the form associated with this table, and try to save a record I get the error 'You can't save this record at this time'. I'm not sure what to do about this. The data entered IS saved, but then everything just seems to stop working. Can someone please take a look at this, I'm stumped.

Thanks for the help,
Bevo S.

Hello, I have created a database that generates and issues part numbers via a request form. I now need to create a form that will creates 10 - 20 numbers at once for one person. Basically I am using the auto number system with a prefix added for the part numbers. How do you create or add say, 10 records at once? What's the code??

Thanks in advance!

Michael

Hi gurus,

I'm a bit stumped on this one. I am trying to add multiple records from 1 form to 1 table. If you open the "Form1" form from the attached test02.mdb you will probably see what I mean.

The requirement is to add changed prices to each company on a daily basis. I could do this through the "Company" from using the Pricessubform but I would ideally like to be able to launch the "Prices" form, select a company from the combobox, add the new price from the textbox and use the command buttton to programmatically add the records to the table.

The current code works but is a little wieldy (the production db has many more companies than this test db) but I was wondering if there is a more efficient way to do this i.e. some form of loop?

Thanks for you time.

All,

I want to add a whole recordset to a table without having to add one record at a time, is this possible?

thanks,

I have a required training table that lists 20 items a new employee needs to train on. I want to be able to add these records for one employee into another table that lists all employees and their training records. How can I do this?

How to append more lines at the end of a memo field?
I have table field called NOTES which I use to write various notes, sometimes the same note is stretched across several records & I would like to be able to append it to the records using an update query. However, if any of the affected records already contain pre-existing notes then I’d like to be sure that the new note I append to the field adds an ENTER character before appending the new note, how do I do this though? I tried writing something like this in the UPDATE TO row of the query, but it doesn’t work.

VBCRLF & “This is the new note appended to the end of the note field”

Please advise.

Hi all,
Please help! I am new in DB access!
I have a DB with several relational tables. I want to add new records to a table (tbl_returns). To add new records (serial number of returned cards) I am using a regular form. There is a serial number for each returned card. The problem came up when I wanted to add more than one record each time.
For instance a range of serial numbers between 6729 and 6731 & another range of 8213-8219 are returned, must be recorded in tbl_returns.
There is already an inv_ID for each returned card in tbl_allPins showing that it was sold before. Thus inv_ID in front of each serial number in tbl_allPins must be removed (assign to NULL) as well.
Normally each card is returned once, but in some cases returned cards can be sold somewhere else and they still can be returned. Sell's date and date of returning cards is always different.
Therefore serial numbers in tbl_returns is not necessarily a unique number!

Thanks for your time

Hey guys,
Does anyone have any ideas as to how you might go about editing multiple records at once? So maybe have a listbox with checkboxes, so you can select items in your DB, then, using a form, edit/set one field for all the selected items. Any ideas?

I have a form where I only want the user to be able to close the form and submit the form via buttons. I have disabled the menus, but I noticed a loop hole - some laptops will let the user close the form by pushing a quick button that I can't disable in access.

I have everything set up, but I just want to get rid of one annoying access window. After BeforeUpdate cancels the updating, a message comes up that says "You can't save the record at this time" and then asks the user if they still wish to close the database object. If the user selects yes, it eliminates the entries in the form, but won't close the form because the BeforeUnload event procedure won't let it close without the button.

Any help to resolve would be great. thanks!

I have a students table which is liked to a block table. This block table contains studentID and BlockID (which is linked to a table that contains blockname, SubjectID, and blockID being the primary key, an autonumber). A student can do one subject in one block, there are 5 blocks in total, so each student has at least 3 records in this table. I want a form where I have information on the student such as name gender and tutor which is from the student table but also want to show all the blockids from the block table. If i create a query with these two tables linked it will only show one blockid, I want it to show them all and to be able to enter data in each one, saving it to a new record each time in the block table. how do i do this


Not finding an answer? Try a Google search.