Requiring Data in field on Form with enter new record in Subform

[mma40]

I have an existing table that I now require certain fields to be
entered which it does with new records. How do I go about making the
user fill in all the missing "required" fields on the main form before
adding a record to the subform. There are about 5 fields that are now
required which were not when the previous person created the table.
Any help would be appreciated.

 

[Sandra Daigle]

I would suggest going to the table and making those fields required. Even
though you may have existing data that violates the new rule, you can change
the rule to apply to new records.

 

[mma40]

What I am looking for is when a customer is pulled up and is missing a
required field such as state and/or county, that an order can't be
entered on the subform until those field are completed. Sorry for the
confusion

 

[mma40]

I have already made the fields required but that will only work on new
records. I am talking about existing records.

 

[Sandra Daigle]

Ok - that makes sense. To require that the record be fixed before the user
enters subform records you can create an Enter event for the subform control
that does the validation - something like this:

Private Sub sfrmOrder_Enter()
'Only check existing records
If Not Me.NewRecord Then
If IsNull(Me.City) Then
MsgBox "City must be entered"
Me.City.SetFocus
Elseif isnull(me.StateCode) then
MsgBox "State must be entered"
Me.StateCode.SetFocus
End If
End If

End Sub

 

[mma40]

I have looked and tried other places but I cannot find an On Enter
event for the subform. Also the city/state is on the Main form, do I
need to include that in the SetFocus line?

 

[Sandra Daigle]

Click the subform once to select the subform control. Then look at the
events tab on the property sheet. The enter event is on this tab sheet.

Yes, you would need to include Elseif branches for each field you want to
require.

 

[mma40]

I found it and put in the following but get error msg: Method or data
member not found and SetFocus is highlighted.
'Only check existing records
If Not Me.NewRecord Then
If IsNull(Me.County) Then
MsgBox "County must be entered"
Me.County.SetFocus
ElseIf IsNull(Me.State) Then
MsgBox "State must be entered"
Me.State.SetFocus
End If
End If

 

[Sandra Daigle]

Make sure the control is visible and enabled. Otherwise I'm not sure why
this would create an error.

 

[mma40]

Finally, I figured out what I was doing. My county field is called
Combo251 because I added it later and forgot. But I now have another
problem. Once i click ok to "county must be entered", it goes ahead
and opens the other form. How do I keep that from happening before the
fields are done?

 

[Sandra Daigle]

I'm not sure what form you are referring to - I thought we were talking
about a subform. Post your code and I'll take a look.

 

[mma40]

Here is my code. I have it check for required fields of an existing
record when a command button on the Main form is clicked. Once all
required fields are entered, my dataentry form opens to input
information that goes back to a linked table. Even though I have the
focus on the Main form, it goes ahead and opens the dataentry form.

Private Sub VisitEntry_Click()
On Error GoTo Err_VisitEntry_Click

'Only check existing records
If Not Me.NewRecord Then
If IsNull(Me.County) Then
MsgBox "County must be entered"
Me.County.SetFocus
ElseIf IsNull(Me.EthnicGroup) Then
MsgBox "Ethnic Group must be entered"
Me.EthnicGroup.SetFocus
End If
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "VisitEntry"

stLinkCriteria = "[UserNumber]=" & Me![UserNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_VisitEntry_Click:
Exit Sub

Err_VisitEntry_Click:
MsgBox Err.Description
Resume Exit_VisitEntry_Click

End Sub


Sorry to be a pain. I am new to this and trying to find info on the
questions already posted but that is not very easy. Thank you for your
time and expertise.

 

[mma40]

Here is my code. I have it check for required fields of an existing
record when a command button on the Main form is clicked. Once all
required fields are entered, my dataentry form opens to input
information that goes back to a linked table. Even though I have the
focus on the Main form, it goes ahead and opens the dataentry form.

Private Sub VisitEntry_Click()
On Error GoTo Err_VisitEntry_Click

'Only check existing records
If Not Me.NewRecord Then
If IsNull(Me.County) Then
MsgBox "County must be entered"
Me.County.SetFocus
ElseIf IsNull(Me.EthnicGroup) Then
MsgBox "Ethnic Group must be entered"
Me.EthnicGroup.SetFocus
End If
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "VisitEntry"

stLinkCriteria = "[UserNumber]=" & Me![UserNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_VisitEntry_Click:
Exit Sub

Err_VisitEntry_Click:
MsgBox Err.Description
Resume Exit_VisitEntry_Click

End Sub


Sorry to be a pain. I am new to this and trying to find info on the
questions already posted but that is not very easy. Thank you for your
time and expertise.

 

[Sandra Daigle]

Just add logic so that the form is not opened. Using a boolean variable you
can control whether the openform occurs. Also you should probably move the
Dim statements to the top of your procedure. While it's not technically
wrong for them to be inline, it's also unusual and could make it hard to
debug.

Private Sub VisitEntry_Click()
Dim stDocName As String
Dim stLinkCriteria As String
'New variable
dim fContinue as Boolean

On Error GoTo Err_VisitEntry_Click
'Start with fContinue true then mark it
'false whenever a field needs to be fixed
fContinue = true
'Only check existing records
If Not Me.NewRecord Then
If IsNull(Me.County) Then
MsgBox "County must be entered"
Me.County.SetFocus
fcontinue=false
ElseIf IsNull(Me.EthnicGroup) Then
MsgBox "Ethnic Group must be entered"
Me.EthnicGroup.SetFocus
fcontinue=false
End If
End If

if fContinue then
stDocName = "VisitEntry"
stLinkCriteria = "[UserNumber]=" & Me![UserNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
endif

Exit_VisitEntry_Click:
Exit Sub

Err_VisitEntry_Click:
MsgBox Err.Description
Resume Exit_VisitEntry_Click

End Sub

 

[Sandra Daigle]

Also - no apologies necessary - I (and others) wouldn't answer questions if
we didn't feel like helping others. This is how many of us got started. You
might want to try using Google for finding answers to more common questions.
Look under the Groups tab of Google to see newsgroup threads that pertain to
your search key words.

 

[mma40]

Great!! That works perfectly. Thank you.