Combo box and required record problem

[newby2VBA]

Is this the place for VBA help?

 

[HiTechCoach]

Welcome to AWF!

Yes it is the place. Ask away.

 

[newby2VBA]

I think I'm in the right place so here goes
I have a combo box on a form linked to my employee table.
The combo box is called cbocity and get it data from the citytable and I was able to use vba to open the city form to add to the list of values when the city was not in the list to begin with.
Now that I made the city field require in the employee table I can't go an update the record on the other form.
Is there a solution to this?

 

[newby2VBA]

I think I'm in the right place so here goes
I have a combo box on a form linked to my employee table.
The combo box is called cbocity and get it data from the citytable and I was able to use vba to open the city form to add to the list of values when the city was not in the list to begin with.
Now that I made the city field require in the employee table I can't go an update the record on the other form.
Is there a solution to this?

here is the code were xxx is City
Private Sub XXX_DblClick(Cancel As Integer)
'Double click to go to the form the dropdown refers to enter a new entry.
On Error GoTo Err_XXX_DblClick
Dim lngXXX As Long
If IsNull(Me![XXX]) Then
Me![XXX].Text = ""
Else
lngXXX = Me![XXX]
Me![XXX] = Null
End If
DoCmd.OpenForm "XXXFrm", , , , , acDialog, "GotoNew"
Me![XXX].Requery
If lngXXX <> 0 Then Me![XXX] = lngXXX
Exit_XXX_DblClick:
Exit Sub
Err_XXX_DblClick:
MsgBox Err.Description
Resume Exit_XXX_DblClick
End Sub

 

[newby2VBA]

Please Help me with this I want to go Home It's Friday

 

[missinglinq]

I would:

1. Keep your original, working code for adding a new city
2. Remove the Required from the field
3. Use validation code to ensure that a city is selected

Use code like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Nz(Me.cboCity, "") = "" Then
   MsgBox "You Must Select a City Before Saving this Record!"
   Cancel = True
   cboCity.SetFocus
   cboCity.Dropdown
 End If
End Sub

 

[newby2VBA]

I would:

1. Keep your original, working code for adding a new city
2. Remove the Required from the field
3. Use validation code to ensure that a city is selected

Use code like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Nz(Me.cboCity, "") = "" Then
   MsgBox "You Must Select a City Before Saving this Record!"
   Cancel = True
   cboCity.SetFocus
   cboCity.Dropdown
 End If
End Sub

Thanks that seems to Work
I also found that when I changed null in my code to 0 it also worked.
Now I have two options it seems.

 

[missinglinq]

Glad you go it working!

Good luck on your project!

Linq