Populating a table using VBA code

[dkwong]

I need help developing a VBA function. I have the following two tables:

EVALUATIONQUESTION (contains 20 records/questions)
QuestionID Number(PK)
QuestionDescription Text

COURSEEVALUATION
CourseID Number(PK)
QuestionID Number(PK)

In the function, I want to pass in a CourseID. I then want to insert records into the CourseEvaluation table using the CourseID argument of the function and all the QuestionIDs in the EvaluationQuestion table. Therefore, there will be twenty records in all inserted into the CourseEvaluation table.

e.g. if the CourseID = 33

33 1
33 2
33 3
33 4
...
33 20

Any help would be much appreciated!

 

[Tranman]

Try this out:

Public Function insQ()
Dim intQId As Integer
Dim intQNbr As Integer
Dim rstEvalQ As Recordset
Dim rstCEval As Recordset
intQId = InputBox("Enter course ID:", "COURSE ID&quot

'count the questions in case you add or subtract one
rstEvalQ = CurrentDb.OpenRecordset("Select count(QuestionID Number(PK)) as QCount from EVALUATIONQUESTION;&quot

'don't return any rows--just create an empty recordset
'(1 will never equal 2)
rstCEval = CurrentDb.OpenRecordset("Select * from COURSEEVALUATION where 1 = 2;&quot

For intQNbr = 1 To rstEvalQ.Fields("QCount&quot
rstCEval.AddNew
rstCEval.Fields("CourseID Number(PK)&quot = intQId
rstCEval.Fields("QuestionID Number(PK)&quot = intQNbr
rstCEval.Update
Next

rstEvalQ.Close
rstEvalQ.Close

Set rstEvalQ = Nothing
Set rstEvalQ = Nothing
End Function


Good Luck,
Paul

 

[dkwong]

Thanks, Paul! There is an error message that states "Invalid Use of Property". The 1st line of the function is highlighted and the cursor is set at
rstEvalQ = CurrentDb.OpenRecordset...

Further, I'm wondering if there is an equivalent of a trigger in Access. Instead of having the user enter the CourseID manually, is there any way to have the function execute whenever a new record is inserted into the Course table (whenever a new course is added to the database)? Hope this makes sense

 

[KyleS]

dkwong,
Unfortunately there are no triggers in Access.

If you're getting that error, you're probably on Access 2K or XP, so you;ll need to go into the references and add DAO as well as adjust the code:

Go into the design of a module (where you put the code) and select "Tools" --> "References" from the menu. check the box next to "Microsoft DAO 4.0 Object Library". Click OK and change these lines to look like this:

Dim rstEvalQ As DAO.Recordset
Dim rstCEval As DAO.Recordset Kyle

 

[Tranman]

You're right, Kyle. I assumed Access 97 with its default to the DAO.

dkwong, this is because at the time Access 97 was written, ADO did not exist, and all recordsets were DAO because that is all there was.

I did accomplish something that had the effect of automatic updating once, by creating a startup form, and in the form load event, querying a table to see if any records had been added, but not updated. It was ugly, and only ran at startup, but it worked.

I believe I read on the MS site that events like table open and table update; etc. would be available in either Access 2000 or XP (can't remember which). Guess these would, used properly, be able to replicate triggers.

Paul

 

[dkwong]

Hmmm... I might have to go with creating a startup form as suggested. I don't think it's sufficient to have it only run at startup. Basically, I need to run the code to have an "empty shell" for each course each time they add one in the Add Course form.

As for the error, I am using Access 97. I tried adding the DAO anyway, and I still get the same error. I also looked at the references and the following are checked off:

Visual Basic For Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 2.5/3.51 Compatibility Library

I tried a using different DAO libraries instead, but I still get the "invalid property" error as before. Thanks for your help, guys!

 

[Tranman]

dkwong,
Sorry, my dumb. Repeat after me, "To establish a relationship between an object variable and an object, we use the "Set" verb---To establish .....".

I did not test the code, and don't use DAO much, so I made an error.

The correct code is:
Set rstEvalQ = currentdb.Openrecordset.....

Again, sorry.
Paul

 

[dkwong]

Thanks, Paul! I should have caught the "Set" keyword.

 

[Tranman]

dkwong,
It sounds like you must have an "Add Course Form", maybe with an "ADD" button. Probably somewhere in that button's click event would be a place where you could call this function.

Of course, in the Add Course Form, you would already know the course number, so you could pass it to the function automatically, as an argument, rather than using the inputbox.

Declare function: Public function insQ(intQId as Integer)

and get rid of: Dim intQId as Integer
and get rid of: intQId = InputBox("Enter Course Id",
"COURSE ID&quot


Paul

 

[dkwong]

But if the CourseID is an autonumber, I wouldn't know the courseID at the time I press the button, right? Retrieving the largest CourseID and add 1 to it wouldn't do it either because the CourseID might not be in perfect sequence (say if you added a course, then deleted it). Wouldn't I have to do a comparison of the CourseEvaluation table and Course table to see what courseIDs aren't in the CourseEvaluation table and then call the function?

 

[Tranman]

You're right--if the course number is autonumber, you would not know what number was issued. You would have to run a query to discover the highest course number in the table after whatever process adds the course to the course table. That should be a fairly simple process.

Whatever event adds the course, declare a recordset in it, then at the end of the event, after the course has been added to the table, just open the recordset.

So you would:
dim rst as recordset

and at the end of the event after the row has been added:
SET rst = currentdb.openrecordset (&quot;Select top 1 <course_ID> from <Courses> order by <course_ID> desc;&quot

and the highest course id would be in:
rst.fields(0)
'that's a zero--fields may be referred to by name or ordinal
'position in the recordset (0 relative)

then you would call the function and pass the argument:
insQ(rst.fields(0))

This will pass the highest course id to the function.

We're getting into the fun part now, aren't we? I love this kind of stuff.

Paul

 

[dkwong]

Thanks for all your help, Paul and Kyle!