Link to home
Create AccountLog in
Avatar of Taras
TarasFlag for Canada

asked on

MS Access Combo box Requery not populating combo box

On Main form frmMain I have combo box cboSearchStudent , some fields from tblStudent and two  buttons “Filter” and button “Clear” and two subforms.

Combo box Row Source type is Table/Query

Row Source = “Select StudentID, Student_Name from tblStudent”

Combo box is setup to search as you type.

When I select a student from combo box and click on “Filter” button form frmMain  is populated properly.  If I want another selection I  click button  “Clear”  to remove selection or click on drop down arrow on combo box  and select another student. This is working ok.

Problem happens when I am typing student name in combo box and name is found then I select name and click on button “Filter”. Filter is working and form is populated properly, however when I click now on dropdown arrow in combo box to select new Student it will not give me list of student only shows in drop down that selected student. If I click on “Clear” button  it will clear selected student from combo box but not from drop down list it stay there.

Not sure why Combo box will not requery and show items from Row Source. Here is code from my “Clear” button:

 

  Me.cboSearchStudent.SetFocus

  Me.cboSearchStudent.Text = ""

  Me.cboSearchStudent.RowSource = ""

  Me.cboSearchStudent.RowSource = "SELECT DISTINCT tbl.Student.Student_ID,    tblStudent.Student_Name FROM tblStudent; "

  Me.cboSearchStudent.Requery

   Any idea?

Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Try just this:

Private Sub cmd_ClearFilter_Click
    me.cboSearchStudent.RowSource = "SELECT DISTINCT tbl.Student.Student_ID,    tblStudent.Student_Name FROM tblStudent; "
    me.cboSearchStudent = NULL
End sub

Open in new window

Resetting the rowsource already implements the requery, so no need for that line.
I prefer to work with the Value property of lists and combo boxes, instead of the .Text property because .Value is the default in Access and the only time you can refer to the .Text property of a control is when it has the focus.


Avatar of Taras

ASKER

Thank you  Dale,  tried it  and it is the same. It clear selection but drop down still show that only one selection and combo box row source is not required?
Can you post a screen shot of the form you are working on, in design view, so that I can see all of the controls?
ASKER CERTIFIED SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi,

this code you could use i.e. in the Form_Load event to be executed only once.

In the code of the "Clear" button you only need:
Me.cboSearchStudent = Null

Open in new window


And if you bind the combobox with the RowSource property directly to the qrySearchStudent, then you also would not need the rest of the code, also not in Form_Load.

(On the other hand you would need additional code to remove a Filter from the form.)

Cheers,

Christian