Lookup Table with Sub routines

[furnitureheaven]

Hi,
I am still new in access, although I have asked so many questions in this forum but still don’t understand how things work. Any way now I am again stuck in the following project. Details are

I have 2 tables. Table A contain fields (Type, Refno, Fname), Table B got fields (Type and RefNo). In a table Type is listed 1 to 9 and against these type users have a reference number.

I also have a form. I want when user select a Type on a form and move a cursor to next text box then it update table B (RefNo) against that (Type) and bring Table B (updated RefNo) to a Form RefNo text box. And when user fill all text boxes on the form and click on add/save button all values must save on Table A.

I tried but fail, so I am going to attach a project as well so you could get better idea. I know I am not a very good describer.

Thanks.

 

[furnitureheaven]

What happened, Program guru don't find the solution. I can't believe this. Some body must have a solution.

 

[Pat Hartman]

RefNo does not need to be stored twice so it should be removed from tableA. If you want to see both type and refno on your form, create a query that joins the two tables and use that query as the RecordSource for the form. When you use this method, it is important to set the locked property to yes for "lookup fields" because they should not be updated from this form. So, Type should be selected by choosing from a combo and Access will automatically populate RefNo. Just make sure to lock refNo so that it isn't accidentally changed.