Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combobox requery (97)

    Hi, I have a form in datasheet view i am using for datainput. It has a Query ' Update' as its record source. This Query in turn has a parameter 'Protocode' which selects a set of records each time it is opened. One of the fields I am filling in is 'Author'.

    I have found that for each set of records the query brings up (up to 200) i am typing in the same authors name each time. I therefore sought to make a combobox that picked up all the names of author's i had already typed in the form. To get it to pick up any new authors i had typed i called the Requery method on the combobox on the Form_Current event. Unfortunatley this brought up the 'Protocode' parameter each time!

    To get around this i made an unbound textbox, and referenced this as the parameter from the query, and did the following code:

    Private Sub Form_Current()
    Me.Author.Requery
    End Sub

    Private Sub Form_Open(Cancel As Integer)
    Me.Text6 = InputBox("Protocol Code")
    Me.RecordSource = "Update"
    End Sub

    I had to remove the record source on the form as it was running the query with the textbox Null value before the form open event.

    This method works well, but cant help thinking its a bit of a 'bodge' !

    Any ideas?
    Thanks,

    pmatz

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

    Re: Combobox requery (97)

    The alternative would be to create a separate Authors table and use that as row source of the combo box. This would list ALL authors, however, not only the ones used in this session.

    If you want the user to be able to add new authors to the Authors table by typing a new name into the combo box, set the Limit to List property of the combo box to Yes, and write code in the On Not In List event to insert a new record in the Authors table. Doing a search for NotInList in this forum will bring up numerous examples.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combobox requery (97)

    Thanks,

    the NotInList method whould still require me to requery the combobox to update it though, wouldnt it? and the problem of the 'popping up parameter prompt' is still there!

    I thought about doing this:

    make a recordset clone of the current forms records.
    Do a SQL query on it - to extract authors.
    Use this as the source for the combobox.

    but its a lot of bother. perhaps an append query table as the source would be better, only appending new authors. this could then be the source for the combo.

    anyway, thanks for your help. i will post if i get a slick solution.
    Thanks,

    pmatz

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

    Re: Combobox requery (97)

    If you use the On Not In List event, Access takes care of updating the combo box for you. And in the solution I proposed, the combo box would have a table as row source, not a query, so the parameter prompt would not occur.
    You apparently prefer to start out with an empty list of authors. I don't know your situation, but in general I would prefer to be able to reuse names entered in earlier sessions. This reduces the probability of duplicate entries will small differences in spelling.

Posting Permissions

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