We help IT Professionals succeed at work.

MS Access VBA Combo Box Issue

jonsuns7
jonsuns7 asked
on
Hi Eveyone,

I have a combo box on a form that opens up after the user clicks on a button in another form.
When the form opens up, the combo box is populated already with a name in the box. The values displayed for the combo box are  populated by way of a query.(In the record source property)

I don't want any names showing in the combo box when the form is first opened. I am having a brain lapse, I thought you could do that through a property. Am I wrong?
Comment
Watch Question

Commented:
The row selections for a combobox are usually handled in the RowSource property, and this is usually assigned in design time or as the form loads.

For the combobox to have no names when the form is first opened, make sure the combobox's RowSource property is clear and make sure nothing in the form load or open events are assigning this property.

However, something must populate the combobox at some point for it to be useful. ;-)

Lee
Consultant
CERTIFIED EXPERT
Commented:
It's not clear whether you (a) want the drop-down list to be empty, or (b) want the drop-down list populated but with no item currently selected.

For (a), set the RowSource of the combo box to a zero-length string:
    MyComboBox.RowSource = ""

For (b), set its value to Null:
    MyComboBox.Value = Null

Best regards,
Graham
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"Am I wrong?"
Unless you specifically have code that is somewhere - setting the combo to a value, then *nothing* should be showing when your form opens.  There s/b no need to mess with either the Row Source or setting the combo to Null.

Something else is going on ...

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Unless of course, the combo box is Bound to a field in the Form's RecordSource ... and a record is displaying when the Form opens ....

mx

Author

Commented:
Hi Eveyone,

I used GrahamMandeno's approach and it seemed to work.

Though I am curious about DatabaseMX's comment. The drop-down list is based on values from a parent table.

The form is used to track personnel participation at community events. What was happening was that everytime I opened up the form to enter a new record, one of the values, for one of the employees, would already show up in the combo box which is what I do not want.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"Though I am curious about DatabaseMX's comment. "
Right.  Something else is going on ... that s/b addressed.  Again, the suggested fixes should not be necessary,  Can you upload the db?

mx
Graham MandenoConsultant
CERTIFIED EXPERT

Commented:
Can you please post the following properties of the combo box:

- ControlSource

- DefaultValue

I'm guessing either the combo box has a default value, or it is unbound - or bound to the wrong field in the RecordSource.

Thanks,
Graham
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
yes ... could certainly have a default value.

mx

Author

Commented:
Control Source   =        EmpIDNum    (This is a column in the underlying table)

Default Value     =       Default Value is blank

Row Source     =     SELECT [LastName] & "," & [FirstName] AS Employee, dbo_ref_TBLEmployees.EmpID  FROM    dbo_ref_TBLEmployees;

Bound Column  =  2

Column Widths  =  3";1"


I hope this will maybe give some idea. Though the app works, I hope there is no unseen problem.

Commented:
If this combobox is used to select other records for display in the form, you need to empty the ControlSource property. It needs to be Unbound.

Lee
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Looks normal.  However, try this change for fun:

Row Source     =     SELECT dbo_ref_TBLEmployees.EmpID ,  [LastName] & "," & [FirstName] AS Employee  FROM    dbo_ref_TBLEmployees;


Bound Column  =  1

Column Widths  =  0;3

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"If this combobox is used to select other records for display in the form, you need to empty the ControlSource property. It needs to be Unbound."

Which is sort of what I was implying when I note :

"Unless of course, the combo box is Bound to a field in the Form's RecordSource ... and a record is displaying when the Form opens ...."

mx

Author

Commented:
It's peculiar, it seems like the default behavior should be to show nothing. I need this bound to a field because this is how we select the employee number that is ultimately stored in the table.

As I said Grahams approach earlier worked. This seems like a lot of work for something that should be second nature.

Author

Commented:
Furthermore, this form is mailnly used to input new records, not really for existing records.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"It's peculiar, it seems like the default behavior should be to show nothing."
Question ... when this form opens, is there are *existing* record showing?

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Also ... is there *any* code in the Form Open, Load or Current event(s) ?

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
"As I said Grahams approach earlier worked. "
Well .. sure, but ... it's covering up some other issue ...

mx
Graham MandenoConsultant
CERTIFIED EXPERT

Commented:
1. If you open the table and go to the bottom where you can enter a new record, do you see anything in the EmpIDNum column?
2. Does your form have any code for any of these event procedures:
    Form_Open
    Form_Load
    Form_Current?
    If so, please copy and post it.
3. You say the form "opens up after the user clicks on a button in another form".  Please post the code that opens the form.
--
Graham

Author

Commented:
Hi Graham,
Here is the code that opens the form:

Private Sub btn_AddEmpl_Click()
'Send value of Outreach Event ID and ProgramID to Global Variables

' Populate Global Variable   variable_global_LOBEventID

variable_global_LOBEventID = Me.LOB_ProgramID.Value

'Populate Global     variable_global_LOBEventID

variable_gbl_eventID = Me.EventID_Outreach

 DoCmd.Close acForm, "frm_EventDetails", acSaveYes

DoCmd.OpenForm "frm_Events_to_Employees"
'Set the global variables that will ultimatly be passed to the form that is now opening.

End Sub

Here is the code that executes when the form is loaded:

Private Sub Form_Load()

txt_EventID.Value = variable_gbl_eventID
Event_ProgramID.Value = variable_global_LOBEventID
'cmb_EmpIDNum

Dim EventNameRef As String

EventNameRef = DLookup("[Event_Name]", "DBO_Events", "EventID_Outreach =" & Me.txt_EventID.Value)

txt_EventNamed = EventNameRef

End Sub
Graham MandenoConsultant
CERTIFIED EXPERT

Commented:
What is the RecordSource for frm_Events_to_Employees?

If you are intending to add a new record (not edit existing ones) then open the form in data entry mode:

DoCmd.OpenForm "frm_Events_to_Employees", DataMode:=acFormAdd

-- GM
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:

Is this commented out and what is it?
'cmb_EmpIDNum

Author

Commented:
Thanks to both Graham and Database MX. I was able to get it to operate by setiing the data entry property to No.

I have to be candid, don't know exactly why that worked.

But many thanks to both of you. You are both brilliant.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.