Forms :: Copying Multiple Records From One Table To Another And Other Stuff

Sep 5, 2013

It basically is used to create, manage and track Customers, Parts, Work Orders and Suppliers, payments and employees.You simply create a Customer and then create a Work order that contains a list of parts and labour items.

What I want to do is to create a "Kit" of parts so that the user does not have to select each part individually when they create a Work Order. For example, if I use a mechanics workshop as an example, the mechanic may sell his "Signature 1916cc Engine" that consists of 40 different parts and some other items such as labour and other services. In this scenario, instead of the user individually selecting 40 different parts, all he needs to do is select the Kit called "Signature 1916 cc Engine" from a drop down list and all of the 40 parts and other items would automatically populate the correct Work Order Parts/Labour tables.

I would also like the user to be able to select another "kit" that would either replace all parts and labour records or append to what is already there.

I have changed the form called "Workorders" to have 2 buttons. One called "Load Kit" the other called "Save Kit".If the user presses "load Kit" a form will be displayed that enables them to select a "kit" from a list and whether they want to replace all of the existing parts and labour records or if they want to append to the existing records.

In order to create a new kit, the user can simply manually create a new work order by individually selecting each part and labour item and then pressing the "Save Kit" button. The user will then be asked to provide the short name of the Kit and the long description (I have created table called tKit). The new kit will then be saved.

It appears that the "Entered by" field that is linked to the Employee table must not be blank other wise the Workbook is NOT saved....I have tried to figure out why this is happening but can't!

The Work Order Parts are stored in "Work Order Parts" and the Work Order Labor is stored in a table called "Work Order Labor". I have created 2 extra tables called "tKitWorkOrderParts" and "tKitWorkOrderLabor" that I assume will contain the items that make up the kits..

View Replies


ADVERTISEMENT

Queries :: Copying Multiple Records From One Table To Another

Sep 7, 2013

I'm using Access 2007.I have 2 tables that are identical in structure.The tables are called [Workorder Parts] and [Workorder Parts Temp]. They both have the same structure:

WorkOrderPartID (Autonumber)
WorkorderID(Number)
PartID(Number)
Quantity(Number)
UnitPrice(Currency)
Notes(Memo)
KitID(Number).

[Workorder Parts] has 128 records in in and [Workorder Parts Temp] has 28 records in it that are a small subset of [Workorder Parts] therefore, the contents of the key field column in [Workorder Parts Temp] is the same as in [Workorder Parts].I tried the following SQL but get an error message saying "Cannot Update "WorkOrderPartID" Field not updateable":

DoCmd.RunSQL "INSERT INTO [WorkOrder Parts] SELECT * FROM [Workorder Parts Temp]"

My aim is that I want all of the records from [Workorder Parts Temp]to be copied to [Workorder Parts] and have their "WorkOrderPartID" fields correctly updated with a new value...

View 14 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

Counting Records And Other Fun Stuff

Dec 5, 2005

Hey all,

I am building a database to help my unit in Iraq. Here are the fields I have so far.

Field 1: Date
Field 2: Time
Field 3: Location
Field4: SIGACT (Significant Act)

Well that is the basics look like. I would like to be able to count the number of times a type of SIGACT occured in durring the day, week, and month. I would like to have this come out in a report I could then take to Excel and graph. The SIGACT has a couple different options like IED, and SAF. Thanks for any help you can provide

View 5 Replies View Related

Copying Records To A New Table

Feb 16, 2005

I am trying to help a new employee with an MS Access 2000 problem. The db was created years ago, and the main table (permits) needed some major changes. She created a new db with a table that has all the same fields as the old db, but many new ones as well. We want to now import the data from the old db's "Permit" table to the new db's "Permit" table. All the field names are correct, but we keep getting errors trying to import and export. There are approximately 940 records.

Is there an easy way to do this? Thank you!

Mike

View 1 Replies View Related

Forms :: Copying Data To A New Record - Skip Null Records

Aug 13, 2013

I've created some code that enables a user to copy an existing record to a new record, which looks like this:

