Query fields will not update after intial Entry

[Curben]

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

SELECT [Initial Input].[Part ID], ItemMaster.DESCRIPTION, ItemMaster.ITEM_COST
FROM ItemMaster RIGHT JOIN [Initial Input] ON ItemMaster.SEGMENT1 = [Initial Input].[Part ID];
 

[Curben]

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

[puppydogbuddy]

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]

[Curben]

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.

[puppydogbuddy]

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.

[Curben]

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?

[MMcCarthy]

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.

[FishVal]

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.SEGM ENT1 is primary key
  [Initial Input].[Part ID] is foreign key
• the query you've posted
  

  SELECT [Initial Input].[Part ID], ItemMaster.DESCRIPTION, ItemMaster.ITEM_COST
  FROM ItemMaster RIGHT JOIN [Initial Input] ON ItemMaster.SEGMENT1 = [Initial Input].[Part ID];
   

  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.SEGM ENT1 = what you've entered.

Am I right?

[MMcCarthy]

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.

[NeoPa]

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

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
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.

[Curben]

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.SEGM ENT1 is primary key
  [Initial Input].[Part ID] is foreign key
• the query you've posted
  

  SELECT [Initial Input].[Part ID], ItemMaster.DESCRIPTION, ItemMaster.ITEM_COST
  FROM ItemMaster RIGHT JOIN [Initial Input] ON ItemMaster.SEGMENT1 = [Initial Input].[Part ID];
   

  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.SEGM ENT1 = what you've entered.

Am I right?

This Sounds about exactly what I am looking for.