Page 7 of 11 FirstFirst 1234567891011 LastLast
Results 91 to 105 of 165
  1. #91
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Michael and others,

    I tried to use the database supplied in #90 by Michael to enter the sample record he proposed. I have included a few comments.

    An example individual? Okay I will make one up --- An inhumation burial from the Early Iron Age settlement at Bornais. They have no radiocarbon date but are thought to be from 700-500 BC. They were placed in a circular pit, 1m x 1m x 0.5m. They were crouched on their left side, aligned N-S, and placed with a copper bead. They are a young adult male, aged 20-25. They have not been subject to isotopic or aDNA testing but would be a good candidate.

    Fleshed out version:from post #46fleshed out the brackets below: An inhumation(deposittype) burial from the Early Iron Age(TimePeriod) settlement(siteType) at Bornais(Site).
    They have no radiocarbon date(AMS_C14_Dates TABLE) but are thought to be from 700-500 BC(RelativeDate FIELD). They were placed in a circular pit(DepositContext), 1m x 1m x 0.5m (Dimensions) They were crouched on their left side (Position), aligned N-S(orientation), and placed with a copper bead(ArtefactType FIELD, AssociatedGoods TABLE). They are a young adult(AgeCategory FIELD, OsteoInfo TABLE) male(OsteoSex FIELD, OsteoInfo TABLE), aged 20-25(AgeRangeLow and AgeRangeHigh FIELDS, OsteoInfo TABLE). They have not been subject to isotopic(ScienceInfo) or aDNA(ScienceInfo) testing but would be a good candidate.

    I realize we are still working to flesh out the model. When I tried, I could not find a way to enter all the data.
    Implication,
    -my process was incorrect or
    -the form lacks certain options or
    -the relationships do not support the sample or
    -the sample may not be logically consistent or
    - something else??

    Some observations for consideration:
    Perhaps RelativeDate should be a prepopulaed list of choices or you'll get mishmash
    No access to DepositContexts
    No access to OsteoIfo
    No access to ScienceInfo

    Perhaps it's time to define all tables and all fields for clarity
    Aren't inhumation, cremation, disarticulated, partial-secondary and comingled all the subtypes of Deposit?

    If so, seems an approach to designing data entry --at east for Deposit and subtypes

    -define common fields for all deposit types along with source of FK fields
    -define unique fields for inhumation
    -define unique fields for cremation
    -define unique fields for disarticulated
    -define unique fields for partial_secondary
    -define unique fields for comingled

    The sources of info for each of the fields in Deposits and each subtype have to be available when entering data.
    From a recording perspective, starting with frmSites seems logical.
    Attached Thumbnails Attached Thumbnails AttempttoAddSampleRecord.png  
    Last edited by orange; 05-13-2022 at 01:01 PM.

  2. #92
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Hi @Orange and everyone

    I had a little play around at the weekend, I haven’t gotten far but I think its working (a bit)

    Perhaps RelativeDate should be a prepopulaed list of choices or you'll get mishmash

    You are bound to be right but there could be so many options for it, it would be hard to predict and make a list for

    Aren't inhumation, cremation, disarticulated, partial-secondary and comingled all the subtypes of Deposit?

    Yes they are, I did mention this a few times but it has probably gotten lost in the thread

    -define common fields for all deposit types along with source of FK fields
    -define unique fields for inhumation
    -define unique fields for cremation
    -define unique fields for disarticulated
    -define unique fields for partial_secondary
    -define unique fields for comingled


    I have done this also – I have it as an excel sheet if that’s helpful? But the fields are also in tables in the latest database (See attached)


    Building on from Vlad’s database, and with Mike’s and your suggestions too (plus Ken), I have made the attached database. So far, I have a sites form, which seems to work, plus a deposits form, which allows you to select the site that each deposit is from. The deposits form has working combo boxes for fields applicable to all deposit types, and I have added two tabs so far with subforms:

    One for AMS/C14 dates
    One for inhumation-specific fields

    The AMS/C14 dates subform does not work, but it does allow for multiple entries per deposit, which is what I need (as each deposit may be dated more than once)
    The inhumation subform seems to work, and links to the DepositID successfully.

    ISSUES
    - The AMS/C14 subform does not automatically connect to the DepositID and will not save data – I think this is because I set up its relationship from tblAMS_C14_Dates to tblDeposits wrong, it is connected to a DatingID_FK field, not the DepositID, but I got an error when I tried connecting DepositID_FK (tblAMS_C14_Dates) to DepositID (tblDeposits).
    - For tblDepositContext, tblCutShape, and tblAssociatedGoods, I have link tables with composite keys, connecting each of these tables to DepositID in tblDeposits, but for tblPeriod, it is linked directly to PeriodID_FK in tblDeposits – is this wrong?
    - I made a listbox so you could select multiple associated goods (things found with the dead) per deposit, but it doesn't seem to do anything - I can select multiple fields, but when you click on to the next deposit, the same fields are still selected, and it seems to have no impact on the tables. I am guessing I am using the listbox wrong?

    My next step is going to be to build and add other tabs and see if they work as intended. I would love to hear all your thoughts and criticisms

    Scotland database redesignVladEdit.zip
    Last edited by Michael91; 05-16-2022 at 02:38 AM.

  3. #93
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Michael

    In your summary you stated the following:-

    "The deposits form has working combo boxes for fields applicable to all deposit types, and I have added two tabs so far with subforms:

    One for AMS/C14 dates
    One for inhumation-specific fields

    The AMS/C14 dates subform does not work, but it does allow for multiple entries per deposit, which is what I need (as each deposit may be dated more than once)
    The inhumation subform seems to work, and links to the DepositID successfully."

    The Sub-form for Inhumation is correct.
    The Subform is wrong for AMS Data.

    If you look at the attached screenshot of the Relationship between
    tblDeposits and tblAMS_C14_Dates, in tblDeposits you have a Foreign Key DatingID_FK which means when you create your Form , all you are able to do is select 1 Value from the related tblAMS_C14_Dates.

    So if you want to store Multiple records related to tblDeposits you need the structure as shown in the attached updated database.


    Attached Thumbnails Attached Thumbnails Rel.JPG  
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #94
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76

    Talking

    Hi Mike and everyone,

    Thanks for your reply - I thought that might be the issue

    I have fixed that now, and built all the tables, relationships and forms I think I will need. I have tested them and I THINK they mostly work.

    Attached is a current version with sample data, and a relationship screenshot.

    I do still have a couple of issues if its okay to keep asking in this thread?


    • There are a few fields where I want to be able to select multiple options (records?) – I have made some of them into listboxes with Simple Multi Select turned on – but when I select them, the same selection carries over to the next deposit, and vice versa (see, for example, the fields Osteo detail in the Osteo subform of frmDeposits, and Associated Goods in frmDeposits, and Analysis in the Science subform of frmDeposits, and Deposits dated to in frmSites).



    • Not sure if this is an issue but both tblinhumationsInfo and tblPartialSecondaryInfo have One-to-Many relationships with tblPositions, tblOrientations, tblFacingDirection, and tblBound, as these are all relevant to inhumation and partial/secondary deposit types, and appear on forms for both. I seem to be able to enter new records okay?



    • Most of my tables are connected to the tblDeposits via a link table, and the seem to work fine, they’re all connected to DepositID via foreign keys. tblPeriod and tblDepositTypes are connected directly to tblDeposits though, with PeriodID and DepositTypeID FKs in tblDeposits. Is this wrong, should they be made the same as the other tables?




    • The Osteo and Science subforms work, but they are clunkier than I would like – They will not be relevant to every deposit, which is why they exist as subforms, but some part of them will be relevant to most deposits, so I would ideally like a more fluid way of utilising them, rather than the tab system. Would it be better to just add this info as fields in the Deposit form, and only enter the relevant data for each new record? Any thoughts would be appreciated.


    It really feels like I am getting somewhere with this and I owe it entirely to this forum, thank you all again

    Click image for larger version. 

