473,770 Members | 6,091 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query fields will not update after intial Entry

Curben
47 New Member
Hello all, two days of websearching and I cannot find an answer yet.

Reasonably new to access development.

Access 2000 DB using access 2003 on windows XP

I am having an issue with creating a query that I can enter the Part ID as value and hvae the description, cost etc. populate in the rest of the query.

I have two Tables; ItemMaster and [Initial Input]
[Initial Input] has one Value: [Part ID]
and ItemMaster has several but i have limited to Segment1, Description, Item_Cost

I want to create a form based on a query that when the Part ID is entered that the query runs and displays the rest of the information. This will later be appended to a table which will be followed up on by another user.

The problem I have is that the query will not update unless saved, closed and reopened, or switch views. So instead of Entering in the one value and the rest of the values filling in automatically, enter just goes to the next blank value.

I have done this several time before and they have all worked. One item that may make a difference is i just transfered to a new PC and attempted to change all the options so perhaps i checked/unchecked something i shouldnt have.

Any help or assistance can be appreciated

Thanks

Expand|Select|Wrap|Line Numbers
  1. SELECT [Initial Input].[Part ID], ItemMaster.DESCRIPTION, ItemMaster.ITEM_COST
  2. FROM ItemMaster RIGHT JOIN [Initial Input] ON ItemMaster.SEGMENT1 = [Initial Input].[Part ID];
  3.  
Oct 17 '07
35 9978
Curben
47 New Member
OK I just went back and looked at your initial query. You have a right join and this is your problem. Your right join is saying give me everything from [Initial Input] table but only those records from ItemMaster table that match. This will not occur until after the record is saved. You can really only add records to a query where there is one and one matching record per table in the query. This is known as a one to one relationship. Normally you can only build a form on this type of query.

In all other circumstances you will need to use Subforms to populate all but the main table.
HMM, I can try that, but i have used this with a one to many relationship before.
User entered in the part id, and a tracking number, since many of the same part ID could come in it was a one to many
Oct 19 '07 #21
Curben
47 New Member
I can't help feeling you guys have got into some miscommunicatio n somewhere (talking at cross-purposes).
I know it's very easily done as I have managed to get myself into that scenario a couple of times myself on here. It's mainly because of the medium involved and the need for absolute precision and accuracy while communicating when most of the clues we normally rely on (visual etc) are absent.

Let me see if I can't have another look through the thread when I get some time and see if I can pick up on anything that might help. I may well need to ask some further questions of the OP (Curben), so please, if I do, be patient with me. I'm very precise and picky, and that seems to irritate some people, but I find it is helpful when dealing with technical things that are easily misunderstood.

In the mean-time, could I ask you to post the meta-data of the two tables you're using. Relevant fields only required. All linking fields (to the other table particularly) are relevant of course. Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
**Edit**
Forget this - I was preparing this while others nipped in and posted. Mary's solution may be all you need. Let us know how you get on.
Is there an easy way to pull that, or will i need to manually type it in, if its manual it will take me a bit but i can provide
Oct 19 '07 #22
Rabbit
12,516 Recognized Expert Moderator MVP
You can have a one-to-many relationship.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Initial Input].[Part ID], ItemMaster.DESCRIPTION, ItemMaster.ITEM_COST
  2. FROM ItemMaster RIGHT JOIN [Initial Input] ON ItemMaster.SEGMENT1 = [Initial Input].[Part ID];
  3.  
Using this SQL will work if SEGMENT1 is a primary key. And the data type of SEGMENT1 matches the data type of Part ID.
Oct 19 '07 #23
MMcCarthy
14,534 Recognized Expert Moderator MVP
You can have a one-to-many relationship.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Initial Input].[Part ID], ItemMaster.DESCRIPTION, ItemMaster.ITEM_COST
  2. FROM ItemMaster RIGHT JOIN [Initial Input] ON ItemMaster.SEGMENT1 = [Initial Input].[Part ID];
  3.  
