473,320 Members | 1,977 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query fields will not update after intial Entry

Curben
47
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 #1
35 9895
puppydogbuddy
1,923 Expert 1GB
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.  
Sounds to me like you have the setting "Confirm Action Queries" turned on. Go to the Command Menu, select Tools>Options>Edit/Find Tab>uncheck the box that says "Confirm Action Queries"
Oct 17 '07 #2
Curben
47
Nope, Was unchecked already.

I did check it test, uncheck test and nothing so far.

I have since tested on another database on another PC and i am also having a similiar issue there, so maybe it is just me.
Oct 17 '07 #3
puppydogbuddy
1,923 Expert 1GB
Nope, Was unchecked already.

I did check it test, uncheck test and nothing so far.

I have since tested on another database on another PC and i am also having a similiar issue there, so maybe it is just me.
By the way, the query that you are showing in your post is not an update query or insert query ...it is just a select query. This is not the query that you were talking about it not updating is it?
Oct 17 '07 #4
Curben
47
By the way, the query that you are showing in your post is not an update query or insert query ...it is just a select query. This is not the query that you were talking about it not updating is it?
Yes, this is the query I am refering to. The values will not populate in "real time"

I am trying to get this to a form that will show the data so when a user inputs the ID they will have the rest of the data to make a desision on. they will be able to either overtype the part ID if they do not wish to submit it, or move to the next record. I already have one query that does this for another project.

