Results 1 to 14 of 14

Thread: combo box

  1. #1
    Join Date
    Oct 2003
    Location
    Finland
    Posts
    80

    Unanswered: combo box

    I need to add a value to combo box. The box gets it's values from a query, and it's used in a pop-up form that runs a query.

    I need to add a value to the combo box, which returns all the values (the box holds names of schools, and when you choose one, it's impossible to change it back to searching all the records [I have code that checks the fields, and if they're empty it uses * in them].

  2. #2
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91

    Re: combo box

    Originally posted by Sham81
    I need to add a value to combo box. The box gets it's values from a query, and it's used in a pop-up form that runs a query.

    I need to add a value to the combo box, which returns all the values (the box holds names of schools, and when you choose one, it's impossible to change it back to searching all the records [I have code that checks the fields, and if they're empty it uses * in them].
    After adding the new value, have you issued a requery ?

    I mean:

    me.YourComoboName.requery
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  3. #3
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: combo box

    Originally posted by Sham81
    I need to add a value to combo box. The box gets it's values from a query, and it's used in a pop-up form that runs a query.

    I need to add a value to the combo box, which returns all the values (the box holds names of schools, and when you choose one, it's impossible to change it back to searching all the records [I have code that checks the fields, and if they're empty it uses * in them].
    You seem to be asking 2 questions. If you are trying to add a record to a combo box you shuold use an INSERT INTO SQL statement to add a record to the underlying table and then requery the combo box - Me("MyComboBox").Requery

    If you are trying to turn the filter off the form there are a number ways of doing this depending on how you have filtered the form. You could try :

    1. Me.FilterOn = False - this turns the form filter off
    2. reset the recordsource of the form by using Me.RecordSource = MySQL_Table_Or_Query

  4. #4
    Join Date
    Oct 2003
    Location
    Finland
    Posts
    80
    Thank you for suggestions!

    What I need is a all-selection in the combo box, which could search all the records. I need it because once user has pressed f. ex. School1, he can't change it back to empty value. I used IsNull to check for empty fields and if it finds one it changes the value to "*", this code is in the search-button OnClick-event.

    Btw, is it possible to check the empty fields in query. I'm not familiar with SQL, and I don't know how it works.

  5. #5
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by Sham81
    Thank you for suggestions!

    What I need is a all-selection in the combo box, which could search all the records. I need it because once user has pressed f. ex. School1, he can't change it back to empty value. I used IsNull to check for empty fields and if it finds one it changes the value to "*", this code is in the search-button OnClick-event.

    Btw, is it possible to check the empty fields in query. I'm not familiar with SQL, and I don't know how it works.
    To get a 'Select All' option in a combo box you need to use a Union SQL query. It would look something like:

    SELECT 0, "Select All Schools" FROM MySchoolTable
    UNION
    SELECT ID, [MySchoolFieldName] FROM MySchoolTable

    Save this as a query and use it as the RowSource for the combo box. Then use the AfterUpdate Event of the combo box to requery the form eg

    Private Sub cboMyComboBox_AfterUpdate()
    If Me("cboMyComboBox").Value = 0 Then
    Me.FilterOn = False
    Else
    Me.Filter = "SchoolName = '" & Me("cboMyComboBox").Column(1) & "'"
    Me.FilterOn = True
    End If
    End Sub

  6. #6
    Join Date
    Oct 2003
    Location
    Finland
    Posts
    80
    Thank you justin... I'll try it now...

  7. #7
    Join Date
    Oct 2003
    Location
    Finland
    Posts
    80
    When I press the combo box it asks for ID. There must something wrong in the query. I double-checked all the field and table names and they should be ok.

  8. #8
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by Sham81
    When I press the combo box it asks for ID. There must something wrong in the query. I double-checked all the field and table names and they should be ok.
    What is the SQL that you are using for the combo box?

  9. #9
    Join Date
    Oct 2003
    Location
    Finland
    Posts
    80
    SQL for the combo box, all the names are in finnish so ask if there's something you don't understand.

    SELECT Opiskelijat.Etunimi, Opiskelijat.Sukunimi, Opiskelijat.Titteli, Opiskelijat.Koulutusohjelma, Opiskelijat.txtKuvaOsoite
    FROM Opiskelijat
    WHERE (((Opiskelijat.Etunimi) Like CStr([Forms]![Popup]![Etunimi_popup]) & "*") AND ((Opiskelijat.Sukunimi) Like CStr([Forms]![Popup]![Sukunimi_popup]) & "*") AND ((Opiskelijat.Koulutusohjelma) Like CStr([Forms]![Popup]![Koulutusohjelma_popup]) & "*"))
    ORDER BY Opiskelijat.Sukunimi;

  10. #10
    Join Date
    Oct 2003
    Location
    Finland
    Posts
    80
    whoops... I pasted the wrong one... here's the right SQL

    SELECT DISTINCT Opiskelijat.Koulutusohjelma
    FROM Opiskelijat
    ORDER BY Opiskelijat.Koulutusohjelma;

  11. #11
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by Sham81
    whoops... I pasted the wrong one... here's the right SQL

    SELECT DISTINCT Opiskelijat.Koulutusohjelma
    FROM Opiskelijat
    ORDER BY Opiskelijat.Koulutusohjelma;
    Is Opiskelijat the name of the saved query that has the UNION SQL in it? If it is send me the UNION SQL that you are using instead. This query as it stands is a valid query

  12. #12
    Join Date
    Oct 2003
    Location
    Finland
    Posts
    80
    No it isn't... Opiskelijat is the name of the table where the schools column is located. That must be the problem.

  13. #13
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by Sham81
    No it isn't... Opiskelijat is the name of the table where the schools column is located. That must be the problem.
    To test the SQL, copy and paste into into a new query (you will need to use the SQL view of the query). Run the query - if it returns records then the SQL is fine.

    The problem may lie with the combo box - is there any code attached to the combo box? If there is disable it first then run the form and test the combo box.

    If this works then step through the code as you run the form and see where it asks for the ID

  14. #14
    Join Date
    Oct 2003
    Location
    Finland
    Posts
    80
    Combo box has code for two buttons (search, close), nothing else. I may have made some mistake somewhere... I'll try to figure it out

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •