Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    False coding (Access 2000)

    In my form i am trying to make a list of the products depending on the number of the option box chosen ( called office).I get no error but the result are not true
    In my example the choise of the option box is 1 and the control is called items0.But the results are not true, all the figures are wrong
    For exampe
    product 13, items0 = 13, but in the form items0 are 0
    product 9 items0 = 182 but in the form items0 is 5




    Private Sub Form_Open(Cancel As Integer)
    Dim city As Integer
    city = Me![Office] - 1
    Dim StrSQL As String
    StrSQL = " SELECT products.*, products.items" & city & " FROM products WHERE (((products.items" & city & ")>0));"
    Me.RecordSource = StrSQL
    Me.Controls("items" & (Me.Office - 1)).Visible = True
    End Sub
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: False coding (Access 2000)

    The Form_Open event procedure only runs when the form is opened, not when the user selects an office. You should put the code in the After Update event of Office.

    I would use a different data design, with three tables:
    - A Products table with ProductID and the name of the product etc.
    - An Offices table with OfficeID and the name of the office etc.
    - A Sales table with ProductID, OfficeID and Items.

    Each record in the sales table contains the number of items for one product and one office. It would look like the picture below.
    Attached Images Attached Images
    • File Type: png x.png (2.1 KB, 0 views)

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: False coding (Access 2000)

    But the office is already selected. Its Default value is set to one, which means office 1 = items0. In the attashement i sent, in order to check, the SQL is set to :
    StrSQL = " SELECT Products.Productid, Products.items0 FROM Products WHERE (((Products.items0)>0))"

    And then it all works, and the form shows the right figures. it is only when i set the sql to :
    StrSQL = " SELECT products.*, products.items" & Afil & " FROM products WHERE (((products.items" & Afil & ")>0));"
    that i get the wrong figures. It follows that my error is somewhere is this last code. Is it true ?
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: False coding (Access 2000)

    Your SQL is incorrect and it makes no sense since there is no field named Afil.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: False coding (Access 2000)

    but i have declared it as an Integer ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: False coding (Access 2000)

    Sorry, my mistake. But this time, you have only one text box Items0, there are no text boxes Items1 etc.
    Please read my suggestion to change the table structure. You're making things difficult for yourself by using an incorrect table design.

Posting Permissions

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