Experts:
Good morning -- I have a question about determining the value for an **unselected** combo box. Allow me to expand on the current process:
Process:
- I have a form that includes two combo boxes.
- User must select a value for the 1st combo box. If so, a record update routine will execute. If user did NOT select value for the 1st combo box, a msg box is thrown to remind user to "select a value".
- Now, selecting a value for the 2nd combo box is **optional**. If a value was selected, another update routine will be executed. However, if no value for 2nd combo was selected then only 1st update routine will be executed.
Controls:
- Both combox boxes are unbound controls
- Both combos use SQL statements as their row source
Current VBA:
Code:
Private Sub cboMoveStaffMember_Click()
On Error GoTo cboMoveStaffMember_Click
'If user presses "Yes", then Staffmember will be reassigned to a different organization
If MsgBox("Are you sure you want to move " & Auto_Title0 & " into the new organization?", vbYesNo + vbQuestion) <> vbNo Then
'Moves Staff Member to a New Organization
CurrentDb.Execute "UPDATE T01_StaffMembers INNER JOIN (T01_Organization INNER JOIN (T01_Billets INNER JOIN T00_JunctionTable_OBS " & _
"ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) " & _
"ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk SET T00_JunctionTable_OBS.OrganizationIDfk = " & OrganizationID_New & " WHERE (((T00_JunctionTable_OBS.RecordIDpk)=" & RecordIDpk & "));"
'Update Record Modified Date-Time_Stamp
CurrentDb.Execute "UPDATE T00_JunctionTable_OBS SET T00_JunctionTable_OBS.Record_Modified_Date = Now() WHERE (((T00_JunctionTable_OBS.RecordIDpk)=" & RecordIDpk & "));"
'Throw message upon successful move
MsgBox Auto_Title0 & " has been successfully moved into the new organiation!", vbInformation, "Information"
'Requery current organization
Me.N_Code_Current.Requery
Me.OrgName_Current.Requery
Me.OrganizationID_Current.Requery
If Me.BIN_New.Value = Null Then
Msgbox "Do nothing (for testing only right now)!", vbInformation, "Information"
Else
MsgBox "Billet ID will be updated!", vbInformation, "Information"
End If
End If
Exit_cboMoveStaffMember_Click:
Exit Sub
cboMoveStaffMember_Click:
MsgBox Auto_Title0 & " was *NOT* moved into new organization!" & vbCrLf & vbCrLf & "Please select a new organization from the 'New N-Code' drop-down and then click on 'Move Staffmember'.", vbCritical, "Warning"
Resume Exit_cboMoveStaffMember_Click
End Sub
My question:
Now, in the above VBA, all works great except the IF/THEN/ELSE for the **Me.BIN_New.Value = Null**. Regardless whether or not I chose a value from the 2nd (optional) combo box, I always get the message "Billet ID will be updated!"
Why is it what I don't get the message box "Do nothing! if I did NOT select a value? Ultimately, I want to place the secondary update query into the Else statement but I don't want to update the records if nothing was selected.
Thank you,
EEH