VBA Across Multiple Records

Mar 28, 2012

I have drafted a simple VBA code in a form button that takes the result calculated in a query and pastes the result into a table. I have attached my test database to illistrate..In my attached database, i have a list of 5 records, i am wanting to push the calculation button have all the records updated with the result that is calculated in the query in 1 go.

As it stands right now i have to do it one at a time per record, which is not preferable when there are potentially hundreds or thousands of lines. I am wanting to do this because i want to reduce my reliance on calculated cells in forms and reports where i have to draft long formulae across 3 separate queries to get the result i am looking for.

View Replies


ADVERTISEMENT

Modules & VBA :: Inserting Multiple Records From Multiple Unbound Text Boxes

May 6, 2014

I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table

the code I have started off with is

Code:

CurrentDb.Execute "INSERT INTO ColdTemperatures (ProductID, ColdTempDate, Temperature) VALUES (" & Lettuce & ", #" & Me.RealTime & "#, " & Me.Lettuce & ")"

which adds 1 successfully however if i repeat the code above for all 15 this Im assumming will create a potential bottleneck and slow the system down

is it possible to add all 15 records at once? do you think Im going at this the right way

View 5 Replies View Related

Queries :: How To Count Records Based On Multiple Criteria From Multiple Tables

Jan 4, 2014

I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".

View 4 Replies View Related

Tables :: Linking Records In One Table To Multiple Records In Another And Assign Percentage?

Nov 21, 2012

I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).

I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.

View 3 Replies View Related

Combining Multiple Tables With Multiple Records

Jul 16, 2007

Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -

I have units A, B, C
Parking units p1,p2,p3,p4,p5,p6
Storage units s1,s2,s3.

Unit A uses parking units p1, p2, p5 and storage unit s2.

Unit B uses parking unit p3 and storage unit s1.

Unit C uses parking unit p4, p6 and storage unit s3.

How do I write a query/report that would show:

Unit Parking Storage
Unit A p1, p2, p5 s2
Unit B p3 s1
Unit C p4,p6 s3

Thanks,
Chester Campbell
ccampbell@jfreed.com

View 3 Replies View Related

Queries :: Put Multiple Records In Multiple Columns

Jul 31, 2013

I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).

The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!

What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.

The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).

Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.

See the attached file ....

View 2 Replies View Related

Linking Multiple Records To Single Records

Apr 22, 2013

I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.

View 3 Replies View Related

Query To List Multiple Records In A List From Multiple Queries

Jul 11, 2013

I have a DB where you there's 5 tables all linked together by one project ID

tables below

Project , Staff, Asset, allowances, travel, mark up

What I can do is create a new project, then add records to each of the other tables on what different items I require,

i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)

Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.

At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.

View 7 Replies View Related

Re-using 1 Value In Multiple Records

Jun 27, 2005

I am trying to manage a contract price from month to month. Every month, some portion (or none) of the total contract will be completed. I have a form in which the user enters 'Amt Completed this Pay Period.' Then the 'Total Completed To Date' is automatically calculated by adding the 'Amt Completed this Pay Period' to the 'Previously Completed Amt.' I would then like to use the new 'Total Completed to Date' as the next months 'Previously Completed Amt.'

Basically, I am trying to calculate a value in a form for one entry, and then passing that value to another entry to use. Does anyone know how I would pass this info along?

View 3 Replies View Related

Getting Rid Of Multiple Records

Dec 12, 2006

Hi Folks,

I'm doing a Schedule Adherence Report in Access. One table has the schedule start time and stop time. The second table has exception times (lunches, breaks, etc). Both tables have the common denominator of a unique Schedule ID. How do I bring them together with repetitive Schedule records from the first table. What I get is the following:

ID Code Time Code Time
1 Open 12:00 Break 2:00
1 Open 12:00 Lunch 4:00
1 Open 12:00 Break 6:00

What I want is the following:

ID Code Time Code2 Time Code3 Time Code4 Time
1 Open 12:00 Break 2:00 Lunch 4:00 Break 6:00

Please help,

Rick.

View 3 Replies View Related

Multiple First Records

