Tying tables to forms

I have four connected tables that work well as table input but when I put 
them in a form some of the fields will not let me make entries.  Does this 
happen because I am using the Id fields and subsequent data from the wrong 
tables?
-- 
Taylor
0
Utf
9/6/2007 7:22:04 PM
access.forms 6864 articles. 2 followers. Follow

4 Replies
962 Views

Similar Articles

[PageSpeed] 14

It sounds like you have created a non-updatable form. 

One cautionary note first:
Don't tie your forms directly to the tables.  Use queries instead.  The 
queries will act as a stop light for which data is written and when.  If more 
than one person tries to make a change to the same record at the same time, 
you will run into problems.

From what you have written, you are linking several tables to one form.  If 
the relationships between these tables is not set correctly, Access will not 
know how to relate the information being entered.  That is probably what is 
happening.  

Create a select query that contains the tables with the relationships as you 
think they should be.  Execute that query and look in the lower left hand 
corner.  You may see a note saying that this query is not updatable.  If that 
is case then your relationships are ambiguous.   

PJ

"TaylorLeigh" wrote:

> I have four connected tables that work well as table input but when I put 
> them in a form some of the fields will not let me make entries.  Does this 
> happen because I am using the Id fields and subsequent data from the wrong 
> tables?
> -- 
> Taylor
0
Utf
9/6/2007 9:00:01 PM
On Thu, 6 Sep 2007 12:22:04 -0700, TaylorLeigh
<TaylorLeigh@discussions.microsoft.com> wrote:

>I have four connected tables that work well as table input but when I put 
>them in a form some of the fields will not let me make entries.  Does this 
>happen because I am using the Id fields and subsequent data from the wrong 
>tables?

Probably, but you don't say how you've done it.

If you're basing the Form on a massive query combining four tables... you're
on the wrong track. It's much better to have a Form (for the "one" side table
in a one to many relationship) and one or more Subforms (for the related
"child" tables).

             John W. Vinson [MVP]
0
John
9/6/2007 10:10:14 PM
My tables are:  Organization - Subspecialty - Subspecialty Details (info 
changes yearly) - Customers - Orders - Orders Details.  How would you suggest 
combining tables for queries?
-- 
Taylor


"John W. Vinson" wrote:

> On Thu, 6 Sep 2007 12:22:04 -0700, TaylorLeigh
> <TaylorLeigh@discussions.microsoft.com> wrote:
> 
> >I have four connected tables that work well as table input but when I put 
> >them in a form some of the fields will not let me make entries.  Does this 
> >happen because I am using the Id fields and subsequent data from the wrong 
> >tables?
> 
> Probably, but you don't say how you've done it.
> 
> If you're basing the Form on a massive query combining four tables... you're
> on the wrong track. It's much better to have a Form (for the "one" side table
> in a one to many relationship) and one or more Subforms (for the related
> "child" tables).
> 
>              John W. Vinson [MVP]
> 
0
Utf
9/7/2007 1:40:00 PM
On Fri, 7 Sep 2007 06:40:00 -0700, TaylorLeigh
<TaylorLeigh@discussions.microsoft.com> wrote:

>My tables are:  Organization - Subspecialty - Subspecialty Details (info 
>changes yearly) - Customers - Orders - Orders Details.  How would you suggest 
>combining tables for queries?

I have no way to know, because I don't know the structure of your tables, how
they are related, what real life entities they represent, or how they are
related.

And I did not suggest combining tables for queries. Instead, I suggested using
Forms and Subforms. For example, you could have a form based on Orders with a
subform based on Orders Details. I don't know enough (any!) about your
business to be much more specific though!

             John W. Vinson [MVP]
0
John
9/7/2007 6:38:15 PM
Reply:

Similar Artilces:

