Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2004
    Posts
    29

    Unanswered: Lookup combobox using a query

    Hi,

    I have a table that uses a combobox lookup field that is attached to a query. This query returns the types of services available for a company.

    When I put this combo field in the form, the data is available in it however I cannot select it...

    I don't know why this is going on...maybe because It is due to the fact that the table that contains the lookup field does not have a relationship with the underlying table of the query?

    Thanks

    Duaner

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    your problem sounds like your form's reacordset opens readonly, or your combobox' locked property is set to Yes.
    ghozy.

  3. #3
    Join Date
    Sep 2004
    Posts
    29
    No my control is not read only...

    Also I have other controls setup in the same fashion with other queries and they work correctly. Just this one...and it is the last one too...

    Thanks

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    hrm another posiblity is combobox' bound column data type doesn't match with form's field's datatype? if it is not could you attach your database here. so I could take a look.
    ghozy.

  5. #5
    Join Date
    Sep 2004
    Posts
    29
    Hi,

    Hmmm...this is weird! At the table level (DOSSIER table), the combo box and query works fine I see the data and I can select it.

    This is the SQL being used for the lookup field for the table:

    SELECT Service.Code_service, Service.Description
    FROM Service;

    A simple query...

    Now for the form...This is the query that is built for all the fields that are in that form:

    SELECT Dossier.Id_Dossier, Dossier.Id_Intervenant, Dossier.No_réquisition, Dossier.Date_référence, Dossier.Date_assigné, Dossier.Date_fermeture, Client.Code_SFP, Client.Suffixe, Client.Nom, Client.Prénom, Client.DDN, Client.Sexe, Client.Adresse1, Client.Adresse2, Client.Code_postal, Client.Province, Client.Tel_res, Client.Tel_trav, Client.Courriel, Client.NAS, Client.Assurance_maladie, Client.Commentaires, Référent.Id_agent, Référent.Référent, Référent.Code_facture, Dossier.Dossier_détruit, Dossier.Id_Service
    FROM Référent INNER JOIN (Client INNER JOIN Dossier ON Client.Code_SFP = Dossier.Code_SFP) ON Référent.Id_référent = Dossier.Id_Référent;

    The lookup field in question is the last one from the SELECT statement (Dossier.Id_Service). This field is the one that works correctly at the table level however on the form I can see the data but can't select it.

    As per my previous post I have other lookup fields on that form such as (Dossier.ID_intervenant) which is part of the same table as my problematic combo box. The only difference between the two is that Dossier.ID_intervenant parent table (INTERVENANT) as a 1 to many relationship established with the DOSSIER table. The Dossier.Id_service field uses a query on the SERVICE table which does not have a relationship with the DOSSIER table.

    Back at you

    Thanks

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    on a copy of your database, try to convert your combobox to textbox, then see if you can edit it with a textbox.
    ghozy.

  7. #7
    Join Date
    Sep 2004
    Posts
    29
    Tried it and cannot edit the textbox control either.


    Maybe it is in Readonly...but why would it be at the form/query level and not at the table level??

    I have attached a copy of the DB. The Table name is DOSSIER, the quey is qryCodeService the form is RÉFÉRENCES the combo box in the form is Id_service

    Attached Files Attached Files
    Last edited by duaner; 10-16-04 at 13:14.

  8. #8
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I'm looking into it now. it is not about combobox, it is about your query structure. I will give my advice after I finish revieving whole thing.
    ghozy.

  9. #9
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    allright. I found it. in your query you should change field;
    Client.Code_SFP to Dossier.Code_SFP
    and
    add Dossier.Id_Référent field into query. dossier table was showing up Read-only because you didn't include foreign keys in your query and query didn't know how to fill them up.
    ghozy.

  10. #10
    Join Date
    Sep 2004
    Posts
    29

    Cool

    Hi Ghozy,

    I was away for a while...now I am back to the grind!

    OK I have read your last post. It gave me an idea...the form that I was working with did not contain any subforms it was all based on the Dossier_ID.

    So I went and tested different forms/subforms for the client information. All I am trying to do with these forms is input all of the client information in the client form and all pertinent information for is consultation.

    So after creating a form called "Références2" and a sub form "Références_details" I still get the same problem with the service field. At the table level (Dossier) it works fine but at the form it does not.

    I am attaching printscreen of the DB structure, let me know what you think.

    I have made some modifications to the service field they are now called Code_service instead of ID_service.

    Thanks

    Duaner

    Attached Files Attached Files

  11. #11
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    Hi Duaner,
    sorry I'm just about to leave the toen for a week. so I won't be able to answer your problem at the moment. probably next week.
    I'm sorry.
    ghozy.

  12. #12
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Morning duaner,

    Let me start by saying Welcome to the Forum as I see you haven't been here too long. Good to have you.
    Now, for your DB problem I d/l it and tried to figure out just what was going on all in it but couldn't. However, I think you had a problem with the ComboBox in the References form? The one labeled Code de Service
    If so then I fixed that part of the program for you as it now pulls in the data. At first, as you stated it would drop-down and show you the choices, but not let you select any. As for how all your other forms are to work I wasn't sure because I didn't know what one thing had to do with the other. Not really sure but some of the TableRelationships might be not so right, but that's a guess. If I had more time I would try to work everything out for you but time is not such a luxury for me. But will try to work on it off and on and check to see occassionally if you have it all working. Anyway, just wanted to try to lend a hand.

    have a nice one,
    Bud

    Silly me, I forgot to attach it back here for you.....geez..must be late. Well here goes......WELL, guess I will try later today after about 5 mins. it still didn't upload. Keep looking......
    Last edited by Bud; 10-24-04 at 05:33.

  13. #13
    Join Date
    Sep 2004
    Posts
    29

    Unhappy Desperatly seeking solution :)

    Hi Bud or Ghozy,

    This form issue is a pain in the neck...

    My problem with the CODE_SERVICE field is resolved and I still don't know what was the problem?? I have not changed anything to the queries that I was using for that field, it just started behaving like it was supposed to.

    I have another field (ID_INTERVENANT) in the same table (RÉFÉRENCES_DÉTAILS) that does the same weird action at the form level. I see the choices from the combo box but can't select them just like if the field was locked but it's not. The source for that combobox is a query that is built like so:

    SELECT Intervenant.Id_intervenant, Intervenant.Prénom & " " & Intervenant.Nom & ", " & Intervenant.Fonction AS Intervenant
    FROM Intervenant;

    The CODE_SERVICE field uses a similar query:

    SELECT Service.Code_service, Service.Description
    FROM Service;

    The only difference is that the first query concatenates three fields into one for the field INTERVENANT.

    This is nuts...both field at the table level are built in the same fashion and they both work correctly but at the form level the ID_Intervenant combobox does not work!

    Duaner

  14. #14
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    as I said before your problem is not the the query in the combobox' control source. it is about the query of your form. The Query of your form returns readonly results for some specific fields. which stops combobox to change that field. please check your form's query.
    ghozy.

  15. #15
    Join Date
    Sep 2004
    Posts
    29
    Hi Ghozy,

    Exactly!

    I have spent a lot of hours looking at that F?????? (?=any letter of your choosing ) form query and it seems alright to me.

    I have found the following article http://www.mvps.org/access/lookupfields.htm

    I am going to try that this evening. I'll remove all lookup query from the table level and define all fields to characters and then use the lookup on the form only. Not both table and form. I think that is why my lookups fields were acting strangely.

    Will let you know how it turns out.

    Duaner

Posting Permissions

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