Need help with Query

[titch89]

I got 2 tables which are called Word and Word Answer.

The fields in Word are:

Word ID
Word
Answer - User input

and In word Answer are:

Word ID
Answer - correct answer
Marks -

This DB is like a vocab test for German Students at my skool. The word is in english and they have to put in the answer in german. They fill the answer in a form and then i have a button which runs a query. What i want is some sort code so when the answer from the Answer they have inputted matches the one that i have dun which is correct then in the marks column it will add 1.

Pls help as i am really stuck on this and i gettin annoyed cos each code i try it dont work.

 

[dcobau]

use a DLookup to look at the answer in Word Answer then use an If...Then to allocate the grade in the form.

Dave

 

[titch89]

Thanks for that...... i got the dlookup bit right i think but i stuck on the if...then statement

This is wot i got so far


=DLookUp("[MyAnswer]","WordAnswer")

 

[dcobau]

something like this in the Exit event of the answer field:

dim txtRightAnswer

txtRightAnswer = dlookup etc

if Me!FieldName = txtRightAnswer then

Me!ScoreField = 1

else

Me!ScoreField = 0

end if

Dave

 

[titch89]

it aint working..... i think that i may be puttin it in wrong place........ where is the exit event of the answer...... at moment i puttin it in the VBA code bit. Also wen i do it it sez it dont recognise the ME! bit. am i meant to change this or change any other bits...... is this me being bit stupid or wot??
And it also sez it cnt find the table Answer although it is in there

 

[dcobau]

pls post your code in full

Dave

 

[titch89]

That is wot i got might be in wrong bit

btw wen i run it it comes up that it cant find a field but dont say which one

Private Sub Command38_Click()
On Error GoTo Err_Command38_Click



Dim stDocName As String

stDocName = "Word Answer Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_Command38_Click:

Dim txtRightAnswer

txtRightAnswer = DLookup([Word_Answer], "MyAnswer")

If (PupilAnswer) = txtRightAnswer Then

[Word_Answer](Marks) = 1

Else

[Word_Answer](Marks) = 0

End If


Exit Sub

 

[phatnq2002]

That is wot i got might be in wrong bit

Private Sub Command38_Click()
On Error GoTo Err_Command38_Click



Dim stDocName As String

stDocName = "Word Answer Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_Command38_Click:

Dim txtRightAnswer

txtRightAnswer = DLookup([Word_Answer], "MyAnswer")

If (PupilAnswer) = txtRightAnswer Then

[Word_Answer](Marks) = 1

Else

[Word_Answer](Marks) = 0

End If


Exit Sub

I dont know how your query is designed, but when I read your code, I found some problem:
DoCmd.OpenQuery will open the window which browsing the resilt of query if it is a select query, or it will run a sql statement if the query is an action query.
Your source tables and their fields are not match with those in your code. So I don't know where is your fault. Can you post all your source tables and your requirements?

(After words,
If you want to update a new value to a field of a table, you may use a DAO Recordset.)

 

[titch89]

well i changed my tbls half way thru so they may have buggered it up a bit but i changed em back now but still dnt work

These are my table names and fields now

Word
Word ID
Word
Answer

Word Answer
Word ID
Word
Answer

I may haev put the code in the wrong part i will post below the whole code and could some1 pls tell me where exactly to put it.

I have a form which has words in english in 1 field then in another they input the answer in German. I then have a run query button which i want to check to see whether the answer matches the answer i have wrote in the word answer table. Then it either adds 1 or stays as 0 depending on whether it matches or not. Dnt no wot type of query it is just made it up quickly. I tried putting the code in the SQL but dnt work at all and dont no where to put it

Option Compare Database

Private Sub Answer_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Answer_Click()

End Sub

Private Sub Answer_Enter()

End Sub

Private Sub Answer_Exit(Cancel As Integer)


End Sub

Private Sub Command38_Enter()

End Sub

Private Sub Command38_Exit(Cancel As Integer)

End Sub

Private Sub Next_WordClick()
On Error GoTo Err_Next_Word_Click


DoCmd.GoToRecord , , acNext

Exit_Next_Word_Click:
Exit Sub

Err_Next_Word_Click:
MsgBox Err.Description
Resume Exit_Next_Word_Click

End Sub
Private Sub Previous_Word_Click()
On Error GoTo Err_Previous_Word_Click


DoCmd.GoToRecord , , acPrevious

Exit_Previous_Word_Click:
Exit Sub

Err_Previous_Word_Click:
MsgBox Err.Description
Resume Exit_Previous_Word_Click

End Sub

Private Sub Query_Click()
On Error GoTo Err_Query_Click
Dim stDocName As String

stDocName = "Word Answer Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Query_Click:
Exit Sub

Err_Query_Click:
MsgBox Err.Description
Resume Exit_Query_Click



End Sub
Private Sub Command38_Click()
On Error GoTo Err_Command38_Click



Dim stDocName As String

stDocName = "Word Answer Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_Command38_Click:

Dim txtRightAnswer

txtRightAnswer = DLookup([Word_Answer], "Answer")

If (Answer) = txtRightAnswer Then

[Word_Answer](Marks) = 1

Else

[Word_Answer](Marks) = 0

End If


Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click


End Sub
Private Sub Command39_Click()
On Error GoTo Err_Command39_Click


DoCmd.GoToRecord , , acNext

Exit_Command39_Click:

Exit Sub

Err_Command39_Click:
MsgBox Err.Description
Resume Exit_Command39_Click

End Sub

 

[dcobau]

One thing I have noticed:
**********************************************
stDocName = "Word Answer Query"

txtRightAnswer = DLookup([Word_Answer], "MyAnswer")
*********************************************
shouldn't txtRightAnser be:

DLookup([Word Answer Query], "MyAnswer")?

also, pls post the SQL of the query

Dave

 

[phatnq2002]

OK, I know what you want.
But, I think you should re-structure your database. We need three tables:
Table 1: Sessions
SessionID, PupilName, Marks
Field Marks is the total correct answers the a test session
Table 2: Tests
SessionID, WordID, PupilAnswer
Table 3: Word Answers
WordID, EnglishWord, GermanWord


When begin a new test, you run an action query to add all of English words from table Word Answers to table Tests.

You create a form for table Sessions, and make a code for the Form_AfterInsert event procedure:
CurrentDB.Execute "INSERT INTO Tests(SessionID, WordID) SELECT " & [SessionID] & ", WordID FROM [Word Answers]"


Second, you create a form that have Recordsource property:
SELECT A.SessionID, B.PupilName, A.WordID, C.EnglishWord, A.PupilAnswer FROM Tests A INNER JOIN Sessions B ON A.SessionID = B.SessionID INNER JOIN [Word Answers] C ON A.WordID = C.WordID

In the Form_AfterUpdate of this form, you write:

Dim res
res = DLookup("GermanWord", "[Word Answers]", "WordID = " & WordID)
If PupilAnswer = IIf(IsNull(res), "", res) Then

CurrentDB.Execute "UPDATE Sessions SET Marks = Marks + 1 WHERE SessionID = " & [SessionID]​

End If


I hope this' right for you.

 

[titch89]

hey thanks for ya help i got the first form fine but dont understand wot i gotta put in the 2nd 1 wot u mean by recordsource????

 

[phatnq2002]

hey thanks for ya help i got the first form fine but dont understand wot i gotta put in the 2nd 1 wot u mean by recordsource????

This make a form has name of pupil, word content instead of their ID numbers.