Name:	Relationships.jpg 
Views:	33 
Size:	124.5 KB 
ID:	47820Scotland database redesign CURRENT WORKING DOC.zip

  5. #95
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hi Michael,

    Attached is a current version with sample data, and a relationship screenshot.

    I do still have a couple of issues if its okay to keep asking in this thread?
    Thanks for forwarding. I will look at it today--sometime today.

    The purpose of the forum is to help those developing/designing/maintaining (and/ or those with an interest in) database (primarily Access). So, as long as the questions are "real" and considered relevant by readers, I'm sure you will get advice, answers and suggestions. Keep asking.

    Your relationship diagram is quite dark - a little difficult to read. It certainly has progressed. You may now want to prepare a few scenarios pertaining to potential reports or queries and see if the structure has the data, is accessible and satisfies the requirement.

    You may want to re-review Ken's TypeHierarchy material and accept/reject/alter the approach based on facts. He has knowledge and experience of both archaeology and database. I am not trying to dissuade you, just want you to question and ensure that the structure supports your needs before getting too deep into physical database.

    You might consider including some version number or identifier in database(s) attached to posts so readers are all referring to the same item/instance.

    You've done a lot. You're asking good questions. Good luck with your project.

    Looked at processing a sample record (mockup based on your original)

    An example individual? Okay I will make one up --- An inhumation burial from the Early Iron Age settlement at Bornais. They have no radiocarbon date but are thought to be from 700-500 BC. They were placed in a circular pit, 1m x 1m x 0.5m. They were crouched on their left side, aligned N-S, and placed with a copper bead. They are a young adult male, aged 20-25. They have not been subject to isotopic or aDNA testing but would be a good candidate. Facing Upward. Had a Rolex wristwatch with Silver bracelet and a personal necklace with bronze Medalion




    I am attaching a few comments for consideration.
    Attached Files Attached Files
    Last edited by orange; 05-18-2022 at 05:46 PM.

  6. #96
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Quote Originally Posted by orange View Post
    Hi Michael,



    Thanks for forwarding. I will look at it today--sometime today.

    The purpose of the forum is to help those developing/designing/maintaining (and/ or those with an interest in) database (primarily Access). So, as long as the questions are "real" and considered relevant by readers, I'm sure you will get advice, answers and suggestions. Keep asking.

    Your relationship diagram is quite dark - a little difficult to read. It certainly has progressed. You may now want to prepare a few scenarios pertaining to potential reports or queries and see if the structure has the data, is accessible and satisfies the requirement.

    You may want to re-review Ken's TypeHierarchy material and accept/reject/alter the approach based on facts. He has knowledge and experience of both archaeology and database. I am not trying to dissuade you, just want you to question and ensure that the structure supports your needs before getting too deep into physical database.

    You might consider including some version number or identifier in database(s) attached to posts so readers are all referring to the same item/instance.

    You've done a lot. You're asking good questions. Good luck with your project.

    Looked at processing a sample record (mockup based on your original)

    An example individual? Okay I will make one up --- An inhumation burial from the Early Iron Age settlement at Bornais. They have no radiocarbon date but are thought to be from 700-500 BC. They were placed in a circular pit, 1m x 1m x 0.5m. They were crouched on their left side, aligned N-S, and placed with a copper bead. They are a young adult male, aged 20-25. They have not been subject to isotopic or aDNA testing but would be a good candidate. Facing Upward. Had a Rolex wristwatch with Silver bracelet and a personal necklace with bronze Medalion




    I am attaching a few comments for consideration.
    Hi @Orange,

    Thanks for your reply. I will plan out some queries this week and see how it works, and re-examine Ken's approach as you say
    Regarding your comments:



    Not sure of the value of going to frmSites. Unless it would be to identify a new site, or edit an existing site. TheSites "list" is available on frmDeposits. Seemes you could adjust the combo on frmDeposits to insert/edit a Site. FrmSites exists to add new sites, so they then appear in the "site" field in frmDeposits. I didn't think site data should be added as a subform in frmDeposits, and I couldn't think of a way to merge them without it being clunky or complicated. So long as the relationship works (i.e., I am able to say which deposits come from which sites etc.) then I am happy with them being separate forms, but obviously if this will cause an issue I would like to know.


    How do you complete a record? Should user just go next? Click a button? - At present, yes, you just click next, but I will add a button in for ease



    AssociatedGoods is artefact? - Correct, I will alter these for consistency


    all controls should have meaningful names - Noted

  7. #97
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Deleted double post

  8. #98
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Michael,
    There are 7 comments in the attached txt file in previous post. #5 relates to multiselect list box. The png file below shows a button that I added that allows multiple selections. The vba code shown in comment #5 shows how the selected values are added to the database.


    Click image for larger version. 

