Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Location
    Edinburgh, Scotland
    Posts
    17

    Unanswered: Queries in If Statements

    I'm trying to insert a query into an If Statement. The user will pick the make of the car & then the models of that make should appear in the model combo box. The code I'm using at the moment is

    If Make = "Toyota" Then
    Model = Toyota Query;
    End If

    I've also tried using

    If Make = "Toyota" Then
    Model = "Celica, MR2, Yaris"
    End If

    But this produces

    "Celica, MR2, Yaris" in the model combo box and not a drop down list.

    If this worked I could use it instead of a query. It would just take a lot longer.

    It's probably just how I'm writing it, but I hope someone can help me on this.

  2. #2
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    I'm not sure but it could be the name of your query (Toyota Query) having a space in it. Try renaming it to something like qryToyota or ToyotaQuery and use the same code.


    Eventually I'll get one of these right.....

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Model is a combo?
    Toyota Query is a saved query?

    101% agree kccpo's recommendation: eliminate all spaces in names-of-things. "Toyota Query" is a bad choice of name, but "ToyotaQuery" or "qryToyota" is fine, "Toyota_Query" is ugly but tolerable. spaces in names-of-things eventually bite you in the bum.

    next, check out the .rowsource property of a combo.
    Model.rowsource = "qryToyota"
    will point the combo at your (renamed) query. followed maybe by a
    Model.requery
    to update the combo contents.



    by the way, this is not a great way to do things -- you will end up with dozens of saved queries for Ford, Volkwagen, Ferrari, Mercedes, BMW, etc etc ...not at all pretty, and when you add Bugatti, Fiat, Lancia you will have to make yet another three queries which is also very boring to do.

    Make should be in a combo (it probably is already).
    then you use the Make selected in the first combo as criteria for a second (one only, for all Makes) query that feeds the second Model combo.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    Just to be sure, your tables should be set up as 1:N (Make to Model).
    Your 'models' combo box (cboModel) should be based on a query of the Model table:
    MakeID (FK) = [Forms]![MyForm]![cboMake]
    ModelName
    On the AfterUpdate event of cboMake, requery cboModel.
    I guess what i'm sayin' is, you don't necessarily need an IF THEN.
    Have you already tried this way?
    w

  5. #5
    Join Date
    Oct 2004
    Location
    Edinburgh, Scotland
    Posts
    17
    Thanks guys that's really helpful.

    Make & Model are in combo boxes, but I already have a separate table of all makes & models. Is there a way I can connect this table straight to my form without having to make all the queries? Now I've thought about it I can see it's going to take forever doing it that way!

  6. #6
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    you said:
    "...I already have a separate table of all makes & models. Is there a way I can connect this table straight to my form..."
    do you mean you have *two* tables of Make and Model? you should.

    if you are going to work with more than one table you have to use a query (you can use a wizard but it builds a query in the background anyway). that's kind of the point of any database (RDMS) - you store things separately and put things together with queries. you can't get around it.

    and you only need (at least to start) two queries: one for the Make combo and one for the Model combo.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    D O N ' T "make all the queries".

    you need precisely one query that takes the Make from your combo as a criteria.

    izy

    LATER: just saw your post wazz: ok two queries if you include the one needed to drive the Make combo
    currently using SS 2008R2

  8. #8
    Join Date
    Oct 2004
    Location
    Edinburgh, Scotland
    Posts
    17

    Got it!!

    Finally got it to work! Here's what I did in case anyone else has the same problem:

    1.Make 2 tables:

    tblMake
    Make (list Makes)

    tblModel
    Model (list all models)
    Make (list all make of model)



    2.Make 2 quereis

    QryMake
    SELECT [tblMake].[Make]
    FROM tblMake
    ORDER BY [tblMake].[Make];

    QryModel
    SELECT [tblModel].[Model]
    FROM tblModel
    WHERE [tblModel].[Make]=[FORMS]![Add New Vehicle]![cboMake]
    ORDER BY [tblModel.Model];



    3.On a form place 2 combo boxes

    cboModel
    Rowsource: QryMake

    cboModel
    Rowsource: QryModel

  9. #9
    Join Date
    Oct 2004
    Location
    Edinburgh, Scotland
    Posts
    17

    Adding new data

    My next problem is how do I add new makes.

    I can add new models ok as long as the make already exists.

    I'm not sure how to add new makes though. It probably doesn't help that I have Make in 2 tables (tblMake & tblModel) and these aren't linked in any way.

    I can add a new make and model to tblModel, but I need the new make to also be saved in tblMake.
    Last edited by longIT; 10-13-04 at 08:00.

Posting Permissions

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