Control not picking up value from other field/How do I save with a null?

[Perineum]

I am an inexperienced MS Access 2003 programmer. I have added 2 fields (combo boxes) to a form. A value is chosen for the first field. The second field chooses a value based on the selection from the first field. The second field doesn’t seem to be picking up this value. When I click on the down arrow, there are no selections.

Secondly, I want to be able to save the form with both of these fields being blank. However, when I try to do this, I get an error message: “You tried to assign a Null value to a variable that is not a Variant data type”. How do I allow the form to save when these 2 fields are null?

The row source for the second field is an sql statement:

SELECT tbl_Sub_Trend.ST_id, tbl_Sub_Trend.ST_name_e, tbl_Sub_Trend.ST_acronym_e
FROM tbl_Sub_Trend
WHERE (((tbl_Sub_Trend.ST_TRD_id)=[TRD_name_e].[Value]));

Thanks
Sean

[June7]

The second combobox must the requeried after selection in first combobox. Could use the AfterUpdate event of first combobox. Code in the VBA procedure: Me.combobox2name.Requery

I don't understand why the form will not save if data not selected in the comboboxes. Are you trying to save record by code? Record entry/edit committed to table when form closes, move to another record, or run code. Show your code.

Omit the .[Value] reference. This is default and will be presumed.

Want to provide project for analysis? Follow instructions at bottom of my post.

[Perineum]

Thanks for your help! There was already code to requery after the first combobox was filled. I realized that I needed to add an "On Change" event to trigger the code. Once I did that I started getting an "Object Required" error. Here is the code:
Private Sub cbxTrend_Change()
'Refresh Dropdown list with the corresponding Sub_trends for a specific Trend category
cbxSub_Trend.RowSource = "SELECT tbl_Sub_Trend.ST_id, tbl_SubTrend.ST_name_e, tbl_SubTrend.ST_acronym_e FROM tbl_SubTrend WHERE tbl_SubTrend.ST_TRD_id=[cbxTrend];"
cbxSubTrend.Requery
'Change has been made, therefore NEED to save
bNeedSave = True
End Sub


For the "Null Value" problem - Yes, the record is saved by code:

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
'Save new information to database
Call SetData

'No need to ask to save because already saved
bNeedSave = False

Exit_cmdSave_Click:
Exit Sub
Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click
End Sub



Private Function SetData()
'Date the incident occured
INC_date.Value = txtDate.Value
'Ticket Number
INC_ticket_num.Value = txtTicketNum.Value
'Saves TOTAL OUTAGE (Minute value) to database
INC_duration_outage = SetOutageTime(txtOutageHour, txtOutageMinute)
'Saves REPORTED OUTAGE (Hour value) to database
INC_duration_reported = SetOutageTime(txtReportedHour, txtReportedMinute)
'Severity
INC_sev_level.Value = cbxSeverity.Value
'Outage impact (Full/Partiel)
INC_outage_type.Value = cbxImpact.Value
'Responsibility (CRA/CBSA)
RA_name_e.Value = cbxResponsibility.Value
'Incident due to a change (Yes/No)
INC_change.Value = cbxChange.Value
'Reoccuring incident (Yes/No)
INC_reoccuring.Value = cbxReoccuring.Value

'Escalate to Probelm Management (Yes/No)
INC_follow_up.Value = cbxProblemManagement.Value

'Application Group (ICS/ACROSS/Corporate...)
GRP_name_e.Value = cbxAppGroup.Value
'V2.2 - Trend Categories
TRD_name_e.Value = cbxTrend.Value
'Specific Application that was impacted
APP_name_e.RowSource = cbxApplication.RowSource
APP_name_e.Requery
APP_name_e.Value = cbxApplication.Value
'V2.2 - Sub-Trend
ST_name_e.RowSource = cbxSubTrend.RowSource
ST_name_e.Requery
ST_name_e.Value = cbxSubTrend.Value

'Failing component (Root cause)
CMP_name_e.Value = cbxComponent.Value
'Sub-component
INC_sub_component.Value = txtSubComponent.Value
'Type of error that caused the incident (Human/Software/Hardware/Application...)
ERR_name_e.RowSource = cbxErrorType.RowSource
ERR_name_e.Requery
ERR_name_e.Value = cbxErrorType.Value
'V2.1 - actual root cause
Actual_ERR_name_e.RowSource = cbxActualRootCause.RowSource
Actual_ERR_name_e.Requery
Actual_ERR_name_e.Value = cbxActualRootCause.Value

'Short text - Description of incident
INC_Description_e.Value = txtDescription.Value
'Short text - Impact incident had on Business
INC_Impact_e.Value = txtBusinessImpact.Value
'Short text - Root cause of incident
INC_root_cause_e.Value = txtRootCause.Value
'Short text - Short term fix to resolve incident
INC_short_term_fix.Value = txtShortTermFix.Value
'V2.0: Short text - RFC Number
INC_RFC_num.Value = txtRFCnum.Value
'V2.0: Short text - Comments
INC_comments.Value = txtComments.Value
'V2.0: Short text - PMWG Recommendation Summary
INC_PMWG_Summary.Value = txtPMWGSummary.Value
'V2.2: Short text - Problem Management Solution
INC_Prob_Mgmt_Sol.Value = TxtProbMgmtSol.Value

INC_last_update_date.Value = Now()
INC_last_update_user.Value = fOSUserName

End Function

[June7]

Not going to try to debug all that in my head. Step debug. For info on debug techniques refer to link at bottom of my post.