Dlookup or Me.Recordset to update form?? 1

[robmkimmons]

I have tblProducts which lists products by their stock number StkNum and then has a sub field Sub which helps differentiate different colors of that product. To make it faster and reduce error, I would like the form to fill in the ItemDescription field with the same information as any previous record which has a matching StkNum so that the user would only have to input the Sub and Color fields.

So in the example below, on the third row, once the user enters in “1820” the ItemDescription field will pull in the info from one of the other 1820’s and put it in.

StkNum Sub ItemDescription ItemColor
1820 A Drawer Pull Large Brown
1820 B Drawer Pull Large Grey
1820 {fills in above info}

(the form will actually be in single form mode, but i listed it this way for ease of explanation)

I can do limited coding and assume this can be done on the AfterUpdate event of the StkNum field. Perhaps using the Dlookup function or Me.Recordset

Any help is much appreciated!


~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[SteveR77]

Rob,

You are correct. I believe the Dlookup would be a bit easier to code. It would look something like this and be added to the after update of the StkNum field -

Me.[ItemDescription].Value= DLookup("[ItemDescription]", "tblProducts", "[StkNum] = Me.[StkNum]&quot


Hope this helps,


Steve