Jan 11, 2005

Is there away to return just the first record of a bunch of tables? I have stuff that gets updated daily and need to be able to search thru all of it.

View 4 Replies View Related

Multiple Selection Of Records

Dec 17, 2005

I'm building a database for a realtor friend. Part of his job is keeping track of where his clients want to live. I have added a field named "Areas". I need to populate that field with names of cities where his clients want to buy thier house. Sometimes there are only a couple of cities. Other times there could be more then 10. I don't want him to type these cities in. He is not a good typer, either am I, and he is prone to abbreviations and typos. Garbage in garbage out. I would like to provide him a drop down list, or something like that, of all the cities or areas and have him select each area and then either hit a command button or copy/paste it to that text box. Either way will work. The command button would be nifty. The result would give him the option of doing a form filter and being able to filter that text box for ex: "atlanta" and "syracuse". He then could cue these people when he has a property come available in either one of those cities.

I DON'T understand VBA code. I don't know how to write it or where to put it.

I tried to search this site and I could not find any threads like this, to my amazement. If there is a similiar post out there and I could not find it I apologize in advance.

View 1 Replies View Related

Copying Multiple Records

Nov 6, 2006

Hi all!

I need a little help with a new function in our CRM db (Ac2000).

I've made a system for handling sale-prospects. When a sale is done, I want to transfer the costumer info to the costumer table. All this is well, no problems with this.

BUT, each prospect (And costumer) has contacts. There can be several contacts to each prospect, connected with a prospect number.

The challange is to transfer the contacts as well. And assigning the new costumernumber..

Dont bother going techincal right away, but a point in the right direction would be great!

View 7 Replies View Related

Entering Multiple Records

Apr 7, 2008

Heloo all,

I have a table, and a data access page for data entry.... is it possible for me to add a mulltiple records at a time.... please help

View 1 Replies View Related

Delete Multiple Records

May 9, 2007

how do i delete multiple records in a table, for example if i have a table that as serial_num 123 twice, how do i delete the two records, basically i don't want to delete one record, i want to delete the two records. Thanks.

View 5 Replies View Related

Need Help With Adding Multiple Records

Jul 5, 2005

I believe this can be done with a query, but am having problems with it.

What i have is a db with a table for employee another table for training items and a third table to tie them both together. These employees are divided into several "teams" (team 1, team 2 etc ...) The training items are assigned to some or all of the teams. ( i know that this is a many to many relationship and i believe that is where i get confused ) Now the problem. What i would like to do is have a form that will allow the user to assign a new training item to teams 4, 5 and 6 and have access automatically create a record in the table. Now that the 20 or so employees that are assigned these training items will have a record that the user can simply fill in a date when the training has been completed by the employee.

I believe what i am looking for is an append query. Is that right?? Have been playing with this one for awhile and have no luck so i would appreciate any help i can get on this one


thanks again

Ricky

View 5 Replies View Related

How To Delete Multiple Records

Aug 15, 2005

Hi,

I'm trying to delete say 4000 records, out of total 5000 records. I want to keep 1000 records based on the ID.

How can I write the SQL DELETE query, so that only 1000 records remain based on the query?

Thank you in advance for your great help.

regards,
Pranesh
Alberta, Canada

View 1 Replies View Related

Append Multiple Records

Oct 25, 2005

I Have a situation where a client will want to pay some lump sum amounts of money off a loan and it may be something like $100 per month for 10 months. I have created a table for lump sums payments into which this info would be entered but I need to create an individual payment for the period of time designated ie 10 individual records of $100 each starting in a month and incrementing the month.

I see it being a append query but do not know how to make the append query create 10 instances of the one record based on a field value. I also do not know how to make the Month increment by one for each record ie Mar05,Apr05,May05 etc.

If this is possible any suggestions would be appreciated.

View 6 Replies View Related

Reduce Multiple Records To One

Jun 28, 2007

Hello,

I hope you don’t mind my using this forum as a learning tool. I am running an Access 2007 database. I am trying to create a query from 3 tables:
TblTrainingPrograms containing the following fields
Program Number (unique value)
Program Title
Program Date

