Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    estergr is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2022
    Posts
    10

    How to create a new record and automatically assign id from another table

    Hi all, I hope you can help with a problem I have, that of course I don't know how to solve.

    I work in cancer research, so my database is about cell lines. We use special boxes to store cells, each box has 81 spot, in each spot is stored one vial of cells.

    This is what I need

    Each time I add a new box, I need automatically to assign all the 81 spot to that box, even if there are some spot with no vials at the time I create the box.

    I have a table called BoxT (which is populated by the form BoxF) and I have the table PositionT (for the spots)



    BoxT has the following structure
    IDBox
    BoxName
    StorageFridge

    PositionT has the following structure
    IDPosition
    IDBox
    Position (this goes from 1 to 81).

    I hope I have been clear and that somebody can help.

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    run an APPEND query to add the record from tbl1 to tbl2
    the tbl1.ID (auto) would got to tbl2.ID (long int)
    then open the form2 on that ID.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you upload a small sample of your database (remove any sensitive info, run a compact and repair then zip it and use the Go Advanced button to get to the upload section)? What exactly are you trying to achieve, shouldn't PositionT table also have a field for the VialID (or do you have another linking table that has that)?
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    Quote Originally Posted by estergr View Post
    Each time I add a new box (parent record), I need automatically to assign all the 81 spot to that box (insert 81 child records), even if there are some spot with no vials at the time I create the box.
    The following procedure can be added to a module, but can also be added to a form.
    Code:
    Sub AssignSpotsToBox(theBoxID As Long)
        ' Declare variables to hold the current spot count and total spot count
        Dim theCurrentCount As Long
        Dim theSpotCount As Long
        theSpotCount = 81 ' Each box has 81 spots
        
        ' Loop through each spot in the box and insert a new row into the database
        For theCurrentCount = 1 To theSpotCount
            CurrentDb.Execute "INSERT INTO PositionT " & _
            "(IDBox, Position) VALUES (" & theBoxID & ", " & theCurrentCount & ");"
        Next
    End Sub
    This is how you would use the procedure above from outside of a form, or from an unbound form.
    Code:
    Sub SomeProcedure()
        ' Call AssignSpotsToBox with a box ID of 2
        AssignSpotsToBox 2
    End Sub
    If you have a form bound to your BoxT table, and you are adding the Box from there, then you can use the After_Insert event of that form like this:
    Code:
    Private Sub Form_AfterInsert()
        ' Call AssignSpotsToBox with current BoxID
        AssignSpotsToBox Me.Recordset.Fields("IDBox").Value
    End Sub
    With this code you will add 81 spots to the PositionT table populated with numbers from 1 to 81 in the Position field once the Box has been inserted. The insertion of the 81 position records will happen only if you inserted the Box from this form, and it won't fire if you are adding the Box from somewhere else, like another query. If you want to trigger the insertion of the 81 position records whenever a box has been added, you can use a table macro. But I don't like them, so I don't recommend them. I prefer the VBA approach, which is what I posted.

    I understand what you mean by "even if there are some spots with no vials at the time you create the box" within the context of your physical workplace. However, it's not clear to me what behavior you expect from the database in this scenario.

  5. #5
    estergr is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2022
    Posts
    10
    Thank you everybody for your help.
    Try to explain as clear as possible, even though it will be a bit long.
    We have a list of cell lines ( In real db I have about 9000 records). These cells are in vials stored in boxes. Each box contain 81 spot, in each spot there is a vial . Thing is that they are not “fixed”, I mean it doesn’t not happen that once is stored that’s it, it can happen that we use that vial and store again in another box, so the previous spot become available for next vial (but this I ve sorted out).

    In the picture below you will see a box with the spot and cell line.

    Click image for larger version. 

