Create an association (1 Viewer)

[blacktide]

I want to create an association with 3 different cells.

In cell "A" I type in a part number then cell "B" automatically gives the part description and cell "C" automatically gives the shop that part number goes to.

I am sure that this is possible, but I just don't know what to look for. If someone can just point me in the right direction that would be great. Thanks for your help.

Michael

 

[Jimused]

Have your form based on a query that uses the input from “A” as the criteria. Then it will return B & C respectively.

 

[Pat Hartman]

The columns of a table are not "cell". They are nothing like the cells of a spreadsheet. They are properly called Fields or Columns.

You need to create a query that joins the two tables and selects the necessary fields from each table. Then use that query as the RecordSource for your form. When you enter a value in the part number field, the associated fields from table b will automatically populate.

 

[blacktide]

Pat,

I have tried to understand your post and I have researched other posts and I am just not getting the jist of how to make this work. Perhaps you could give me a little more insight.

Mike

 

[Pat Hartman]

1. Open the query builder
2. Add the two tables
3. click on the PartNumber in the OrderDetails table drag to the PartNumber in the part table.
4. Doubleclick on the center of the join line to bring up the join dialog. Change the join type to "select all rows from the OrderDetails table"
5. Select the columns you want from the two tables.
6. Save the query.


When you run the query, you can type a partnumber into the partnumber field and as soon as you tab out of the field, the description field and any other fields you selected from the part table will auto-populate.

Search help for autolookup query for further discussion.

 

[blacktide]

Pat here is what I have done.

I opened my form: Work Order in design view, clicked the properties button. Under the Data tab I selected the Query builder. This brought up a blank query with just the Serial List table so I added the Part Number Association (PNA) Table and two other tables.

I clicked on Part# on the Serial List Table and drug it to Part# on the PNA Table. I double clicked the line that is now joining these two table to bring up the Join properties window. I checked option 3: Include ALL records from Serial List and only those records from PNA where the joined fields are equal anc clicked ok.

I saved my new query and closed it. Now it is the Record Source for my Form.

I close the form properties and go to Form View and I only get 127 of 2900 records to show up and I cannot enter any data nor can I create a new record. So in the Join Properties I choose option 2 and I get 2521 of my 2900 records and I cannot enter any data nor can I create a new record. So I choose option 1 in the Join Properties and I get 2521 of my 2900 records and I can create a new record, but when I type in the part number the fields I need to auto fill do not.

I know that I am still doing something wrong. I have read the Access Help on this and it does not make this any clearer to me. Is there a book that would recommend reading not only for help with the autofill but with the rest of Access? I don't want to keep bothering people on here for answers cause I know how that can be too.

Thanks for your help.

Mike

 

[Pat Hartman]

Your initial question involved 2 tables. Your query now seems to contain 4 tables. While having a 4-table join will not necessarily make a query non-updatable, your risk increases as you add more tables. You need to make sure that the joins between the tables are correct. My guess is that one or more of the tables do not belong in the query. You'll need to provide more detail on the tables and how they are related for further help.

 

[blacktide]

While messing around I added the other two tables and they are not nessecary. The two that I am worried about are the serial list and the part number associaiton. Sorry for the confusion.

Mike

 

[Pat Hartman]

But they are impacting your query. Did you remove them? Is the query working now?

 

[blacktide]

I removed them and I still get the same results.

I close the form properties and go to Form View and I only get 127 of 2900 records to show up and I cannot enter any data nor can I create a new record. So in the Join Properties I choose option 2 and I get 2521 of my 2900 records and I cannot enter any data nor can I create a new record. So I choose option 1 in the Join Properties and I get 2521 of my 2900 records and I can create a new record, but when I type in the part number the fields I need to auto fill do not.

Mike

 

[Pat Hartman]

You need to post your query and tell us which are the primary keys of the tables in the query.

 

[blacktide]

Pat,

Thanks for having patience with me. I don't know what I did different, but I went through and made a new query from the two tables, joined them and now my Department, MFG, and Nomenclature Fields show the data that is in the Table, however the Part # Field has a #Name? in it and the form is now a read only form. I cannot add data to it. It also has a Runtime Error '2105" Which is because I have it setup to open to a new blank record.

I am afraid that I cannot get the file small enough to post it on here.


Thanks,

Mike

 

[Pat Hartman]

You don't need to post the db. Just post the SQL string and tell us about your tables.

 

[blacktide]

All right sorry about that here you go.

AUTOFILL QUERY, SQL VIEW:
SELECT [SERIAL LIST].[CUSTOMER], [PART NUMBER ASSOCIATION].[DEPARTMENT], [SERIAL LIST].[PO/RO_NO], [SERIAL LIST].[W/O], [SERIAL LIST].[SHIP_VIA], [SERIAL LIST].[RECEIVED], [SERIAL LIST].[REQUIRED], [PART NUMBER ASSOCIATION].[MFG], [PART NUMBER ASSOCIATION].[PART _NO], [SERIAL LIST].[SERIAL_NO], [PART NUMBER ASSOCIATION].[DESC], [SERIAL LIST].[TSN], [SERIAL LIST].[TSO], [SERIAL LIST].[TSLR], [SERIAL LIST].[A/C_TYPE], [SERIAL LIST].[A/C_REG_NO], [SERIAL LIST].[POS], [SERIAL LIST].[DATE_REMOVED]

For the Serial List Table the Primary Key is the ID Field and for the PNA Table the Primary Key is the ID Field. Let me know if there is something else you need.

 

[Pat Hartman]

You did not paste the entire SQL string. There is no From clause so I can't see the join. Just open the query in SQL view, copy and paste the string.

 

[blacktide]

Some how the last piece was missing. Sorry. This is all that shows up.

SELECT [SERIAL LIST].CUSTOMER, [PART NUMBER ASSOCIATION].DEPARTMENT, [SERIAL LIST].[PO/RO_NO], [SERIAL LIST].[W/O], [SERIAL LIST].SHIP_VIA, [SERIAL LIST].RECEIVED, [SERIAL LIST].REQUIRED, [PART NUMBER ASSOCIATION].MFG, [PART NUMBER ASSOCIATION].[PART _NO], [SERIAL LIST].SERIAL_NO, [PART NUMBER ASSOCIATION].DESC, [SERIAL LIST].TSN, [SERIAL LIST].TSO, [SERIAL LIST].TSLR, [SERIAL LIST].[A/C_TYPE], [SERIAL LIST].[A/C_REG_NO], [SERIAL LIST].POS, [SERIAL LIST].DATE_REMOVED
FROM [PART NUMBER ASSOCIATION] RIGHT JOIN [SERIAL LIST] ON [PART NUMBER ASSOCIATION].[PART _NO] = [SERIAL LIST].PART_NO;