Combo Box: check if null

  • Thread starter BonnieW via AccessMonster.com
  • Start date
B

BonnieW via AccessMonster.com

I'm modifying some old code in some old forms to make them work (hopefully).
We have a form that had three subforms on them, all combo boxes: one is not
being used, the other is working as intended, and the third I'm having a
problem with. The third subform, Activity, would default to inputting the
first record on the list (odd coincidence, but completely unrelated to my
first post today). It's pulling these records from a small sql query which
apparently works just dandy. Looking at this subform, I could see no reason
why it was a subform and not a combo box, so to try to simplify things I made
it a combo box. Now, there's a bit of code in the form which tests to see if
the values of various fields are null (before entering the record... it
doesn't actually work, but that's another gripe, I think); I cannot get this
to recognize the combo box as not null. I'm not sure if I'm referencing it
correctly; furthermore, it's not being entered into the table with the other
data- so it probably really *is* null, despite it showing up quite nicely on
the form.

Here is the trainwreck of code for the form (if you're sensitive, shield your
eyes- I know no VBA and even I realize this has got to be bad):

Option Compare Database

Private Sub Child32_Enter()

End Sub

Private Sub Combo24_Change()
Refresh
ManageUnitID = Text34.Value
End Sub

Private Sub Command45_Click()



Rem Set up variables to use in this subroutine
seqNum_MasterRec = ConsEffortID
savedNatAreaID = NatAreaID.Value
savedActivity = Activity.Value
Rem NOTE - Management Unit is not yet active for volunteers
Rem savedMgtUnit = ManageUnitID.Value
Rem savedMgtUnitName = Combo24.Value

Rem Make sure that required fields are present
If IsNull(Activity.Value) Then
MsgBox "Missing Required Field - Activity!"
Exit Sub
End If
If IsNull(EffortDate.Value) Then
MsgBox "Missing Required Field - Effort Date!"
Exit Sub
End If
If IsNull(Hours.Value) Then
MsgBox "Missing Required Field - Hours!"
Exit Sub
End If
If IsNull(PeopleType.Value) Then
MsgBox "Missing Required Field - PeopleType!"
Exit Sub
Rem Else
Rem Refresh
Rem DoCmd.Save
Rem DoCmd.GoToRecord , , acNext, 1
Rem Refresh
End If

End_Sub:
PeopleType.Value = Combo0
savedConsEffortID = ConsEffortID
NatAreaID.Value = savedNatAreaID
Activity.Value = savedActivity
Rem NOTE - Management Unit is not yet active for volunteers
Rem ManageUnitID.Value = savedMgtUnit
Rem Combo24.Value = savedMgtUnitName
Rem DoCmd.GoToControl "Combo24"
DoCmd.GoToRecord , , acNext

Rem Look for special circumstances that require subforms for data entry
If savedActivity = "Trail Work" Then
DoCmd.OpenForm "frmTrailWorkEntry", , , , acFormAdd
End If
If savedActivity = "Invasives Control" Then
DoCmd.OpenForm "frmInvasivesControlCrewNew", , , , acFormAdd
End If
If savedActivity = "Revegetation" Then
DoCmd.OpenForm "frmRevegetationNew", , , , acFormAdd
End If

End Sub
' Private Sub Form_AfterUpdate()
' MsgBox "Saved."
' End Sub


Private Sub Command62_Click()
DoCmd.OpenForm "tablePeople", , , , acFormEdit
PeopleID.Value = [Forms]![tablePeople]![PeopleID]
Text58.Value = [Forms]![tablePeople]![FirstName]
Text60.Value = [Forms]![tablePeople]![LastName]
Refresh
End Sub

Private Sub Form_GotFocus()
Requery
PeopleID.Value = [Forms]![tablePeople]![PeopleID]
Text58.Value = [Forms]![tablePeople]![FirstName]
Text60.Value = [Forms]![tablePeople]![LastName]
Activity.Value = [svbFrmtbl1Activity].Form![ActivityName]
PeopleType.Value = [frmPeopleType].Form![PeopleType]

End Sub

'Private Sub frmPeopleType_Exit(Cancel As Integer)
' PeopleType.Value = [frmPeopleType].Form![PeopleType]
' Refresh
' DoCmd.GoToControl "Notes"
'End Sub