TblInstructors with these fields
InstructorName, address, etc.

TblInstructorProgram with the fields
InstuctorID
ProgramNumber(linking this instructor to a particular training)
InstructorNumber(in the case of multiple instructors for the same training, the user may add new records and enter “1”, “2” or “3” here)

I need to run a query that will display even multiple instructor programs in one record, so that Instructor# 2 will appear in another field in the same record rather than having 2 or three records displayed in the datasheet view. I’ve tried several combinations, but when there is only one instructor, the Program does not appear in the query results.
I don’t really know a lot about SQL, but I’m hoping there might be a way to do this that way. Any help would be appreciated.

Thanks in advance,

Pat

View 2 Replies View Related

Multiple Records From Query

Jan 26, 2008

Hi,

I have a query where it will return multiple records. What I want to do is loop through all the records received from the query and delete them from the specific table. I know it is a simple question, but I'm not sure about VB. I know how to deal when a query receives just one record but not multiple records.

How would you implement this?

Thanks in advance for your help!

View 8 Replies View Related

Multiple Records Need The Newest

Feb 1, 2008

I've been searching the forum for an answer and see that there are a lot of knowledgable people out there but didn't find an answer to my problem so I'm hoping someone can help.

I have a table that has PartNo, Unit_Price, Updated_Date. It is filled with every purchase we have made for the past two years, so there are multiple records for the same part numbers with different prices and different dates. I'm looking to pull the most recent dated record for each part number. For example:
PartNo Unit_Price Updated_Date
123 7.89 08/12/07
123 7.91 10/15/07
123 8.02 1/10/08

I would like it to return the bottom record only (and do this for each of the hundereds of part numbers we have) so I end up with a table that looks like...
123 8.02 1/10/08
456 1.52 1/22/08
789 10.59 8/02/06
and so on... :D

Any ideas:confused:

View 7 Replies View Related

Multiple Records From A List Box

May 11, 2005

Ok, I'm sorry if this is somewhere else in the forum, but I can't seem to locate it. Here's my task:

I am writing an attendance program for an Ambulance Company, and I actually have a large chunk of it done. I have a text box for the date and a combo box for the type of activity. I have two side-by-side list boxes that lets the officers select peoples names and put them in the right side list box. The part that I need help with is: How do I add new records to the table that includes each of the members names, but all the same dates and activities.


Ex.:

John Smith 5/10/05 Meeting
Jane Smith 5/10/05 Meeting
Tom Jones 5/10/05 Meeting

I think that i need to write a loop, but my access programming is way out of shape. Any help would be appreciated, ladies and gentlemen

~Damon Vogel

View 4 Replies View Related

Multiple Records Pulled Up At Once

Dec 18, 2005

First, I apologize if this has been answered before. I've tried searching for it and came up empty.

Is there any way to bring up more than one record in a "Columnar" Form? i'm using this database for an archery tournament and what I need is to enter the target number into a search function in the header and call up the 3 or 4 archers on that target to enter "running" scores. Any ideas? If this can't be done in a "columnar" form, is there any other way?


Thank you guys in advance. If anyone knows the answer, it's you guys.

Steve


PS If someone knows of a thread that answers my question, please post the link. Thanks!

View 4 Replies View Related

Editing Multiple Records At Once...?

Jul 6, 2006

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?

View 4 Replies View Related

How Can Update Multiple Records??

Sep 13, 2005

Hello,
I would like to update multiple records in one query.
The scenerio is that i have 3x3 (3 in row and 3 in column) text feilds and i want to update it once, in one query after entering the data in text fileds.
Can anybody help me please......????

Waiting of your quick reply.

View 3 Replies View Related

Insert Multiple Records

Oct 11, 2005

I am working on an electronic catalog for my company. I am populating the database and I'm trying to speed the process. This is what I want to do. This database deals with cars and trucks, I would like to design a form where I can enter:

1989-1995 Chevy Malibu, etc.

Once the form is full and I save the records it will create 7 individual records, one for each year.

1989 Chevy Malibu
1990 Chevy Malibu and so on.

Any ideas?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved