Updating a record from a listbox (1 Viewer)

[AChir]

I have a form containing a listbox (lstGroupList) containing all the subject groups for a particular student (selected in a further listbox) and a listbox containing all the possible groups for the student (lstFullGroupList). I'd like to be able to select an existing group in lstGroupList and a new group in lstFullGroupList, double click on the former (or add a command button) and have the student transferred to the new group. I am using:

DoCmd.RunSQL "UPDATE [Students and Classes] SET [Students and Classes].SubjectSetCode = " & Me.lstFullGroupList _
& " WHERE ((([Students and Classes].StudentClassID)= " & Me.lstGroupList & "));"

However, I'm getting "Enter Parameter value" messages or a run time error depending on the value of the SubjectSetCode. These are defined by another package and inlcude studd like:
11-T/EN (which results in a demand for a non-existent parameter T)
11D/Dr1 which gives a runtime error.

I have tried altering subject codes to remove all punctuation in case that's a problem but am stuck.

Any thoughts gratefully received

Thanks

 

[Mile-O]

Change your code to this:

Dim strSQL As String
strString = "UPDATE [Students and Classes] SET [Students and Classes].SubjectSetCode = " & Me.lstFullGroupList _
& " WHERE ((([Students and Classes].StudentClassID)= " & Me.lstGroupList & "));"
Debug.Print strSQL

' DoCmd.RunSQL strSQL


And then post back with what gets printed in the debug windo.

 

[Mile-O]

I see what it is.


DoCmd.RunSQL "UPDATE [Students and Classes] SET [Students and Classes].SubjectSetCode = """ & Me.lstFullGroupList _
& """ WHERE ((([Students and Classes].StudentClassID)= " & Me.lstGroupList & "));"

 

[Mile-O]

If your value is a text value then you must enclose it within inverted commas. Not so, if it is numerical which is the way you had it. 11-T/EN is certainly not numerical.

It was trying to do mathematics with an undefined parameter T. Think algebra. It would probably have asked for EN next.

You are better off just making this a stored UPDATE query than one built in code. Have a look at my last post on this thread to find out why.

 

[AChir]

Thanks for the help - certainly your other thread convinces me that a query would be better...

I tried your revised code to start with and I get no error messages, but nothing happens. As you explained, a query qould be better, so I tried running a query on double click:

UPDATE [Students And Classes] SET [Students And Classes].SubjectSetCode = [Forms]![StudentGroupChange].[lstFullGroupList]
WHERE ((([Students And Classes].StudentClassID)=[Forms]![StudentGroupChange].[lstGroupList]));

Again, no joy
I'll keep trying, anyhow

Thanks