Refer to OpenArgs to identify field name

0
votes

I am having some issues trying to use OpenArgs to refer to a field name in a table. I'm fairly new to this so please bear with me.

So I have a Report(CourseCatalog) that has text boxes with course names that it pulls from a table(tblCourses). When you click on a course, it opens a Form which gives you the option to rate the course(frmRate). I use OpenArgs (from the report to the rating form) to make the caption for frmRate. Works fine.

Now i need to take the data (number of stars selected, (intNumStars)), which is defined previously in the code, and put it into a table. That table ("Allratings") has course names (the OpenArgs value) as the column names and I want to put the intNumStars(1-5) into the cells in those columns.

I seem to have some problems referring to the VarArgs to accomplish that. My syntax/logic may be (is probably) wrong, and if anyone knows a better way to accomplish this task, please let me know what you think! Thank you! 

Private Sub btnSubmit_Click()
Dim varargs
Dim rst As dao.Recordset
Dim db As dao.Database
Dim fld As dao.Field
varargs = Me.OpenArgs
Set db = CurrentDb()
Set rst = db.OpenRecordset("Allratings")

For Each fld In rst.Fields
    If fld.Name = "varargs" Then
        rst.AddNew
        rst!"varargs" = intNumStars
        rst.Update
    End If
Next

End Sub

2
ms-accessvbams-access-2013
That table ("Allratings") has course names as the field names - if this is true (and you don't mean you have a column for course name), this is bad design. This should be a junction table (see Wikipedia). - Andre
I misspoke there, it's the column names vice the field names. I edited the post to reflect the correction - Fiddle_stixx

2 Answers

1
votes

You're doing weird things with strings, and you should get a descriptive compile error when you try to save/compile this.

varargs refers to the variable varargs

"varargs" is a literal string containing the letters v, a, r, etc.

rst!"varargs" should be rst.Fields("varargs") to avoid a compile error, but should actually be rst.Fields(varargs)

If you correct all incorrectly placed quotes, you get:

Private Sub btnSubmit_Click()
Dim varargs As String
Dim rst As dao.Recordset
Dim db As dao.Database
Dim fld As dao.Field
varargs = Me.OpenArgs
Set db = CurrentDb()
Set rst = db.OpenRecordset("Allratings")

For Each fld In rst.Fields
    If fld.Name = varargs Then
        rst.AddNew
        rst.Fields(varargs) = intNumStars
        rst.Update
    End If
Next
End Sub

This seems valid, if Me.OpenArgs contains only a field name

0
votes

Eric has shown how to make your code work, but your table design will prove impractical. Adding a new column for each new course is not how you work with databases.

I suggest

tblCourses

+----------+------------+
| CourseID | CourseName |
+----------+------------+
|        1 | foo        |
|        2 | bar        |
+----------+------------+

tblUsers

+--------+----------+
| UserID | UserName |
+--------+----------+
|      7 | John     |
|      8 | Kate     |
+--------+----------+

allRatings (this is a Junction table)

+----------+--------+----------+
| CourseID | UserID | NumStars |
+----------+--------+----------+
|        1 |      7 |        1 |
|        1 |      8 |        4 |
|        2 |      7 |        3 |
+----------+--------+----------+

So a new rating is a new record in allRatings, with fixed column names. You can get the CourseID from the passed Course Name with a DLookup call.

And a view with Courses and their ratings as columns is achieved with a Crosstab query.