Private Sub btnCopytoNewRecord_Click()
Dim Salutation As String
Dim First_Name As String
Dim Surname As String

'Copy fields to variables
Salutation = Me.Salutation

[Code] ....

This code is working fine, until it hits an empty field, and then I get an error message: Run time error '94':

Invalid use of NullIs there some code I can use to tell it to skip any null fields?

View 2 Replies View Related

Help With Msys Stuff And Forms

Aug 26, 2004

I am trying to create a Switchboard of my own that will do everything the database window will, but restrict certain users from accessing certain areas of the switchboard. The only part i am needing assistance on is Adding, Deleting, Modifying and Running my database items from the form.

I already have used that MSysObjects code to create list boxes for my Tables, Queries, Forms and Reports. Now, I'd like to setup 4 custom command buttons on the form that say:

Code:Private Sub AddTable_Click () [listTables.Value = New Table, prompt user for standard New table wizard]End Sub Private Sub DeleteTable_Click () [listTables.Value = Delete this table (with normal "Are you sure" prompts)]End Sub Private Sub ModifyTable_Click () [listTables.Value = Open this table in Design view]End Sub Private Sub OpenTable_Click () [listTables.Value = Open this table in Datasheet view]End Sub

Now, I know that this code is by far not the correct code, but I hope it helps you understand what I'd like to do. Basically, I want the button to correspond with whatever is selected in the list box for that item (except for the add button, it really doesn't matter what the listbox says when the add button is pushed). As far as my list boxes go, i'd like 2 functions for it to do:

1. If a table/query/etc. is double clicked, that item is opened.

2. Allows multiple selections to delete. (i.e. the user can select multiple tables and click the Delete button, and all the tables that are selected are deleted.)

Let me know if all of this can be done from my form. It would be great to get all this working by tomorrow. I appreciate all of aspfree's users help. You guys rock!

View 3 Replies View Related

Forms :: Subform To Add Multiple Records To One Table

Jun 11, 2015

Off of the same dB I would like to create a subform which will allow me to enter multiple records to the main table, the one attached to the main form. How to go about creating one?

View 12 Replies View Related

Forms :: Inserting Multiple Records From Form Into Table?

Jun 18, 2014

I have a tabular form with many rows of records. Users add some additional information and now I would like to insert it into a new table.

I tried to use below code and it works but it inserts only first row out of many. So I just wonder how to amend it to insert all data?

Code:
CurrentDb.Execute "INSERT INTO [PO Lines - Table] ([SKU], [SKU Description], [Barcode], [Qty], [Unit Price], [PO Number], [Range])" & _
" VALUES ('" & Me.SKU & "','" & Me.SKU_Description & "',''" & Me.Barcode & "''," & Me.Qty & "," & Me.Unit_Price & ",'" & Me.PONumber & "','" & Me.Range & "')"

View 12 Replies View Related

Forms :: Add Multiple Records At Once To A Child Table From A Form?

Apr 16, 2015

I have an unbound new contact data entry form that adds records to many different tables. I have no problems adding one record to a child table at a time, but one of the things we are collecting is a list of online networks a person belongs to, and I want to be able to stick a "check all that apply box" on my form and have it add multiple records to the child table. Here's the basic set up, I have a parent table with the main contact information with a primary key field "IID", a lookup table with "Facebook, LinkedIn, Twitter..." etc in it with a primary key field "online_id", and a bridge table to link the two which should have multiple entries for IID, one for each online_id. I want to use a listbox (or something like it) that the user can select multiple online networks and then have records added to the bridge table. I can figure out how to add the listbox on the form, what I can't figure out is how to get the values out of the listbox. T

View 13 Replies View Related

Forms :: Select Multiple Records In Subform To Create New Table

May 21, 2013

I need to create some new records based on main form data and a selection of records from a sub form. The main form and sub form have different sources. I wanted to show the source fields in the sub form along with a check box to allow the users to select individual records. The record source for the sub form contains >1000 records, so the user will first enter data in the main form, use filters to find the records he wants to 'assign' to the main form data, click those he selects, then click a command button in the main form to create the record(s) based on the main form data and the selected records from the sub form. The new records will be appended to a new table.