Using this SQL will work if SEGMENT1 is a primary key. And the data type of SEGMENT1 matches the data type of Part ID.
I'm not talking about the query Rabbit I'm talking about the form.
Oct 19 '07 #24
NeoPa
32,573 Recognized Expert Moderator MVP
Is there an easy way to pull that, or will i need to manually type it in, if its manual it will take me a bit but i can provide
If you take out the spaces after the "[" characters you can edit this version. You'll need to enter the details as they pertain to your tables though.
Here is an example of how to post table MetaData :
[ b]Table Name=tblStudent[/b]
[ code][ i]Field; Type; IndexInfo[/i]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time[/code]
Remember, you intelligently select which fields to show so you needn't do the whole table.
This may not require my answer as Mary seems to have put her finger on a point. If it does I'll do what I can. Whatever, meta data nearly always makes a problem easier to understand and work on.

PS. I will give it a look through though to see if I notice anything.
Oct 19 '07 #25
MMcCarthy
14,534 Recognized Expert Moderator MVP
HMM, I can try that, but i have used this with a one to many relationship before.
User entered in the part id, and a tracking number, since many of the same part ID could come in it was a one to many
There is a big difference between using another table as a lookup and actually merging data from two tables.

If you do as NeoPa says and post the table meta data we might be better able to help.

Mary
Oct 19 '07 #26
Rabbit
12,516 Recognized Expert Moderator MVP
I'm not talking about the query Rabbit I'm talking about the form.
This works on a form too.

Table1
ID; FK
Expand|Select|Wrap|Line Numbers
  1. ID
  2. 1
  3. 1
  4. 2
  5. 3
  6. 4
  7.  
Table2
ID; PK
Field2; Text
Expand|Select|Wrap|Line Numbers
  1. ID   Field2
  2. 1   A
  3. 2   B
  4. 3   C
  5.  
Query1
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID, Table2.Field
  2. FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID;
  3.  
Form1 - Query1
Expand|Select|Wrap|Line Numbers
  1. ID   Field2
  2. 1   A
  3. 1   A
  4. 2   B
  5. 3   C
  6. 4
  7.  
It's updateable. You can change either field and add records to Table1.
Oct 19 '07 #27
MMcCarthy
14,534 Recognized Expert Moderator MVP
Yes that works fine as a lookup. But the OP is trying to do the reverse of that unless I'm very much mistaken.
Oct 19 '07 #28
Rabbit
12,516 Recognized Expert Moderator MVP
After some testing, this seems to work.
Table1
ID; FK
Expand|Select|Wrap|Line Numbers
  1. ID
  2. 1
  3. 1
  4. 2
  5. 3
  6. 4
  7.  
Table2
ID; PK
Field2; Text
Expand|Select|Wrap|Line Numbers
  1. ID   Field2
  2. 1   A
  3. 2   B
  4. 3   C
  5.  
Query1
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID, Table2.ID, Table2.Field2
  2. FROM Table1 RIGHT JOIN Table2 ON Table1.ID = Table2.ID;
  3.  
It's updateable. You can change either field and add records to either table as long as you don't try to fill in Table2.ID with a duplicate before filling in Table1.ID, at which point Table2.ID and Table2.Field2 are automatically filled in anyways. It's convoluted and you're better off doing it with a subform but it seems to work ok. If you do put in Table2.ID first with a duplicate value, you have to close the query and reopen it because you get stuck in an error loop.
Oct 19 '07 #29
Curben
47 New Member
OK all thanks for the help, I stumbled across my explanation when i was looking up something else in access help, apparently when i looked up help the first time i used the wrong keywords:

Essentially the problem was my foreigh key was set to "No Duplicates" which isnt allowed in access for an autolookup query.
About AutoLookup queries that automatically fill in data (MDB)

Note The information in this topic applies only to a Microsoft Access database (.mdb).

You can design a multiple-table query to automatically fill in certain field values for a new record. When you enter a value in the join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) field in the query, or in a form, report, or data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.) based on the query, Microsoft Access looks up and fills in existing information related to that value. For example, if you know the value in the join field between a Customers table and an Orders table (typically, a customer identifier such as CustomerID), you could enter the customer ID and have Access enter the rest of the information for that customer. If no matching information is found, Access displays an error message when the focus leaves the record.



Add a new order including the Customer ID, and then press ENTER.

Access fills in the rest of the customer information.

Prerequisites for creating an AutoLookup query

For AutoLookup to work, certain conditions must be met:

