Auto filling fields in a sub-form (Access 2003)

[BigAl]

I'm sure I solved this problem years ago but I don't do much database
writing these days.
Anyway:

2 tables relevant to my problem:

One table - NewLibraryPractice (don't ask, I didn't write that area)
contains a number of fields, the relevant ones to my problem being:
ShadeNumber (Primary Key, no duplicates),
Shade
Material
CustomerCode


The second table - WWOrders - I currently have set up with a look-up field
to the ShadeNumber from NewLibraryPractice.

What I need to do is update the ShadeNumber field in WWorders from the
NewLibaryPractice but at the same time auto filling 2 fields on the sub-form
with Shade and Material from the NewLibaryPractice table - there's no need
to store them anywhere, they just need to be displayed. The user HAS to see
those 3 fields (ShadeNumber, Shade, Material) when they make their selection
of the ShadeNumber as they need to see the extra detail to ensure they
select the correct code. At the same time, as there are over 6,000 shades to
choose from, they only want to see the relevant details for the specific
customer as determined by the CustomerCode field.

I nearly cracked it by using a combo-box showing the 4 fields with a
parameter entry for the customer code. That put my ShadeNumber in and by
having 2 fields (each =fieldname.column(1) and (2)) displayed the relevant
detail. The problem being that the ShadeNumber field in the table WWOrders
doesn't update with the selected data which needs to happen.

How can I fix this please folks, bearing in mind that I don't write code at
all (sorry, just a blind spot for me I'm afraid).

TIA for any and all advice.

BigAl

 

[BigAl]

<snippety dooh dah>


Well, since no-one appears to know how to do this I'd better put in what
I've done so that others may benefit later :--) but I also need a bit of
advice still.

Solved the initial problem by deleting the look-up field and creating an
ordinary text field called ShadeNumber and then getting the list box to put
the ShadeNumber data from the NewLibraryPractice table into it thus allowing
the relationship between the 2 tables to exist. I've then amended the list
box criteria for the CustomerCode to be a parameter field and everything
works - or at least as testing so far is concerned but it does prompt for
the CustomerCode immediately on opening the form but that can be lived with
if necessary.

However, as the CustomerCode is one of the fields on the main form I don't
see any reason why I can't use this information instead of the parameter
query.
So I changed it to =[forms]![HD_Lot_Orders]![Customer Code]

SQL being:

SELECT [NEWLIBRARY PRACTICE].[SHADE NUMBER], [NEWLIBRARY PRACTICE].SHADE,
[NEWLIBRARY PRACTICE].MATERIAL, [NEWLIBRARY PRACTICE].[CUSTOMER CODE]
FROM [NEWLIBRARY PRACTICE]
WHERE ((([NEWLIBRARY PRACTICE].[CUSTOMER
CODE])=[forms]![formhd_lot_orders]![customer code]))
ORDER BY [NEWLIBRARY PRACTICE].SHADE, [NEWLIBRARY PRACTICE].MATERIAL;

Unfortunately it only selects the company code for the first record on
opening the form. Try to select a different HD_Lot_No from its list box and
it doesn't reflect the change of customer code in the sub-form.

There are only 3 fields for the main form and the SQL for the list box is:

SELECT tableHD_Lot_Desc.HD_Lot_No, tableHD_Lot_Desc.[Customer Code],
tableHD_Lot_Desc.Material
FROM tableHD_Lot_Desc;

in case that is any use.

It could be, of course, that I'm doing something incredibly stupid and I
certainly don't mind that being pointed out, providing a reasonable answer
is supplied at the same time :--)

BigAl