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 RepliesI 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 RelatedI 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?
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...
I have the following tables:
tblProjects
ProjectsID (Primary key - exclusive)
ClientProjects
tblProjectsDetails
ProjectsDetailsID (Primary key)
ClientProjectsDetails (Secondary key)
tblQuotes
QuotesID (Primary key - exclusive)
ClientQuotes
Service
Rate
tblQuotesDetails
QuotesDetailsID (Primary key)
ClientQuotes (Secondary key)
Service
Rate
I have the following forms:
fmProjects
fmProjectsDetails (parent/child)
fmQuote
fmQuotesDetails (parent/child)
When I add a project I need to have data from the tblQuotes and tblQuotesDetails to populate tblProjects and tblProjectsDetails respectively.
It is very easy to populate tblProjects from tblQuotes since it is a single record. Therefore I use:
Me.ClientProjects = Me.ClientQuotes
:confused:
The question is: how to match the data of the 2 subforms? Since there will be more than one record per subform?
Any help is really appreciated.
I have a Sales Order form that is used to input sales orders. the main form is comprised of customer and shipping information that is created from a query based on a customerOrder table while the subform is comprised of product information that is created from a query based mainly on the CustomerOrderDetail table.
I need to be able to put multiple products in the subform as a customer can order many items, when I save and close the form I look in the customer order table and see only one record for that sales order while seeing all the records in the detail table for all the products ordered for that sales order as I should but the problem I run into is the query that I have for the sales order shows multiple records for every product that was ordered on that sales order and shows up multiple times in my sales order maintenance form when I only want to see the one. if I change the query to a total query that fixes that problem and only see one copy of the sales order but makes the forms un updateable which is no good.
I cant figure out what I did to make this happen as I have a purchase order form that is setup the same but doesn't have this problem and works great, no matter how many items I have in the subform I only see one purchase order in the query and in the maintenance form.
I am trying to design a subform to allow multiple records to be entered and then uploaded to a table. I've designed the subform but it is currently pulling all of the records from the table through and all I want is a blank subform for data entry.
I have tried putting a macro in the On Load and On Open events to get it to go to a new record but I keep getting error number 2046.
Form: field taken from various related tables
I am trying to build a database. And, I want a form that pulls in info from different tables. I build this form from scratch only to discover afterwards that this form would not show any records since fields are from more than one table.
So next, I played around and created the form the same way but with info from only table "POInfoDDDT1"; since the fields are filled, the form showed the data nicely. As soon as I include the fields projectName and ProjectType from "CCCInfoT1," form does not display any data. But, those tables are related! The design view of the form has option like "Add existing field" and within that there is a link "show all tables" or "related fields" but if Access does not allow to show all info together, then what is the point of this function? I don't want any subform visible.
So, basically POInfoDDDT1 and CCCInfoT1 will be prefilled which means the top two sections of form will be filled. The user will fill out the info on the bottom, which comes from two separate tables. All project info comes from "CCCInfoT1" and Service info comes from "POInfoUserT2." The user needs to be able to see those top info in order to fill the bottom section. So, when user puts in info, those info would be saved to these two different tables.
How to achieve this structure and functionality of the form?
I have a table that has the list of "Project design" choices, and I enter in there the choices that a project can be. I then have that table related to a junction table that has the "Project design choices" linked to the "project code." Anyway, I THOUGHT that one of the bonuses to using Access was that if you see something spelled wrong, you could fix it in one table and it would fix it everywhere. However, when I see that I spelled something wrong in "TBLProjectDesignChoices" and I want to fix it, it tells me that I can't because it contains related records.... ok so, if this is bad design.. I may have to leave it because I have spent countless hours doing data entry for this..
View 8 Replies View RelatedI have a form with a subform. In the form header I have combo boxes for the user to filter data. I want to add a button that they would click to export the filtered records to a table. I have the following code and get the message that records were exported successfully but when I open the table they are not there. I don't get any errors when I compile the code but I also don't get any results in the immediate window either.
Code:
Private Sub cmdAddToTable_Click()
'Dim tmpRS As DAO.Recordset
'Dim tmpSQL As String
[code]...
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 & "')"
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 RelatedIt 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..
I have a query which looks for like * surname*
in tblemployee fname lname dept active
this works fine and i can search using a requery button
however as deptartments are stored in tbldepts
when i change the query to retrieve the dept name instead of number directly from the table and i try to change this on the datasheet subform it changes it in tbldepts instead?
how can i change what dept the employee is in (as in change the number in tblemployee - but display the actual name?)
Create form to search multiple fields in table
Return records that match search
Open the record that you want in Form View for editing
Hoping that this is a simple problem :confused:
I have an orders form and subform, ie one order can have several product records with the following firlds
Product
Ordered
Received
At the bottom I have a command button 'ReceiveOrder'
What I want is on click of this button that the received field (that is 0 by default) will equal Ordered.
ie If I had orderd 2 and I hit the button it would change the recieved to 2 as well.
This much I have done with the following code
Private Sub ReceiveButton_Click()
Me.frmReceivingSubform.Form!QtyReceived = Me.frmReceivingSubform.Form!QtyOrdered
End Sub
This work fine if there is only one product, but if I have more than one it will only update the selected record, is there an ammendment I can do that means all the product records on that particular order will update.
I've searched the forum for the answers but can't find the answer, can anyone help,
Thanks
I am currently building a database with the purpose of capturing data about various employees' skills. The idea is that a supervisor can fill out a questionnaire of sorts, with this employee, and give them a rating out of 5 for each skill. I have been working on this for a few days, and I am most certainly not an Access genius, so I have come to a bit of a wall. I know where I want to get, but I don't know how or if it is even possible, to get there.
I want to have a form that has all the questions on it, so each time the supervisor pulls it up they can just tab through and fill out the form. I have a main form with a subform, although this isn't ideal, it is the only way I can think of to even get close to what I am looking for. The main form portion, after a bit of testing, seems to be producing the desired results I am looking for in populating the specified table, but my subform (questionnaire) is just not doing what I had hoped. I know some of my formatting will change, but I just need a way to allow the supervisors to fill out the questionnaire, hit a button that adds all of the information in both the mainform and subform to their respective table(s) (which I know will require multiple records-1 for each question), and then be able to produce reports by employee.
I want various text boxes to autofill with the questionnaire info, and then have a box for the supervisor to input the ratings. I have used a Dlookup for the default property in each of the question and description text boxes in the subform, so they will reference info from a questionnaire table.
Attached Database...
I currently have a report which contains a subform. The master/child fields used to link them are called 'StudentID'.
In the table on which the subform is based, it is possible for multiple records to be associated with a single StudentID: in this case, the subform shows the locations where a student is studying, therefore if a student is based in three different locations the subform will display all three.
This seems to be resulting, when I run the master report, in three identical iterations of the same report being displayed (at least this is the only reason i can see why these duplicates would be displayed; it doesn't happen if a student only has one record in the subform).
Clearly, as a single instance of the report shows all three records in the subform anyway, I don't want to be seeing these duplicates.
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.
I have a sub form that shows me a companies history. This subform is used on 2 different Parent forms. The record source for the sub form uses a criteria that looks at a companyID field on the parent to determine which records to return. My question is how do I have the query criteria depend on which form is currently opened? The criteria would look something like this:
[Forms]![frmCompanyHistory]![txtCompanyID] OR [Forms]![frmCompany_Project_Details]![txtCompanyID]
If I open the form with this criteria, I will be asked for a value in txtcompanyID for whatever parent form is not open. How do I properly do this without making multiple queries that are almost identical?
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?
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.
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.
I have a form and subform. I need to -
1) show the number of records in the subform on the main form
2) count the number of records in the subform where a value [Public] is True
3) count the number of records in the subform where another value [Analyst] is True.
I can achieve the first two by using the following VBA on the Main form current event -
Quote:
Private Sub Form_Current()
Dim rst As DAO.Recordset
Dim lngCount As Long
[Code]....
when i try to get number 3 done I get the same value as for [Public] (using lngCount for both...not surprising really!)
how i can get a count done for [Analyst] = True in the same event?
I'm trying to use the following code to programmatically sort four fields on the continous subform:
Code:
Me.SPlanChange_03_OFFSET.Form.OrderBy = "AOBJ ASC, ORG ASC, AVAILABLE DESC, AGFND ASC"
Me.SPlanChange_03_OFFSET.Form.OrderByOn = True
It appears that the code works partially - the values in the "AOBJ" field are as they be and so are the values in "AVAILABLE" field. The values in the "ORG" and "AGFND" fields will not sort.
Is what I'm trying to do even possible?
So I have a master form, with multiple subforms on different tabs within the master form. This form is not used to look up data, just to only enter data in. In one of the tabs, I have a subform in the form of a data table where the person filling it in can put multiple locations of one dealership. In the next tab, I want these loctions to autofill into the next subform on the second tab in the data sheet. If I were to go with the solution of using an unbound textbox on the parent/master form to refernece the first subform and then have the second subform reference the textbox, how would I go about doing this? Just with the expression builder and conrol source? Am I able to autofill multiple rows of data from one subform to the other for one recond?
View 6 Replies View Related