empty ComboBox not always registering as null

[Louise]

Hi, I have a combobox (linked to a table) on my form which allows users to
select a type of marketing material to keep track of how successful their
marketing is. I have a pop-up form telling them which fields have not been
filled in when they move off the current record. It works very well for all
the other compulsory fields but sometimes the combobox field is ignored
completely and sometimes it is picked up. I have tested and tested and I
can't see a pattern. I have tried the Is Null method and setting its value
as < 1 and get similar results. I do not get any error messages - when it
doesn't work it simply ignores the field. Can you help?

 

[Guest]

Hi, Louise.

Please post your Form BeforeUpdate event procedure and the name of your
combo box.

Sprinks

 

[Louise]

Hi Sprinks - thanks for getting back to me.

I have placed the code in the cmds to move around the database - not in the
FormBeforeUpdate event - perhaps this is where I have gone wrong?!

Anyway - here is an example in my code when the cmdNextRec button is
clicked - taking user into next record. I am self-taught and have never
shown my code before - so please be gentle with me. The name of the combo
box is cmbMarketing.

Thanks, Louise


Dim intCustNo
intCustNo = Me!CustomerNumber

Dim StrMobileMsg As String
Dim StrPostCodeMsg As String
Dim StrEmailMsg As String
Dim StrHowDidTheyHearMsg As String
Dim StrAddressMsg As String

Dim StrFrmReminder As String
StrFrmReminder = "frmCustChildReminder"

Dim blTest As Boolean

'checks to find out whether Customer Number field filled in on child's form

Me!CustomerNo.SetFocus

If IsNull(CustomerNo.Value) Then

'takes number from Customer form and copies to child's

Me!CustomerNo.SetFocus

Me!CustomerNo = intCustNo

End If

'checks whether text fields has been filled in by user and gives prompt if
not


Me!cmbMarketing.SetFocus

'check whether field is filled in

If IsNull(cmbMarketing.Value) Then

'create message for reminder
StrHowDidTheyHearMsg = "How did they hear?"

'set test to true so reminder form is triggered

blTest = True

End If


Me!MobilePhoneNo.SetFocus

'check whether field is filled in

If IsNull(MobilePhoneNo.Value) Then

'create message for reminder

StrMobileMsg = "Mobile Telephone Number"

'set test to true so reminder form is triggered

blTest = True

End If

Me!txtCustAddress.SetFocus

'check whether field is filled in

If IsNull(txtCustAddress.Value) Then

'create message for reminder

StrAddressMsg = "Address"

'set test to true so reminder form is triggered

blTest = True

End If

Me!txtPostCode.SetFocus

'check whether field is filled in

If IsNull(txtPostCode.Value) Then

'create message for reminder

StrPostCodeMsg = "Post-Code"

'set test to true so reminder form is triggered

blTest = True

End If

Me!EMail.SetFocus

'check whether field is filled in

If IsNull(EMail.Value) Then

'create message for reminder

StrEmailMsg = "Email Address"

'set test to true so reminder form is triggered

blTest = True

End If

If blTest = True Then

'Open up reminder form

DoCmd.OpenForm StrFrmReminder, acNormal

'fill in relevent text boxes with message

Forms!frmCustChildReminder!txtMobileMsg = StrMobileMsg
Forms!frmCustChildReminder!txtAddressMsg = StrAddressMsg
Forms!frmCustChildReminder!txtPostCodeMsg = StrPostCodeMsg
Forms!frmCustChildReminder!txtEmailMsg = StrEmailMsg
Forms!frmCustChildReminder!txtHowDidTheyHearMsg =
StrHowDidTheyHearMsg


'move focus of form to first possible missing field

Me!txtCustAddress.SetFocus

'if all fields complete then form will move to previous record



Else:
'updates records
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70


DoCmd.GoToRecord , , acNext

End If

 

[Guest]

Hi, Louise.

Although it’s impossible to tell without knowing what controls you have on
your main and subforms, I believe the reason your code does not work is that
you are not using the proper syntax to refer to a child field from a main
form event procedure. To refer to a control on the main form, use the
construct:

Me!YourControlName

or, better,

Me![YourControlName]

to clearly identify it as an object. To refer to a control on a subform,
the syntax is:

Me![YourSubformName].Form![YourSubformControlName]

The subform is a *itself* a control on the main form. To get at its
controls, you refer to the subform first, then use Form! to get at its own
collection of controls.

An easier way, however, to implement form-level checking is using the form’s
BeforeUpdate event, which triggers when the user attempts to leave an edited
record, and looping through all the controls in the form’s collection.

If you do not need all fields required, but wish to mark only selected ones,
you can use the control’s Tag property that Access provides as a versatile
place-holder. In this case, you can tag all of your required fields with a
string like “R†or “Required†(don’t type in the quotation marks). The
procedure loops through all the form controls; if the Tag matches, it will
assure that it’s properly filled in.

This technique can be modified further to do control-specific testing, as
shown in the Select Case section below.