Expand|Select|Wrap|Line Numbers
  1. SELECT Processed.[Rec#], Processed.[Part ID], Processed.LPN, Processed.[DOA/DOI], Processed.Resolution, Processed.[Date Entered], Processed.FW, ItemMasterSynced.DESCRIPTION, ItemMasterSynced.ITEM_COST, ItemMasterSynced.[Source Pole], ItemMasterSynced.Modality, ItemMasterSynced.Category, ItemMasterSynced.Notes, ItemMasterSynced.[Where to send], ItemMasterSynced.Contact, ItemMasterSynced.Address1, ItemMasterSynced.Address2, ItemMasterSynced.City, ItemMasterSynced.State, ItemMasterSynced.Zip, ItemMasterSynced.Phone, ItemMasterSynced.Location, ItemMasterSynced.O1vt, ItemMasterSynced.Reason, ItemMasterSynced.Source, Processed.User, ItemMasterSynced.XelusLU
  2. FROM ItemMasterSynced RIGHT JOIN Processed ON ItemMasterSynced.SEGMENT1 = Processed.[Part ID];
This does the same thing just with alot more Fields
Oct 17 '07 #5
puppydogbuddy
1,923 Expert 1GB
Ok, so all you are trying to do is populate your form, you are not updating anything, you are just displaying information from your table.

1. When you run the query by itself, independent of the form, does it display the data you want?

2. Do you have just a main form, or do you have a main form and a subform?

3. Is the form bounded to the query by referencing the query in the form's record source property?

4. Are the controls (textboxes,etc) bound to the table via their control source property?
5.Do you have any code behind your form setting form and control properties?
Oct 17 '07 #6
Curben
47
I cannot get to the form, as the query will not populate the values in the rest of the feilds in real time.

I should be able to enter the data directly in the query and the query should complete for that value, go to the next record, and do the same, it is not however. when i type the part ID into the field part ID
and go to the next record, the rest of the values remain blank until i manually refresh the query
Oct 18 '07 #7
puppydogbuddy
1,923 Expert 1GB
I cannot get to the form, as the query will not populate the values in the rest of the feilds in real time.

I should be able to enter the data directly in the query and the query should complete for that value, go to the next record, and do the same, it is not however. when i type the part ID into the field part ID
and go to the next record, the rest of the values remain blank until i manually refresh the query
You lost me.
You do not need the form to run the query. Run the query by itself.

What happens?
Oct 18 '07 #8
Curben
47
You lost me.
You do not need the form to run the query. Run the query by itself.

What happens?
The query runs fine, it just will not run in real time which will be needed once the form is attached.

In layman's english, when you enter a value in the first field when the rest of the fields are tied to another table, then once you press enter to lock in the value, then the rest of the query does not run. It WILL run if i close the query and reopen it, or if I switch to design view and back. But the query will not run on entries I make while i still have the query open.
Oct 18 '07 #9
puppydogbuddy
1,923 Expert 1GB
The query runs fine, it just will not run in real time which will be needed once the form is attached.

In layman's english, when you enter a value in the first field when the rest of the fields are tied to another table, then once you press enter to lock in the value, then the rest of the query does not run. It WILL run if i close the query and reopen it, or if I switch to design view and back. But the query will not run on entries I make while i still have the query open.
Ok, I think I see where the confusion is. You really want a parameter query...where you can enter a value and have the rest of the info returned. Let's do this:

1. Place your query in design view
2. In the criteria row of the first field in your query grid, type the following:
[Enter a value]
3. save your query and run it
4. Tell me if that is what you meant.
Oct 18 '07 #10
Curben
47
Ok, I think I see where the confusion is. You really want a parameter query...where you can enter a value and have the rest of the info returned. Let's do this:

1. Place your query in design view
2. In the criteria row of the first field in your query grid, type the following:
[Enter a value]
3. save your query and run it
4. Tell me if that is what you meant.
No, not at all

I want to know why this query wont run in real time, like all the others i have written would

I open the query as datasheet, field one relates to one table, the rest of the fields relate to another table, they are joined by one PartID number to display all from table one, and matching from table 2, so when i enter a value for field 1 on the query, the query returns the values from table 2 that has the corresponding partID. In the past, the query has returned the values as i went to the next record of the query, now the record remains blank for all other values until i close the query and reopen it
Oct 18 '07 #11
puppydogbuddy
1,923 Expert 1GB
No, not at all

I want to know why this query wont run in real time, like all the others i have written would

I open the query as datasheet, field one relates to one table, the rest of the fields relate to another table, they are joined by one PartID number to display all from table one, and matching from table 2, so when i enter a value for field 1 on the query, the query returns the values from table 2 that has the corresponding partID. In the past, the query has returned the values as i went to the next record of the query, now the record remains blank for all other values until i close the query and reopen it
Listen, your said before that your query runs fine when you first open it, which I assumed you meant that it displays all your PartID's and related information from the two tables in accordance with your query definition. You then said it is only when you enter a value in field1, that all other columns go blank.

First of all, if field1 is the primary key field, you can not change (update) its value,,,, period. Secondly, if field1 is not a primary key field, and you change its value in any row,the remaining columns related to that row remain unchanged, but they don't disappear. That is the way an updateable select query works,

Now if what you are wanting to do is select a specific partID from the query and see information related to that specific PartID, then the easiest way to do it is to specify a query parameter in the criteria row in the query grid for PartID that will prompt you to enter a specific partID and if it exists, will return all remaining columns related to that specific part ID.

Your parameter in the criteria row of the PartID field would look like this>>> [Enter a PartID]
Oct 18 '07 #12
Curben
47
Listen, your said before that your query runs fine when you first open it, which I assumed you meant that it displays all your PartID's and related information from the two tables in accordance with your query definition. You then said it is only when you enter a value in field1, that all other columns go blank.

First of all, if field1 is the primary key field, you can not change (update) its value,,,, period. Secondly, if field1 is not a primary key field, and you change its value in any row,the remaining columns related to that row remain unchanged, but they don't disappear. That is the way an updateable select query works,

Now if what you are wanting to do is select a specific partID from the query and see information related to that specific PartID, then the easiest way to do it is to specify a query parameter in the criteria row in the query grid for PartID that will prompt you to enter a specific partID and if it exists, will return all remaining columns related to that specific part ID.

Your parameter in the criteria row of the PartID field would look like this>>> [Enter a PartID]
I am not trying to change it, I am trying to add to it, and this works in multiple databases that i have built already, and in the databases used by a dozen other people i work with.
Table one is blank and exists for the sole purpose of recording the PartIDs that are being queried, By entering the PartIDs as new records on the query, they are added as records on the Table and will then allow me to look up what parts the users have looked at and reviewed.
By adding in the Criteria, all that creates is a popup box to add a single value, and seems unnessecary after having it work in other queries. esspeciially as i will end up needing multiple new records each time

If the query will not run live, then the form will not work as this is what i discovered when my first attempt at a form was not working and the textboxes on the form where not filling with the data either.
Oct 18 '07 #13
puppydogbuddy
1,923 Expert 1GB
I am not trying to change it, I am trying to add to it, and this works in multiple databases that i have built already, and in the databases used by a dozen other people i work with.
Table one is blank and exists for the sole purpose of recording the PartIDs that are being queried, By entering the PartIDs as new records on the query, they are added as records on the Table and will then allow me to look up what parts the users have looked at and reviewed.
By adding in the Criteria, all that creates is a popup box to add a single value, and seems unnessecary after having it work in other queries. esspeciially as i will end up needing multiple new records each time

If the query will not run live, then the form will not work as this is what i discovered when my first attempt at a form was not working and the textboxes on the form where not filling with the data either.
Ok, if you are trying to add a new record, that is different. Generally, a form with the allow additions property set to yes and bound to the query is used to add new records. Adding new records via the query itself requires what is known as an "append query", which is generally used to append a batch of records at the same time.

So, once you create your form, set its allow additions and allow edits property to yes and make the form bound (the form' recordsource references the query) to the query, you will be able to add or edit records the way you want.
Oct 18 '07 #14
Curben
47
Ok, if you are trying to add a new record, that is different. Generally, a form with the allow additions property set to yes and bound to the query is used to add new records. Adding new records via the query itself requires what is known as an "append query", which is generally used to append a batch of records at the same time.

So, once you create your form, set its allow additions and allow edits property to yes and make the form bound (the form' recordsource references the query) to the query, you will be able to add or edit records the way you want.
Several access tutorials, my own limited access experience, and the experience of several coworkers all say differently, because that method doesnt allow the use of the query as a lookup as well. And it should be useable from the datasheet view of the query as well, which i and others use in multiple scenarios. The query should lookup up , in real time; the data from the second table. Its not, why?
Oct 19 '07 #15
MMcCarthy
14,534 Expert Mod 8TB
OK firstly to sort out the confusion here.

My understanding of the situation is that if you add a new record to the query the foreign key of the join table doesn't populate so the record in the second table doesn't get added.

Assumption is that both tables have a one to one relationship.

I think I know what the problem is just give me a few minutes to check it.
Oct 19 '07 #16
FishVal
2,653 Expert 2GB
Hi, Curben.

I've read the whole thread and didn't figured out clearly what you actually mean.
Though it may be as simple as the following.
  • ItemMaster.SEGMENT1 is primary key
    [Initial Input].[Part ID] is foreign key
  • the query you've posted
    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.  
    is RecordSource of your form
  • You want to input data to control bound to [Initial Input].[Part ID] and want other controls bound to [ItemMaster] table fields to be automatically populated with the values of record where ItemMaster.SEGMENT1 = what you've entered.
Am I right?
Oct 19 '07 #17
MMcCarthy
14,534 Expert Mod 8TB
OK firstly to sort out the confusion here.

My understanding of the situation is that if you add a new record to the query the foreign key of the join table doesn't populate so the record in the second table doesn't get added.

Assumption is that both tables have a one to one relationship.

I think I know what the problem is just give me a few minutes to check it.
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.
Oct 19 '07 #18
NeoPa
32,556 Expert Mod 16PB
I can't help feeling you guys have got into some miscommunication 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.
Oct 19 '07 #19
Curben
47
Hi, Curben.

I've read the whole thread and didn't figured out clearly what you actually mean.
Though it may be as simple as the following.
  • ItemMaster.SEGMENT1 is primary key
    [Initial Input].[Part ID] is foreign key
  • the query you've posted
    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.  
    is RecordSource of your form
  • You want to input data to control bound to [Initial Input].[Part ID] and want other controls bound to [ItemMaster] table fields to be automatically populated with the values of record where ItemMaster.SEGMENT1 = what you've entered.
Am I right?
This Sounds about exactly what I am looking for.
Oct 19 '07 #20
Curben
47
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
I can't help feeling you guys have got into some miscommunication 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 Expert Mod 8TB
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 Expert Mod 8TB
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,556 Expert Mod 16PB
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 Expert Mod 8TB
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 Expert Mod 8TB
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 Expert Mod 8TB
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 Expert Mod 8TB
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
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
Rabbit
12,516 Expert Mod 8TB
Never trust the user.
Oct 19 '07 #31
FishVal
2,653 Expert 2GB
Essentially the problem was my foreigh key was set to "No Duplicates" which isnt allowed in access for an autolookup query.
After all the problem appears to be one-to-one relationship.
:)

Best regards

Fish
Oct 19 '07 #32
NeoPa
32,556 Expert Mod 16PB
Never trust the user.
LOL - you'll probably get as many posts identical to this as there are experts that see the choice :D
Oct 19 '07 #33
Curben
47
Never trust the user.
the user is a manager so I am sure i have even less reason to trust ;)

As it is once i am done making things functional, i will be going back over all the forms and locking what i can so he (or anyone else who ends up needing access) cannot break things.
Oct 22 '07 #34
Curben
47
LOL - you'll probably get as many posts identical to this as there are experts that see the choice :D
True, I already knew that, but the difference is between, get it done with and hope, or spend the time to finish it up and do it right, and with the deadline, i may go with the lazy option till i can come back to it
Oct 22 '07 #35
NeoPa
32,556 Expert Mod 16PB
Good answers - but be careful ;)
Oct 22 '07 #36

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

Similar topics

2
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...
3
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...
7
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 ...
1
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...
5
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
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...
0
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...
9
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....
7
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...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.