Searching a record in form

[MJD1]

Hello all, I'm looking for some guidance. I have a form that is tied to table A that contains the following fields: Store, sku and comment.

In my form, I have a list box that is tied to Table b that contains a number of fields including Store, Sku, description, units, cost, ect..

I would like my list box to look up a record in table A based on Store AND sku so that I can add comments. I also want that when I select a record in the list box and that there isn't a corresponding Store and SKU in Table A that the fields remain empty until I click on add comment button.


hopefully I made myself clear above. So far I'm able to lookup based on sku only and when there's isn;t a record, it seems to default to the first sku and comment previously entered. Also, when I click on the Add Comment button, I have it copying the store and sku from Table b onto Table A corresponding fields. This i working great.


any help is appreaciated.

thanks
Martin

 

[kjv1611]

It'll probably be best if you tackle all of this one piece at a time. Otherwise, it could get quite cumbersome.

For starters, is the SKU always unique, regardless of store? If it is, then you have a duplicated field. You should only need the Store Name in one table... So, instead of:

table A:
Store, sku, comment.

table B:
Store, Sku, description, units, cost, ect..

You should have:

TableA:
Sku, comment

TableB:
Store, Sku, description, units, cost, ect..

-------------
Before we can move to other pieces, please verify whether the SKU is 100% unique to each record, regardless of Store, or whether 2 different stores can contain the same SKU.

--

"If to err is human, then I must be some kind of human!" -Me

 

[MJD1]

hi, the sku is unique for each store. for exemple I can have sku 123456 appear in two different stores with different quantities thus different totals.

What I'm working on is a variance report. So I may have a total $ variance of $500 at store 100 for sku 123456 and 300$ at store 200. The idea is to be able to have comments for each store because the variance reason could be different. Also, table B gets updated daily. So I want to ensure I don't lose my comments for the corresponding sku and store. from one day to another, the $$ could increase or decrease so I want to keep track of actions taken in my comments field.

hope this help better understand what I'm trying to do.

cheers
Martin

 

[MJD1]

I figured out a way to do it using another list box to search the existing comments and created a small section to add new comments.

thanks
martin