Validate data from Combo Box before is added ... help!

[Guest]

I have a form based on tblRepCity which displays the records (continuous
form) from this table. In the header section of this form, I have a Combo Box
called comCity which is based on tblCity (it has cities names only) and
stores the data in tblRepCity. What I am trying to accomplish is to
programmatically avoid adding the same city two times in the list or entering
a blank record (if a name is selected from the list and then delete with the
backspace from the keyboard). I know I can set the property in the table not
to allow duplications and that fields can not be black, however this will
generate a series of errors message and makes the process not user friendly
for my users. How can I address these problems programmatically? Doing some
research I came up with something like this but is does not seem to work
well. Any TESTED recomandation will help and please be as specific as you
can. Thank you all.

Private Sub comcity_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("City", "tblRepCity", "[city]=" & "ComCity.Value")) Then
MsgBox "City already selected"
Exit Sub
End If
Else
DoCmd.GoToRecord , , acNewRec
End If
End Sub

 

[John Vinson]

Private Sub comcity_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("City", "tblRepCity", "[city]=" & "ComCity.Value")) Then
MsgBox "City already selected"

This will return a value if you have a city in the table named
ComCity.Value... or it would if you had the syntactically required
quotemarks.

You're concatenating the NAME of the control, not its value!

Try

If Not IsNull(DLookup("City", "tblRepCity", "[city]='" & ComCity &
"'"))

so that the criterion is something like

[city] = 'San Anselmo'

after evaluation.

John W. Vinson[MVP]

 

[Guest]

John, if I attempt to make a duplicate entry and then delete it from the
combo box after getting my message, when I try to close the form I am getting
a message saying ...the field cannot contain a null value, etc... (I guess
because the table is preventing a null value). Also, if I live the value in
the combo box and I try to close the form then I am getting the message that
the record was not saved because it would constitute duplication (again, I
guess that's because of the table not allowing duplications). This is exactly
what I am trying to prevent. Also, all the .requery do not work at all. For
further clarification, what I am trying to do is to generate a list (as
subform) of selected cities from a combo box (this list will be use to
generate reports etc). Any further help is appreciated.

Private Sub comcity_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("City", "tblRepCity", "[city]=" & "ComCity.Value")) Then
MsgBox "City already selected"

This will return a value if you have a city in the table named
ComCity.Value... or it would if you had the syntactically required
quotemarks.

You're concatenating the NAME of the control, not its value!

Try

If Not IsNull(DLookup("City", "tblRepCity", "[city]='" & ComCity &
"'"))

so that the criterion is something like

[city] = 'San Anselmo'

after evaluation.

John W. Vinson[MVP]

 

[Guest]

This is what I have now:

Private Sub comcity_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("City", "tblRepCity", "[city]='" & ComCity & "'")) Then
MsgBox "City already selected"

Else

Me.frmRepSubCitySub.Requery
Forms.frmRepSubCity.Requery
Me.Refresh
End If

 

[Guest]

Don't worry, I solved the problem by excluding data already selected and
found in the table. The combo box will not show names already listed in table
so there is no way to enter duplications. To help other, this is how the Raw
Souce look like now:

SELECT tblCity.ID, tblCity.City
FROM tblCity
WHERE (((tblCity.City) Not In (select city from tblRepCity)))
ORDER BY tblCity.City;

Thank you all.

Private Sub comcity_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("City", "tblRepCity", "[city]=" & "ComCity.Value")) Then
MsgBox "City already selected"

This will return a value if you have a city in the table named
ComCity.Value... or it would if you had the syntactically required
quotemarks.

You're concatenating the NAME of the control, not its value!

Try

If Not IsNull(DLookup("City", "tblRepCity", "[city]='" & ComCity &
"'"))

so that the criterion is something like

[city] = 'San Anselmo'

after evaluation.

John W. Vinson[MVP]