View 6 Replies View Related

Forms :: Adding Multiple Records To A Table Using Main Form And Not A Subform

Sep 12, 2013

I have an existing Main form that has a sub form that the user uses to enter multiple records into a table....it works fine EXCEPT that I need to make it even easier and more intuitive and add a lot of labels. Basically the user selects items from a drop down list that adds items to a Work Order. I need to add some labels to the form to make it more descriptive for the user.

So, what I want to add multiple records using a single main form.

Is is possible to?:

1. simply turn the subform into a single main form? Can this be done by using a Command button or something similar?

2. copy all of the controls etc from the sub form into a new main form and have it all work nicely?

View 2 Replies View Related

Forms :: Search Multiple Fields In Table And Open Matching Records For Editing

Dec 10, 2014

Create form to search multiple fields in table

Return records that match search

Open the record that you want in Form View for editing

View 1 Replies View Related

Modules & VBA :: Copying Records From Linked Table / Each Record Slower And Slower

Jul 30, 2015

I have an access db with a linked table (to Sharepoint) which contains about 15000 records. I have to copy those records to the local access db. To do this I have a bit of VBAcode. It works, but for each next record that is copied it takes a bit longer. When starting the program the records are copied quite fast, but soon you see it slowing down until after a couple of thousand records it just goes too slow to be usefull, meaning it would take a lot of days to copy all records.

What I now do is let it copy 500 records and exit ms-access completely and then start it again and let it copy the next 500 records. This works but is tedious as I have to do it about 35 times. Why this gradual slowdown occurs?

The VBA code is pretty straightforward.(I use VBA because I do a bit of data wrangling while reading in the values of the records)

Code:

Dim importdb1 As DAO.Database
Dim rsimportdb1 As DAO.Recordset
Dim i As Double
Set importdb1 = CurrentDb
Set rsimportdb1 = importdb1.OpenRecordset("2015")
Set rsMain = CurrentDb.OpenRecordset("tbl_import")

[code]....

View 3 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

Copying Records Based Off Of Conditions

Jul 8, 2006

Hello. Can this be done?

I am creating a mini scheduling program, and I have one table that handles all requests (tblRequests), then a second table is the final schedule (tblFinalSchedule). On the table for the requests, I have a field called "locked" that is of type yes/no. The purpose of this field is for a meeting time that is of uptmost importance and that I don't want the program to manipulate it in any way.

Is there a way to program in VBA so that when a button on a separate form is clicked, those records that when the yes/no field is checked (set to true), are copied into the appropriate fields from tblRequests into tblFinalSchedule?

View 1 Replies View Related

Modules & VBA :: Split Table Record Into Multiple Records / Rows In A New Table

Nov 10, 2014

In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.

the data has a fixed part lets say

Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this

Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...

What is the best approach?

View 3 Replies View Related

Modules & VBA :: Generate Multiple Records In One Table From Single Record In Another Table

Sep 20, 2014

I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.

I have one table with the following field and data:

ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5

I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:

ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green

The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.

View 8 Replies View Related

Modules & VBA :: Parent Table Revision History - Insert Multiple Records Into Child Table

Sep 8, 2014

I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.

The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.

This is what I have so far:

Code:

Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String

[Code] ....

When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.

View 14 Replies View Related

Modules & VBA :: Copying File To Specified Folder And Updating Records With New Location?

Jan 28, 2015

I'm currently working on a database to track and inventory documents in my organization similar to a library catalog, but with a digital archive as well. I'll need the ability to "attach" documents to records in the main inventory (though using links instead of storing it in the db) and have the documents live in a centrally accessible location on our shared server.

I will need to have this function work through a few different tables and in a few different iterations, but I'm trying to work on the simplest part first, which is attaching a thumbnail/preview of a given document to the record for that document.