A Go To Box in a Form
All I want to do is be able to find a record in the database. Based upon two fields. The help menu says you can put a "Go To" box in a form, but nowhere does it describe how to do that. When you make a new combo box, you can select option #3. That option helps you setup up a record finder based on your choices. Richard To add some to Richard's answer, here is a tutorial that I have that shows you how to implement such a combo box: Combo box to find a record http://www.access.qbuilt.com/html/find_a_record.html Here's a sample that uses a form to...

Custom form for Calendar needs frequent cache clearing
Hi, We have a custom form which our Outlook 03/07 users can enter details of forthcoming change requests. There are 3 tabs on this form normally but we have recently noticed that one of the tabs is intermittently missing. It is easily resolved by manually going to Tools > Options > Other > Advanced options > Custom Forms and then selecting "Clear Cache". The missing tab is then available to the user. Can anyone shed any light on why this cache clearing is required and how we can go about preventing it from being a necessity? Thanks in advance. ...

Pivot table
When I create a pivot table by using wizard, I drag fields to the diagram. For fields contained numbers, sometimes it appears to be sum of xxx and sometimes it defaults to be count of xxx. I know I can change it by double clicking. I'm not sure if there is a way to default it to count or sum. Thanks, Lily If all the data in that field are numbers, then you'll get Sum. If any of the data is text (or blank), then you'll get Count. I don't think you can change this behavior. Lily wrote: > > When I create a pivot table by using wizard, I drag fields to the diagram...

Embedding excel charts in my C# windows form
I'd like to be able to have my C# windows form have access to excel's charting capabilities. I'd like to be able to define a data set, then feed it to eh excel charting engine, and open a new little graphics window to display the resultant chart. Does anyone have pointers on where to start? -- - Matt Kosorok ...

populate form from listbox
I need to be able to populate a continuous form with multiple records from a multi-select listbox. I have done this before. I remember running into a problem where the listbox has to be bound to a table if you want to multi-select. I am getting the error again for this listbox but cannot get around it! Can anyone explain why a multi-select listbox has to be bound? Maybe your explanation will help me understand better. What /how should I bind it what when all I want to do is open a continuous form displaying selected listbox items (records) already in a table? ...

transfer data from one text box in a form to another
how do i transfer data from one text box located on a form to another text box located on a sub-form on the main form. complicated i know. what vba code do i use. any solutions thank you you need to specify if you want to make the operation after which event in subform. then you need to put something like this: [textboxinsubform] = Forms!FormName![texboxinform].text LD -- Lorenzo Díaz Cad Technician "vss85" wrote: > how do i transfer data from one text box located on a form to another text > box located on a sub-form on the main form. complicated i know. ...

