How to d look up for 2 conditions

[Jon]

Greeting,

I have student ID (condition1) and results (condition2) if the student is
register and his results is “Trueâ€, administer cannot add him. If the student
result is “false†the student can be added one more time with the same ID. I
try the following code put I does not work unless I check result checkbox
first
If DLookup("[ID]", "[Students]", "[ID]=[Forms]![studentMarks]![ID]
And[Result]=[Forms]![studentmarks]![Result]") Then
MsgBox "Duplicated Student"
DoCmd.CancelEvent

End If
Any help please?

 

[Stefan Hoffmann]

hi Jon,

I have student ID (condition1) and results (condition2) if the student is
register and his results is “Trueâ€, administer cannot add him. If the student
result is “false†the student can be added one more time with the same ID. I
try the following code put I does not work unless I check result checkbox
first
If DLookup("[ID]", "[Students]", "[ID]=[Forms]![studentMarks]![ID]
And[Result]=[Forms]![studentmarks]![Result]") Then
MsgBox "Duplicated Student"
DoCmd.CancelEvent
End If

DLookup() returns a value, this value needs to be compared with
anything. In your case a DCount() makes more sense, you also need to
correct your condition for the [Result] part. This may work:

Dim Count As Long

Count = DCount( _
"[ID]", _
"[Students]", _
"[ID]=[Forms]![studentMarks]![ID] AND " & _
"NOT [Result]")

If Count = 1 Then
'add id
Else
MsgBox "Duplicated Student"
End If


mfG
--> stefan <--

 

[Jon]

Hi Stefan
Thank you put the msgbox appears every time I enter a number even check box
is unchecked!! please adivce??

hi Jon,

I have student ID (condition1) and results (condition2) if the student is
register and his results is “Trueâ€, administer cannot add him. If the student
result is “false†the student can be added one more time with the same ID. I
try the following code put I does not work unless I check result checkbox
first
If DLookup("[ID]", "[Students]", "[ID]=[Forms]![studentMarks]![ID]
And[Result]=[Forms]![studentmarks]![Result]") Then
MsgBox "Duplicated Student"
DoCmd.CancelEvent
End If

DLookup() returns a value, this value needs to be compared with
anything. In your case a DCount() makes more sense, you also need to
correct your condition for the [Result] part. This may work:

Dim Count As Long

Count = DCount( _
"[ID]", _
"[Students]", _
"[ID]=[Forms]![studentMarks]![ID] AND " & _
"NOT [Result]")

If Count = 1 Then
'add id
Else
MsgBox "Duplicated Student"
End If


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Jon,

Thank you put the msgbox appears every time I enter a number even check box
is unchecked!! please adivce??

You have a bound form, with two fields ID (Long Integer) and Result
(Boolean)?
How do you call your code?


mfG
--> stefan <--

 

[Jon]

Hi,

Yes, exactly 2 fields. One is long integer and other is Boolean (Yes/No field)
Code is located on “before update†event for ID field

 

[Stefan Hoffmann]

hi Jon,

Yes, exactly 2 fields. One is long integer and other is Boolean (Yes/No field)
Code is located on “before update†event for ID field

Try this (named the control for the field ID txtID):

Private Sub txtID_BeforeUpdate(Cancel As Integer)

Dim Count As Long

Count = DCount( _
"*", _
"Students", _
"ID = " & Me![ID] & " And Not Result")

Cancel = (Count > 1)
If Cancel Then
MsgBox "Duplicate."
End If

End Sub


mfG
--> stefan <--

 

[Ken Sheridan]

If I understand you correctly the position is that a row can only be legally
inserted into the table if:

1. No existing row for the current student has a TRUE value at the Result
column position.

2. There are currently two rows in the table for the current student. The
value at the Result column position is irrelevant in this case as you say a
student can only be added 'one more time' if a row already exists for the
student even if the value is FALSE.

Right?

If so try this:

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?
strCiteria = "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
MsgBox conMESSAGE, vbExclamation, "Invalid operation"
Cancel = True
End If
End If

Ken Sheridan
Stafford, England

 

[Jon]

hi There
Thank you guys but both of codes are not working!!?? please help me???

If I understand you correctly the position is that a row can only be legally
inserted into the table if:

1. No existing row for the current student has a TRUE value at the Result
column position.

2. There are currently two rows in the table for the current student. The
value at the Result column position is irrelevant in this case as you say a
student can only be added 'one more time' if a row already exists for the
student even if the value is FALSE.

Right?

If so try this:

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?
strCiteria = "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
MsgBox conMESSAGE, vbExclamation, "Invalid operation"
Cancel = True
End If
End If

Ken Sheridan
Stafford, England

Greeting,

I have student ID (condition1) and results (condition2) if the student is
register and his results is “Trueâ€, administer cannot add him. If the student
result is “false†the student can be added one more time with the same ID. I
try the following code put I does not work unless I check result checkbox
first
If DLookup("[ID]", "[Students]", "[ID]=[Forms]![studentMarks]![ID]
And[Result]=[Forms]![studentmarks]![Result]") Then
MsgBox "Duplicated Student"
DoCmd.CancelEvent

End If
Any help please?

 

[John W. Vinson]

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.

 

[Jon]

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

 

[Ken Sheridan]

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

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.

 

[Jon]

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.

 

[Ken Sheridan]

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.

 

[Jon]

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?

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.

 

[Ken Sheridan]

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

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?

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.

 

[Jon]

Many Thanks , It works

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

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?

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.