Page 8 of 11 FirstFirst 1234567891011 LastLast
Results 106 to 120 of 164
  1. #106
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Quote Originally Posted by mike60smart View Post
    Hi Michael

    Take a look at the Deposits Form.

    I modified the code that Orange supplied which might be a little easier to understand.
    This is cool, thank you! I will give the code a read, and re-read Orange's, and try to understand it I noticed that the listbox for AssociatedGoodsType, that you have now renamed GoodsList, is unbound now - why is this, does it not need a control source anymore?

  2. #107
    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,171
    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.

    Please note that my comment about the buttons was not a criticism of buttons per se. It was the location of the buttons next to a multi-select listbox. You wouldn't necessarily want to change deposit records after selecting values in the listbox. You could be adding/editing other controls on the deposit form for this same deposit record.

    Also note that my intent is not to slow your project development; nor to suggest there is only 1 way to do things. You have an interest to learn and it must be confusing to assimilate info from multiple sources. Members here have a variety of experiences and offer suggestions in good faith. It is sometimes difficult to offer focused, constructive criticism while the target is moving. Best that you ask for clarification and/or options.

    The relationships in the database and the listbox on the form showed as Bound. As Mike says in #106 he changed this to Unbound. This is a common approach. When you have reference tables/lookup tables, the associated control is often unbound; the selection(s) are used to populate the link table. (See Bound and Unbound Controls)


    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?

    Basically that's true. Critical at this point is to ensure that the logic of code and structure of the database gets you access to, storage and retrieval of data where/when you need it. The code can be "prettied up" later once you know the functionality is sound.



    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?

    The green text represents comments. Comments can be inserted almost anywhere in vba code.
    The comment starts with a single quote ('). Comments are used to clarify the purpose or definition or rationale of some aspect of the business/variables/logic involved. This can help you when developing and you or anyone else who is maintaining or adjusting code. Comments do NOT have to be removed necessarily.

    Debug.print statements will display information in the immediate window. This can be an excellent debugging tool.
    I used the comments to assist you in understanding/learning a little vba and the associated logic.

    The Blue and Black text is a result of my using a coloring program to display code. The Blue colour highlights some key
    syntax/constructs within vba.(eg: For /Next; With/End With; If/End If). The Black shows generic vba programming code.
    I thought the colour would help highlight vba syntax. But I notice that it loses all indentation of the code.

    What is/are your next steps?

    At some point you might consider providing descriptions/definitions for each table and field. I will look at some of these based on posts within this thread and will forward anything I create to you for consideration. The purpose is to firm up table designs.
    Last edited by orange; 05-20-2022 at 08:27 AM. Reason: spelling

  3. #108
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Please note that my comment about the buttons was not a criticism of buttons per se. It was the location of the buttons next to a multi-select listbox. You wouldn't necessarily want to change deposit records after selecting values in the listbox. You could be adding/editing other controls on the deposit form for this same deposit record.

    Makes sense - I will move them to the bottom of the form

    Also note that my intent is not to slow your project development; nor to suggest there is only 1 way to do things. You have an interest to learn and it must be confusing to assimilate info from multiple sources. Members here have a variety of experiences and offer suggestions in good faith. It is sometimes difficult to offer focused, constructive criticism while the target is moving. Best that you ask for clarification and/or options.
    Oh I would say almost everything about this process has been confusing haha! But all the help has been amazing too.

    The relationships in the database and the listbox on the form showed as Bound. As Mike says in #106 he changed this to Unbound. This is a common approach. When you have reference tables/lookup tables, the associated control is often unbound; the selection(s) are used to populate the link table. (See Bound and Unbound Controls) I think I understand, thank you. I had assumed that if it was not bound anymore, then it would have no relationship / wouldn't know where to get the info from.

    What is/are your next steps? Thanks for your explanation above, and it is helpful, but I still don't really understand anything at all about coding/coding language, so I will either use Mike's solution in #103, or try to add your code from #104 and see if I can get it to work this time, then all being well, try to replicate this for the other multi-value boxes. I can add table/field descriptions if it will be helpful for people here. I am the only person that will be entering data into the eventual database so I don't need to add them in for other end-users Aside from that, I have some query ideas to test, but surely I need some data in first?

  4. #109
    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,171
    ??Do you understand the code in #104??
    If you read the comment, does it make sense?
    The comments are positioned above the code that "answers" the comment - at least that's the general intent.
    Just enough vba to get some appreciation of code intent and syntax.

    If you have ~10-15 test records and use your forms to enter that data, you can then devise some queries to select specific records for various conditions. This is part of the debugging/vetting process to ensure accessibility for whatever "business/archaeology activity" is involved/needed.

    My earlier points on defining/describing tables and fields is to add descriptions so that the descriptions can stay with whatever tables and adjustments are finalized. In effect, the tables are designed and descriptions added and those do not have to be repopulated again from scratch. Once populated (at least to some degree) that version/instance of the table will be part of the database you are developing and that readers/members will have for review and testing. At this point we all have different instances --NOT a show stopper but could lead to more confusion (or attempting to "solve" an issue that was "fixed" in a different instance.

    I am going to take a copy of your latest database and create a procedure for adding descriptions.

    Which database is the "latest" at the moment? I will use that as base.

  5. #110
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    ??Do you understand the code in #104??
    If you read the comment, does it make sense?
    The comments are positioned above the code that "answers" the comment - at least that's the general intent.

    The comments do make sense, the issue is my general understanding – What I mean is that I don’t know what any of these kinds of terms mean:
    “Sub” “Dim”, “AsVariant”, “DCount”, “Me.”, “dbFailOnError”, “Const sql_Delete As String”

    Or how to use any of them. So for example where you have written this:

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

    I have changed List173 to a different name, do I just replace List173 with it to make this work?

    I also don’t understand why there are brackets around certain things, or (.), or underscores, or when to use “, or how spaces work, or why you have multiples of 10 at the side of the code. I don’t understand why a code may not work, or how to go about fixing it if it doesn’t. I am not asking you to explain all this to me, I am sure the information is out there

    As I said the comments are very clear, but if I go off the code itself more than the comments, I think your code in #104 is asking the database to see how many artefacts are currently assigned to each deposit, and what types, and then if it is more than 0, delete them?? Which connects to the second sub somehow? Then I am not sure, then is it asking it to create a record in the link table, containing the DepositID and the associated goods data? Then make it actually happen?

    Which database is the "latest" at the moment? I will use that as base.
    Attached is V.11 – This is basically unchanged but I moved the buttons in the Deposits form, and it has Mike’s working button for associated goods 😊 Database v 11.zip

  6. #111
    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,171
    Michael,

    Not to worry re the specifics of the vba at the moment.

    The comments do make sense Great!

    I don’t know what any of these kinds of terms mean: Or how to use any of them.
    “Sub” “Dim”, “As Variant”, “DCount”, “Me.”, “dbFailOnError”, “Const sql_Delete As String”


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

    Here's the scoop on the line of code

    50 ---is the line number; most people don't use line numbers; I do

    Debug.Print-- is a vba instruction that means Print/Display the following info in the immediate window

    "Number of Artefacts selected is " ---is a text that I created; it is simply informational

    &---is a vba element that says concatenate/combine whatever precedes this symbol with whatever follows

    Me.List173.ItemsSelected.Count ---this means on this current form(Me) Count the number of SelectedItems for the Listbox control named (List173).

    All to display the following:

    Number of Artefacts selected is 2

    It (debug.print) is a technique to view the values of controls while coding/debugging. Using various Debug.Print statements before and after certain Events (points where Access responds to some user interaction eg click a button, open a form, after adding a value to a control.....). Don't get bogged down in this stuff at this point.

    I'll start looking/using Database in the zip posted in #110.



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

    Not to worry re the specifics of the vba at the moment.

    The comments do make sense Great!

    I don’t know what any of these kinds of terms mean: Or how to use any of them.
    “Sub” “Dim”, “As Variant”, “DCount”, “Me.”, “dbFailOnError”, “Const sql_Delete As String”


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

    Here's the scoop on the line of code

    50 ---is the line number; most people don't use line numbers; I do

    Debug.Print-- is a vba instruction that means Print/Display the following info in the immediate window

    "Number of Artefacts selected is " ---is a text that I created; it is simply informational

    &---is a vba element that says concatenate/combine whatever precedes this symbol with whatever follows

    Me.List173.ItemsSelected.Count ---this means on this current form(Me) Count the number of SelectedItems for the Listbox control named (List173).

    All to display the following:

    Number of Artefacts selected is 2

    It (debug.print) is a technique to view the values of controls while coding/debugging. Using various Debug.Print statements before and after certain Events (points where Access responds to some user interaction eg click a button, open a form, after adding a value to a control.....). Don't get bogged down in this stuff at this point.

    I'll start looking/using Database in the zip posted in #110.


    Thank you!!

  8. #113
    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,171
    Just a post to say I have a few things that may be helpful and will forward same in the next few days.
    A heads up to avoid unnecessary duplication of effort.

    Latest Info/Status:
    1. Exchanged forum posts to show comments for consideration; get some guidance on next steps so we're not colliding unnecessarily. Have downloaded Michael's latest zip and will use it for any testing/adjustment and will provide him with whatever I have via forum.
    I have named it ?currentdb.Name
    C:\Users\JP\Documents\MichaelDatabase v 11_J.accdb Note: Database v 11

    I have copied some tables, queries report and modules dataAdmin and DataDictionary to this new database.


    2. Using DocumentTables and forum posts for info, created a table Data_MichaelDocT to assign names and descriptions as appropriate for input to the database(tables and fields).

    Suggest this or similar should be authority for definitions/descriptions. Michael can adjust as needed/practical.

    Added some routines to add descriptions to tables and fields within tables and populated some table designs.
    3.
    Here is preliminary list of tables followed by description where description exists
    Data_MichaelDocT
    Table to gather Table, field names and descriptions for use with Michael's archaeological database

    Data_PropertiesAllControlsAllForms
    Control Properties of All Forms

    data_tblControlsOnForms
    Contains Contol info for Forms in this database

    tblAMS_C14_Dates
    Accelerator Mass Spectrometer--radiocarbon dating

    tblAssociatedGoods
    A table containing several preset grave good options

    tblAssociatedGoodsLink
    A table relating a Deposit and AssociatedGoods

    tblBound
    A table of preset options to indicate if individual was bounr

    tblCutShapes
    a table containing preset cut shapes

    tblDepositContextLink
    a table relating a deposit and its context

    tblDepositContexts
    a table containing 17 preset burial contexts

    tblDeposits
    Table of unique deposits/individuals

    tblDepositTypes
    a table containing 5 deposit type options (inhumation, cremation, partial, disarticulated, comingled)

    tblPeriod
    a table containing several preset time period options

    tblSites
    A table of unique archaeological sites

    tblSiteTypes
    a table of preset SiteTypes eg settlement, cemetery..



    Sample of table and fields:
    tblSites: a table of preset SiteTypes eg settlement, cemetery..

    SiteTypeID: the unique id of preset siteTypes associated with this site
    SiteType: text describing the siteType where the individual was found

  9. #114
    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,171
    Michael,

    I am returning your zip file. In addition to the database you sent to me, included is the copy I used to create and populate the Table descriptions and field descriptions based on the various posts in the thread. I changed nothing else in that database.
    I just added the descriptions.

    If you open any table in design mode, you will see the field descriptions. These can be edited in the design view.

    If you right click ona table name, you get an option to show Table Properties, click that to see the Table descriptions. Again, you can review/edit and save as you see fit.

    These descriptions and any edits you make will stay with these objects.

    You will notice that some descriptions are empty. This is because I wasn't sure of the real meaning.

    I have attempted to get the descriptions as close to what was discussed in the forum, but you can remove/adjust/edit as needed for your project.

    You can rename the database as you wish.

    Good luck with your project.



    PS:

    These are last few comments from my list:

    14: Created form to assist review/edit/update of Table and Field descriptions.
    Need a mechanism to transfer these descriptions and related processes to Michael.

    Will test a separate DB to apply the descriptions to Tables and fields in another copy of Michaels'database.
    Will need a second database, not CurrentDB,create a routine so Michael can run at his end
    OR
    Just return the database with the descriptions populated.



    15: Opted to return a copy of his zip file with a second copy of his database with table and field descriptions all populated.
    Attached Files Attached Files
    Last edited by orange; 05-22-2022 at 04:24 PM. Reason: added latest comments

  10. #115
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Thank you @Orange, these are great! I will start filling in the rest now

  11. #116
    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,171

  12. #117
    Michael91 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    76
    Quote Originally Posted by orange View Post
    How are things progressing??
    Hi!

    I have made some developments - had some chats with Mike and he helped a lot (he did most of it) - I have all necessary tables, fields and forms (I think), all fields have descriptions. I now have several functioning buttons for entering listbox values, and Mike suggested adding subforms that show the entered values, just to confirm it works as needed.

    However -

    I have now got three more buttons with code that will not work. I have the same issue every time, even though the code is pasted and edited from functioning buttons. I had the same issue with a previous button and Mike found a workaround by building a macro. I would be interested to know if you have any thoughts as well - why is it not working? Is there a solution without a macro?

    The broken buttons are for:

    - Adding elements in the PartialSecondary subform (Deposits form)
    - Adding osteological analysis in the OsteoInfo subform (Deposits form)
    - Adding scientific analysis in the ScienceInfo subform (Deposits form)

    In each case I get this error: Error 2450 (Microsoft Access cannot find the referenced form '-----'.) in procedure -----, line ---.

    Obviously in each case the referenced form, procedure name and line changes. I have checked for spelling mistakes, I think the right form is present each time too.

    Once these are done I have a couple more things to figure out, but one issue at a time

    If I have not given enough detail just say - current database attached. Database v 12.zip

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

    I have fixed the process for Partial Elements. I have still used a delete query as I find it much easier to manage.
    Attached Files Attached Files

  14. #119
    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,171
    Quote Originally Posted by Michael91 View Post
    Hi!

    I have made some developments - had some chats with Mike and he helped a lot (he did most of it) - I have all necessary tables, fields and forms (I think), all fields have descriptions. I now have several functioning buttons for entering listbox values, and Mike suggested adding subforms that show the entered values, just to confirm it works as needed.

    I don't know how MIke's response

    However -

    I have now got three more buttons with code that will not work. I have the same issue every time, even though the code is pasted and edited from functioning buttons. I had the same issue with a previous button and Mike found a workaround by building a macro. I would be interested to know if you have any thoughts as well - why is it not working? Is there a solution without a macro?

    The broken buttons are for:

    - Adding elements in the PartialSecondary subform (Deposits form)
    - Adding osteological analysis in the OsteoInfo subform (Deposits form)
    - Adding scientific analysis in the ScienceInfo subform (Deposits form)

    In each case I get this error: Error 2450 (Microsoft Access cannot find the referenced form '-----'.) in procedure -----, line ---.

    Obviously in each case the referenced form, procedure name and line changes. I have checked for spelling mistakes, I think the right form is present each time too.

    Once these are done I have a couple more things to figure out, but one issue at a time

    If I have not given enough detail just say - current database attached. Database v 12.zip
    Hi Michael;

    Glad to hear you are progressing and getting some help.

    When you enter data, or even when you want to check that entered records are available for confirmation activity it is wise to build a quick form or a query or whatever and check,review and confirm before entering ALL of the data. You'd be surprised at the number of people who have spent weeks/months entering their data only to find out that some field was missing, the relationships are incorrect or the design doesn't allow for XXX.

    Work against the model; test and confirm; adjust if necessary; keep notes on issues as they arise (a log of things such as broken buttons;...anything that is problematic no matter how trivial ---you can always ask the forum, and when you do you'll have some context/detail that will facilitate communications.

    I personally would stay clear of macros. There will be 1 or more vba based solutions in most cases. The forum can help with that.

    I see that Mike has responded with fixed issues so haven't pursued any detail. I suggest that when you provide a database, or when someone sends a database in response, that it is clearly identified so you/we don't work on an already solved, or worse a new version headed down a new path. You can use version numbers/subnumbers whatever you need. Since it's your project, I suggest you assign versions to your databases as you change things.

    I don't know if your Database_v_12.zip is the same as the attached to Mike's post or not. I'd think not since he said he fixed things.


    When you do have an issue===identify the form or table, any event info or specific line. If you have an issue where you tried X and got an error or B, tell us what you did, the data you used and describe what you expected to get.
    eg
    1) I clicked the right arrow button on FormGetInfo I thought I'd go to the first record; but I got error 999.
    2) I ant to divide the TotalAmount by the number of months, but I got an error that said Error202 division by 0.

    When you say you are populating Listboxes, how exactly are you doing that---point form please. There may/may not be an opportunity to try another method.

    Getting things done is one thing, but there may be some optimization and learning options that will give you a degree of self-confidence with the database and processes. The forum is available to advise and answer questions - especially those where you have put in effort. We do get the requests "Could somebody create an accounting system for me?" You know your archaeology subject matter, you have a good idea for what you want, it's now a question of How to do this with Access--and that sets you in position to get focused responses.
    Just remember, we know some things about databases and Access, but zip of archaeology. So communications - terminology, examples, analogies, concepts will be key and clarity is expected.

    Enough soap box. You can PM me, or ask in the forum as you see appropriate. There are many ways to accomplish things and no one person has them all.

    Good luck.

  15. #120
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,053
    Hi Orange

    I have taken heed of your guidance on not using macros. I converted all of the Macros back to your recommended VB Method.

    I also took note of naming the version.
    Attached Files Attached Files

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