RabbieBurns Posted February 3, 2009 Posted February 3, 2009 Ive got a web form to enter codes, which updates a table in a database. The field it's updating is linked to another table, which has a table of all the codes, and therefore the code being entered has to exist in the table of codes. If I enter in the form a code that doesnt exist in the other table, I get this message, which is understandable. [Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'codes'. How would I go about making sure that what is entered in the form is a valid code in the code table? Would I do another query on the update page, and then compare the data from the form to the recordset from the new query or something like that?
CESIL Posted February 3, 2009 Posted February 3, 2009 How many codes are there? Could you populate a dropdown with all the valid codes to avoid the problem? 1
CESIL Posted February 3, 2009 Posted February 3, 2009 Or you could execute a query to return any matching records from the code table against the code from the form. If any records are returned (it should be just 1 I guess) then the code is valid.
ICTNUT Posted February 3, 2009 Posted February 3, 2009 I agree with cadjs, is there any reason why you cannot create adrop downlist with the codes pre populated. This would avoid the requirement for validation completly. If you need code forthe dropdown let me know 1
RabbieBurns Posted February 3, 2009 Author Posted February 3, 2009 hmm, thats an idea actually.. however, its basically a form for changing the set a student is in, and theres 38 set codes, but only 2-4 sets per subject. So I thought for them to just change "4MATH 4" to "4MATH 3" by just changing the last digit, it might be easier. http://www.peninsulafm.com/sets.PNG I will have a dabble with the dropdown, thanks for the idea
CESIL Posted February 3, 2009 Posted February 3, 2009 you should be able to filter the codes in the dropdown by using the subject. That way they will only see valid codes for the subject involved.
RabbieBurns Posted February 3, 2009 Author Posted February 3, 2009 I agree with cadjs, is there any reason why you cannot create adrop downlist with the codes pre populated. This would avoid the requirement for validation completly. If you need code forthe dropdown let me know Ive changed them to dropdowns, however my code only seems to populate the first entry with all of the available codes, then not the rest. Ive obvioulsy messed up a loop somewhere.. Can you spot my mistake? edugeek private pastebin - collaborative debugging tool
RabbieBurns Posted February 3, 2009 Author Posted February 3, 2009 you should be able to filter the codes in the dropdown by using the subject. That way they will only see valid codes for the subject involved. Sounds ideal.. could you show or point me in the direction to learn about filtering please, its not something I know about yet.
CESIL Posted February 3, 2009 Posted February 3, 2009 you need to reset the record pointer to the first record in your recordset once the first loop is done 1
CESIL Posted February 3, 2009 Posted February 3, 2009 to filter the codes you could either do a query per subject or use the subject to determine each subject as you loop through the recordset. As long as the codes are sorted by subject you would know that all the maths ones are together etc.
RabbieBurns Posted February 3, 2009 Author Posted February 3, 2009 (edited) Sorted, thanks. Now, to be able to filter the sets per subject would be the icing on the cake edit: to filter the codes you could either do a query per subject or use the subject to determine each subject as you loop through the recordset. As long as the codes are sorted by subject you would know that all the maths ones are together etc. I cant think in my head how that would work, the codes are sorted alphabetically, as are the subjects, but different subjects have different amounts of codes etc Edited February 3, 2009 by RabbieBurns
CESIL Posted February 3, 2009 Posted February 3, 2009 BTW I think your counter 'bug' is caused by staring the counter at 1 rather than 0...
RabbieBurns Posted February 3, 2009 Author Posted February 3, 2009 (edited) BTW I think your counter 'bug' is caused by staring the counter at 1 rather than 0... If i start it at 0 I get this: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'reportID ='. /S4/updatesets.asp, line 36 which corresponds to the conn.execute line here: http://edugeek.pastebin.com/m38b8a3b4 Edited February 3, 2009 by RabbieBurns
RabbieBurns Posted February 3, 2009 Author Posted February 3, 2009 Actually, thinking about it, I am just going to leave the dropdown as it is. Often here students change their subjects from say maths to Art instead for example. This way itll let me change the whole subject by just changing the set.
CESIL Posted February 4, 2009 Posted February 4, 2009 there is a counter in the update page that starts at 1...this should probably start at 0 as well...
RabbieBurns Posted February 4, 2009 Author Posted February 4, 2009 there is a counter in the update page that starts at 1...this should probably start at 0 as well... Just tried setting them both to 0 and commenting out the counter -1 line... Same problem...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now