Private Sub svbFrmtbl1Activity_Exit(Cancel As Integer)
Activity.Value = [svbFrmtbl1Activity].Form![ActivityName]
Refresh
DoCmd.GoToControl "EffortDate"
End Sub

Private Sub Text34_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Toggle26_Click()
Me.Undo
DoCmd.Close , , acSaveNo
End Sub
Private Sub Command54_Click()
On Error GoTo Err_Command54_Click
DoCmd.Close
DoCmd.Close , tablePeople

Exit_Command54_Click:
Exit Sub

Err_Command54_Click:
MsgBox Err.Description
Resume Exit_Command54_Click

End Sub
 
B

BonnieW via AccessMonster.com

I should add that the combo box in question is called combo0.
 
C

Carl Rapson

Where are you testing the value of combo0 to see if it's Null?

Carl Rapson

BonnieW via AccessMonster.com said:
I'm modifying some old code in some old forms to make them work
(hopefully).
We have a form that had three subforms on them, all combo boxes: one is
not
being used, the other is working as intended, and the third I'm having a
problem with. The third subform, Activity, would default to inputting the
first record on the list (odd coincidence, but completely unrelated to my
first post today). It's pulling these records from a small sql query
which
apparently works just dandy. Looking at this subform, I could see no
reason
why it was a subform and not a combo box, so to try to simplify things I
made
it a combo box. Now, there's a bit of code in the form which tests to see
if
the values of various fields are null (before entering the record... it
doesn't actually work, but that's another gripe, I think); I cannot get
this
to recognize the combo box as not null. I'm not sure if I'm referencing
it
correctly; furthermore, it's not being entered into the table with the
other
data- so it probably really *is* null, despite it showing up quite nicely
on
the form.

Here is the trainwreck of code for the form (if you're sensitive, shield
your
eyes- I know no VBA and even I realize this has got to be bad):

Option Compare Database

Private Sub Child32_Enter()

End Sub

Private Sub Combo24_Change()
Refresh
ManageUnitID = Text34.Value
End Sub

Private Sub Command45_Click()



Rem Set up variables to use in this subroutine
seqNum_MasterRec = ConsEffortID
savedNatAreaID = NatAreaID.Value
savedActivity = Activity.Value
Rem NOTE - Management Unit is not yet active for volunteers
Rem savedMgtUnit = ManageUnitID.Value
Rem savedMgtUnitName = Combo24.Value

Rem Make sure that required fields are present
If IsNull(Activity.Value) Then
MsgBox "Missing Required Field - Activity!"
Exit Sub
End If
If IsNull(EffortDate.Value) Then
MsgBox "Missing Required Field - Effort Date!"
Exit Sub
End If
If IsNull(Hours.Value) Then
MsgBox "Missing Required Field - Hours!"
Exit Sub
End If
If IsNull(PeopleType.Value) Then
MsgBox "Missing Required Field - PeopleType!"
Exit Sub
Rem Else
Rem Refresh
Rem DoCmd.Save
Rem DoCmd.GoToRecord , , acNext, 1
Rem Refresh
End If

End_Sub:
PeopleType.Value = Combo0
savedConsEffortID = ConsEffortID
NatAreaID.Value = savedNatAreaID
Activity.Value = savedActivity
Rem NOTE - Management Unit is not yet active for volunteers
Rem ManageUnitID.Value = savedMgtUnit
Rem Combo24.Value = savedMgtUnitName
Rem DoCmd.GoToControl "Combo24"
DoCmd.GoToRecord , , acNext

Rem Look for special circumstances that require subforms for data entry
If savedActivity = "Trail Work" Then
DoCmd.OpenForm "frmTrailWorkEntry", , , , acFormAdd
End If
If savedActivity = "Invasives Control" Then
DoCmd.OpenForm "frmInvasivesControlCrewNew", , , , acFormAdd
End If
If savedActivity = "Revegetation" Then
DoCmd.OpenForm "frmRevegetationNew", , , , acFormAdd
End If

End Sub
' Private Sub Form_AfterUpdate()
' MsgBox "Saved."
' End Sub


