OK, lets try it out:
1. Create a new blank database.
2. In the new database execute the following SQL statement to create a
Students table by opening the query designer (don't add any table). Switch
to SQL view and paste in the following in place of what's already in the
place:
CREATE TABLE Students (
ID LONG,
Student TEXT(50),
Result YESNO);
3. Select Run from the query menu.
4. Leave the SQL window open and paste in the following in place of the
above:
INSERT INTO Students (ID, Student, Result)
VALUES(1,"Jon", TRUE);
5. Select Run from the query menu and answer Yes when prompted.
6. With the SQL window open and paste in the following in place of the above:
INSERT INTO Students (ID, Student, Result)
VALUES(2,"Ken", FALSE);
7. Select Run from the query menu and answer Yes when prompted. Close the
query designer (no need to save the query). You should now have two rows in
the Students table.
8. Create an autoform based on the Students table and out the following
(debugged) code in the ID control's BeforeUpdate event procedure:
Const conMESSAGE = "No more records can be created for this student."
Dim strCriteria As String
' does a row exist for this student with a TRUE Result?
strCriteria = "ID = " & Me.ID & " And Result"
If Not IsNull(DLookup("ID", "Students", strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Invalid operation"
Cancel = True
Else
' do two rows exist for current student?
strCriteria = "ID = " & Me.ID
If DCount("*", "Students", strCriteria) = 2 Then
MsgBox conMESSAGE, vbExclamation, "Invalid operation"
Cancel = True
End If
End If
9. In the form try adding a record with ID number 1. Because a row for ID
1 with a TRUE result already exists you should get the "No more records can
be created for this student." and be unable to proceed until a different
StudentID has been entered. Try entering 2 as the ID. You should not get
the message so add the new record for Ken. Now try adding another record
with ID number 2. You should again be prevented from doing so because two
rows already exist for Ken. This is based on my interpretation of your
critreria as 'one result or two strikes and your out'. Is this what you
expect to happen?
Ken Sheridan
Stafford, England
Jon said:
Thank you Ken
I can only input one record for all after that the msgbox appears†No more
records can be created for this
student" even if there is no duplicated record
one record only can be entered to the table? Please advice?
Ken Sheridan said:
Oops! Remove the final quote:
strCriteria = "ID = " & Me.ID
This assumes ID is a number data type.
If it still doesn't give the expected result then the logical assumptions I
made for the two criteria are incorrect, so please comment on these.
Ken Sheridan
Stafford, England
:
hi Ken
your code gives me the msgbox"No more records can be created for this
student" even if there is no duplicated value. I can only enter 1 record ,
different records can not be input. Also, there is a mistake in your code
which is
strCriteria = "ID = " & Me.ID"
it appears with redness please advice?
:
You haven't said in what way it's "not working". Are you getting an error?
If so what? Or is it executing without an error but not cancelling the
update when you expect it to? If so the problem is one of the underlying
logic. Are the two criteria which I set out for when a row can be inserted a
correct interpretation? If so have you tried the code I gave you in the ID
control's BeforeUpdate event procedure?
Ken Sheridan
Stafford, England
:
Hi, John
my code is
Dim Count As Long
Count = DCount( _
"*", _
"Students", _
"ID = " & Me![ID] & " And Not Result")
Cancel = (Count > 1)
If Cancel Then
MsgBox "Duplicate."
End If
:
hi There
Thank you guys but both of codes are not working!!?? please help me???
Please post the actual code you're using; indicate in what way it is "not
working"; and set a Breakpoint in the code by mouseclicking into the grey bar
to the left of the code window (it'll make a red dot). Run the code and step
through it using the Debug menu options (F8 to singlestep through the code).
If you're building a SQL string, please post the actual string that your code
generates.