Name:	Cell Box.jpg 
Views:	42 
Size:	33.5 KB 
ID:	49893


    When you click on one of the spot, it will open the forms showing the details of the vial stored in that position, if you click on an empty spot it will open the detailed record but with no information except for group, box, rack and position. So far everything works exactly as I need, as these records have been imported from an excel file, so all 81 spots were imported even though some of them were empty.
    Here is my problem.
    If I (or other members of my research group) add a new box, a new box is created, and I can see it in the form (ScatolaM) with all the 81 spot. But if I click on an empty spot, it does open the cell form but it doesn’t show group, box, rack and position, and I know that is because a record with that idbox and idposition has not been created yet.
    So I was trying to find a way that when I add a new box, automatically 81 new records referring to that box are created, so that when I click on an empty spot of the new box I already have the basic information I need (group, box, rack and position). I know that we could just insert from scratch, but believe me that has caused quite a mess and we can’t afford to lose information on cell line.
    Sorry for the long post, hope you can help

    Thanks

    I ll attach the db in the next post

  6. #6
    estergr is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2022
    Posts
    10
    Here is the db
    Attached Files Attached Files

  7. #7
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    You can check the attached modification of your database to know how to deal with that using your current design.

    It would be irresponsible of me not to tell you that the design could be improved. You are storing IDGruppo in two tables. Your line table, which is a child record, should not have that field, only your ScatolaT table.

    The design of the form is clever, but there are things that can be done with less effort. For instance, your Line form could simply reference the Scatola form in its query and it all would be in sync. You have buttons on top of textboxes when you could use the click event of the textbox. Instead of 81 Dlookups for the textboxes, you could open one recordset and populate all 81 textboxes with that, etc.

    The fix presented in the database attached is like a hack, I'm referencing your Scatola form from the default value property of the controls you want to have populated. By the way, for positions, I only fixed the first 9 positions, your top row. You can add the other 72 using the same approach.

    Let me know how it goes.
    Attached Files Attached Files

  8. #8
    estergr is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2022
    Posts
    10
    Quote Originally Posted by Edgar View Post

    It would be irresponsible of me not to tell you that the design could be improved. You are storing IDGruppo in two tables. Your line table, which is a child record, should not have that field, only your ScatolaT table.
    You are right, I ve settled that

    Quote Originally Posted by Edgar View Post
    The design of the form is clever, but there are things that can be done with less effort. For instance, your Line form could simply reference the Scatola form in its query and it all would be in sync.
    Can you explain better what you mean?

    Quote Originally Posted by Edgar View Post
    You have buttons on top of textboxes when you could use the click event of the textbox.
    Thanks a lot, I' ll try to sort that

    Quote Originally Posted by Edgar View Post
    Instead of 81 Dlookups for the textboxes, you could open one recordset and populate all 81 textboxes with that, etc.
    I knew there would be a simpliest way to deal with the spots instead of using dlookup for each spot, but to be honest I am not in access to know how to do it, so I used dlookup because I am familiar with it


    Quote Originally Posted by Edgar View Post
    Let me know how it goes.

    Thanks a lot, that worked just as I needed

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I also had a go at this.

    Added Option Explicit to all modules.
    Replaced "Word Style" double quotes with "Access Style" double quotes in several places.
    Obtained clean compile, code cleanup.
    Fixed a dozen or so run-time errors.
    Fixed cascading search combos on form LineaContinuaM.
    With addition of box to ScatoleT, 81 corresponding records added to LineaT.
    Set tab stops to NO for 81 textboxes on form ScatolaM.
    Replaced 81 on-click procedures with one procedure on form ScatolaM.
    Added error handler to all new and existing code where appropriate.
    Removed criteria from some queries, as criteria better suited to put in form combo click_update procedures.
    More that I don't immediately recall...


    Navigation between forms is muddled. OP needs to clean up interaction between forms when opening and closing.

    CellLineDB-davegri.zip

  10. #10
    estergr is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2022
    Posts
    10
    Quote Originally Posted by davegri View Post
    I also had a go at this.

    Added Option Explicit to all modules.
    Replaced "Word Style" double quotes with "Access Style" double quotes in several places.
    Obtained clean compile, code cleanup.
    Fixed a dozen or so run-time errors.
    Fixed cascading search combos on form LineaContinuaM.
    With addition of box to ScatoleT, 81 corresponding records added to LineaT.
    Set tab stops to NO for 81 textboxes on form ScatolaM.
    Replaced 81 on-click procedures with one procedure on form ScatolaM.
    Added error handler to all new and existing code where appropriate.
    Removed criteria from some queries, as criteria better suited to put in form combo click_update procedures.
    More that I don't immediately recall...


    Navigation between forms is muddled. OP needs to clean up interaction between forms when opening and closing.

    CellLineDB-davegri.zip

    Thanks a lot, I knew there were lots of problems, but I have done the best I could with my knowledge. Unfortunatly we tried to buy a proper db but none is really usefull for our needs, all of them are full of staff we don't need, so best option we had was to try with access.

    Thanks a lot for your help, really appreciate

  11. #11
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    Access is a great choice for running local network applications. However, it may not be suitable for web-based applications, strong security requirements, modern software aesthetics, or real-time notifications of table changes. Keep in mind that if you require any of these features in the future, you may encounter limitations with Access. It's good that I don't see the need for any of those things in what you are currently working with, so I believe you made a good choice.

  12. #12
    estergr is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2022
    Posts
    10
    Quote Originally Posted by davegri View Post

    Fixed cascading search combos on form LineaContinuaM.


    CellLineDB-davegri.zip
    Once again thanks for all your support (to both of you).
    Unfortunatly for the form LineaContinuaM your solution to use combobox for searching through the db is not useful. I try to explain. We have about 8000 vials of cell lines, lots of them have the same name, because basically are the same line. Difference between them is the time they have been treated (passaggi), or their derivation (etichette). When we decide which vial we have to use for an experiment we have just to type a keyword, so that we have an overall sight of the cell line we have available, after that we might go (or not) with the derivation or the number of treatments. Same for the boxes, we cannot go trough combobox because we have about 500 boxes.

    Problem is I tried to restore my form, but now it doesn't work anymore, and I can't figure out why, maybe because of something you changed that I can't see? Can you help on this last matter?

    Thanks again

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    CellLineDB-davegri-v02.zip

    OK. See new version attached. You don't need to scroll down through the comboboxes, just start typing in the box and it will automatically select the matching value in the dropdown. As soon as you see your desired match in the dropdown, just hit ENTER or TAB.

    Click image for larger version. 