I've got some VBA working well which opens the file picker and copies the file to a directory relative to the DB. It also creates a folder with each employee's ID number if it doesn't exist already (which it gets from the Windows log in) and adds "Copied_" to the front of the file.

Now I'm trying to get the VBA code to interact with the record. Presumably I need to tell the form button I'm using to launch the VBA code to pass the primary key of the record being displayed to VBA. Then, at the end of the VBA code I need to take that value and find the record again from VBA so I can update the MediaThumbnailLink in the original record.

Does this order of operations make sense? If so, how should I go about passing these values back and forth and writing the new link value?

Here's the VBA I'm using so far...

Code:
Option Compare Database
Public Sub GoCopyThumbnail()
Dim fDialog As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim varFile As Variant
Dim LUser As String

[Code] ....

View 2 Replies View Related

Tables :: Normalize Existing Table Containing Data And Copying PK Value To FK In New Table

Nov 21, 2014

I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.

After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.

I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"

I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.

View 6 Replies View Related

VBA Stuff Up

Oct 16, 2006

In trying to write code to automatically hide database window I have completedly stffed up and now have code that closes the database window everytime I startup, hence closing the database.

Does anyone know how to disable VBA or somehow keep the database open??

View 1 Replies View Related

Wierd Stuff..

Oct 25, 2005

Recently I've been having a problem with Access (2003) that I can't seem to get around. If I click on "NEW" to create a new query, do some editing, then go to close, it prompts me as to whether or not I want to save, which would be ok, if it would let me choose NO!!

I only get OK or Cancel options. I've NEVER had this problem before.
I have not done any updates added any addons. Is there some hidden trick here or some way to get around this cause its really annoying.

TIA,
Tim

View 3 Replies View Related

Appending Multiple Records To A Table

Sep 11, 2005

Hi,

can someone please help me with this problem.
currently im working on a student management system with MS ACCESS. Im stuck on the create_assessment_form that allows you to query previous assessment results or to create a new assessment. It has a subform that shows the assessment results of all the students relating to a selected assessment_ID.

The query of the results are working properly,
But I need everyone's help for the creating new assessment part.....

When I create a new assessment, I have to choose a course_ID the assessment is relating to.
>>>Question<<<
How do I make it so that it will generate a list of assessment_results record for each of the student relating to the new assessment?


maybe my explaination is a bit messy. Please let me know if you do not understand my question. But I really need an answer to this question urgently!!!

Thanks in advance...

View 3 Replies View Related

Inserting Multiple Records Into Table At Once

Mar 4, 2015

How does one get multiple records inserted into a table at once, when you press the "Add Record" button? Resulting in the table below:

MAKE
MODEL
GAS / ELECTRIC
COUNT
YES / NO

Honda
Accord
Electric
1
YES

Honda
Accord
Gas
3
YES

Honda
Civic
Gas
0
NO

Ford
Mustang
Gas
4
YES

This is how it must be setup, all I need is to know how.

View 5 Replies View Related

Add Multiple Records To Same Table From Subform

Feb 7, 2013

I've only started using Access 2010 since the beginning of January and have googled almost every problem to date.

My db is used to show the status of material through a manufacturing process. We start with one slab of material which gets cut up in to many parts.

The db works so far however I've come to the conclusion that the date was not normalized correctly. So I've created the following tables to fix this.

I have two tables the first of which holds data on the slab we start with and the second shows the status of the parts it has been cut up into.

Table 1 has the following fields

Cast Number
P/O
UST Status
UST Comment

Table 2 has the following fields

Cast Number
Blade ID (which will be 1 to 32)
Status
Comment

I would like table one to be displayed on a form with all fields. Have table two as a subform on the main form. But here is where I get lost!

I need the subform to show 32 text boxes to represent each part. Have each box assigned a default blade ID (1 to 32). Then depending which of the 32 text boxes get used to update those multiple records within Table 2.

I did have 32 status fields for every part but realized that meany were left null. I'm using, Access 2010 with Win 7...

View 5 Replies View Related







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