Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > Forums > Developers
Back to Access Developer Forum    Comments List
Record Selection question Upload Images   Link   Email  
Victor Beardwell        
2 months ago
Hi All,
I know this is my first posting and I apologise for it be a long one.

I have a situaton that has me stumped on how to resolve it.

I have got to a point where I have a form with a list of records. The idea of this for is that there is a tick box against each record for the user to decide which records they want to process further.

These records have test results associated to them.  The test results have multiple Yes/No fields (denoting various test pass or fail conditions).

I need a validation method that checks when the user select more than 1 record, that the test results for all the selected records match.

So the user would select the first record, and then as each subsequent record is selected a validation is made to check that the all fields in the record, match with those in the first record selected.  If not doesn't not allow the next record to be selected and throuws up an error message.

This is so when the selection of records are processed further, they all match the first record in the selection.

Any suggestions on how I can best achieve this?

I know the solution is likely via VBA coding, but I am a novice in VBA and I have wracked my limited brainpower and tried many Google searches for the last few weeks trying to find something that might help me with this.

Any pointers or suggestions on this would be very very much appreciated.
Sami Shamma             
2 months ago
To help you further, please tell us what the business process is, not how the form is set. The odds are that this will need some VBA.
Give example of the Data as well.
Richard Rost            
2 months ago
Yeah, I can't wrap my head around this based on your description. Could you possibly show us some examples? Maybe if you have it in Excel or a mock-up that you can do with text files? Something, anything, just so we can visually see what this process looks like. Sometimes that's easier than trying to understand it from words. And we don't know your business, so it's hard for us to understand what is going on.
Victor Beardwell        
2 months ago
Hi Sami & Richard,
Many thanks for your replies.
I should have given more backgorund info.
This is a small equipment testing and certification database I am trying to build.
It is made up of 3 main sections.
An equipment register, an inspection register and a certfication register.
Equipment is inspected and certified to a schedule, normally 6 months.
The user has a form which lists all the equipment that is due to be inspected.
They click on an "Inspect" button to open the Inspection Form for that that piece of equipment, fill out the form and when finished clik a Confirm Inspection button.
This writes the inspection results to the inspection table.
When the user wants to create the certificates for this equipment they open  "Select for Certificate" form which lists all the inspections  that do not have a certificate number.
The user then ticks a selection box to select which items of equiment is to go on the certificate.
It is from this point I start to run into the the issue I have... and get stuck.
If the user wants to create one certificate per piece of equipment that isn't a problem, they select one item and click the "Create Certificate" button. They get a print-preview and once confirmed the certificate number is written automatically to the inspection record and the record disappears from the "Select for Certificate" form as it is now completed. If they cancel the operation at any point the whole process aborts and they have to go back to the selection form and start over.
I want to be able to let the user select mutliple pieces of equipment to go on a single certificate, but this can only be allowed to happen if all the records match, so they have to be the same type of equipment, inspected on the same day, and has the same inspection results.
So the user selects the first item, but when the select the next one, I need to somehow validate that the 2nd items date, category and inspection results, matches the first one and so on for each subsequent item selected.

I can't have diferent types of equipment, inspected on different dates, or with different inspection results, on the same certificate, so I need a way to valildate this and throw up an error message if anything doesn't match.

I hope this is making sense...
I will upload some screenshots that will hopefully make it a bit clearer (or not....  maybe...  LOL!)

How do I get a "Trekkie" badge.
Like yourself Richard, I am also a life long trek fan!!

(Also a huge "Babylon 5" fan, along with the rebooted "Battlestar Galactica").  

Victor Beardwell        
2 months ago

Victor Beardwell        
2 months ago

Victor Beardwell        
2 months ago

Victor Beardwell        
2 months ago

Kevin Robertson            
2 months ago
Kevin Yip       
2 months ago
Hi Victor, if you need to look for identical records, Access can do it automatically with a query, without having the user to manually browse and check for identical items themselves.  As soon as the user selects one item, Access already has all the information to look for like items with the same equipment type, inspection date and results, etc.  A fairly simple update query made with VBA can be made to process all like items in one go:

    Dim sqltext As String
    sqltext = "UPDATE MyTable SET Cert = True WHERE EquipType = " & a & " And InspectDate = #" & b & "# And InspectResult = " & c
    CurrentDb.Execute sqltext

The above code will set the "Cert" field (indicating certification needed) to True for all the items with equipment type equal to a, inspection date equal to b, and inspection result equal to c.  (These are just placeholder names, so you need to use your own field names.)

All the ampersands (&) shown above are needed for a technique called string concatenation, which is needed to form SQL statements in VBA.  a, b, and c above are called variables, and they store the values from the record selected by the user.  

When the user checks a box, it triggers a "event."  You need to write code for this event so it will store the values of the record selected by the user into the variables a, b, and c.  

If you are unfamiliar with all these topics (VBA, events, variables, and string concatenation), hopefully the other experts can point you to the right courses.
Victor Beardwell        
2 months ago
Hi Kevin,
Thanks for your reply and support. Much appreciated.

I understand what you are saying, however, the end users have a preference to select the records that go onto the certificates manually.  (end users!  Life would be so much easier without them!!)

Looking at the table screenshot I posted you will notice that records 1 & 3 match, as do records 2 & 4.
However, when you look at the 'Inspection Selection for Certification' (ISfC) from screen shot, which shows the same 4 records, you can see the equipment types, and so records 1,2,3 are the same, but record 4 is a different equipment type.

What I looking to achieve is this sort of functionality..
If the user checks the 'Select for Cert' checkbox on the ISfC form for the record in row 1 first, then the only other record that he will be allowed to selected is the row 3 record. (same equipment type & inspection results)

If the user selects the record in row 2 first, then it is the only one he can select as the remaining records have different inspection results or are different equipment types.

If the user selects the record in row 3 first, then only the row 1 record can be selected. (same equipment type and inspection results)

If the user selects the record in row 4, then this is also the only record that can be selected, as it is the only record of this equipment type.

I know this is a case of me trying to run before I can walk, as far as my Access Knowledge in concerned. I am pretty sure that as I progress through Richard's training sessions and seminars, that I will realise that there is probably a far simpler and easier solution for this project.
I guess my impatients is getting the better of me''.

Thanks again,
Kind regards

Kevin Yip       
2 months ago
When the user selects a row, you may "highlight" all the rows with matching criteria so the user can spot them more easily, and then allow the user to select the highlighted rows or not.  See the picture below from my app as an example: when I select the row "Terrence Young," the other rows with the same name are also highlighted (in blue).  This has more to do with user interface design, and there are many other ways to do this.  The needs of your users will decide how it should be designed.  The method used in my picture is a VBA technique called "format conditions," which add row-specific formatting based on criteria.   I believe this site has a course on that as well.

It's not good to run before you walk, but it helps to see what running looks like if you are curious about it.  If you take Richard's courses, he definitely emphasizes knowing only what you "need to know" at any point in time -- because giving you too much information all at once would be distracting and overwhelming.  Schools always teach on a need-to-know basis.  (That's why some people don't go to school: they want to learn whatever and whenever they can.)
Kevin Yip       
2 months ago

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 4/15/2024 9:46:26 AM. PLT: 1s