Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Combo box visual basic question (2000sr1a)

    Sorry, this may take a minute to explain.

    I have a subform for entering data about an individual. If enough geographical information is available, I want to assign the unique household ID to that individual. I am currently trying to do this through a combo box with code behind the subform exit button as follows:

    LIne1: If Not IsNull(Me!Household) Then
    Line2: Me!PropertyUID.RowSource = "SELECT [PropertyList].[Village Code], [PropertyList].[Hamlet Code], [PropertyList].[Group], [PropertyList].[Household], [PropertyList].[PropertyUID] FROM PropertyList WHERE ((([PropertyList].[Village Code])=[Forms]!" & DaddyForm.Name & "![Household Data New subform].[Form]![Village Code]) And (([PropertyList].[Hamlet Code])=[Forms]!" & DaddyForm.Name & "![Household Data New subform].[Form]![Hamlet]) And (([PropertyList].[Household])=[Forms]!" & DaddyForm.Name & "![Household Data New subform].[Form]![Household]));"
    Line3: Me!PropertyUID.Requery
    Line4: If Me!PropertyUID.ListCount<>1 Then MsgBox "Error Message" Else DaddyForm!PropertyUID=Me!PropertyUID
    Line5: EndIf

    I have 2 problems - the first is that on breaking at line 4, ListCount is persistently empty, the second is how to force PropertyUID to adopt the lone value in the list. I have set AutoExpand and LimitToList to True, and the BoundColumn is 5.

    How can I do this?

    I would welcome suggestions as to other ways to run this miniquery from VBA, as I may be being stupid.

    I must add that the reason for all the subform shenanigans (passing values from one form to another rather than merely populating my form with controls sourced on query(ies) based on the value of the village, hamlet and household fields) is that:

    1) The data is always very, very, very dirty, and this allows me to put certain mechanisms in place to clean it to at least a usable level, and at the same time send specific kinds of dirt back to the data collectors for them to clean.

    2) Such queries always seem to end up with non updateable recordsets - clearly no use for data entry.

    I should also add that the reason for DaddyForm rather than parent is that this is not a true subform sometimes, as it is used both for entering new data and editing data that has been entered through it.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo box visual basic question (2000sr1a)

    Try the SQL this way:

    Me!PropertyUID.RowSource = "SELECT [PropertyList].[Village Code], [PropertyList].[Hamlet Code], " _
    & "[PropertyList].[Group], [PropertyList].[Household], [PropertyList].[PropertyUID] " _
    & "FROM PropertyList WHERE ((([PropertyList].[Village Code])=" _
    & Forms( DaddyForm.Name)![Household Data New subform].[Form]![Village Code] _
    & ") And (([PropertyList].[Hamlet Code])=" _
    & Forms(DaddyForm.Name)![Household Data New subform].[Form]![Hamlet] _
    & ") And (([PropertyList].[Household])=" _
    & Forms(DaddyForm.Name)![Household Data New subform].[Form]![Household] & "));"

    As for assigning it, you can always use this:

    Me!PropertyUID = Me!PropertyUID.ItemData(0)

    In Access 2000, a lot of queries come back as not updateable until you set the unique record property to true, which you can do by using the DISTINCTROW predicate in the SELECT statement.
    Charlotte

Posting Permissions

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