Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2005
    Posts
    7

    Question Unanswered: DLookup's, Drop Boxes, Forms, and Confusion

    I'm very new to access, and I'm designing a contacts database for the company I work for. I have two combo boxes on the form - Country & StateOrProvince. In order to make data entry easier, I've managed to set it up so that Country is stored in a drop box which displays a list of all countries that have been entered so far, as well as letting you input new ones.

    I would like to set up a similar thing with the StateOrProvince dropbox. HOWEVER, I only want it to display values that have been entered for the particular country selected on the form - so, if you select USA on the Country dropbox, it would display a list of previously entered US states on the StateOrProvince box. If you selected China for the country, the StateOrProvince box would show previously entered Chinese provinces, and so on.

    I've tried to do this using DLookup, and I can't get it to work.

    SELECT DISTINCT [StateOrProvince] FROM Contacts WHERE Country=DLookup("Country","Contacts","ContactID=" & ContactID);

    I've tried many variations on the above, but I think my problem may be that DLookup looks at a value in a table rather than a value in a form.

    Does anyone know of any (relatively uncomplicated) way to do this?

    Cheers,
    Daniel

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try something like this in _AfterUpdate of the first combo

    dim strSQL as string
    strSQL = "SELECT tblStates.StateID, tblStates.StateOrProvince FROM tblStates "
    strSQL = strSQL & "WHERE tblStates.CountryID = " & Me!firstCombo & ";"
    secondCombo.rowsource = strSQL
    secondCombo.requery

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i worry a little about your tables having seen the way you are building the Dlookup(). methinks you should have something like:

    tblCountry:
    CountryID, auto, PK
    strCountry, text

    tblStates:
    StateID, auto, PK
    CountryID, FK on tblCountry
    strState, text

    tblXxxxxx
    XxxxxID, auto, PK
    StateID, FK on tblStates


    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Feb 2005
    Posts
    7
    i worry a little about your tables having seen the way you are building the Dlookup().
    Err, yep, I'm only using one table ('Contacts') which has all the data for each contact. I don't have a seperate table for countries or states or anything. I probably should have - I take it there's no easy way to do it without creating new tables?

  5. #5
    Join Date
    Feb 2005
    Posts
    7
    I've tried:

    dim strSQL as string
    strSQL = "SELECT Contacts.ContactID, Contacts.StateOrProvince FROM Contacts "
    strSQL = strSQL & "WHERE Contacts.Country = " & Me!Country & ";"
    StateOrProvince.rowsource = strSQL
    StateOrProvince.requery

    This didn't work - probably unsurprisingly

    My knowledge of VB or SQL is virtually zilch; I can usually vaguely follow the flow of code but this stuff has got me stumped. Basically I just want a piece of code that says:

    'Grab the StateOrProvince from all records where the value in the Country field is the same as the value in the Country combobox on the current form'.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Me!Country is text ??

    if yes, use:
    strSQL = strSQL & "WHERE Contacts.Country = '" & Me!Country & "';"

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Feb 2005
    Posts
    7
    Thanks - still doesn't work though - when I select the StateOrProvince combo box, it brings up a message: "Enter Parameter Value" with a text box for me to type it in. On the plus side, entering in a given country does result in it displaying the correct provinces, so that's a start!

  8. #8
    Join Date
    Feb 2005
    Posts
    7
    OK, that was happening because I still had an old SQL statement in the rowsource property of the StateOrProvince combo. I've fixed that - now, when I select the combo, it shows nothing.

  9. #9
    Join Date
    Feb 2005
    Posts
    7
    Ah, almost works now, after fiddling a bit (not sure what I did). Unfortunately the user has to reselect the country before it registers in the StateOrProvince box.

  10. #10
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    Maybe instead of Country = Me.country, you can use Country Like '*' & Me.Country & '*' so if country is blank, it won't filter.

  11. #11
    Join Date
    Feb 2005
    Posts
    7
    Working now. I probably did it in a fairly complicated way. Also set it up so that if country is blank, it won't filter, but I did this with nested ifs. It works - that's all that matters for now

    Thanks for the help

Posting Permissions

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