Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: Forms and Controls

    Hello out there,

    I want to make a form where where one control determines what happens in another control. Specifically, I created a drop down box in which i want the user to seleect a field. Once the field is selected, i want a list box to display the names of people where the previously selected field is Null. I want to know if there is way of doing this wihtout using a call back funciton or arrays. This is what i tried, it makes sense but i think there are syntaxt problems.

    Private Sub MissingField_DblClick(Cancel As Integer)

    Dim item As Variant

    item = Forms!MissingItems!MissingField

    Me!List6.RowSourceType = "Table/Query"
    Me!List6.RowSource = "SELECT tbl_Contact.first_name, tbl_Contact.last_name" & _
    "FROM tbl_Contact" & _
    "WHERE IsNull(tbl_Contact.item)" & _
    "ORDER BY tbl_Contact.first_name;"

    End Sub

    Any help is much appreciated

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

    Re: Forms and Controls

    Originally posted by dannypolo
    Hello out there,

    I want to make a form where where one control determines what happens in another control. Specifically, I created a drop down box in which i want the user to seleect a field. Once the field is selected, i want a list box to display the names of people where the previously selected field is Null. I want to know if there is way of doing this wihtout using a call back funciton or arrays. This is what i tried, it makes sense but i think there are syntaxt problems.

    Private Sub MissingField_DblClick(Cancel As Integer)

    Dim item As Variant

    item = Forms!MissingItems!MissingField

    Me!List6.RowSourceType = "Table/Query"
    Me!List6.RowSource = "SELECT tbl_Contact.first_name, tbl_Contact.last_name" & _
    "FROM tbl_Contact" & _
    "WHERE IsNull(tbl_Contact.item)" & _
    "ORDER BY tbl_Contact.first_name;"

    End Sub

    Any help is much appreciated
    Put spaces before the " , I mean: Select tbl_Contact.first_name, tbl_Contact.last_name " &

    The sintax of IsNull:

    IsNull(tbl_Contact.item) = TRUE
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Forms and Controls

    Originally posted by dannypolo
    Hello out there,

    I want to make a form where where one control determines what happens in another control. Specifically, I created a drop down box in which i want the user to seleect a field. Once the field is selected, i want a list box to display the names of people where the previously selected field is Null. I want to know if there is way of doing this wihtout using a call back funciton or arrays. This is what i tried, it makes sense but i think there are syntaxt problems.

    Private Sub MissingField_DblClick(Cancel As Integer)

    Dim item As Variant

    item = Forms!MissingItems!MissingField

    Me!List6.RowSourceType = "Table/Query"
    Me!List6.RowSource = "SELECT tbl_Contact.first_name, tbl_Contact.last_name" & _
    "FROM tbl_Contact" & _
    "WHERE IsNull(tbl_Contact.item)" & _
    "ORDER BY tbl_Contact.first_name;"

    End Sub

    Any help is much appreciated

    You might want to put some spaces in between words first and I would also reword the Where clause to "Where [tbl_contact].[item] Is Null;"
    Declare a variable such as strSQL to store the SQL in first and then use a MsgBox function or the debug window to display the SQL string before using it to see how it looks. You can usually spot missing spaces and quote marks this way. You can also create it in the query design grid and specify a specific criterea to see how a working model looks.

    Me!List6.RowSourceType = "Table/Query"
    Me!List6.RowSource = "SELECT tbl_Contact.first_name, tbl_Contact.last_name " & _
    "FROM tbl_Contact " & _
    "WHERE IsNull(tbl_Contact.item) " & _
    "ORDER BY tbl_Contact.first_name;"

    Gregg

  4. #4
    Join Date
    Nov 2003
    Posts
    16
    Thanks for your help.

    Im tryingt to fill the list box with this code, but it doesnt seem to work. It keeps giving me a pop up asking me to enter a parameter value. I tried another approach using a SELECT statement to identify what the user has chosen and then simply using that value as the WHERE condition. For instance, if telephone is chosen then WHERE IsNull(tbl_Contact.telephone) is the condition however this doesnt fill the box, from VB but when i copy the SQL in the design view it works. What the hell is going on!

  5. #5
    Join Date
    Nov 2003
    Posts
    16
    Thanks guys i got it this is what i used:

    Private Sub MissingField_DblClick(Cancel As Integer)

    Dim item As Variant

    item = Forms!MissingItems!MissingField

    Select Case item

    Case "téléphone"

    Me!List6.RowSourceType = "Table/Query"
    Me!List6.RowSource = "SELECT tbl_Contact.first_name, tbl_Contact.last_name " & _
    "FROM tbl_Contact " & _
    "WHERE IsNull(tbl_contact.téléphone)=TRUE " & _
    "ORDER BY tbl_Contact.first_name; "

    Case "FAX"

    Me!List6.RowSourceType = "Table/Query"
    Me!List6.RowSource = "SELECT tbl_Contact.first_name, tbl_Contact.last_name " & _
    "FROM tbl_Contact " & _
    "WHERE IsNull(tbl_contact.FAX)=TRUE " & _
    "ORDER BY tbl_Contact.first_name; "

    End Select

    End Sub

    Thanks for your 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
  •