Dlookup function

  • Thread starter Thread starter PeteEHP
  • Start date Start date
P

PeteEHP

i need to know how to check to see if duplicates , i used the followin
code. but if their is no duplicates it dosent submit th
information???

heres the code

Private Sub Order_ID_Cards_Click()

Let [Id Flag] = "Y"
Dim anystring As Variant
anystring = DLookup("[SurName]", ("Id Card Order"), "[Surname] = "
Chr$(34) & Surname & Chr$(34))
If anystring <> "" Then MsgBox "You already ordered a ID Card for thi
person"


Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Id Card Order")

rst.AddNew
rst![Idcode] = [Idcode]
rst![Offset] = [Offset]
rst![Surname] = [ Surname]
rst![Givename] = [Givename]
rst![Address1] = [Address1]
rst![Address2] = [Address2]
rst![City] = [City]
rst![State] = [State]
rst![ZipCode] = [ZipCode]
rst![Phone] = [Phone]
rst![Source] = [Source]
rst![GroupID] = [GroupID]
rst![Location] = [Location]
rst![UniqueId] = [UniqueId]
rst![Salary] = [Salary]
rst![Policy] = [Policy]
rst![Incept] = [Incept]
rst![Expire] = [Expire]
rst![PlanID] = [PlanID]
rst![Category] = [Category]
rst![Coverage] = [Coverage]
rst![PaidThru] = [PaidThru]
rst![Status] = [Status]
rst![Cobra] = [Cobra]
rst![CobraStart] = [CobraStart]
rst![CobraEnd] = [CobraEnd]
rst![PatientSSN] = [PatientSSN]
rst![DOB] = [DOB]
rst![Sex] = [Sex]
rst![Relationship] = [Relationship]
rst![Prior Coverage] = [Prior Coverage]
rst![Student Status] = [Student Status]
rst![School Year End Date] = [School Year End Date]
rst![EffectiveDate] = [EffectiveDate]
rst![Pre-Existing Date] = [Pre-Existing Date]
rst![Alt Ident] = [Alt Ident]
rst![COB Status] = [COB Status]
rst![Network] = [Network]
rst![PCP ID] = [PCP ID]
rst![HMA Unique ID] = [HMA Unique ID]
rst![RXBin] = [RXBin]
rst![RXPCN] = [RXPCN]
rst![RXGRP] = [RXGRP]
rst![Issuer] = [Issuer]
rst![PO Box] = [PO Box]
rst![ID Date] = [ID Date]
rst![ID Logo] = [ID Logo]
rst![Id Flag] = [Id Flag]
rst![Exclusion Records] = [Exclusion Records]
rst![Time Stamp] = [Time Stamp]

rst.Update

End Sub


now when their isnt a duplicate it should submit the information. Whe
their is a duplicate it should submit the information? Right now th
code checks for duplicates and then submits it anyway????


thank
 
It's submitting the information because you don't have anything in your code
to tell it not to!

Private Sub Order_ID_Cards_Click()

[Id Flag] = "Y"
Dim anystring As Variant
anystring = DLookup("[SurName]", ("Id Card Order"), "[Surname] = " &
Chr$(34) & Surname & Chr$(34))
If anystring <> "" Then
MsgBox "You already ordered a ID Card for this person"
Else

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Id Card Order")

rst.AddNew
rst![Idcode] = [Idcode]

rst.Update
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



PeteEHP said:
i need to know how to check to see if duplicates , i used the following
code. but if their is no duplicates it dosent submit the
information???

heres the code

Private Sub Order_ID_Cards_Click()

Let [Id Flag] = "Y"
Dim anystring As Variant
anystring = DLookup("[SurName]", ("Id Card Order"), "[Surname] = " &
Chr$(34) & Surname & Chr$(34))
If anystring <> "" Then MsgBox "You already ordered a ID Card for this
person"


Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Id Card Order")

rst.AddNew
rst![Idcode] = [Idcode]
rst![Offset] = [Offset]
rst![Surname] = [ Surname]
rst![Givename] = [Givename]
rst![Address1] = [Address1]
rst![Address2] = [Address2]
rst![City] = [City]
rst![State] = [State]
rst![ZipCode] = [ZipCode]
rst![Phone] = [Phone]
rst![Source] = [Source]
rst![GroupID] = [GroupID]
rst![Location] = [Location]
rst![UniqueId] = [UniqueId]
rst![Salary] = [Salary]
rst![Policy] = [Policy]
rst![Incept] = [Incept]
rst![Expire] = [Expire]
rst![PlanID] = [PlanID]
rst![Category] = [Category]
rst![Coverage] = [Coverage]
rst![PaidThru] = [PaidThru]
rst![Status] = [Status]
rst![Cobra] = [Cobra]
rst![CobraStart] = [CobraStart]
rst![CobraEnd] = [CobraEnd]
rst![PatientSSN] = [PatientSSN]
rst![DOB] = [DOB]
rst![Sex] = [Sex]
rst![Relationship] = [Relationship]
rst![Prior Coverage] = [Prior Coverage]
rst![Student Status] = [Student Status]
rst![School Year End Date] = [School Year End Date]
rst![EffectiveDate] = [EffectiveDate]
rst![Pre-Existing Date] = [Pre-Existing Date]
rst![Alt Ident] = [Alt Ident]
rst![COB Status] = [COB Status]
rst![Network] = [Network]
rst![PCP ID] = [PCP ID]
rst![HMA Unique ID] = [HMA Unique ID]
rst![RXBin] = [RXBin]
rst![RXPCN] = [RXPCN]
rst![RXGRP] = [RXGRP]
rst![Issuer] = [Issuer]
rst![PO Box] = [PO Box]
rst![ID Date] = [ID Date]
rst![ID Logo] = [ID Logo]
rst![Id Flag] = [Id Flag]
rst![Exclusion Records] = [Exclusion Records]
rst![Time Stamp] = [Time Stamp]

rst.Update

End Sub


now when their isnt a duplicate it should submit the information. When
their is a duplicate it should submit the information? Right now the
code checks for duplicates and then submits it anyway????


thanks
Click to expand...
 
Back
Top