Query Table Values in Append Query

[AKQTS]

I have the following Query:

INSERT INTO Job_Materials ( Part_Number, Order_Number, [Lot Number], Tran_Date, [MFG Lot] )
SELECT [Enter Part Number:] AS Expr1, [Forms]![Reject_Record]![Order_Number] AS Expr2, [Enter Lot Number:] AS Expr3,
Date() AS Expr4, [Forms]![Reject_Record]![userchar2] AS Expr5;

Basically whats going on is the user clicks a button on a form, which runs this "add part" query. The query obtains values from the current form (Order Number and MFG Lot) as well as values that are prompted for entry by the user (Part Number and Lot Number).

What I cannot seem to figure out is:

This append query is updating an Access database table (Job_Material)

Bascially Expr1 from the query above = dbo_part.partnum (a different table value)

I would like this query to also update the Access database table (Job_Material) with the part description from (dbo_part.partdesc) based on the value entered by the user for Expr1.

This avoids the user entering the part descriptions manually.

How can I accomplish this?



Thanks

[jzwp11]

I would like this query to also update the Access database table (Job_Material) with the part description from (dbo_part.partdesc) based on the value entered by the user for Expr1.

Having the description for the part in two tables is redundant and is not a good database practice. If you want to display the part number and its associated description along with info from the Job_Material table you would use a query that makes the join between the two tables and then you can select the fields you need to display from both tables.

Out of curiosity, why don't you have your users supply all of the information using controls on the form rather than supplying some and then prompting for others? I would recommend a combo box so that the user can select the part (and you can include the description there for them to see) and use textbox controls for all of the other info.

[AKQTS]

When I link multiple tables toghether in the source query for my form, the form becomes uneditable. Making this have to be sourced from a single table. If someone could solve why this happens it could fix more than one of my problems.

Also, the user would have to select from 1 of 10,000+ descriptions and parts so a combo box will not work. Hand keying would work, but i am trying to take as much input off the user as possible. Some descriptions get fairly lengthy and there are not barcode scans for them.

[jzwp11]

Yes, when you join certain tables, the resulting dataset may become unupdateable that is why you generally use a form/subform setup. The table on the one side of a one-to-many relationship would be the record source for the main form while the table holding the many related items would be the record source for the subform.

Also, the user would have to select from 1 of 10,000+ descriptions and parts so a combo box will not work. Hand keying would work, but i am trying to take as much input off the user as possible.

If you categorize your parts, you can use the cascading combo box technique to filter the list down based on the user's choices.

Based on your original post, I assumed that the form you were working with was not bound to a table or query.

Also, I'm a little unclear about your table structure since you have both mfg_lot and order number in the job material table. If there are many manufacturing lots for a particular part then you have to make that relationship first. And then tie that to the order.

[AKQTS]

In hindsight I would of liked to do an order and part table. The form is bound to a query and when I query from more than one table it makes the recordset uneditable.

I think the quick fix is to have the user enter the part description. They will rarely use this feature as it is.

[jzwp11]

If the user enters a part description it must match exactly what you have in the table. You will have to probably have certain conventions in your part descriptions and of course no spelling errors otherwise your users will get totally frustrated. Trying then to have part descriptions in two tables becomes even more of a nightmare as they must match exactly in two tables.

In hindsight I would of liked to do an order and part table.

I'm not sure what you meant by this exactly, but based on what you have described, your application requires it. As such, it does not sound like your tables are structured correctly for what you want to do. That really needs to be corrected before you go further. If you don't, I think you will have constant issues that will cause you a lot of headaches.