Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Dec 2007
    Posts
    70

    Question Unanswered: Adding multiple samples with a button

    I have a database with a sample booking in form. The form consists of various fields that need to be filled in and once completed this generates an auto number for the sample. The form then has a button that adds this info into a sample table (tbl_samples) and a survey specific table based on the survey field filled in in the booking in form (ie, tbl_analysis_AD, tbl_analysis_AL, tbl_analysis_CC, etc). This all works fine when booking in one sample into the database, which is what we do at the moment. But I have been asked if it is possible to book in samples in bulk to save time? i.e we have 11 sample that we have to book in every monday morning, how can we "fill in" 11 forms with just the click of a button? Or could a form be created that has the 11 samples perminantly filled in and we'd just have to fill in the date?
    Is anything like this possible?
    Cheers.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Um... yes.

    Just use VBA to create the 11 records.

    I don't have time to give you a sample code atm... someone else might
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2007
    Posts
    70

    Found some simple code, but need more!

    Hi,
    I have found some basic code for updating a table with one record.

    [code]
    Dim MyDb As DAO.Database
    Dim Myrst As DAO.Recordset

    Set MyDb = CurrentDb
    Set rst = MyDb.OpenRecordset("TblExample")

    With Myrst
    .AddNew
    !Field1 = xxx
    !Field2 = yyy
    !Field3 = zzz
    .Update
    End With

    Set Myrst = Nothing
    Set MyDb = Nothing
    [End code]

    This is great for adding one record to one table. But I need to add 11 new records and it needs to update Fields in two different tables that are linked by a common Field which is an autonumber in the first table.

    Can anyone help?

    OB1

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Not without further details. Since you have not told us anything more than you need 11 records, all that can be suggested is to repeat the code segment for as many times as you need to accomplish the goal.

    .AddNew
    !Field1 = xxx
    !Field2 = yyy
    !Field3 = zzz
    .Update
    .AddNew
    !Field1 = aaa
    !Field2 = bbb
    !Field3 = ccc
    .Update

    ...etc...
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Dec 2007
    Posts
    70
    Hi, Thanks I will try to elaberate.

    I have a generic table for all samples that are booked in (tbl_Samples) and then each sample will have tests carried out on them and different samples have different tests. Each test then has another table (tbl_analysis_AD, tbl_analysis_AL, tbl_analysis_CA, etc..).
    I need to make the recordset cover all of these tables so I can fill in the Fields of each relivent table, here's an example:

    A sample is booked into tbl_Samples which generates an autonumber for the sample (BiologyID), say this sample is for the "CA test", I need the relivant fields in the tbl_analysis_CA filled in and the autonumber generated in tbl_Samples to link the 2 tables.

    Normally we do all of this on a form for each sample as it comes in. But every monday morning 11 quality control samples need to be added so they will have exactly the same field enteries every time (Except for date that would need to be entered as a pop up).

    Now I think I can do most of that in VB, I'm just not sure how to make the code I posted earlier cover all the different tables and get the autonumber in tbl_Samples to populate the correct Field in the table for its test?

    Also forgot to mention that some samples require more than 1 test (i.e. more than 1 table linked via autonumber)

    I really hope this make sense. I'm finding it very difficult to explain.

    Cheers,
    Owain.
    Last edited by OB1; 02-01-08 at 05:22.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so there is no easy way. You basically have to use that sample code 11 times in your command button (or how ever many records are needed to setup the data as you need it) and use variables in the code to remember keys so that relationships are kept intact.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So what's wrong with using a loop..?
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Am I missing something?

    Code:
    INSERT INTO myDestinationTable(col1, col2)
    SELECT TemplateCol1, TemplateCol2
    FROM my11RecordProFormaTable
    ????

  9. #9
    Join Date
    Dec 2007
    Posts
    70
    Thanks for verifying that.

    When you say variables do you mean, as an example of a bit of code, somthing a bit like this?

    !Field1 = [tbl_Samples].[SampleID]

    Field1 being the field in the tbl_analysis_?? that needs the BiologyID autonumber from tbl_Samples. How do I get it to corrispond to the correct autonumber in tbl_Samples? i.e. When the sample goes into tbl_Samples it generates the autonumber '2000' for SampleID, how do I get the test for that specific sample to have that same number (2000) in it's Field of the same name (the name of the field for all tables is SampleID)?

    I'll have a go at writting the code and see how far I can get? I have never attempted a code this big/complicated before!

    Thanks again for all your help,

    OB1
    Last edited by OB1; 02-01-08 at 10:27.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ummm... you need to do some study on what variables are. The idea is you use the code to write a record, then you use a variable to remember the autonumber assigned to that new record, then you write another record with code using the variable to assign to the foreign key.

    Quote Originally Posted by pootle flump
    Am I missing something?

    Code:
    INSERT INTO myDestinationTable(col1, col2)
    SELECT TemplateCol1, TemplateCol2
    FROM my11RecordProFormaTable
    ????
    If all 11 records were destined for a single table, I'd agree with you. But since keys are going to be generated and related records are required to be entered matching that, data templating isn't going to help much.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can't see why it can't be extended. Don't know if it should be extended but it is still viable as far as I can tell based on the info so far.
    Code:
    INSERT INTO myDestinationTable(col1, col2)
    SELECT TemplateCol1, TemplateCol2
    FROM my11RecordProFormaTable
    Code:
    INSERT INTO tbl_analysis_AD (forKeyCol, Col2, Col3)
    SELECT autoNumberCol, Col2, Col3
    FROM tbl_analysis_AD_template,  (SELECT TOP 11 autoNumberCol FROM myDestinationTable ORDER BY autoNumberCol DESC) AS last_11_records
    ... and so on.

    I have a preference for this sort of solution not least because the data (in this case template data) is in tables rather than code (less obfuscation, better reuse, more easily changed etc).

    EDIT - what the heck - let's throw in a supporting article that George is probably sick of the sight of
    http://weblogs.sqlteam.com/jeffs/arc...2/10/9002.aspx

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, but the problem I have with that approach is malicious users can go and hack into the data much more easily than hacking into code... especially if it's an Access 2007 database which has zero security for tables.

    I agree with the article in a lot of cases, but yeah, it depends on each situation imo.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would certainly never defend Access's security model - foolproof it ain't

    I would say that the contents of almost all the other data tables in the database are likely to be more sensitive than the suggested template data. If you are concerned about security of your data then you need to be concerned irrespecive of whether or not you use these tables - it does not in itself introduce a new problem.

    But in any event it is just an alternative idea to what the OP initially considered - just a thought. The loop might be better or the OP more comfortable with that method but I thought I would throw the way I would do it into the mix.

  14. #14
    Join Date
    Dec 2007
    Posts
    70
    Hi thanks for all your input. I think i'm going to keep trying to use the method StarTrekker has suggested as it's what I started doing and quite frankly the Pootle Flump method was way over my simple head!

    Is it easy to add a message box for someone to enter the date into the code? I was thinking 1 message box, enter date, this then fills in the SampleDate field in tbl_samples for all the 11 enteries? Or would you have to type it in 11 times for each entery?

    I do apologise for any headaches I'm causing due to my inability to articulate my problems properly.

    Cheers again,

    OB1

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Look up InputBox in the helpfiles; remember you'll have to validate the user input too!
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •