By Richard Rost
3/27/2008 3:54:54 PM
Here's a really good Access question someone sent me. I've been meaning to cover this in my classes, and it will be in a future lesson:
i searched the index of courses on your website but didn't find this...
i've built a database from scratch for our department. i need a form for entering scores achieved on a certain assessment. the table for this data includes the following fields:
each candidate will always have [say] 10 records, one for each of the components in the assessment. can i create a form that will have 10 records, each one pre-filled with one of the assessment components? and then the user can just put in the score for each component? this form would function as a subform along with other like subforms, all on a form with the candidate's name at the top.
thanks for any help you can give me.
You're right - I haven't covered this in any of my classes yet. I plan to very soon. There are 3 ways I can think of to handle this situation:
1. A recordset
2. DoCmd.RunSQL and an INSERT statement
3. Cheezy GotoRecord commands
The first would be the best choice - open up a recordset and programmatically insert the records into your table, then refresh the subform to display them. This is also the hardest to code.
The second choice would involve a loop and an INSERT query to do the same thing. Also some coding - but not as hard.
The third choice is probably the easiest to accomplish without as much advanced coding. Basically the pseudocode would be:
' DoCmd.GotoControl "Subform" ' the name of your subform
' For X = 1 to 10 ' or however many you need
' DoCmd.GotoRecord ... acNewRec ' go to a new record
' strCandidate = Forms!MainForm!Candidate
' strScorer = Forms!MainForm!Scorer
' Next X
This will leave you with the pre-filled in fields that you want. It's a bit cheezy of a solution, but it should work. You'll need to look up the exact syntax for the GoToRecord command - there are some other parameters needed in there, but that should get you started.
I've been wanting to do something like this in my class database. The example I was going to use was pre-filling a table with a customer's payments. Let's say you owe $1000 and you want to schedule 10 monthly payments of $100. The database could automatically fill them into the payment table at scheduled dates. You could also use this for loan amortization schedules.