H57, the first thing I needed to solve in my head when i started working with Access is that one should not try to solve too many things just through code.
Rather get into the mindset of forms, queries, tables and reports.
If your want to change data programatically, ask yourself if you can change it through a standard query first.
If you can create a typical update/insert/delete query that does the job for you in the query designer then you know what the query needs to do.
Next ask yourself what data needs to be input by the user and what can you extract without user input. For example, you may already know the user's details so you can inputer the logonID as the person who has updated the record. You may also have certain default values available. For example the South African Identity number can be "unpacked" to provide date of birth and gender, so i do not need to collect this from the user as well - possibly the same could be true for social security numbers or other national id numbers.
Now, armed with the knowledge of what needs to be collected you can populate a form with text boxes that can collect this needed info.
You can then generate a sqlString that will replace the query with a dynamically constructed query and run this with a docmd.runsql.
There are several methods you can follow with this example, it really comes down to programming styles and preferences.
With regards to your DB, there are a couple of comments so far.
- Set the startup form to be the form you want them to see first so that they do not need to first open the DB then open the form. This is in any event going to become problematic if you want to start delivering your solution via the run-time method, so better to get into the habit of setting the opening and closing forms from the beginning.
- I cannot understand the purpose of the CurrentAnalytics form. I understand that this is a skeletal system for demonstration purposes, but do you need to have this form bound to a table?
- The GroupName combo box's record source should preferably be:
- SELECT DISTINCT tblGroups.[GroupName], tblGroups.GTSN
GROUP BY tblGroups.[GroupName], tblGroups.GTSN
ORDER BY tblGroups.GTSN;
- You should also then set the bound column to one, the number of columns to 2 and the column widths to 0;4cm so that the information provided to the end user is user friendly while ensuring the DB's integrity needs are also met.
- You should also consider normalising your groups table so that you can avoid the distinct clause. It becomes exceptionaly difficult to guarantee referential integrity through your code when in fact that is exactly what an RDBMS is designed to do for you. That way also you will be using the first combo box to select a group from the first normalised group table, requery 2nd combo to filter out all of the relevant options and then move on.
- The Navigation buttons and record selectors are really necessary for this form if there is no need for it to be bound to a table, but then I do not understand the business need for the form.
- Lastly, I would suggest changing the 2nd combo box's row source to :
- SELECT DISTINCT tblGroups.GroupName, tblGroups.GTSN
ORDER BY tblGroups.GTSN;
- Furthermore, even this will be refined once you have properly normalised your DB. The point here is to point your criteria at the current form, not at just a value. Use the builder wizard for this - it is faster.
Hope this gets you started.
Post an updated DB once you done some work on this and I will gladly take another look.