The query must be based on more than one table and the tables must have a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.). (Referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) doesn't have to be enforced.)
The join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) field on the "one" side of the relationship must have a unique index (unique index: An index defined by setting a field's Indexed property to Yes (No Duplicates). A unique index will not allow duplicate entries in the indexed field. Setting a field as the primary key automatically defines the field as unique.). A unique index means that the field is a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or its Indexed property in table Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.) is set to Yes (No Duplicates).
The join field you add to the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) must come from the table on the "many" side of the one-to-many relationship. (In other words, it's the foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) for that table.) For AutoLookup to work, this field can't be a primary key and its Indexed property can't be set to Yes (No Duplicates). For example, in a query that includes the Customers and Orders tables, drag to the design grid the CustomerID field from the Orders table, not the one from the Customers table.
The value you enter in the join field from the "many" side must already exist in the join field from the "one" side.
What happens when you update data

When the value of the join field from the "many" side of the relationship is added or changed in a record, Access automatically finds and displays the associated values from the table on the "one" side of the relationship.

You can always update the join field from the "many" side of a relationship, but you can update the join field from the "one" side only if you enabled cascading updates (cascading update: For relationships that enforce referential integrity between tables, the updating of all related records in the related table or tables when a record in the primary table is changed.) when defining the relationship between the tables. Either way, when you update data, Access automatically recalculates any totals or expressions in the query that are dependent on the updated data.
Thanks for all the help, it appears it was all justa setting otherwise the query was right, and now both the query and the form work perfectly as I designed them.

It does leave me with a desision, i can either prevent duplicates and requery when the first control loses focus, or i can entrust that my users will not duplicate data.
Oct 19 '07 #30

Sign in to post your reply or Sign up for a free account.

Similar topics

2
2935
by: lawrence | last post by:
A very strange bug. www.monkeyclaus.org is run by a cms I'm developing. One of types of users we allow is "justTestingTheSite", a type of user I developed to give demo's to prospective clients. The purpose of this level of security is to let someone log in and see everything as if they were root, and yet not be able to update or delete anything, as they have no real priveledges at all. I just logged in as root and created such an...
3
1772
by: JC Mugs | last post by:
Help needed for project-Access 2002(office xp) PROBLEM: Figuring out how to lookup a record and DDate field in Table1 - Take that DDate-field data from record looked up and assign it to Date field in a new record on a form using Table2. BASIC Table1 Fields: DID -Primary KEY DDate --Information I need to move to new form
7
1788
by: Patrick Fisher | last post by:
Hi I have a table which Contains entries with RefCode field containing INVP or INVPD Common fields in each entry would be InvoiceNo, Total and PurTyp for example. You could have 1001 500.50 INVP 1 1001 500.50 INVPID 1 1002 123.00 INVP 1
1
359
by: Jon | last post by:
Hi there. I'm trying to create a query (or two) that I can use to produce a spreadsheet but I'm having a bit of trouble. I'm using Access 2000. My db is set up like this: Table1: Intrusive Manhole Survey Fields: IntrusiveEntryID (PK), ManholeID, Date, Sewer System, Firm, Inspector, Benching Description, Manhole Type, Manhole Diameter, Sump Elevation, Source, Notes.
5
3510
by: Irfan | last post by:
Hi All, I am trying to create a report but having problem with the critiera selection logic, please help. I have the following fields date1 date2 date3
4
1593
by: johnny | last post by:
hi all, I hope it is easier for you to answer than for me trying to explain it... In a database I have some tables , each one has some mandatory fields at the beginning and a couple at the end. In the middle each table can have some additional fields from 0 to n depending on how many fields have been inserted by who created the table.
0
3303
by: phlype.johnson | last post by:
I'm struggling to find the best query from performance point of view and readability for a normalized DB design. To illustrate better my question on whether normalized designs lead to more complex queries yes or no, I have prepared an example. The example is a database with the following tables: *table person with fields: -persid: autoincrement id -name: name of the person *table material with fields: -materialid: autoincrement id
9
3062
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped. Below you will find the code I've written and the error that results. I'm hoping that someone can give me some direction as to what syntax or parameter is missing from the code that is expected by VBA. Overview: I'm trying to copy calculated...
7
1322
by: KC-Mass | last post by:
I have two tables related to sales transactions and assigned areas. The tblSalesArea has SalesAreaID an autonumber StartZip, Text EndZip, Text The tblSales has many fields detailing all the sales. The two fields of concern are ZipCode, Text and SalesAreaID a number.
0
9595
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9432
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10059
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10008
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9873
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7420
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6682
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3974
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3578
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.