Event Procedure to find duplicate entries and display message box 3

[michelleb1]

I have a form that accepts a street number and street name for a job. After they have been entered, I want to check if there already exists a record with the same address and if so - I want to display a notification (It's okay if there is, I just need them to know about it)If there isn't then I don't want anyhting to happen - I want the check to be transparent.

So I'm guessing I need to create either an afterupdate or onlostfocus event procedure but I don't know how to constuct it. I'm not very familiar with MS Visual Basic. I'll want to query the jobs table and if a record has the same combination of street number and street address - display a dialog box?

Also, can you recommend a good source to get started with enough knowledge of MS VB to accomplish things similar to what I've described.

Thanks in Advance for any ideas and help!

Michelle

 

[Edski]

Private Sub Address_AfterUpdate()

Me.RecordsetClone.FindFirst "[Address] = '" & Address & "'"

If Me.RecordsetClone.NoMatch Then
[green]'do nothing[/green]
Else
MsgBox "WARNING - A record already exists with this address."
End If

End Sub

 

[michelleb1]

Thank You, Thank You - Okay, but what if Street# and Street Name are two separate fields?

 

[Edski]

Yeah, no ploblem. Thought you'd ask that actually.

Me.RecordsetClone.FindFirst "[Street] = '" & Street & "'" & " AND [Street Name] = '" & [Street Name] & "'"

 

[michelleb1]

Thanks so much Edski! I appreciate your time and help. I don't know what I would do without you...


By the way, if you have a chance to look at my latest post - that would be great
thread702-693976

 

[uscitizen]

in my case, the field that we need to guard against duplicating is called MR_Number (for Medical Record Number) and is an integer valued field. would this work by using the same string?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[PHV]

In the Before_Update event procedure:
Me.RecordsetClone.FindFirst "MR_Number=" & [Name of MR_Number control]

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[uscitizen]

hi,

should this work?

Private Sub MR_Number_AfterUpdate()

Me.RecordsetClone.FindFirst "[MR_Number] = " & [Forms]!["ID Table"]![MR_Number]

If Me.RecordsetClone.NoMatch Then
'do nothing
Else
MsgBox ("WARNING -- A record already exists with this MR Number")

End If

End Sub


“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[PHV]

Give it a try with this small change:
Me.RecordsetClone.FindFirst "[MR_Number] = " & [Forms]![ID Table]![MR_Number]
Or even simpler:
Me.RecordsetClone.FindFirst "MR_Number=" & Me!MR_Number



Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[uscitizen]

et voila, phv!

but tell me, how can i also prevent the act of closing the msgbox window from moving the record to another record (the next record in the table)?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[PHV]

By puting your code in the Before_Update event procedure and set Cancel = True before the MsgBox.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[uscitizen]

terrific phv!

this is what we look like now

Private Sub MR_Number_BeforeUpdate(Cancel As Integer)
Me.RecordsetClone.FindFirst "MR_Number=" & Me!MR_Number

If Me.RecordsetClone.NoMatch Then
'do nothing
Else
Cancel = True
MsgBox "WARNING -- A record already exists with this MR Number", , "Check your Data!"


End If

End Sub


i don't understand why the 'Cancel = True' thing works and it'd be kind of neat if i could get it to display one of those red circles with a white 'X' on 'em.


“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[evalesthy]

Try this:

MsgBox "WARNING -- A record already exists with this MR Number", vbCritical, "Check your Data!

 

[uscitizen]

thanks evalesthy !

two quick questions, what does 'Cancel = True' do up there and i wonder if there's a way to replace the number that was in the "MR_Number" control for the one that was found to be duplicated elsewhere in the table? to recapitulate, once the msgbox is cleared, would it be possible to restore the original value, even if it was blank ?

in any event, thanks for the bandwidth.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[uscitizen]

ah, so near, yet so far....

i cobbled together this code:

Private Sub MR_Number_BeforeUpdate(Cancel As Integer)
Me.RecordsetClone.FindFirst "MR_Number=" & Me!MR_Number

If Me.RecordsetClone.NoMatch Then
'do nothing
Else
Cancel = True
MsgBox "WARNING -- A record already exists with this MR Number. The unedited value will be restored to this field.", vbCritical, "Check your Data!"
Me!MR_Number.Value = Me!MR_Number.OldValue
End If

End Sub


when i test it, it results in a 'Run Time error 438' explained as 'Object doesn't support this property of method'? and takes me to the offending line above.

what oh what have i done?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[Edski]

Hi uscitizen,
You can't put a value into the MR_Number in the Before_Update Event! It just doesn't make sense.

I know some people advise against using the SendKeys statement but if you add a [blue]SendKeys "{ESC}"[/blue] after the Warning message you should have what you want.

The Cancel = True statement is telling Access to Cancel the update of data to this field.
Cheers.