Private Sub Command62_Click()
DoCmd.OpenForm "tablePeople", , , , acFormEdit
PeopleID.Value = [Forms]![tablePeople]![PeopleID]
Text58.Value = [Forms]![tablePeople]![FirstName]
Text60.Value = [Forms]![tablePeople]![LastName]
Refresh
End Sub

Private Sub Form_GotFocus()
Requery
PeopleID.Value = [Forms]![tablePeople]![PeopleID]
Text58.Value = [Forms]![tablePeople]![FirstName]
Text60.Value = [Forms]![tablePeople]![LastName]
Activity.Value = [svbFrmtbl1Activity].Form![ActivityName]
PeopleType.Value = [frmPeopleType].Form![PeopleType]

End Sub

'Private Sub frmPeopleType_Exit(Cancel As Integer)
' PeopleType.Value = [frmPeopleType].Form![PeopleType]
' Refresh
' DoCmd.GoToControl "Notes"
'End Sub

Private Sub svbFrmtbl1Activity_Exit(Cancel As Integer)
Activity.Value = [svbFrmtbl1Activity].Form![ActivityName]
Refresh
DoCmd.GoToControl "EffortDate"
End Sub

Private Sub Text34_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Toggle26_Click()
Me.Undo
DoCmd.Close , , acSaveNo
End Sub
Private Sub Command54_Click()
On Error GoTo Err_Command54_Click
DoCmd.Close
DoCmd.Close , tablePeople

Exit_Command54_Click:
Exit Sub

Err_Command54_Click:
MsgBox Err.Description
Resume Exit_Command54_Click

End Sub
Click to expand...
 
P

Phil

You might have already checked this but what is the default value of the
Combo Box (is it Null) if not you are wasting your time looking for Nulls

just a thought

Phil

Carl Rapson said:
Where are you testing the value of combo0 to see if it's Null?

Carl Rapson

BonnieW via AccessMonster.com said:
I'm modifying some old code in some old forms to make them work
(hopefully).
We have a form that had three subforms on them, all combo boxes: one is
not
being used, the other is working as intended, and the third I'm having a
problem with. The third subform, Activity, would default to inputting the
first record on the list (odd coincidence, but completely unrelated to my
first post today). It's pulling these records from a small sql query
which
apparently works just dandy. Looking at this subform, I could see no
reason
why it was a subform and not a combo box, so to try to simplify things I
made
it a combo box. Now, there's a bit of code in the form which tests to see
if
the values of various fields are null (before entering the record... it
doesn't actually work, but that's another gripe, I think); I cannot get
this
to recognize the combo box as not null. I'm not sure if I'm referencing
it
correctly; furthermore, it's not being entered into the table with the
other
data- so it probably really *is* null, despite it showing up quite nicely
on
the form.

Here is the trainwreck of code for the form (if you're sensitive, shield
your
eyes- I know no VBA and even I realize this has got to be bad):

Option Compare Database

Private Sub Child32_Enter()

End Sub

Private Sub Combo24_Change()
Refresh
ManageUnitID = Text34.Value
End Sub

Private Sub Command45_Click()



Rem Set up variables to use in this subroutine
seqNum_MasterRec = ConsEffortID
savedNatAreaID = NatAreaID.Value
savedActivity = Activity.Value
Rem NOTE - Management Unit is not yet active for volunteers
Rem savedMgtUnit = ManageUnitID.Value
Rem savedMgtUnitName = Combo24.Value

Rem Make sure that required fields are present
If IsNull(Activity.Value) Then
MsgBox "Missing Required Field - Activity!"
Exit Sub
End If
If IsNull(EffortDate.Value) Then
MsgBox "Missing Required Field - Effort Date!"
Exit Sub
End If
If IsNull(Hours.Value) Then
MsgBox "Missing Required Field - Hours!"
Exit Sub
End If
If IsNull(PeopleType.Value) Then
MsgBox "Missing Required Field - PeopleType!"
Exit Sub
Rem Else
Rem Refresh
Rem DoCmd.Save
Rem DoCmd.GoToRecord , , acNext, 1
Rem Refresh
End If

End_Sub:
PeopleType.Value = Combo0
savedConsEffortID = ConsEffortID
NatAreaID.Value = savedNatAreaID
Activity.Value = savedActivity
Rem NOTE - Management Unit is not yet active for volunteers
Rem ManageUnitID.Value = savedMgtUnit
Rem Combo24.Value = savedMgtUnitName
Rem DoCmd.GoToControl "Combo24"
DoCmd.GoToRecord , , acNext