yet another: which is faster table or query ?
using A2k; seen LOTS of posts about this topic, but none seem to address the heart of my question. Let's break this into 2 parts: queries (stand-alone); and form data sources (and control data sources) queries - stand alone: Let's say I have a query (#1) that relates information in 3 tables. Now I want another query (#2) that uses that same information, and relates it to more tables. Would query #2 be faster if it uses query #1, and relates that to more tables; OR would it be faster if it had all the table relations in query #1 built into it (not use a query inside a query) ? --- ...

Sequential Form Field Number Formula
I am not sure how to do the following. We have project numbers that are made up of 3 parts "C" (representing Customer), "10" (representing last two digits of year), and then a 3 digit number starting with "000". My issues is I need a default that looks adds 1 to the last 3 digits. So if the Project number was C10000 then the default for a new project should be C10001, C10099 becomes C10100, etc. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201002/1 On Sat, 13 Feb 2010 01:21:54 GMT, "szag...

Access 2007
In Access 2003 I can see the property sheet for a form whether the form is in design or form view. It's handy to have a form open and refer to field names or control sources while designing a new form or report. You can see the property info by clicking on any field on the form. However I am now using Access 2007. I can see the property sheet while I am designing my report, but as soon as I switch over to the form the property sheet disappears for the form. Is there a way to show the property sheet for a form while it is open in form view? Mich ...

I need to populate a sub-form field from the original form
I can't see how to do this. I want to be able to be in form1 and then if I need to use sub-form1, I want to be able to click on the sub-form button and the Primary field be autofilled from form1. For instance, Member ID is the primary key in form1. When I open sub-form1, I want the Member ID to populate in the sub-form. On Fri, 14 May 2010 17:48:01 -0700, Michael <Michael@discussions.microsoft.com> wrote: >I can't see how to do this. I want to be able to be in form1 and then if I >need to use sub-form1, I want to be able to click on the sub-form button an...

Shop Product ?Search form/query Access
I have a form that is based on a query that shows all the products i sell, onthe form i have a search text feild, so i can type in the product then click a button that requerys and only displays the products with that text in, this works fine, but i the form takes a long time to load the first time, is there a way i can have it the form loads with no data then i type in product name then when i clcik the button it loads the data/query. Just looking at ways to speed it up Put the first search field on a separate form where the 'after update' event for the search field opens...

Form/Query Question
In Access 2007, I have a table that has the following columns: First Name, Last Name, Date of Birth, Hair Color, Height, Weight, and Current Location. I want to create a form that I can go into and find all of the individuals that were born after 1980 and with brown hair; or possibly all the individuals with brown hair that weigh under 180. Basically I want to be able to search the table based on inserting the specific parameters I want into a form. And then I want to be able to see a report of all the individuals that meet my parameters to print out. How can I go about this? Y...

Why do inline tables disappear when automaticallyshifting or resiz
Publisher 2003 Scenario: I have a parent table which contains a list of inline tables arranged vertically in one of its cells. Problem: Any time I perform an action that requires one of the inline tables to shift position (ex. adding text above/below it) or automatically resize (ex. adding text within it) the entire page layout disappears. I can cause the layout to reappear by selecting to view the Master Page (View->Master Page) then selecting to hide it. Thank you. Slide the acceleration down on your video adapter, control panel, display folder, settings tab, advanced button, ...

Using subtotals in Pivot table formulas
The set up: Our legacy database provides 30 different report types that our clients can run from our website. I have a spreadsheet listing every report run by customers over the past 2 years; including unique ID#, Client name, report name, User name, Year, Date, Time, etc. The Pivot Table: My pivot table is set up as follows: - ClientName, ReportName, and Username as Row labels - Year as Column Label - Count of ClientName as value - Count of ID# shown as "% of column" as another value The pivot lists ClientName in column A, ReportName in column B, Username in C and the count of Clie...

VLOOKUP & table array selection
I hope someone can help me. Is there a way to select the table array i VLOOKUP from a cell reference? Cause i can't figure out how to do it.: =VLOOKUP(A6,'MY WORKSHEET'!1:65536,27,FALSE) Where MY WORKSHEET would be a ref from A -- Message posted from http://www.ExcelForum.com Hi try =VLOOKUP(A6,INDIRECT("'" & A1 & "'!1:1000"),27,FALSE) Frank > I hope someone can help me. Is there a way to select the table array > in VLOOKUP from a cell reference? Cause i can't figure out how to do > it.:( > > > =VLOOKUP(A6,'MY...

VB6-rotate form 90 degrees?
I have a problem that could be solved more easily if my VB6 form with a text input field and several drop-down controls can be rotated 90 degrees clockwise. (Monitor is being turned sideways - long story - exploring solutions.) Is this possible with Classic VB? Not just to rotate text on the form, but to rotate the entire form so it looks and functions normally? If so, what commands do I need? Ed "Ed from AZ" <prof_ofwhat@yahoo.com> wrote in message news:a7ed093e-38d2-49f2-a606-f5621b34cf0c@i19g2000pro.googlegroups.com... >I have a problem that could be so...

table not updated
i have a form/subform that i have added a new field to, netamt. I added the field to the table, then added it to my query, then added it to my subform that is in my form. The field is set to currency and has =Nz([Quantity],0)*Nz ([UnitPrice],0) as the ControlSource. The number in the subform is not updated in the table? Why is this? I dont need this number to show on the form, just on the Report. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200706/1 "stephendeloach via AccessMonster.com" <u32891@uwe> wrote in message ...

Pivot Table compatibility
I have a pivot table in an Excel XP workbook that several people in our office use. The problem is when this workbook is opened by one of the computers there is a error message saying that one of the sheets has been damaged but Excel recovered it. The sheet damaged is always the pivot table and the recovered version is just a sheet with the formatted data in it but the pivot table functionality is gone. All our computers are running WinXP or Windows 2000 and all have OfficeXP with the latest updates. The only thing really different is the language and regional settings which differ f...

Excel Pivot Tables #3
I have been able to reduce the file size by unclicking the option "save data in pivot table". I can't seem to find an article on the web that will explain to me if I will lose functionality of the pivot table if I turn this option off. Can you help? As answered at your other post: You won't lose the functionality, but it may take longer to refresh the data. There's information in the following MSKB article: How to optimize PivotTable performance in Excel 2000 http://support.microsoft.com/default.aspx?id=273583 Mandy wrote: > I have been able to reduce th...

Junk messages comimg via my website form Please help
A bit long sorry......... I have a form on a page of my website. It gives my possible customers a chance to contact me with enquires or comments. The messages come direct to my email. The problem I am finding is that I am getting messgaes via this well which I think are VERY INAPPROPIATE (if you know what I mean) Does ant one know how I can stop these as I am finding it a little distressing. A customer has also contacted me and said she sent me a messgae which I did not recieve so I am also wondering if there is some kind of possible virus attached. Please help! Naomi Naomi, Hm...

Change quarters in Pivot table when grouping
I want to change the quarters when I do pivot table grouping by dates. Is this possible? thanks! Jugglertwo You don't like the cutoffs that MS uses? If that's the question, then I add an extra field to raw data that returns the quarter (and year). Then use that in my pivottable. I like this format: FY2010Q01 or 2010-01 so that I can sort in nice ascending/descending order. If the quarters start on the first of a month, then I use this formula to show the fiscal year and quarter: ="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MON...

Update Table on another server
I am trying to update data from one server to another server. To run this query takes 20 seconds when trying to update one record. Anyone have ideas on how I can improve/optimize the speed. Currently using SQL Server 2005 I am running this query from Server1: Update [Server2].DB2.[dbo].Table2 Set Title = Table1.Title from Table1 Where ID = tp.PLU Data Sample: Server1:DB1:Table1 (this file is small and contains information on which records need updating) ID, Title 40, 'New Title' Server2:DB2:Table2 (this file is larger (over 20K records)) ID, Ti...

Excel Pivot Table
I have created a Pivot table with the following fields: Customer; Product; Actual; Budget; Variance where 'Variance' is calculated Item within field name 'Version' using formul 'Actual-Budget'. (ie Some records in the raw data have version set t 'Actual' and others have version set to 'Budget'. I am trying t compare Actual and Budget). The problem I have is that when I use thi calculated Item, the results displayed include combination of ever possible Product and Customer even if the data does not include such combination. This means that I have a hug...

Run Queries on Related Tables
Hello, I have 2 Tables in Access(2003), Members and Calls. The two tables are related (via a membership number) so that multiple calls can be made to members and these are all recorded in the Calls table. In the Calls table there is a field called 'Reason' with a limited number of drop- down options. My question is, how can run a query that identifies members who do not have a particular Reason for a Call? Kind regards, SB In a query, join the Members and Calls on the MemberID. Make sure that the join shows all Member and any. Then select the fields that you want t...

I am using VBA, how do you test for new record in a form
What should I test my VBA code so that I know the User is working with record as New in a Form. I am using Access 2007 under Windows XP. I used PrimaryID as an auto generated number, therefore it is Null in the begining that is how I know the user is trying to add a new record and not editing an existing one. So I test for IsNull(PrimaryID) which works if the user do not make mistake but as the user enter more information made mistakes and need to correct the PrimaryID is no longer Null. So my test passed as if the use is editing the existing record when in fact the user is...