Check if the record exists and load it else add new one

[saintmh]

Hello everyone;

I've created a form and a sub-form, to make things easy i will give the following example:
################################################################ #
Country : France
###############################################################
# Cities:
# Paris
# Marseille
# Lyon
# .
#############################################################

Country is the main form and cities is the subform , what i need is when the form is on add new record mode and if the user enters a country already exists, the form loads it's cities otherwise it will add the new country normally .

 

[CJ_London]

the answer depends on how you have your data organised.

Ideally you should have a table like this:

tblCities
CityName text
Country text FK

this would populate your subform.

In the subform control (not the subform itself) you would set linkchildfields to Country and linkMasterFields to the name of the control where you enter France - if this is a combo box you could set its rowsource to

SELECT DISTINCT Country FROM tblCities ORDER BY Country

 

[highandwild]

I think what Saintmh means is that the form needs to prohibit the entry of duplicate Countries into the table.

I would set up a check for an existing record in the Before Update event
of the Country textbox. Change the references and procedure name as appropriate.

Private Sub txtCountryName_BeforeUpdate(Cancel As Integer)

If Not Nz(DLookup("[CountryName]", "tbl_Countries", "[CountryName] = " & Chr(34) & Me.txtCountryName.Text & Chr(34))) = "" Then

MsgBox Me.txtCountryName.Text & " already exists" & vbCrLf & vbCrLf & "Please enter a new country", vbCritical, "Warning!"

Cancel = True

End If

End Sub