Results 1 to 7 of 7
  1. #1
    Miked1978 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    31

    Need advice on sub form data entry

    I have a search form where users can search for the records they want to "print" and in the subform (datasheet) displays the records they searched for and it allows the users to check a box for each record that they want to "Print". For example a user can narrow their search to 100 records using the main form's search buttons and the subform will show those 100 records and then the user can select say for example 5 of those records by clicking the check box for each record in the subform.



    I have to update a backend SQL table with the records the user wants to print.

    My question is what should I do from here? At this point the user has selected the records they want to print by checking the checkbox in the subform. I was thinking about having the user confirm the records they want to print by sending them to another form that displays only the records they selected and have them confirm they want to print the records before making any changes to the SQL table. If they say YES then an update query runs and updates SQL. if they say NO then I take the user back to the search form. However to make things simple what if I just create a SAVE button on the subform that kicks of the Update query that updates the SQL table. If I go this route I want a message box to appear telling the user how many records they are about to update. I'm just not sure if all that should be performed in the subform itself. For instance I'm having problems finding what code I can use to make the count of records in the message box.

  2. #2
    Miked1978 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    31
    Well I got the message box to work counting the records. I think I'm going to proceed with the 2nd option.

  3. #3
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,791
    I have to update a backend SQL table with the records the user wants to print.
    why update a table? what if another user is doing the same thing with different records?

    Depends on your method of users selecting records but you could just pass a criteria to docmd.openreport. criteria would be something like this (assuming you have 'collected' the record PK's in a comma separated string called selectedPKs)

    "PK in (" & selectedPKs & ")"

    with regards showing the user what they have selected, just filter the form using the same basis.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,576
    Please tell us more about
    ...Update query that updates the SQL table...
    .

    Consider, you have 1000 records, the user selects 35 to Print--- where does the Update query fit?
    Could you not use the 35 recordIds or whatever identifies those records for printing, and pass them to your Print process?
    Just asking to clarify the requirement and intent.


    OOoops: A little slow - I see Ajax has responded.

  5. #5
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,791

  6. #6
    Miked1978 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    31
    Thanks guys I figured it out but I do have another question.

    I need to create a Order Number which will be a unique number and I would like to mean something rather than some random #

    So for instance the user opens the search form and using the list boxes provided they filter the data down to 10 records. The user then selects (checks a checkbox) 5 of those 10 records and that is what they want to order. Even though those 5 records are unique I need them to have the same order #. I don't think a simple autonumber field would work because it would create a unique number for each of those 5 records.

    I was thinking about utilizing the data the user is selecting and maybe a date stamp. For instance each record would have the same 4 digit item number so maybe the order number would consist of item # and date. Sample would be xxxx01282020 where xxxx is the item number. However I feel I need something a little more unique at the end of
    xxxx01282020???? to make it truly unique.

    Any suggestions?

  7. #7
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,791
    dates don't make good unique ID's. as a number they are a decimal datatype or as you seem them it is formated as text - which is not ideal for indexing

    I'm a bit puzzled by what you are trying to do, the usual way to handle this would be to have an order header record to containing order number, supplier, date, expected delivery date etc and the line records (which is what you are describing) would have a field linking back to the order header record. And each line record would have an autonumber field as a primary key.

    You might want to google what is often referred to as dmax+1 which looks something like this

    nextorderno=dmax("orderno","myTable")+1

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

Similar Threads

  1. Replies: 2
    Last Post: 12-11-2015, 02:55 PM
  2. Replies: 3
    Last Post: 02-25-2015, 04:48 PM
  3. Replies: 5
    Last Post: 03-09-2014, 07:16 PM
  4. Replies: 5
    Last Post: 08-12-2013, 12:53 AM
  5. Advice for simple data entry form
    By Fuergrissa in forum Access
    Replies: 4
    Last Post: 04-23-2013, 10:41 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
  •  
Tech Forums: Microsoft Office Forums