Hi,
I'm very new here, and I apologize if I'm not in the correct area. Here is my dilemma. I have 4 tables with the following fields.
tblOfficerMain
OfficerRecordID - (primary key)
tblMainInventory
KitID - (primary key)
tblOfficerInventory
KitID - (same as KitID in tblMainInventory)
RecordID (AutoNumber) - (primary key)
tblKitIssuedtoOfficer
OfficerRecordID - Same as OfficerRecordID in tblOfficerMain (Primary Key)
RecordID -Same as RecordID in tblOfficerInventory (Primary Key)
KitID - Same as KitID in tblOfficerInventory
I have relationships set up for the following:
tblOfficerMain.OfficerRecordID to tblKitIssuedtoOfficer.OfficerRecordID (1 to many)
tblMainInventory.KitID to tblOfficerInventory.KitID (1 to many)
tblOfficerInventory.RecordID to tblKitIssuedtoOfficer.RecordID (1 to many)
tblMainInventory.KitID to tblKitIssuedtoOfficer.KitID (1 to many)
My first problem is that I need to create a relationship between tblOfficerInventory.KitID and tblKitIssuedtoOfficer.KitID. It won't allow me to do this because there is already a relationship between these 2 tables (using RecordID).
Since tblMainInventory.KitID is the same as tblOfficerInventory.KitID, is it possible to assign the value required for tblOfficerInventory.KitID by using tblMainInventory.KitID?
Secondly,I have a main form with a subform. The main form is using tblOfficerMain as the RecordSource. The RecordSource that I am using for the subform is:
SELECT DISTINCTROW [tblKitIssuedtoOfficer].[OfficerRecordID], [tblKitIssuedtoOfficer].[RecordID],[tblKitIssuedtoOfficer].[KitID],[tblKitIssuedtoOfficer].[OfficerKitIssued], [tblKitIssuedtoOfficer].[OfficerQuantityIssued], [tblKitIssuedtoOfficer].[Deducted], [tblKitIssuedtoOfficer].[KitReturned], [tblOfficerInventory].[KitID],[tblOfficerInventory].[Item],[tblOfficerInventory].[Quantity] , [tblMainInventory].[KitID] FROM tblOfficerInventory, tblMainInventory INNER JOIN tblKitIssuedtoOfficer ON tblOfficerInventory.RecordID=tblKitIssuedtoOfficer .RecordID;
This subform was working somewhat, but it kept telling me that a field was required from tblOfficerInventory. Now, the subform is a blank screen.
Also, there is a combo box on the subform. Its RowSource is:
SELECT DISTINCTROW [tblOfficerInventory].[KitID], [tblOfficerInventory].[Item] FROM tblOfficerInventory ORDER BY [Item] ASC;
I really need to get this working, so any help would be greatly apreciated.
Thank you
comteck