Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box Value as Criteria in a Query (2000)

    When I open the attached database, select an option in the combo box and click on the "Preview Report" button, I can't get the report to show the data relevant to the option chosen. I guess that's because the query which is the record source of the report doesn't recognize the combo box value put in its criteria. How can I solve this?
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Value as Criteria in a Query (2000)

    Either do this::
    Combo box: idlin bound column is 1 change it to 2

    or a better way (proper way)

    Change the field in your query

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo Box Value as Criteria in a Query (2000)

    Rupert is correct.
    However, if you want to keep linea as a field in your query then you will need to introduce another field in your query (IDLin from table linee) and move the criteria from linea to IDLin.
    If you don't want to see IDLin in your query simply clear the show box.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Along the Same Line...

    Thank you guys.
    I'd like to set to "No" the "Limit To List" property of the LineaID combo box you see at the top of the Varianti form that opens at startup in the attached database but a message pops up saying, "The first visible column, which is determined by the ColumnWidths property, isn't equal to the bound column. Adjust the ColumnWidths property first and then set the LimitToList property."
    What should I put in the ColumnWidths property box?
    Attached Files Attached Files

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

    Re: Along the Same Line...

    You can't set limit to list to No unless the bound column of the combobox is the first column. If you make LineaID the first column and set the columnwidths to 0" and the column count to 2, you would be able to turn off limit to list. However, then the value of the combobox would be LineaID, not the visible Linea and you would not gain any benefit since a list of unique values is still not updateable and so is effectively limited to list but without the ability to trap the event.

    I don't know exactly what you're trying to do here, Giorgio, but you're going to confuse Access by giving your main form and your subform control the same object names and by not renaming your controls to something other than the bound field names.
    Charlotte

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Along the Same Line...

    Thanks Charlotte.
    The Row Source for the LineaID combo box is the Linee table.
    I'd like to be able to enter an item in the combo box that's not in the list and as a consequence add the item to the Linee table too. How can I do it?

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

    Re: Along the Same Line...

    That's the best reason to turn the LimitToList property on! If LimitToList is set to Yes, then their typing in an item that isn't in the list will trigger the NotInList event. That's where you put the code to add the item to the table and then requery the combobox list to include the new item. This kind of code can be found in the help files and has been posted fairly often in this forum, so do a search on LimitToList for threads on the topic, and check out this post by HansV for some code.
    Charlotte

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Along the Same Line...

    Thanks again.
    When I select one of the options in the LineaID combo box as soon as the Varianti form opens on startup, the expand indicator on the Varianti1 subform(cf.picture) doesn't appear right away; I have to change the form to design view and then back again to form view to achieve that. Any way to fix that?
    Attached Images Attached Images

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

    Re: Along the Same Line...

    I don't have a clue as to what you're doing here, Giorgio. Or why you have and expander at all.
    Charlotte

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Along the Same Line...

    If you open the attached mdb(which is the same as the one previously posted on this same thread but I attach it here for your convenience) and select one of the options in the LineaID combo box as soon as the Varianti form opens on startup, the expand indicator column(the leftmost narrow column containing the plus/minus sign, cf. expand.jpg in the zip file) on the Varianti1 subform doesn't appear right away; I have to change the form to design view and then back again to form view to achieve that. The first thing that comes to mind to solve the problem is to shift the Varianti form's view (once the form is open) to design and then back to form view using VBA code in the LineaID combo box's AfterUpdate event but what VBA method allows you to do that?
    Attached Files Attached Files

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

    Re: Along the Same Line...

    I've looked at it, and I'm still not sure what you're doing. There's no code behind your forms, except for one requery line in the subform, so what are you using to cause the subform to requery after you select an item in the combobox? The only way I can see to get this to work is to force a requery on the subform immediately after you select an item in the combobox. That will bring up the appropriate record in the subform.

    In addition to the requery, you need to trigger the afterupdate event of the subform so that the expander will become visible. In the AfterUpdate event of the combobox on the parent form, put this:

    <pre>Private Sub LineaID_AfterUpdate()
    Me.Varianti.Requery
    Me.Varianti.SetFocus
    Me.Varianti.Form.ShowExpander
    End Sub</pre>


    Now put this code behind the subform:

    <pre>Public Sub ShowExpander()
    Me.Dirty = True
    DoCmd.RunCommand acCmdSaveRecord
    End Sub</pre>

    That will force the subform datasheet into a dirty condition and then force a save to the record without changing any data. The save will trigger the AfterUpdate event of the form, and the expander will appear.
    Charlotte

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    You really know how to make Access sing!

    Thanks Charlotte, you're great.
    I've implemented your advice and it works perfectly. This message is because I have a question of a conceptual nature regarding:
    <pre>Private Sub LineaID_AfterUpdate()
    Me.Varianti.Requery
    Me.Varianti.SetFocus
    Me.Varianti.Form.ShowExpander
    End Sub</pre>

    From where I stand, it looks like Varianti is redundant in the above code since Me refers to the form containing LineaID which is Varianti, yet for some misterious(to me) reasons it all goes awry if you remove Varianti and use:
    <pre>Private Sub LineaID_AfterUpdate()
    Me.Requery
    Me.SetFocus
    Me.Form.ShowExpander
    End Sub</pre>

    Can you please shed light?

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

    Re: You really know how to make Access sing!

    I mentioned in an earlier message that it isn't a good idea to name controls with the same name as the objects they represent. You're confusing yourself, I think, because of the naming. Your main form is called Varianti. However, the subform control on that form is also called Varianti, even though the subform itself is named Varianti1. So what the code is actually saying is to requery the subform control named Varianti and set the focus to it and call its ShowExpander method.

    It might confuse you less if you replaced the dot operators with bangs like this: Me!Varianti.Requery, etc. It would confuse *me* less if you would change the way you name objects. I would call the subform control something like fsbVarianti1 so you could see that the object represented a subform control based on the form Varianti1. Then code like Me.fsbVarianti1.Requery would be much clearer.
    Charlotte

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: You really know how to make Access sing!

    Gotcha <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    I hadn't realized what really counted was the name given to the subform control, not the name of the underlying form you see in the database window.

    I don't know if it can be of help to anyone but since expanding the subform by clicking on the expand indicator and then choosing a different item in the LineaID combo box gave me the attached message, I've had to add the following line to your code:
    <pre>Private Sub lineaID_AfterUpdate()
    Me.Varianti.Requery
    Me.Varianti.SetFocus
    <font color=red>Me!Varianti!Descrizione.SetFocus</font color=red>
    Me.Varianti.Form.ShowExpander
    End Sub</pre>

    (using <pre>Me.Varianti.Descrizione.SetFocus</pre>

    doesn't work, though I can't figure out why)
    Attached Images Attached Images

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

    Re: You really know how to make Access sing!

    Me.Varianti.Descrizione.SetFocus

    That presents an argument for NOT using dot operators, because Access sometimes gets confused between collections. In this case, it is looking for a Descrizione method of the subform and not finding it, but It should work if you make it:

    Me.Varianti.Form.Descrizione.SetFocus
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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