Name:	combo.png 
Views:	18 
Size:	27.4 KB 
ID:	49927

  14. #14
    estergr is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2022
    Posts
    10
    Quote Originally Posted by davegri View Post
    CellLineDB-davegri-v02.zip

    OK. See new version attached. You don't need to scroll down through the comboboxes, just start typing in the box and it will automatically select the matching value in the dropdown. As soon as you see your desired match in the dropdown, just hit ENTER or TAB.

    Click image for larger version. 

Name:	combo.png 
Views:	18 
Size:	27.4 KB 
ID:	49927
    I am sorry but that doesn't work. As I told you we have about 9000 records. When we search for a vial, we search for keyword which not always is at the beginning. For example we might search for OPN, to get all the osteopontin cells, and if I type OPN in the combobox it doesn't return any results.

  15. #15
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I am sorry but that doesn't work. As I told you we have about 9000 records. When we search for a vial, we search for keyword which not always is at the beginning. For example we might search for OPN, to get all the osteopontin cells, and if I type OPN in the combobox it doesn't return any results.
    The DB that you supplied has 324 LineaT records, with no OPN NomeLinea records. Have you tried the DB in post#13? The searched for NomeLinea does not need to be near the first. In fact, if it's the 9000th record in LineatT, it will be automatically SHOWN and SELECTED if you type its name in the combobox.

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

Similar Threads

  1. Automatically create new record if null
    By mlrucci in forum Forms
    Replies: 9
    Last Post: 04-25-2021, 10:08 AM
  2. Automatically assign number to every new record
    By BigJohn89 in forum Programming
    Replies: 15
    Last Post: 01-08-2018, 03:32 PM
  3. Replies: 2
    Last Post: 03-16-2017, 05:50 AM
  4. Replies: 5
    Last Post: 06-02-2015, 02:51 PM
  5. Replies: 7
    Last Post: 04-17-2012, 11:53 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