record already exists on subform

[tubar]

I have a field on my data entry form called NAME. I also have a field on an attached subform called NAME. If a name is entered on the data entry form that matches a name on the subform I display a message. The VBA for the button is as followed

If Me.[FRMMODBUILDSUB].Form.NAME = Me.NAME Then
MsgBox "THAT NAME ALREADY EXISTS"
Else
DoCmd.OpenForm "LOGIN DIALOG", acNormal
DoCmd.Close acForm, "FRMMODBUILD", acSaveYes
End If
End Sub

 

[pbaldy]

You haven't said what the problem is, if any, but almost certainly that code is returning the names of the forms, not the values of the records. I'd change the field name, but if it's too late try bracketing the field name.

 

[tubar]

i bracketed around the field names. still no luck. once the button is pushed the record still gets created even if the name matches a name in the subform.

 

[jdraw]

How are the form and subform related?

 

[tubar]

The form is a data entry form and the subform lists all names. both forms come from the same table but are not linked on the form.

 

[pbaldy]

That will only test the subform record with focus, not all of them. I'd use a DCount() against the table.

 

[tubar]

worked like a champ. thanks again. i am new and im learning by jumping in the fire...

LTotal = DCount("[name]", "tblpersonoptions", "[name] = Forms![frmmodbuild]![name]")
If LTotal <> 0 Then
MsgBox "THAT NAME ALREADY EXISTS"
Else
DoCmd.OpenForm "LOGIN DIALOG", acNormal
DoCmd.Close acForm, "FRMMODBUILD", acSaveYes
End If
End Sub

 

[pbaldy]

Happy to help!