Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,082
    Why are member names stored into other tables (Guest Tickets and Keys_issued_register) instead of member number? What purpose does Keys_issued_register table serve?

    Strongly advise not to use spaces in naming convention. Also advise to give controls more meaningful names than the Access defaults.

    Debug/Compile fails on procedures for buttons that don't exist.



    New_key_register table has ID field defined as PK but Keyno is saved into Angling_details as FK. Designated PK should be saved as FK. Either set Keyno as PK or save ID into Angling_details.

    Still trying to understand business processes. Since Angling_details appears to be a transaction table for keys issued and payments, under what circumstances would you want to do a bulk return of keys and set NEWKEY to null? Or since this button is not visible, is procedure not even used?

    Why is year 2022 hard-coded in the SQL for IssuedKeys procedure? I ran this procedure for 2023 and I am confused by the KSEASON data. Value in New_key_register is not what is shown in sfrmKeys. Why is Chambers listed when their status is LAPSED? - other LAPSED were not included.

    If you want to utlilize USED field to indicate keys that should not be assigned (for whatever reason - already assigned, lost), can certainly do that. However, the process to programmatically update that field when keys are issued/returned is what is not advised. Keys unavailable for issue due to assignment can be calculated but then if need to additionally restrict a key because it is LOST that must be identified somewhere. This brings to mind MS LendingLibrary template. Each book loan is a transaction with CheckOut and CheckIn data. If CheckIn field is null book is out and not available. If asset is removed from inventory (lost, sold, destroyed) a date field in Assets table indicates such.
    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.

  2. #17
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by June7 View Post
    Why are member names stored into other tables (Guest Tickets and Keys_issued_register) instead of member number? What purpose does Keys_issued_register table serve?

    Strongly advise not to use spaces in naming convention. Also advise to give controls more meaningful names than the Access defaults.

    Debug/Compile fails on procedures for buttons that don't exist.

    New_key_register table has ID field defined as PK but Keyno is saved into Angling_details as FK. Designated PK should be saved as FK. Either set Keyno as PK or save ID into Angling_details.

    Still trying to understand business processes. Since Angling_details appears to be a transaction table for keys issued and payments, under what circumstances would you want to do a bulk return of keys and set NEWKEY to null? Or since this button is not visible, is procedure not even used?

    Why is year 2022 hard-coded in the SQL for IssuedKeys procedure? I ran this procedure for 2023 and I am confused by the KSEASON data. Value in New_key_register is not what is shown in sfrmKeys. Why is Chambers listed when their status is LAPSED? - other LAPSED were not included.

    If you want to utlilize USED field to indicate keys that should not be assigned (for whatever reason - already assigned, lost), can certainly do that. However, the process to programmatically update that field when keys are issued/returned is what is not advised. Keys unavailable for issue due to assignment can be calculated but then if need to additionally restrict a key because it is LOST that must be identified somewhere. This brings to mind MS LendingLibrary template. Each book loan is a transaction with CheckOut and CheckIn data. If CheckIn field is null book is out and not available. If asset is removed from inventory (lost, sold, destroyed) a date field in Assets table indicates such.
    Hi June7

    To get the DB down to uploadable size, I had to delete a lot of components. Itís possible I deleted something that I should have left in.

    When keys are returned, our fishery manager, who is the sole db user, waits until returns are pretty much complete, physically sorts the keys and inputs them as a sequential group where possible and individuals or short runs where he canít. If a key is lost we will assign a membership number way out of range to define it and replace it. Öwhen and if it happens. That piece of code works fine. There is now a separate form for returns.

    2022 was hard coded by one of your fellow forum members as a basis for using Div and Mod to return the correct key series for odd and even years. (Thatís my understanding!)

    Iím not sure why Chambers is appearing. I will take a look. It may be that there is a 2023 membership being picked up. I didnít write the modkeys module, it was kindly done for me and I confess I can understand queries but that piece of SQL is beyond me. The relationship between the two tables on the form have been fine when starting with a completely unused set of key numbers for the last two years but this is the first year where there used keys that cannot be issued.

    In running the ďissueĒ code, it needs to find members with no key for the current year in angling_details, ignore any that do have keys. From the New_key_register, only unused keys can be issued. When I tested it a couple of years ago it certainly seemed to bypass anyone with a key successfully.

    The difference in Kseason data and the key register has me stumped too. I think thatís the final piece piece of modkeys code issuing all key numbers available or not and confusing things.

    Itís probably not how the code was written but had I been capable of doing writing it I would expect bulk issue to show members without a key one side and only available keys the other. After issue, no members one side and only remaining keys the other side.

    I hope that answers everything. Iím certain that once used is taken account of, my problems are solved

    Best wishes and thanks
    PDilly

  3. #18
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by June7 View Post
    Why are member names stored into other tables (Guest Tickets and Keys_issued_register) instead of member number? What purpose does Keys_issued_register table serve?

    Strongly advise not to use spaces in naming convention. Also advise to give controls more meaningful names than the Access defaults.

    Debug/Compile fails on procedures for buttons that don't exist.

    New_key_register table has ID field defined as PK but Keyno is saved into Angling_details as FK. Designated PK should be saved as FK. Either set Keyno as PK or save ID into Angling_details.

    Still trying to understand business processes. Since Angling_details appears to be a transaction table for keys issued and payments, under what circumstances would you want to do a bulk return of keys and set NEWKEY to null? Or since this button is not visible, is procedure not even used?

    Why is year 2022 hard-coded in the SQL for IssuedKeys procedure? I ran this procedure for 2023 and I am confused by the KSEASON data. Value in New_key_register is not what is shown in sfrmKeys. Why is Chambers listed when their status is LAPSED? - other LAPSED were not included.

    If you want to utlilize USED field to indicate keys that should not be assigned (for whatever reason - already assigned, lost), can certainly do that. However, the process to programmatically update that field when keys are issued/returned is what is not advised. Keys unavailable for issue due to assignment can be calculated but then if need to additionally restrict a key because it is LOST that must be identified somewhere. This brings to mind MS LendingLibrary template. Each book loan is a transaction with CheckOut and CheckIn data. If CheckIn field is null book is out and not available. If asset is removed from inventory (lost, sold, destroyed) a date field in Assets table indicates such.
    Sorry June7
    I missed answering a couple of questions
    Guest tickets is a modified throwback from days gone by. To print a ticket with a guest and member’s name, holding them in the table was the easiest way. The printer document has been deleted as it contained confidential info.

    I suspect the keys issued register is probably no longer in use.

    angling details is an annual record
    some of the information is for financial stats or other annual data

    I do note the other points you’ve made. I’m quite certain my db will have made you wince. It has had to evolve. I built it for my use probably 15 years ago in simpler form and handed the task over 5 years ago having to make changes along the way. If I’d had the time, I would rewritten from scratch.
    we generally have under 200 members with 2000 lapsed or archived. I’m removing a lot of these later this year. Despite the untidy way it’s put together, it works! …except for bulk issue.
    Best wishes
    PDilly

    One day, I’ll have a clear out!

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,082
    Kseason is a field in New_key_register. SEASON is a field in Angling_details. It is the latter value displayed on sfrmKeys. Keyno 21, 22, 23 show 2021 Kseason but 2023 SEASON. What purpose does Kseason field serve?

    The hard-coded year 2022 is filter criteria - not following how this relates to MOD calculation, however, possibly not pertinent to issue:
    strSQLReg = strSQLReg & " LEFT JOIN (SELECT APERMNO, SEASON, NEWKEY FROM No_key WHERE SEASON=2022) AS Iss"

    Chambers is the only LAPSED to have a 2023 record in Angling_details. Should STATUS be changed?

    Key register form continues to show records on left side because that's how form RecordSource is designed.

    Which used keys cannot be reissued?

    "If a key is lost we will assign a membership number way out of range to define it" - what would this look like, not finding example in data.

    You must decide if you want to abandon approach of calculating keys that are available for issue or rely on a USED (status) field. If you stick with calculating, supplement with either a LOST field or dummy member number you described, or follow CheckOut/CheckIn approach I described.

    I have to say this process that records key numbers in Angling_details transaction records and then saves member number assigned the key in NEW_key_register is just complicated and doesn't really fit normalization principles.

    Should fix PK issue I identified.
    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.

  5. #20
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by June7 View Post
    Kseason is a field in New_key_register. SEASON is a field in Angling_details. It is the latter value displayed on sfrmKeys. Keyno 21, 22, 23 show 2021 Kseason but 2023 SEASON. What purpose does Kseason field serve?

    The hard-coded year 2022 is filter criteria - not following how this relates to MOD calculation, however, possibly not pertinent to issue:
    strSQLReg = strSQLReg & " LEFT JOIN (SELECT APERMNO, SEASON, NEWKEY FROM No_key WHERE SEASON=2022) AS Iss"

    Chambers is the only LAPSED to have a 2023 record in Angling_details. Should STATUS be changed?

    Key register form continues to show records on left side because that's how form RecordSource is designed.

    Which used keys cannot be reissued?

    "If a key is lost we will assign a membership number way out of range to define it" - what would this look like, not finding example in data.

    You must decide if you want to abandon approach of calculating keys that are available for issue or rely on a USED (status) field. If you stick with calculating, supplement with either a LOST field or dummy member number you described, or follow CheckOut/CheckIn approach I described.

    I have to say this process that records key numbers in Angling_details transaction records and then saves member number assigned the key in NEW_key_register is just complicated and doesn't really fit normalization principles.

    Should fix PK issue I identified.
    kseason gives the user an easy visible record of what keys have been used and when
    I put the three 2021 keys in there to show that presently the code would issue them despite being used.

    The season=2022 was added into the code later. There is a post earlier in the thread. I confess, I'm not entirely sure of it's relevance and it has no real bearing on Mod or Div to the current year. That will always be 1 or 0. As you rightly say, it doesn't, or shouldnt affect the current problem.

    Chambers was my fault. In putting in dummy data he should have been current. That was a knife & fork job. The system wouldn't normally let me do it.

    All keys flagged as used cannot be reissued. Whether that be from current or previous years

    I was wrong about setting an out of range membership number to denote a lost key. Will use a Year (kseason) such as 9900. there is also a comments field in the key register we can update manually.
    The returns routine uses kseason 9999 to visibly show a return has been made and sets used to false. The 9999 season is ignored and overwritten when the key is issued

    Would prefer to rely on used True/False field. in previous iterations of the db, that has been bulletproof.

    PK issue noted thanks.

    I hope I've covered everything

    Best wishes
    PDilly

  6. #21
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,082
    Okay, if you want to go with USED field, exactly what is holding you up? Change code as appropriate.
    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.

  7. #22
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by June7 View Post
    Okay, if you want to go with USED field, exactly what is holding you up? Change code as appropriate.
    Hi June7

    Iím afraid I didnít write the modkeys module code, it was written for me by another forum member, and Iím not competent enough with SQL to get the right syntax of code in the right place.

    I have had several attempts at what, I thought would work, but alas none did. Hence that was my reason for resurrecting the thread.

    I rather imagine that someone with your expertise, looks at the code and wonders why I havenít just changed it to suit!

    Best Wishes
    PDilly

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,082
    Would seem to be simple:
    Code:
        'SQL for the available keys for the given year.
        strSQLReg = "SELECT Keyno FROM New_key_register"
        strSQLReg = strSQLReg & " WHERE KeySeries= " & [Forms]![frmKeys].[cboYear] Mod 2 & " AND NOT Used;"
    I do wonder why code references form combobox instead of intYear variable.
    Code:
        'SQL for the available keys for the given year.
        strSQLReg = "SELECT Keyno FROM New_key_register"
        strSQLReg = strSQLReg & " WHERE KeySeries= " & intYear Mod 2 & " AND NOT Used;"
    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.

  9. #24
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by June7 View Post
    Would seem to be simple:
    Code:
        'SQL for the available keys for the given year.
        strSQLReg = "SELECT Keyno FROM New_key_register"
        strSQLReg = strSQLReg & " WHERE KeySeries= " & [Forms]![frmKeys].[cboYear] Mod 2 & " AND NOT Used;"
    I do wonder why code references form combobox instead of intYear variable.
    Code:
        'SQL for the available keys for the given year.
        strSQLReg = "SELECT Keyno FROM New_key_register"
        strSQLReg = strSQLReg & " WHERE KeySeries= " & intYear Mod 2 & " AND NOT Used;"
    Thank you June7. That works exactly as I wanted.

    The form with the combo box was part of the coding that was done for me. I confess that the combo box seems a bit pointless. It's not as if selecting other years has any purpose. I will look to change it as you suggest along with other points you've made.

    When you you say "it would appear to be simple", that's from someone with a wealth of knowledge of what to leave out of the code.
    My simple inexperienced approach was unfortunately based upon what to add in !!

    I'm very grateful for your help, time, effort and patience.

    Many thanks
    PDilly

  10. #25
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,082
    I said 'seem' because of my uncertainty I fully understood your process.

    Glad it worked out for you.
    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.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-27-2021, 10:49 AM
  2. code to change field size is not keeping change
    By markjkubicki in forum Programming
    Replies: 7
    Last Post: 04-04-2020, 01:28 PM
  3. FTP module needed (Chilcat
    By Cleave_1b in forum Programming
    Replies: 1
    Last Post: 02-12-2016, 04:20 PM
  4. Replies: 3
    Last Post: 04-17-2014, 08:37 PM
  5. Run Code Module Automatically
    By rmoreno in forum Modules
    Replies: 3
    Last Post: 06-18-2013, 11:55 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