Forms :: Regular Form From Multiple Table Without Subform
Jun 26, 2015
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?
View Replies
ADVERTISEMENT
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
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
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
May 2, 2015
I'll simplify this form to a
- textbox
- command button
- subform (showing a table with 4 fields, including an "EntryID" field but just one record)
What I'm trying to accomplish is to enter a value in the textbox (this value will be one of the EntryIDs in the EntryID field), click the command button, and the subform should refresh itself, showing the record from the table (the 4 fields) which has the same EntryID.
View 2 Replies
View Related
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
Jun 26, 2013
I am attempting to create a search form where a user can search by either employee name or company name. I have 5 tables to use. Is there a simple way of creating a search method for this? I would like to be able to have the user type in a name and click a button that says search with any records matching the search come up. However, I could definitely use a method where they type it in a box and it finds it as the person types.
How to search from all five tables at the same time.
View 3 Replies
View Related
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
Jul 4, 2013
i'm creating a database which holds all animals. i'm trying to make the parents selectable in a subform based on the same table as the main form - this doesn't work for whatever reason ("table is already opened exclusively by another user,..."). it can't just be one field or a query, it needs to be a subform because besides name and eartag i also want to display a small picture of the parent and only the filename is being stored.
i'd really like it to be selectable records in a continuous subform, so the risk of selecting a wrong id is minimized. is there a way around this not being able to use the same table for a subform?
View 14 Replies
View Related
Jul 15, 2013
I have a form, a subform and a master unique table. I need the unique table to updated in the following manner: Form has certain fields that need to be assigned to every record created in the subform[id] and [info1] Subform has a unique [caseid], and contains further [info2] but needs to be bound into [id] in the main form. Example of the master unique table:
id info1 caseid info2
1 aaa 1001 asd
1 aaa 1002 dfg
2 bbb 1003 fff
3 ccc 1004 ffg
3 ccc 1005 ggh
I've managed to accomplish this with a linked table&subform structure, but the master unique table looks like crap because it collapses the subform values into sub-records in the master table when the [id] is similar. The data is in the rigth place, i just want it not to be collapsed/expanded, but every unique recors shows in the table in the regular format
View 8 Replies
View Related
Jul 3, 2014
I'm fairly new to access and have been tasked with fixing a database that to my knowledge is built incorrectly but due to budget constraints and the time it would take to build a new one, we have to stick with this one. Here's my problem, in the database is a form that we use to create new entries in a table but when I try to create a new entry through the form, it creates multiple entries in the table with the rest of the data split between all the new table entries. Is this something that is caused by us moving over to the most current version of Access? Is there a way to fix this problem given the software we are using? At one point I had recreated this table because we had been having issues with other aspects of the database (security deposits were not being updated when entered through this form).
View 14 Replies
View Related
Feb 4, 2015
I am having a problem with calculated fields that are populated on the main form from the subform not saving to the table the main form is linked to.
I created a Purchase Order table that has information on what emplyee created the record, reason for order, customer information if its ordered for a customer, creation date, eta date and notes while the Purchase Order details table is for information on the supplier, product item number, product name, quantity and cost. they are linked by the Purchase Order number which is the primary key in the Purchase order table. it all looked good so i made the form with the Purchase Order Detail as the subform, i got the form all working great and how i wanted it but then i decided i wanted the supplier name and the subtotal of the cost in the main form populated or calculated by the values in the subform.
I copied the fields i wanted populated in the main form to the subform footer and added the calculation for the subtotal then added the formula to the source code on those fields in the main form so the values would populate. it all worked great on the form and the values populated as they were supposed to so i saved the record and went to look at the tables and i found that while the values in the calculated fields that I populated from the subform showed up in the form they didnt save to the table while the non calculated fields saved fine. i did some experimenting and found that if i delete the code and put the data source back to the table in the form and just type something in the text box it would save to the table but not if i had the code in and let it populate.
an example of one of the codes i used to populate the data in the main form is:
=[frmNewPurchaseOrderDetails subform2].[Form]![txtsubfrmSupplierID]
did i mess up in the code or did i do something to the relationship between the form and the table?
View 3 Replies
View Related
Sep 7, 2006
Here's my query.SELECTCheckFieldSheet.[Company Name] IIf([CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 1 *] Or [CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 2 *] Or [CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 3 *],1,(IIf(IsNull([CheckFieldSheet].[Phone Number 1 *]) And IsNull([tbl_clients].[Phone Number 1 *]),1,0))) AS Number1,CheckFieldSheet.[Phone Number 1 *], tbl_clients.[phone number 1 *], IIf([CheckFieldSheet].[Phone Number 2 *]=[tbl_clients].[Phone Number 1 *] Or [CheckFieldSheet].[Phone Number 2 *]=[tbl_clients].[Phone Number 2 *] Or [CheckFieldSheet].[Phone Number 2 *]=[tbl_clients].[Phone Number 3 *],1,(IIf(IsNull([CheckFieldSheet].[Phone Number 2 *]) And IsNull([tbl_clients].[Phone Number 2 *]),1,0))) AS Number2, CheckFieldSheet.[Phone Number 2 *], tbl_clients.[phone number 2 *], IIf([CheckFieldSheet].[Phone Number 3 *]=[tbl_clients].[Phone Number 1 *] Or [CheckFieldSheet].[Phone Number 3 *]=[tbl_clients].[Phone Number 2 *] Or [CheckFieldSheet].[Phone Number 3 *]=[tbl_clients].[Phone Number 3 *],1,(IIf(IsNull([CheckFieldSheet].[Phone Number 3 *]) And IsNull([tbl_clients].[Phone Number 3 *]),1,0))) AS Number3, CheckFieldSheet.[Phone Number 3 *], tbl_clients.[phone number 3 *], IIf([Number1]=1 And [Number2]=1 And [Number3]=1,1,0) AS RejectFROMtbl_clients INNER JOIN CheckFieldSheet ON (tbl_clients.[company name] = CheckFieldSheet.[Company Name]) AND (tbl_clients.[Region Name] = CheckFieldSheet.RegionName) AND (tbl_clients.[Site Name] = CheckFieldSheet.[Site Name]) AND(tbl_clients.[Plot Number/name] = CheckFieldSheet.[Plot Number/Name]) AND(tbl_clients.[Site Code] = CheckFieldSheet.[Site Code])WHERE(((CheckFieldSheet.Returns)="Wrong Number"));Three expressions (Number1, Number2 and Number3), make up 'Reject'If all three expressions are 1, then reject = 1.Any other combinations will make reject = 0.When i view the queery as it is up there, it works fine and shows everything absolutely perfectly. When I want to view all records where 'Reject' = 0, it asks me what the expressions are. (see attachment)I have no idea why this is happening. Any light on why, and how to resolve it would be sweet!Thanks.
View 4 Replies
View Related
Apr 11, 2014
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?
View 5 Replies
View Related
Sep 5, 2007
I have a table with a text field (named Description), which may or may not contain an "ID Number", which is of the form [CHSV]#[A-Z][A-Z][A-Z]## - NB this is not necessarily at the beginning or the end, and will vary between records. For e.g.
"something C4CLE01"
"something H5STT02 Something else"
What I would like to do in a query is to extract just the ID number part and put that in a new field e.g.
"something C4CLE01" => "C4CLE01"
"something H5STT02 Something else" => "H5STT02"
So far I have considering LIKE, which is no good because it doesnt tell you where in the string the regular expression is, and INSTR doesnt work with regular expressions AFAIK.
Added bonus difficulty - I use the database as an external source for an Excel pivot table. I created a function (which I named JOBID) in VBA to do the above job easily enough i.e. I added a field "JOBID([Description])", but when I try to use the data in said pivot table, it says "Undefined function JOBID in expression". I have tried copying the function to the Excel workbook, but that doesn't work.
Im using Office 2003 if that makes any difference. Thanks in advance.
View 5 Replies
View Related
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
Aug 5, 2005
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.
View 2 Replies
View Related
Feb 4, 2014
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?
View 3 Replies
View Related
Feb 17, 2015
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.
View 2 Replies
View Related
Feb 10, 2015
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?
View 1 Replies
View Related
Mar 27, 2015
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.
View 2 Replies
View Related
Jul 1, 2014
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
Jun 1, 2012
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...
View 14 Replies
View Related
May 14, 2013
On my main form, I have a subform to input multiple dates, using date picker.
I also have a text box on the main form to input single dates, again using Date Picker.
On entering both the single date text box and the subform, I don't want the (default) date to be visible. I only want the date to be visible once I have selected a date from the Date Picker.
I have figured out how to do this with the single date text box. I have simply defaulted the forecolor to be white so that you can't see the font against the white backcolor. Then on the Change Event, I set the forecolor to be black. Works like a charm.
However... This same approach will not work with the subform. I've tried playing around with a bunch of different events, but so far nothing allows me to re-create what I am able to do with the single date text box on the main form.
View 2 Replies
View Related
Oct 27, 2013
I wanted to build a dynamic search form using text box instead of the common combo box type.
I found an example that used the combo box and the searching portion of the code is as followed:
Code:
If Nz(Me.txtID, "") > "" Then
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "CategoryID = '" & Me.txtID & "'"
bFilter = True
End If
How to insert (Like "*" & Me.txtID & "*") into the code to make the dynamic search using text box possible.
View 1 Replies
View Related
Jun 14, 2015
I have a problem printing a Subform that uses multiple criteria(in textboxes) as filters.
The search portion of the form works fine. The problem is I have created a report based on the subform and am using the following code to open/filter the report
Code:
Private Sub PrintBtn_Click()
Dim strCriterion As String
Dim strMsg As String, strTitle As String
[Code].....
View 3 Replies
View Related
