Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Problem (Access2000)

    Hiya I've got a form which is based on a query. Im filtering this query using a combo box. What I want this query to do is display everything unless the user choses a particular name. What I've done was put all the names in the defaulf ("SFM_1" Or "SFM_2" Or "INTL_SHARES" Or "Deuville" Or "Classics" Or "O/S

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

    Re: Query Problem (Access2000)

    All that expression equates to is true or false, which is why your form appears empty. The -1 is the True value, but it doesn't match any values in the appropriate field, so your query doesn't return any records until you make a choice. Instead, change the default to the first item in the list by setting it to =[ComboName].ItemData(0), using your control name instead of ComboName. Or change the criteria in your query to handle a null value in the combobox.

    Refresh the form by doing a Me.Requery in the afterupdate event of the combobox.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (Access2000)

    Hi Charlotte,
    Will I be able to enter all the names in the first row of the combo box? I tried using Me.Requery but it gives me the following error message: Main Switchboard can't fine the macro"Me."

    Thank you.

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

    Re: Query Problem (Access2000)

    Where did you put the Me.Requery? You can't do this from a macro or from the properties sheet, only from code behind the form.

    You need to state explicitly what you are trying. It will save everyone time and confusion. If someone tells you to put something in the AfterUpdate event, they aren't normally referring to the item in the properties sheet, they're talking about the underlying event procedure. Most posts make it clear when suggesting that anything be put in the property sheet itself.
    <hr>Will I be able to enter all the names in the first row of the combo box? <hr>
    I don't understand the question. You can only enter a selection that's in the combobox's rowsource. What exactly are you doing or trying to do? You'll have to explain more before you can get an intelligent answer.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (Access2000)

    I did put the requery in the AfterUpdate event code. It wasn't working but I tried Refresh and it works so thats sorted.

    Will I be able to enter all the names in the first row of the combo box?

    By the above question What I meant was: I wanted to display all the records when I open the form at first and the user could select a name if they want to display records which contain only that particular name. I've got all the names in the default:
    "SFM_1" Or "SFM_2" Or "INTL_SHARES" Or "Deuville" Or "Classics" Or "O/S

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

    Re: Query Problem (Access2000)

    In the criteria line in your query, you probably have something like Forms!MyForm!MyCombo as the criteria under the relevant field. Change that to this (with the appropriate form and controls names inserted, of course)

    Like Nz(Forms!MyForm!MyCombo,"*")

    Also, the default value you have now for the combobox to Null or take it out entirely. That way it should come up empty when the form first opens.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (Access2000)

    This code shows all the records but when I select a name for example INTL_SHARE, it doesn't do anything. Im sure its refreshing the form after I select value from the combo.
    Any idea why this is happening?

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

    Re: Query Problem (Access2000)

    You need to requery the form, that's what forces the underlying query to requery and causes the form to display the appropriate records. You've never posted the code you're trying to use, so until you do, you aren't going to get a definitive answer.

    Refresh will *not* force the underlying query to update itself in this situation.
    Charlotte

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (Access2000)

    It works Charlotte,
    on the same form, I also have another option of chosing what I display by the date. Its a text box control, called Date. The Default of that text box is Date(), and the user can enter a date if they want to display the trades for another day, else it will display trades for that day.

    I have the following code in the AfterUpdate event of that feild:
    Private Sub Date_AfterUpdate()
    Me.Requery
    End Sub

    I have the following in the query criteria feild:
    [Forms]![DisplayTrades]![Date]

    The problem I'm having is that everytime I select a name in the combo box, it prompts me to enter the date, instead of using the default date. Why is this? And when I enter a date it fetches records for that date but displays the default date in the text box control.

    Thank you charlotte.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

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

    Re: Query Problem (Access2000)

    You're being prompted for a date because you have a criteria in your query that asks for date and you haven't supplied it. Default values are entered for new records. If the date control is unbound, which it would be if all you're doing is using it as criteria, then the default value doesn't apply.

    You could change your criteria line to this:

    Nz([Forms]![DisplayTrades]![Date], Date())

    That would automatically use the current date if none were entered. However, then you would also need a requery in the afterupdate event of the date textbox as well. Otherwise the recordset wouldn't change when they actually entered a date.

    When you actually enter a date it displays the default date? Where are you entering the date? If it's in the prompt, that has nothing to do with the date on your form--you're entering it directly into the criteria of your query.
    Charlotte

  11. #11
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (Access2000)

    Thanx Charlotte,
    It still is asking me to enter date when I first open the form. I do have a requery command in the AfterUpdate event property. As soon as I enter the date it askes me to enter the date again.

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

    Re: Query Problem (Access2000)

    Princess, why don't you create a database with just that form and query and whatever tables they require, strip out any proprietary information and just include enough records in the tables to display the problem. Compact that and zip it and attach it to a post. The date prompt is because it can't provide a recordset for the form until it has a date and the form hasn't actually loaded yet for it to reference the control.

    There are two ways to handle this. The usual way I avoid this is to load the form with an empty recordset and not set its recordsource until the necessary parameters have been entered. That's done in code, although it doesn't have to be complicated code.

    The other way is to drop the criteria out of the query and use the form's filter property instead to filter the recordset for the date you want. Here's an example of that kind of code:

    <pre> Dim strFilter As String
    strFilter = "[MyDate]=#" & [txtDate] & "#"
    Me.Filter = strFilter
    Me.FilterOn = True</pre>


    With the filter, you don't need to requery after the date is entered, you set the filter instead.
    Charlotte

  13. #13
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problem (Access2000)

    I fixed it.
    Thanx Charlotte.

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

    Re: Query Problem (Access2000)

    Did you compact the database? How big is it zipped? I'll only accept an email as a last resort because that takes at least part of the discussion out of the public forum, which is no help to other Loungers. You might also try running a <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=44732&page=& view=&sb=&o=&vc=1#Post44732>decompile</A> and then compacting it before you zip it.

    And if any of this information is proprietary, you should check with your company on what may be posted or sent out.
    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
  •