Dim ctl As Control
For Each ctl In Me.Controls

If ctl.Tag = "R" Then

If Nz(ctl)=0 Then
MsgBox ctl.ControlSource & " is a required field. Please fill
in."
Cancel = True
ctl.SetFocus
Exit Sub
End If

End If

' Control-specific error checking
Select Case ctl.Name
Case "StartDate"
If DateValue(Me![EndDate]) < DateValue(Me![StartDate]) Then
MsgBox "End date is after start date."
Cancel = True
Me![StartDate].SetFocus
Exit Sub
End If
'Case "SomeOtherField"
' ... field-specific error-checking
' ... etc.

End Select

Next ctl

Hope that helps.
Sprinks

 

[Louise]

Dear Sprinks

Thanks for help. I understand what you are saying about referring to
subforms correctly (as controls) but the pop-up form in my case is not a
sub-form, it is an unbound form just there for the purpose of giving a
reminder to the user as to which fields are not filled in. Or have I missed
the point?

I am going to move my code to the Before update event and have a look at
your tag property - will let you know how I get on.

Thanks, Louise

 

[Guest]

Hi, Louise.

Yes; perhaps I misunderstood re: your control referrals. Just be sure that
if you're referring to a control on another form, you'll have to use full
syntax:

Forms![FormName]![ControlName]

Good luck. If I can help you further as you develop this application, let
me know.

Sprinks

 

[Louise]

Hi Sprinks

I transferred my code into the form's before update event. It now
works beautifully with some of my controls (when I do a search for
example) - I get my popup form which tells the user the compulsory
fields which have not been filled in. However when I click on
CmdNextRec (which moves the user to the next record) it comes up with
an error:

Runtime error 2105 "You can't go to the specified record"

The only code I now have on the CmdNextRec is:

DoCmd.GoToRecord, , acNext.

I have some code in the form's current event which I think may be
conflicting - although it was working fine before. It basically
enables and disables the cmds I use to navigate through the records.
Other than that there is some code which stores a couple of string
values to hold for comparison with the control when it is changed. I
have copied the code below. I'm getting very confused and tired - up
very late trying to work out what was going on!


Dim recClone As Recordset

'make a clone of the recordset underlying the form so _
we can move around that without affecting the form's recordset

Set recClone = Me.RecordsetClone()

'if we are in a new record, disable the <Next> button _
and enable the rest of the buttons

If Me.NewRecord Then
cmdFirstRec.Enabled = True
cmdPreviousRec.Enabled = True
CmdNextRec.Enabled = False
cmdLastRec.Enabled = True
CmdNewRec.Enabled = True
Exit Sub
End If

'If we reach here, we know we are not in a new record _
so we can enable the <New> buton if the form allows new records to be
added

CmdNewRec.Enabled = Me.AllowAdditions

'we need to check if there are no records. If so, we disable _
all buttons except for the <New> button

If recClone.RecordCount = 0 Then
cmdFirstRec.Enabled = False
cmdPreviousRec.Enabled = False
CmdNextRec.Enabled = False
cmdLastRec.Enabled = False

Else

'if there are records, we know that the <First> and _
<Last> buttons will always be enabled, irrespective _
of where we are in the recordset

cmdFirstRec.Enabled = True
cmdLastRec.Enabled = True

'Synchronise the current pointer in the current recordset and the
cloned recordset

recClone.Bookmark = Me.Bookmark

'Next we must see if we are on the first record _
if so, we should disable the <Previous> button

recClone.MovePrevious

'The previous button is enabled if we are not at the beginning of
the recordset or file

cmdPreviousRec.Enabled = Not (recClone.BOF)
recClone.MoveNext

'And then check whether we are on the last record _
if so, we should disable the <Next> button

recClone.MoveNext

'The Next button is enabled if we are not at the end of the
recordset or file

CmdNextRec.Enabled = Not (recClone.EOF)
recClone.MovePrevious
End If

'And finally close the cloned recordset

recClone.Close


'places the current class details into the temporary fields so the new
_
information can be compared with the old when the form is closed.


Dim strClass As String
Dim strStatus As String


Me!ClassName.SetFocus
strClass = Me!ClassName.Text

Me!Status.SetFocus
strStatus = Me!Status.Text

Me!TempStatus.SetFocus
Me!TempStatus.Text = strStatus

Me!TempClass.SetFocus
Me!TempClass.Text = strClass

Hi, Louise.

Yes; perhaps I misunderstood re: your control referrals. Just be sure that
if you're referring to a control on another form, you'll have to use full
syntax:

Forms![FormName]![ControlName]

Good luck. If I can help you further as you develop this application, let
me know.

Sprinks

Dear Sprinks

Thanks for help. I understand what you are saying about referring to
subforms correctly (as controls) but the pop-up form in my case is not a
sub-form, it is an unbound form just there for the purpose of giving a
reminder to the user as to which fields are not filled in. Or have I missed
the point?

I am going to move my code to the Before update event and have a look at
your tag property - will let you know how I get on.

Thanks, Louise