Query Problem

[Edo]

I have drawn up two tables.
Table A = history of the person work previously until today
Table B = Sales order

In Table A, it is compulsory for the user to enter the name of the company the person work for

In Table B, I want the user when select the company that we are selling good to, will immediatly show the name of the person who work in that company ( eg. the contact person who is in charge of the order in the buyer side )

I link table a and table b. but when i select the name from the table B, it shows ALL the name of different person who also does not work in the company that i have selected.

How do i overcome this problem

 

[Fonz]

A Tale of 2 Tables?

Hi
I believe the problem is all in the table design and what relational links you have set up. I don't fully understand your problem so I am showing below what I think your table structures should be and how the link should work

TABLE A
CompanyID / AutoIncrement key Field
CompanyName /Text, 20
CompanyContactName / Text, 20
StreetAddress /Text, 25
City /Text, 25
State /Text, 10
Postcode / Text,15

TABLE B

SalesID /Autoincrement Key Field
CompanyID / Number, Long
SalesDate /Date&Time
SalesProductNo /Text, 15
Quantity /Number, Integer
UnitPrice /Number, Double

Now in your query, show Table B on the LEFT and Table A on the Right and then Inner Join (one to one) CompanyID. So then for evey sales order, you will display the correct Company and Contact Person.

IF you are trying to capture this on a FORM with data entry then do the following:

---------------------------------------------------------------------------
You open the form in ADD mode (it will be at a new record)
RecordSource = Table B

SalesID (This field make it ENABLED = FALSE, LOCKED = TRUE) It will automatically add a number the moment data is entered in the other fields.
SalesDate : ControlSource - SalesDate
Company: Make this control a Combo box with ControlSource - CompanyID,
Row/Source Type = Table/Query
Row Source
= SELECT CompanyID, CompanyName, CompanyContactName FROM TableA;
Column Count = 3
Column Widths = 0cm;3cm;3cm
Bound Column = 1
Width = 3cm
List Width= 6cm
Contact: Controlsource: = [company].column(2)
Enabled = False
Locked -= True
SalesProductNo:
Quantity:
UnitPrice:
Total Order: Control Source: =[UnitPrice]*[Quantity]

Have a SAVE button on the form, so when you click it the record is saved.
To make 'Company' compulsory, add the following to the Form_BeforeUpdate procedure

Sub Form_BeforeUpdate(Cancel As Integer)
If isnull([Company]) then
MsgBox "You MUST pick a Company BEFORE you can save data"
Cancel = True
[Company].Setfocus
End If
End Sub

Tell me how you went with the above
All the Best
Mark