Name:	btnArtefact.PNG 
Views:	26 
Size:	38.9 KB 
ID:	47845

    This shows the records that were added to tblAssociatedGoodsLink

    DepositID_FK ArtefactID_FK
    5 4
    5 5

  9. #99
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Hi @Orange,

    I will reply properly later, but sorry for missign your other comments - I did not scroll down enough in the text file I will take a look at them today. I've fixed the issues you raised in comments 1-4 now too.

  10. #100
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Quote Originally Posted by orange View Post
    Michael,
    There are 7 comments in the attached txt file in previous post. #5 relates to multiselect list box. The png file below shows a button that I added that allows multiple selections. The vba code shown in comment #5 shows how the selected values are added to the database.


    Click image for larger version. 

Name:	btnArtefact.PNG 
Views:	26 
Size:	38.9 KB 
ID:	47845

    This shows the records that were added to tblAssociatedGoodsLink

    DepositID_FK ArtefactID_FK
    5 4
    5 5
    Hi @Orange,

    Regarding your other comments -

    AssociatedGoodsDetail?? How to associate detail with a specific artefact that was entered via multiselect. Seems to be only 1 field for Detailnfo. Perhaps point form/paragraph is sufficient
    This is because each specific artefact (associatedGood) only has a broad category in the combobox (e.g. personal ornaments). The AssociatedGoodsDetail textbox means I can add further information, but it doesn’t need to be too in-depth. For example, someone may be buried with a pot and a brooch, so I would select the Pottery and Personal ornament options in AssociatedGoods, then in the detail box I might write “black burnished ware jar by feet, penannular iron brooch by left shoulder”. This is a best case scenario, normally there is less information to record.

    I figured that when I need to do a query or report about associated goods, I can select the detail field too and read through my notes that way? If I add more detailed options to AssociatedGoods it would be a very long listbox.

    What is grave furniture? Example(s).
    This refers to often structural features of a grave that cannot be considered grave goods – for example, some have timber or stone linings, some have floors, some have layers of gravel. I have left this as a text field for now because I do not yet know what kind of grave furniture I will encounter, and it is not a very important field for analysis.

    Regarding the listboxes, and the code you wrote - I am sorry but I cannot get it to work. I have tried entering it a few ways (pasted it all, pasted it in pieces, changed the listbox name back to list173, written it in) but I just get errors. I don't understadn what any of it means, so I cannot say what is wrong.

    I got it to stop producing errors by making it look like this:

    Private Sub BtnAddArtefacts_Click()


    Dim itm As Variant
    Debug.Print " Number of Artefacts selected is " & Me.List173.ItemsSelected.Count
    For Each itm In Me.List173.ItemsSelected
    Debug.Print "Create a tblassociatedGoodsLink record with DepositID " & Me.DepositID & " and AssociatedGoods " & Me.List173.ItemData(itm) & " " & DLookup("ArtefactType", "tblAssociatedGoods", "ArtefactID=" & Me.List173.ItemData(itm)) & " " & DLookup("ArtefactType", "tblAssociatedGoods", "ArtefactID=" & Me.List173.ItemData(itm))
    Debug.Print "Insert into tblassociatedGoodsLink (depositID_FK,artefactID_Fk) Values ( " & Me.DepositID & ", " & Me.List173.ItemData(itm) & ");"
    CurrentDb.Execute "Insert into tblassociatedGoodsLink (depositID_fk,artefactID_fk) Values ( " & Me.DepositID & ", " & Me.List173.ItemData(itm) & ");", dbFailOnError


    Next itm

    End Sub

    And I created a button, clicked "build event", then "code builder", and then I entered the info pasted above, saved it, and returned to the database.... nothing. Am I doing this part wrong, or did I write the code wrong?

    EDIT: I realised I changed some of the table and field names so they were consistent - so I tried again, editing the code with the new table names - still no luck. I have attached the current version. Database v 10 - Multivalue button issue.zip

  11. #101
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Michael,
    You have changed the names of some controls on the form(s).
    Here are the Listbox names in your latest database. Not a big issue and easily resolved.
    The code refers to the old name.

    You can compare these names with those I included in previous comments.


    form_name control_name
    frmDeposits AssociatedGoods
    frmSites TimeFrame_FK
    frmOsteoInfo OsteoAnalysisID_FK
    frmScienceInfo ScienceAnalysisID_FK

    I'm going to send some utility functions once I get a few more.
    -show all tables and fields
    -show all forms and controls

  12. #102
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Quote Originally Posted by orange View Post
    Michael,
    You have changed the names of some controls on the form(s).
    Here are the Listbox names in your latest database. Not a big issue and easily resolved.
    The code refers to the old name.

    You can compare these names with those I included in previous comments.


    form_name control_name
    frmDeposits AssociatedGoods
    frmSites TimeFrame_FK
    frmOsteoInfo OsteoAnalysisID_FK
    frmScienceInfo ScienceAnalysisID_FK

    I'm going to send some utility functions once I get a few more.
    -show all tables and fields
    -show all forms and controls
    Hi @Orange

    I edited the code so it looks like this, with all references to List173 replaced and the table names updated:

    Private Sub BtnAddArtefacts_Click()




    Dim itm As Variant
    Debug.Print " Number of Artefacts selected is " & Me.AssociatedGoods.ItemsSelected.Count
    For Each itm In Me.AssociatedGoods.ItemsSelected
    Debug.Print "Create a tblassociatedGoodsLink record with DepositID " & Me.DepositID & " and AssociatedGoods " & Me.AssociatedGoods.ItemData(itm) & " " & DLookup("AssociatedGoodsType", "tblAssociatedGoods", "AssociatedGoodsID=" & Me.AssociatedGoods.ItemData(itm)) & " " & DLookup("ArtefactType", "tblAssociatedGoods", "AssociatedGoodsID=" & Me.AssociatedGoods.ItemData(itm))
    Debug.Print "Insert into tblassociatedGoodsLink (depositID_FK,AssociatedGoodsID_FK) Values ( " & Me.DepositID & ", " & Me.AssociatedGoods.ItemData(itm) & ");"
    CurrentDb.Execute "Insert into tblassociatedGoodsLink (depositID_FK,AssociatedGoodsID_fk) Values ( " & Me.DepositID & ", " & Me.AssociatedGoods.ItemData(itm) & ");", dbFailOnError




    Next itm


    End Sub

    Still doesn't seem to do anything. I won't try any others yet because there is no point if I don't understand what bits I am changing. I don't really have a lot of time the next few days but I will try to read up on coding.

    Thanks!

  13. #103
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Michael

    Take a look at the Deposits Form.

    I modified the code that Orange supplied which might be a little easier to understand.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  14. #104
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Michael and Mike,

    I recognize that participants on various forums may all be offering advice and suggestions. At the moment I am reviewing materials on the database version you provided in post #94. You have sent me updates and Mike has also sent changes to an interim version. For your learning sake and in hopes of reducing confusion we need to have some sort of versioning here so we are not working at cross purposes.

    There have been changes to control and fields/columns.

    My point with the button on the Artefacts/AssociatedGoods listbox was there needs to be a method to allow multiselections. The individual code, providing it does what is needed, is not so relevant. The critical point, and based on Mike's response, is that you need an event that when fired will accept and process your multiple selections. His approach for previous and next buttons raises the issue that it is important to be editing the proper record. I was using the record selector at the bottom of the form. He has inserted buttons to do deposit record selection next to the /AssociatedGoods listbox. The record selectors are required for navigation, but in my view are mislocated on the form. Key point is some obvious record navigation is needed.

    The code I have suggested for the AssociatedGoods listbox is as follows. It purposely includes comments and debug statements to assist learning some vba.
    There is a change in logic for assigning multiselected values.
    For consideration, this new approach deletes all(if any) existing AssociatedGoods assignments for this DepositID, and rebuilds assignments based on the latest selections. The rationale for this is there was no way to remove/replace individual assignments. So remove existing and replace with all selected.


    [CODE=rich]' ----------------------------------------------------------------
    ' Procedure Name: BtnAddArtefacts_Click
    ' Purpose: Routine to allow adding of multiple Artefacts with an InHumation.
    ' NOTE: This will remove any existing Artefacts for this DepositID, and then
    ' record the newly selected artefact(s) in tblassociatedGoodsLink
    ' Procedure Kind: Sub
    ' Procedure Access: Private
    ' Author: Jack
    ' Date: 18-May-22
    ' ----------------------------------------------------------------

    Private Sub BtnAddArtefacts_Click()

    Dim itm As Variant
    'How may artefacts currently assigned to this depositid"
    10 Debug.Print "Current count of artefacts for depositID " & Me.DepositID & " is " _
    & DCount( "DepositID_FK", "tblassociatedGoodsLink", "DepositID_FK=" & Me.DepositID)

    20 If DCount( "DepositID_FK", "tblassociatedGoodsLink", "DepositID_FK=" & Me.DepositID) > 0 Then
    30 ClearArtefactsForThisDeposit 'clear all existing artefacts for this depositid
    40 End If

    50 Debug.Print "Number of Artefacts selected is " & Me.List173.ItemsSelected.Count

    60 For Each itm In Me.List173.ItemsSelected
    'show info for what selected artefact(s) is going to be assigned for this depositid
    70 Debug.Print "Create a tblassociatedGoodsLink record with DepositID " & Me.DepositID & " and AssociatedGoods " & Me.List173.ItemData(itm) _
    & " " & DLookup( "ArtefactType", "tblAssociatedGoods", "ArtefactID=" & Me.List173.ItemData(itm))
    80 Debug.Print "Insert into tblassociatedGoodsLink (depositID_FK,artefactID_Fk) Values(" & Me.DepositID _
    & ", " & Me.List173.ItemData(itm) & ");"

    'perform the actual execution
    90 CurrentDb.Execute "Insert into tblassociatedGoodsLink (depositID_fk,artefactID_fk) Values(" & Me.DepositID _
    & ", " & Me.List173.ItemData(itm) & ");",dbFailOnError
    100 Next itm


    End Sub

    ' ----------------------------------------------------------------
    ' Procedure Name: ClearArtefactsForThisDeposit
    ' Purpose: Remove all current artefacts for this depositid
    ' Procedure Kind: Sub
    ' Procedure Access: Public
    ' Author: Jack
    ' Date: 19-May-22
    ' ----------------------------------------------------------------

    Sub ClearArtefactsForThisDeposit()

    Const sql_Delete As String = _
    "DELETE * From tblassociatedGoodsLink where DepositID_FK = p0 "
    10 With CurrentDb.CreateQueryDef( "",sql_Delete)
    20 .Parameters( "p0") = Forms!frmDeposits.DepositID
    30 .Execute
    40 End With

    End Sub [/CODE]

    ' Made with Color Code add-in posted on http://msaccessgurus.com/tool/Addin_ColorCode.htm

  15. #105
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Hi Orange,

    I am not discussing this on any other forums, or on any other threads except this one, but apologies if I have caused things to get confused. As I see it, the latest version of the database is V10 – Multivalue button issue, that I uploaded in post #100. I will be sure to update the file name to V.11 if/when I upload the next version. Mike’s latest post uses and updates this database so there is no issue there �� V10 also implements all the comments you made in post #95, all those I could do anyway.

    There have been changes to control and fields/columns. – These were because you pointed out some of mine were not cohesive, so I made sure they all matched.


    My point with the button on the Artefacts/AssociatedGoods listbox was there needs to be a method to allow multiselections. The individual code, providing it does what is needed, is not so relevant. The critical point, and based on Mike's response, is that you need an event that when fired will accept and process your multiple selections. His approach for previous and next buttons raises the issue that it is important to be editing the proper record. I was using the record selector at the bottom of the form. He has inserted buttons to do deposit record selection next to the /AssociatedGoods listbox. The record selectors are required for navigation, but in my view are mislocated on the form. Key point is some obvious record navigation is needed.

    I actually added those record selection buttons in V.10, not Mike, because you pointed out in your comments that it would be a better solution than clicking “next record” at the bottom of the page – apologies again for any confusion. I can move them somewhere better? I was just using up free space on the page.


    The code I have suggested for the AssociatedGoods listbox is as follows. It purposely includes comments and debug statements to assist learning some vba. There is a change in logic for assigning multiselected values. For consideration, this new approach deletes all(if any) existing AssociatedGoods assignments for this DepositID, and rebuilds assignments based on the latest selections. The rationale for this is there was no way to remove/replace individual assignments. So remove existing and replace with all selected.

    That is very clever, thank you. So I am clear – if I want to implement your code (or adapt Mike’s from V.10), I just need to change the relevant tables/fields each time? And for yours, the green text is you explaining to me what each bit does, so should NOT be included? What is the significance of the blue text over the black?

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

Similar Threads

  1. Replies: 9
    Last Post: 06-15-2017, 03:00 PM
  2. Replies: 4
    Last Post: 05-03-2017, 10:04 PM
  3. multi table queries
    By omid116200@gmail.com in forum Queries
    Replies: 2
    Last Post: 07-19-2016, 09:10 AM
  4. Multi-Table Queries (Where to Start?)
    By dccjr in forum Queries
    Replies: 1
    Last Post: 04-23-2013, 06:34 PM
  5. Replies: 6
    Last Post: 12-30-2011, 09:51 AM

Tags for this Thread

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