Same data in all records

[comteck]

Hi again,

I am creating a database for a cadet Corps, and I want to be able to issue kit to a cadet or to an officer, and have the form automatically deduct this kit from the main inventory. I am using the same form for both, and all fields are entered using VBA. There is a subform attached to the main form, and the main form is for general information (Name, Address, etc), and the subform is used to issue the kit to the appropriate person. The subform uses a combo box, to be able to select the kit from a pull down box.

I have 2 tables, which can be seen from the attachment, one for kit issued to a cadet, and one for kit to an officer. The 2 tables are set up exactly the same (except for the word "Officer" in front of some of the fields on the officer one), and the VBA code is the same for both (again, except for the word "Officer" where needed).

The cadet one works great. It shows on the subform whatever kit is issued to whatever cadet (for example, cadet 1 may be issued 5 items, but cadet 2 may only be issued 4 items. It will show which items are issued to each cadet.).

The problem is on the officer side. When I issue kit to one officer, it shows up on all officer subforms. (For example, Officer 1 may have 4 items, and officer 2 may have 5 items. It will show 9 items to both officers.). Even when I go to create a new record for an officer, before any kit is issued to that officer, all items will immediately be displayed in the subform.

Here is the VBA code for both officers and cadets:

Private Sub IssueKittoCadet_Click()
DoCmd.Close
DoCmd.OpenForm ("frmKitIssued"), acNormal
[Forms]![frmKitIssued]![Label17].Caption = "Issue Kit to Cadet"
Forms.Item("frmKitIssued").Properties.Item("Record Source") = "qryCadetsEnrolled"
[Forms]![frmKitIssued]![RecordID].ControlSource = "RecordID"
[Forms]![frmKitIssued]![LastName].ControlSource = "LastName"
[Forms]![frmKitIssued]![MiddleName].ControlSource = "MiddleName"
[Forms]![frmKitIssued]![FirstName].ControlSource = "FirstName"
[Forms]![frmKitIssued]![Sex].ControlSource = "Sex"
[Forms]![frmKitIssued]![PhoneNo].ControlSource = "Phone No"
[Forms]![frmKitIssued]![Address].ControlSource = "Address"
[Forms]![frmKitIssued]![City].ControlSource = "City"
[Forms]![frmKitIssued]![StateOrProvince].ControlSource = "StateorProvince"
[Forms]![frmKitIssued]![PostalCode].ControlSource = "PostalCode"
[Forms]![frmKitIssued]![BirthDate].ControlSource = "BirthDate"
[Forms]![frmKitIssued]![ParentsNames].ControlSource = "ParentsNames"
[Forms]![frmKitIssued]![Notes].ControlSource = "Notes"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].RecordSource = "SELECT DISTINCTROW [tblKitIssuedtoCadet].[RecordID], [tblKitIssuedtoCadet].[KitID], [tblKitIssuedtoCadet].[KitIssued], [tblKitIssuedtoCadet].[QuantityIssued], [tblKitIssuedtoCadet].[Deducted], [tblKitIssuedtoCadet].[KitReturned], [tblMainInventory].[NumberinStock], [tblMainInventory].[PrevMuster],[tblMainInventory].[StoresDes] FROM tblMainInventory INNER JOIN tblKitIssuedtoCadet ON tblMainInventory.KitID=tblKitIssuedtoCadet.KitID;"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].[RecordID].ControlSource = "RecordID"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].[Kit].ControlSource = "KitID"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].[Kit].RowSource = "SELECT DISTINCTROW [tblMainInventory].[KitID], [tblMainInventory].[PartName] FROM tblMainInventory WHERE [tblMainInventory].[StoresDes]='Cadet' ORDER BY [PartName];"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].[KitIssued].ControlSource = "KitIssued"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].[Quan].ControlSource = "QuantityIssued"
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub IssueKittoOfficer_Click()
DoCmd.Close
DoCmd.OpenForm ("frmKitIssued"), acNormal
[Forms]![frmKitIssued]![Label17].Caption = "Issue Kit to Officer"
Forms.Item("frmKitIssued").Properties.Item("Record Source") = "qryOfficersEnrolled"
[Forms]![frmKitIssued]![RecordID].ControlSource = "OfficerRecordID"


[Forms]![frmKitIssued]![LastName].ControlSource = "OfficerLastName"
[Forms]![frmKitIssued]![MiddleName].ControlSource = "OfficerMiddleName"
[Forms]![frmKitIssued]![FirstName].ControlSource = "OfficerFirstName"
[Forms]![frmKitIssued]![Sex].ControlSource = "OfficerSex"
[Forms]![frmKitIssued]![PhoneNo].ControlSource = "OfficerPhone Number"
[Forms]![frmKitIssued]![Address].ControlSource = "OfficerAddress"
[Forms]![frmKitIssued]![City].ControlSource = "OfficerCity"
[Forms]![frmKitIssued]![StateOrProvince].ControlSource = "OfficerProvince"
[Forms]![frmKitIssued]![PostalCode].ControlSource = "OfficerPostalCode"
[Forms]![frmKitIssued]![BirthDate].ControlSource = "OfficerBirthDate"
[Forms]![frmKitIssued]![ParentsNames].Visible = False
[Forms]![frmKitIssued]![ParentsNames_Label].Visible = False
[Forms]![frmKitIssued]![Notes].ControlSource = "OfficerNotes"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].RecordSource = "SELECT DISTINCTROW [tblKitIssuedtoOfficer].[OfficerRecordID], [tblKitIssuedtoOfficer].[KitID], [tblKitIssuedtoOfficer].[OfficerKitIssued], [tblKitIssuedtoOfficer].[OfficerQuantityIssued], [tblKitIssuedtoOfficer].[Deducted], [tblKitIssuedtoOfficer].[KitReturned], [tblMainInventory].[NumberinStock], [tblMainInventory].[PrevMuster],[tblMainInventory].[StoresDes] FROM tblMainInventory INNER JOIN tblKitIssuedtoOfficer ON tblMainInventory.KitID=tblKitIssuedtoOfficer.KitID ;"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].[RecordID].ControlSource = "OfficerRecordID"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].[Kit].ControlSource = "KitID"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].[Kit].RowSource = "SELECT DISTINCTROW [tblMainInventory].[KitID], [tblMainInventory].[PartName] FROM tblMainInventory WHERE [tblMainInventory].[StoresDes]='Officer' ORDER BY [PartName];"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].[KitIssued].ControlSource = "OfficerKitIssued"
[Forms]![frmKitIssued]![sfrmKitIssued].[Form].[Quan].ControlSource = "OfficerQuantityIssued"
DoCmd.GoToRecord , , acNewRec
End Sub

Thanks for any help on this.
comteck

[June7]

Probably something to do with data structure, which was an issue in your other thread you said was resolved but did not indicate how. https://www.accessforums.net/access/...les-20312.html

Are these forms bound? Why are you using code to set values of fields? Is data being duplicated between tables? A principle of relational database is to relate records by common id field. Most of this data appears to me should not be saved into the destination table and should not need code to retrieve the related info.

Do you want to provide project (without confidential data) for analysis?

[comteck]

How do I provide my project for analysis?

Thanks
comteck

[June7]

Instructions in this thread apply to this forum as well http://www.access-programmers.co.uk/...d.php?t=140587