Rem Look for special circumstances that require subforms for data entry
If savedActivity = "Trail Work" Then
DoCmd.OpenForm "frmTrailWorkEntry", , , , acFormAdd
End If
If savedActivity = "Invasives Control" Then
DoCmd.OpenForm "frmInvasivesControlCrewNew", , , , acFormAdd
End If
If savedActivity = "Revegetation" Then
DoCmd.OpenForm "frmRevegetationNew", , , , acFormAdd
End If

End Sub
' Private Sub Form_AfterUpdate()
' MsgBox "Saved."
' End Sub


Private Sub Command62_Click()
DoCmd.OpenForm "tablePeople", , , , acFormEdit
PeopleID.Value = [Forms]![tablePeople]![PeopleID]
Text58.Value = [Forms]![tablePeople]![FirstName]
Text60.Value = [Forms]![tablePeople]![LastName]
Refresh
End Sub

Private Sub Form_GotFocus()
Requery
PeopleID.Value = [Forms]![tablePeople]![PeopleID]
Text58.Value = [Forms]![tablePeople]![FirstName]
Text60.Value = [Forms]![tablePeople]![LastName]
Activity.Value = [svbFrmtbl1Activity].Form![ActivityName]
PeopleType.Value = [frmPeopleType].Form![PeopleType]

End Sub

'Private Sub frmPeopleType_Exit(Cancel As Integer)
' PeopleType.Value = [frmPeopleType].Form![PeopleType]
' Refresh
' DoCmd.GoToControl "Notes"
'End Sub

Private Sub svbFrmtbl1Activity_Exit(Cancel As Integer)
Activity.Value = [svbFrmtbl1Activity].Form![ActivityName]
Refresh
DoCmd.GoToControl "EffortDate"
End Sub

Private Sub Text34_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Toggle26_Click()
Me.Undo
DoCmd.Close , , acSaveNo
End Sub
Private Sub Command54_Click()
On Error GoTo Err_Command54_Click
DoCmd.Close
DoCmd.Close , tablePeople

Exit_Command54_Click:
Exit Sub

Err_Command54_Click:
MsgBox Err.Description
Resume Exit_Command54_Click

End Sub
Click to expand...
Click to expand...
 
B

BonnieW via AccessMonster.com

If IsNull(PeopleType.Value) Then
MsgBox "Missing Required Field - PeopleType!"
Exit Sub
 
B

BonnieW via AccessMonster.com

I don't believe it had a default value set. :(

Anyway, we've gone back to the subforms and are tinkering with that- we
thought we had a breakthrough with those last night, but this morning, not so
much.
You might have already checked this but what is the default value of the
Combo Box (is it Null) if not you are wasting your time looking for Nulls

just a thought

Phil
Where are you testing the value of combo0 to see if it's Null?
Click to expand...
[quoted text clipped - 152 lines]
Click to expand...
 
B

BonnieW via AccessMonster.com

If IsNull(PeopleType.Value) Then
MsgBox "Missing Required Field - PeopleType!"
Exit Sub

I thought I'd managed to have the combo box = PeopleType.Value, but
apparently that wasn't working... in any case, we've gone back to working on
the subform. Thanks for trying to help.

Carl said:
Where are you testing the value of combo0 to see if it's Null?

Carl Rapson
I'm modifying some old code in some old forms to make them work
(hopefully).
Click to expand...
[quoted text clipped - 148 lines]
End Sub
Click to expand...
Click to expand...
 
B

BonnieW via AccessMonster.com

And, thanks. :)
You might have already checked this but what is the default value of the
Combo Box (is it Null) if not you are wasting your time looking for Nulls

just a thought

Phil
Where are you testing the value of combo0 to see if it's Null?
Click to expand...
[quoted text clipped - 152 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Will this IF work? 6
If...ElseIf 5
Access (XP) crashes on after update of combo box 2
Add New Record for Combo Box 5
Access combo box dd click to open another form problem 0
BefreUpdate set to null error 2115 6
Combo box rowsource based on another combo box 3
value in combo box 8

Top