Results 1 to 11 of 11
  1. #1
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118

    Creating a number of records based on a number in an unbound number field.


    Background, a company sends an order number that includes a number of sessions and an expiry date. As sessions take place the sessions are used until either all used up or the expiry date is in the past.
    I have a table that holds one record for each session and the expiry day. If an order comes in I can add a new record for each session however typically an order is for 40 or 50 sessions so this is time consuming.
    I would like to enter an order number, number of sessions and expiry date into some unbound fields on a form then click a button and that number of records be created.
    I am novice level when it comes to VBA so please try to be clear with any suggestions or even if I am on the right track to achieve this.
    Summary, What code do I need to create the number of records in the [NumOfSessions] unbound field + also optional enter a sequential number starting from 1 in a [SessionNumber] field. So that each session created 1 -40 if 40 sessions created.
    Thanks for any help

  2. #2
    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
    Please step back and describe the underlying business in simple English.
    What exactly is an Order? A session?
    Who actually uses a session?
    Seems that somewhere in your process there is a Customer/User.
    Last edited by orange; 08-01-2021 at 05:08 AM. Reason: spelling

  3. #3
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    It is a HealthCare Business. Order numbers are provided by the local authority guaranteeing payment for a number of sessions (visits by a clinician to a service user) Visits should not take place unless a session order is in place and the session date is not in the past. Order numbers arrive via email and include: an Order Number Ref, Number of sessions in that batch (e,g 50) and expiry date. I intend to put this information in a table (one for each session) (the table has a 1 to many relationship with the service user table) then flag a record as used following each clinician visit.

    When the order number arrives via email it could include order numbers for 20 service users each authorising 50 sessions. I could go in and manually create 1000 individual records but obviously this would take some time and also open to human error. As each session has a monitory value I am trying to make the process as simple and quick as possible. My idea is to navigate to each service users record, enter the order number, number of sessions in the batch (e.g, 50) and expiry date. then click a button that will create that number of records (e.g. 50) each with identical data (possibly with the exception of a sequential number starting at 1 through to number in batch). Fields in this table are: [OrderID] [OrderNumber] [NumberInBatch] [NumberOfBatch] this is the sequential number [ExpiryDate] [Used] yes/No [DateUsed]

    Hope this makes it a bit more clear, happy to provide more info if needed.

  4. #4
    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
    What exactly is a service user?
    When you receive an Email for 20 sessions, how is their use restricted?

    From my interpretation of your post:

    - you could create X(number from email) empty session records each with BatchID_fk and unique sequencenumber (and possibly BatchExpiryDate).
    - I expect you will have a list of Clinicians uniquely identified.
    -when a "Patient" requires service, he/she will be assigned to the next available Session record and the "Patient, Clinician and Date of Service(after verification that Service Date is <=ExpiryDate) is used to update the Session record.

    In effect, there are 2 major steps
    -use the email info to create available session records, and
    -use available session records, patients, clinicians, (possibly type of service, diagnosis, comments, notes, etc) and update accordingly. It is unclear where your involvement starts and ends.

    Click image for larger version. 

Name:	OrderSessionsDraft.PNG 
Views:	16 
Size:	21.4 KB 
ID:	45882

  5. #5
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Quote Originally Posted by orange View Post
    What exactly is a service user?
    When you receive an Email for 20 sessions, how is their use restricted?

    From my interpretation of your post:

    - you could create X(number from email) empty session records each with BatchID_fk and unique sequencenumber (and possibly BatchExpiryDate).
    - I expect you will have a list of Clinicians uniquely identified.
    -when a "Patient" requires service, he/she will be assigned to the next available Session record and the "Patient, Clinician and Date of Service(after verification that Service Date is <=ExpiryDate) is used to update the Session record.

    In effect, there are 2 major steps
    -use the email info to create available session records, and
    -use available session records, patients, clinicians, (possibly type of service, diagnosis, comments, notes, etc) and update accordingly. It is unclear where your involvement starts and ends.

    Click image for larger version. 

Name:	OrderSessionsDraft.PNG 
Views:	16 
Size:	21.4 KB 
ID:	45882



    OK, It may be that I have asked this question in the wrong forum sub group, apologies if that’s the case. Let my try and explain the problem again using the image below.
    I am trying to automatically (on click of button) enter a number of records in a table. The subform below is based on a table [tbl_AuthNumbers] and in this example I am would like to create 50 records (Number in batch) in the table all with Order TA9876543210, Issue Date 02/08/21 Expiry Date 31/10/21. The Qty field would be a sequential number from 1 through to 50. Used & Date Used left blank.
    The data in the header is unbound but could equally be a table if it would make the code easier.
    I am very new to VBA so please try to be clear in any advice.

    Thanks

    Click image for larger version. 

Name:	TANumber screen shot.JPG 
Views:	12 
Size:	121.2 KB 
ID:	45889

    [/QUOTE]

  6. #6
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Some observations if you don't mind. I would rename Qty to BatchNo or similar - Qty 50 sounds like there will be 50 of them, not that it's the 50th in a batch.
    Your Used field is redundant as you have a date used if it's got a UsedDate it was used, no need for the additional check box.

    There are two ways to achieve this, a loop that does X number of single inserts, or a better set based option would be to use a numbers table and a cartesian join to get you the number of records required in one hit.
    The numbers based option is described here https://www.accessforums.net/showthread.php?t=84065 a query that will generate the appropriate number of records.
    Use that as the basis for an Insert query.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118

    Thumbs up

    Quote Originally Posted by Minty View Post
    Some observations if you don't mind. I would rename Qty to BatchNo or similar - Qty 50 sounds like there will be 50 of them, not that it's the 50th in a batch.
    Your Used field is redundant as you have a date used if it's got a UsedDate it was used, no need for the additional check box.

    There are two ways to achieve this, a loop that does X number of single inserts, or a better set based option would be to use a numbers table and a cartesian join to get you the number of records required in one hit.
    The numbers based option is described here https://www.accessforums.net/showthread.php?t=84065 a query that will generate the appropriate number of records.
    Use that as the basis for an Insert query.

    Thanks, Good points re BatchNo & UserDate. Thanks for advise re Cartesian join and using that for an insert query. I confess I don't fully understand how to do this but now have an idea to work on so will give it a go.
    Cheers

  8. #8
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you get stuck post up what you have tried.
    Cartesian joins are a great trick, even if it's not always obvious how it works. (Basically they multiply themselves out)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Sorry, things like this jump out at me, but button caption is incorrectly spelt?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Yes thanks however this was just a quick mock up to explain issue. I will pay more attention to detail in actual product.

    Thanks to Minty, I think I have now sorted, at any rate my append query is working, just got to apply to form and macro buttons now (I might get good at this one day!)

  11. #11
    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

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

Similar Threads

  1. Replies: 12
    Last Post: 07-26-2020, 01:27 PM
  2. Replies: 3
    Last Post: 12-22-2016, 12:54 PM
  3. Replies: 16
    Last Post: 06-20-2013, 09:25 AM
  4. Replies: 2
    Last Post: 08-15-2012, 02:21 AM
  5. Replies: 6
    Last Post: 07-25-2011, 01:54 PM

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