Results 1 to 4 of 4
  1. #1
    comteck is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    12

    Same data in all records

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,175
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    comteck is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    12
    How do I provide my project for analysis?

    Thanks
    comteck

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,175
    Instructions in this thread apply to this forum as well http://www.access-programmers.co.uk/...d.php?t=140587
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cannot add records to the data base
    By vagelism22678 in forum Access
    Replies: 1
    Last Post: 09-19-2011, 12:45 AM
  2. Data from many records into one Memofield?
    By Diane99 in forum Queries
    Replies: 2
    Last Post: 03-27-2011, 02:49 PM
  3. Log Data Changes and Deleted records
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 12-31-2010, 12:41 AM
  4. changing data in many records
    By cforce in forum Programming
    Replies: 1
    Last Post: 07-22-2010, 02:30 PM
  5. Data calculation between records
    By ibergarden in forum Access
    Replies: 3
    Last Post: 04-22-2010, 11:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums