Solved Need help with structuring IF and LOOP statements

[autumnnew]

Hi, I'm struggling with structuring the order of my IF and LOOP statements.

My database is supposed to generate samples (records) per soil boring. For every Boring, add samples to the Samples table. The top half of every boring will start with Continuous samples (Set1) first, then bottom half is Every Other samples (Set2). The loops are the same except for the increment. Some borings have samples already added, and some have none. First loop is to make sure all the borings have all the Set1 samples, and then second loop is to add the Set2 samples.

So basically, the code should be something like:

If Boring Has:​	Then:​	Do:​
Zero samples	Set counter at 1	Add Set1 samples, then add Set2 samples
Some Set1 samples	Set counter at previous sample + 1	Add Set1 samples, then add Set2 samples
All Set1 samples	Set counter at previous sample + 1	Add Set2 samples

All borings need to go through both loops. Here is my code. I had it working beautifully, except I realized I didn't code for borings with zero samples. So I tried to add it and that has messed me up. I moved some code around but got confused and gave up, so I know some of it is out of order. I know how to fix this by duplicating the loop again for each scenario, but that just seems wasteful and redundant. I just thought there is probably a more efficient way to write this, but I just can't think of it because my brain is fried right now.
rs1 is Borings table with sampling definitions.
rs2 is Samples table.
rs3 is Existing samples if they have been added, to determine deepest sample already added.

 Do Until rs1.EOF

        'Checks if boring has any samples.
        If rs3.RecordCount = 0 Then

            SampleNumber = 1      'Sets the counter for sample numbers.
            SampleDepth = 0

        'Checks if more Continuous To samples need to be added.
        ElseIf rs3!DrilledDepth < rs1![Continuous To] Then

            SampleNumber = rs3!LastDrilledSampleNumber + 1      'Sets the counter for sample numbers.
            SampleDepth = rs3!LastDrilledSampleDepth + rs1![Sample Length]

        'If all Continuous To samples are added, then move to loop for Every Other samples.
        'Not sure where this If statement should go
         ElseIf rs3!DrilledDepth >= rs1![Continuous To] Then

            'Sets the depth counter to Every Other increment.
             SampleDepth = rs3!LastDrilledSampleDepth + rs1![Every Other]

        End If

            'Loop for Continuous To samples. This loop populates the samples to the defined Continuous To depth.
             Do
                 rs2.AddNew                          'Begins new record (add mode).
                 rs2!BoringID = rs1!BoringID         'Sets BoringID for new sample to current BoringID.
                 rs2!Number = SampleNumber           'Sets sample number.
                 rs2!Depth = SampleDepth             'Sets sample depth.
                 rs2!Length = rs1![Sample Length]    'Sets sample length to specified sample length.
                 rs2.Update                          'Saves sample record.
                 rs3.Requery

                'Increments sample number.
                 SampleNumber = SampleNumber + 1

                'Checks to see if all samples have been added up to specified Continuous To depth.
                 If rs3!DrilledDepth >= rs1![Continuous To] Then Exit Do

                'Increments sample depth.
                 SampleDepth = SampleDepth + rs1![Sample Length]

            'End of Continuous To loop.
             Loop

         End If

         'If all Continuous To samples are added, then move to loop for Every Other samples.
         If rs3!DrilledDepth >= rs1![Continuous To] Then

            'Sets the depth counter to specified Every Other increment.
             SampleDepth = rs3!LastDrilledSampleDepth + rs1![Every Other]

             'Loop for Every Other samples. This loop populates the samples for the specified Every Other depth.
             Do
                 rs2.AddNew                              'Begins new record (add mode).
                 rs2!BoringID = rs1!BoringID             'Sets BoringID for new sample to current BoringID.
                 rs2!Number = SampleNumber               'Sets sample number starting at 1.
                 rs2!Depth = SampleDepth                 'Sets sample depth starting at 0.
                 rs2!Length = rs1![Sample Length]        'Sets sample length to specified sample length.
                 rs2.Update                              'Saves sample record.
                 rs3.Requery

                 'Increments sample number.
                 SampleNumber = SampleNumber + 1

                'Checks to see if all samples have been added.
                 If rs3!DrilledDepth >= rs1!HoleDepth Then Exit Do

            'End of Every Other loop.
             Loop

        'End of appending samples per boring.
         End If

         rs1.MoveNext                'Moves to the next boring for adding samples.
         rs3.Requery

    'End of adding samples for all borings (EOF).
     Loop

 

[arnelgp]

it is easier to picture if you can show a sample data from all 3 tables.
also on the code you are Looping through records of rs1 but you are not
Checking/Testing if they already exists in rs2 and3.

can this be done through Query?

 

[autumnnew]

Screenshot of an old version of my Borings/Samples form. I AM checking if samples exist in rs3 and retrieving the info of the last sample per boring.

(rs1) Borings table on the left of borings. The fields are the default definitions how the samples will be added for that selected boring. The samples can be individually adjusted after they've been added.
(rs2) Samples table on the right.
(rs3) Checks if samples are already added, and if so, it'll pick up where it left off for that boring.

I wrote code that perfectly populates the samples, and if a boring depth was lengthened or shortened, it would add/delete samples according to what was defined in the Borings table on the left. But like I mentioned, I forgot to write for borings where 0 was entered as the boring depth. If a user changes the depth later, the code needs to populate the samples.

Before I ventured into VBA, I tried to do as much as I could with queries and macros because I didn't know code yet. But now I want a cleaner and more organized database, so I'm trying to put as much into VBA as I can. (also, I want to protect my database, and VBA can be password protected). I had too many queries before.

 

[gemma-the-husky]

I don't really understand the looping requirement. It sounds like spreadsheet thinking

if you get all of your readings in a properly designed table (ie a normalised spreadsheet, or more correctly a series of normalised tables/spreadsheets) then you don't really need a loop. You just need a query or queries to read or save all of the readings associated with a given sample.

Before I ventured into VBA, I tried to do as much as I could with queries and macros because I didn't know code yet. But now I want a cleaner and more organized database, so I'm trying to put as much into VBA as I can. (also, I want to protect my database, and VBA can be password protected). I had too many queries before.

re the above - code is generally a last resort. In a database the answer generally is a query. A query is quicker to run, and easier to write.
Just ask for a query of borings with no samples.
Just ask for a query of the deepest sample for each boring.

eg - In your above image, are all of the samples related to boring B-1. If not, how are they related to the borings?
You wouldn't really need all of those delete buttons. In fact, why would you want to delete a boring you have painstakingly recorded? What would the "restore borings" button do? And so on. If you want to disregard a boring or a sample include a yes/no indicator, and maybe include details of who decided to ignore it, and when, for traceability. What do you want to do when a sample is lengthened? store the new one, and delete the old one? Store the new one, and mark the old one as superseded? Store both? Store the original depth of the sample, and update the depth? Lots of alternatives, and all managed for you without needing code, or at least without code being the first choice.

You shouldn't need a code loop to deal with the borings workflow.
Simply record all the samples you have for a boring.
Then write a query to select the borings with insufficient samples, or gaps, or any other data you need. If you need dates, store the date, and then extract and sort and filter the data based on the date range you need.

if you load a spreadsheet of samples, you can easily just select the new ones and import those, or show the user the changed samples, and ask if they need to be updated, or show the user the samples that have disappeared. Should users be amending spreadsheets to remove readings and change data. That's the sort of stuff you can quite easily control with a database, that's just next to impossible with a spreadsheet.

What you do is temporarily import the spreadsheet, then analyse and compare it in with the existing data in access to identify changes - then decide how you want to deal with the changes - that's what I mean. Most of it could be automated with queries, rather than code, and it's better to look for the way to do it with a query first. Sometimes the process might even reject the spreadsheet for some reason.

Your users don't see the data. They just see a presentation of the data that you want them to see, and manipulate it in ways that you want them to be able to. The battle is to design the data structure, rather than the process. Get a harmonious structure, and the processes develop quite naturally and with relatively little effort. Even the designer doesn't really use the data directly, because the designer needs to comply with the same data management rules as everyone else. (once the debugged processes and code are complete)
It doesn't really matter how many queries there are. Users only use the queries you want them to use through the interface you design. ie - your interface is written to use certain queries, and it doesn't matter really if there are other unused queries. It can be quite difficult to find and delete all the unused/redundant queries.

[edit - sorry, I kept adding to this, as I reread it again and again. I'll stop now]

 

[autumnnew]

This was my job at a civil engineering company that I worked at for 20 years. We did all our work in Excel, which I knew was 100% redundant because information was having to be repeated over and over (see screenshots below). We had no program or database to centralize our drilling and testing information, so I learned how to write databases just for this database so I could make my job easier. Now the company uses my database and I'm preparing to sell it.

My database does much more. It creates work orders for the drillers which can be printed or emailed, it provides a lab test assignment capability and generates the printable lab forms for every single test, it has data entry forms for entering the lab results.

I don't really understand the looping requirement. It sounds like spreadsheet thinking

You shouldn't need a code loop to deal with the borings workflow.
Simply record all the samples you have for a boring.
Then write a query to select the borings with insufficient samples, or gaps, or any other data you need.

Not every boring is the same depth, but the sampling is STANDARD about 90% of the time, so there's little point in just entering the samples that were taken. The engineers and drillers know what samples will be taken before the boring is drilled. Continuous samples to 10 feet, then every other 5 feet after that. It will be specified on the work orders. The looping requirement is ABSOLUTELY necessary. However, even though the sampling is standard most of the time, the users need to be able to define how the samples are taken when the project is not standard.

A user will fill out a form to add batches of borings. 'How many borings? What's the depth? What's the sampling definition if not default? Opt to manually enter samples for a complex boring'. Click Add Borings and the loop runs to add just the borings first. Once the borings are populated, then the user can tweak any borings by adjusting depth or sampling if needed. Click Add Samples and the loops run to add the samples for each boring.

eg - In your above image, are all of the samples related to boring B-1. If not, how are they related to the borings?

Yes, when the user moves from one boring to another on the left table, the samples query on the right refreshes.

if you load a spreadsheet of samples, you can easily just select the new ones and import those, or show the user the changed samples, and ask if they need to be updated, or show the user the samples that have disappeared. Should users be amending spreadsheets to remove readings and change data. That's the sort of stuff you can quite easily control with a database, that's just next to impossible with a spreadsheet.

My database started with exactly this...running a query/table to populate samples. Before I knew code, this is how I populated the samples. I had a table of standard samples down to 200 feet. The query would then and add samples until the boring depth was equal to the sample depth. That was the only way I could figure out how to add batches of samples to a whole list of borings. The only problem with that, is sometimes the sampling isn't standard.. 10% of the time the sampling is really complex. Some samples are 1 foot long.. sometimes 0.5 or 1.5 ft. and need to be taken every 3 feet. Sometimes the sampling is continuous to 20 or more feet. The user needs to be able to define the sampling before the samples are populated. When going with the query/table method, the user has less control over the sampling. Many useless samples get populated and therefore need to be deleted.

You wouldn't really need all of those delete buttons. In fact, why would you want to delete a boring you have painstakingly recorded? What would the "restore borings" button do? And so on. If you want to disregard a boring or a sample include a yes/no indicator, and maybe inlcude details of who decided to ignore it, and when, for traceability.

The delete buttons are for a continuous form, so technically I've only added one button. Like I said, I didn't know VBA and was intimidated by it, so this was my workaround in case a boring needs to get deleted. Then if they accidentally hit the delete button, it's not deleted until the user moves to another form. I was trying to give them the option to restore the boring if they accidentally delete the boring or sample.

The below screenshots are an export from the database to Excel. It's of a real project with 66 borings. The other columns are testing data of tests run on various samples. This data will be imported into a much more powerful program that generates logs and other specific engineering reports. That program doesn't handle work orders or lab test assignment. It definitely doesn't populate any borings or samples either. I used to manually ...MANUALLY... enter and arrange data into this spreadsheet for many years. There was another step to it where I had to calculate the lab data before entering here, so I was actually entering data twice.

Is there anyone who can try to help me with the loops?

 

[gemma-the-husky]

I don't really understand this well enough. Can you clarify this please.

Is Boring B1 a single project consisting of many samples?

So for that boring, you want to set up a permanent record of all the samples that will be part of the project? Is that correct?
How DO you define the pattern/location of the samples and the various depths. How many samples would there be on a typical boring.

if you don't have a standard sample pattern, or even a set of standard sample patterns, I don't see how you can easily do it with code. You would have to re-write the code for every new boring. Does the boring change depending on the area of the site, and maybe even the depth of the drilling?

Assuming it's a manageable quantity of samples, I would be inclined to record the samples on a spreadsheet, where you can drag and drop and modify the design easily, and then import the final spreadsheet to generate the samples for each boring stored in the database.

If 13, as in the example of B1, is a typical number, but the pattern is non-standard, it would be far easier to tabulate the 13 on a spreadsheet. If you find you need to set up some more, you could just add additional ones to the database manually. It doesn;t really matter how many it is, I would have though. 50, 100 whatever.

Not every boring is the same depth, but the sampling is STANDARD about 90% of the time, so there's little point in just entering the samples that were taken. The engineers and drillers know what samples will be taken before the boring is drilled. Continuous samples to 10 feet, then every other 5 feet after that. It will be specified on the work orders. The looping requirement is ABSOLUTELY necessary. However, even though the sampling is standard most of the time, the users need to be able to define how the samples are taken when the project is not standard.

Why would you not record all the samples that were taken. I don't understand that.

Are you recording the sampling results in the database? All the information that's on the spreadsheet? If not, I would have thought that's what you want to get into the database, and not just leave it on spreadsheets.

 

[autumnnew]

I need to reiterate that my code already does all this.. my code already generates the borings and samples based on the defined sampling patterns and boring depths, but it only runs when the borings are initially added. It still needs to be able to add additional samples when the depths of the borings are modified AFTER they've already been added. Say if the drillers needed to drill deeper, or they hit rock and couldn't drill all the way... Then the code needs to add additional or delete samples. Do you see what I mean? I was able to get the code to delete samples from a boring that was shortened.. but for the case of extending the boring, I need the code to assess:

1. if the boring has zero samples and if not, then add samples.
2. if the boring already has samples, then how deep was the last sample? Should the increment be Continuous to or Every Other? Determine last sample drilled and add samples to new boring depth.

I've already written the code to assess these steps, but my ONLY problem is fitting them into the proper IF and LOOP statements.
This is all I'm asking anyone to help me with. I'm tryna figure out what order they go and where. I can't figure out the structure. I can figure this out eventually, but I will probably end up writing a function or writing too many if statements and loops. I'm still only a novice at VBA and my brain is fried from spending the past week writing all this code.

I don't really understand this well enough. Can you clarify this please.

Is Boring B1 a single project consisting of many samples?

Sorry. B-1 is one boring of many for one project. The database will load one project at a time and will have any number of borings, from 6 to 100 depending on the project. The borings are usually labeled with a prefix of B or P, as B-1 or P-1, B for building and P for parking lot. Parking lot borings are typically shorter. Both start at 1.. for example, a project could have B-1 through B-10 and P-1 through P-6. The number of borings ranges based on the project and site. Their depths are based on the site.

So for that boring, you want to set up a permanent record of all the samples that will be part of the project? Is that correct?
How DO you define the pattern/location of the samples and the various depths. How many samples would there be on a typical boring.

if you don't have a standard sample pattern, or even a set of standard sample patterns, I don't see how you can easily do it with code. You would have to re-write the code for every new boring. Does the boring change depending on the area of the site, and maybe even the depth of the drilling?

The sampling pattern is defined when adding a new project. The number of borings and sampling pattern are defined on the same form. See screenshot below. However, there is a standard sampling pattern, I already stated it. Continuous samples from 0 to 10 feet, then every other 5 feet after until they drill to how deep the engineer specifies. However, if there is a lot of variation of the sampling pattern, which is rare, the user can define it for each boring after the borings have been added. Sometimes for 100 or 200 feet borings, they'll sample every other 10 feet instead of 5. Between projects, the sampling pattern will almost always be the same, but the boring depths will not. Even for 1 project, it might have 3 borings that go to 100, 5 that go to 50 and 10 that go to 20. In any case, my code already allows for users to add batches of borings at a time. They can add the 20 ft borings first then the 100 ft borings, etc. Or they can add the total borings at one time and modify the depths afterwards. They can define the prefix as well. Just like the sampling pattern, the prefix of B and P is standard 90% of the time, but occasionally needs to be customized. My code also does this too.

Why would you not record all the samples that were taken. I don't understand that.

Sorry, I meant as opposed to samples that haven't been taken yet. We are adding samples to the database even though they haven't been drilled yet. I meant there's little point in waiting until they've been drilled first to enter them. We enter them regardless of they've been drilled yet, because either way, they'll be drilled.

The below screenshot is the form where the project info is entered and the borings and sampling pattern is defined. The default is already there: continuous to 10 feet, every other 5 feet starting at 15. Users can modify this here or after the project and borings are populated. After these are populated, then the user is taken to the Borings/Samples page where the new borings are loaded. See screenshot in my previous posts. On that page, the user has a chance to make any changes necessary to individual borings. THEN they'll click the Add Samples button and the samples get populated. With this step done, they can move to the Work Orders page and add borings and the sampling specification instructions to work orders for the drillers.

 

[gemma-the-husky]

Thanks for that. So in that last picture, you want to be able to set the parameters to calculate the sampling automatically?

Is that standard the company standard, or the standard for just this boring.

So would you modify these parameters, delete all the samples, and start over. Or would you want to just find new ones. It seems easier to delete all the samples for B1, and start over, but maybe you can't do that, if some of the samples have already been defined and completed.

Alternatively if all the existing samples remain valid, just run the whole process as if you had no samples, but don't add samples that already exist. That ought to simplify the process, as you remove the need to manage the loop closely. If you can find a way to create a unique key/index - not necessarily the PK - you will get a 3022? error (I think) when you try to insert a duplicate record, so you can just ignore that entry, and carry on. How many samples are there per boring? A thousand would only take a few seconds to process.

so you just get this sort of structure. Is this possible? I use while....wend rather than other loop syntax. I just find it easier.

start all samples loop

while in loop
    ...
    ....
   'get to the point where you save a sample
    on error goto append_sample_fail
    currentdb.execute append-sample-SQL, dbfailonerror

nextitem:
wend

finished - report results
close resources
exit sub

append_sample_fail:
if err = 3022 then ' duplicate, just ignore it - I think it's 3022
    resume nextitem:
else
    msgbox "report a different error"
    either resume nextitem, or close recordsets etc, and exit the process, depending on your preference
end if

end sub