Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Requery - update combo boxes (Access 2003)

    Hi,

    I am tyring to use the 'requery' command to update a combo box. This is for users entering info on a address form who find the county they want is not currently in the drop down list - they click to open a form to enter new county and i'm trying to then auto update the combo box back on the address form so the info they need is there when they return to entering the address.

    I am using Forms![form name]![field name].requery. I have tried this in both the close button and the 'on insert' property of the county form but a runtime error is coming up as follows:

    "Runtime error 438 - object does not support this property or method"

    Can anyone help?

    Thanks.

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

    Re: Requery - update combo boxes (Access 2003)

    The best way to do this is to use the On Not In List event of the combo box; this will automatically requery the combo box when an item is added. If you do a search in this forum for NotInList, you will find several threads dealing with this, for example <post#=146637>post 146637</post#>.

    In your code example, you must use the name of the combo box control, not that of the underlying field (Requery is a method of a control, not of a field)

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery - update combo boxes (Access 2003)

    Thanks.

    I have now entered the following code into the 'on not in list' property:

    Private Sub BLTBL_City_Town_NotInList(NewData As String, Response As String)
    If MsgBox("bltbl city town " & NewData & " does not occur not in the list." & vbCrLf & _
    "Do you want to add this as a new city or town?", vbYesNo + vbQuestion) = vbYes Then
    Dim strSQL As String
    strSQL = "INSERT INTO bltbl city town (BLTBL city town) VALUES (" & Chr$(34) & NewData & Chr$(34) & ")"
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
    Else
    cbocitytown.Undo
    Response = acDataErrContinue
    End If
    End Sub

    However, the following error appears when i try to execute it:

    The expression On Not In List you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name.

    Any ideas?

    Not sure if i have entered the relevant table and field names in the correct format.

    Thanks.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery - update combo boxes (Access 2003)

    Check ALL the code in the form module to see if you have more then one

    Private Sub BLTBL_City_Town_NotInList(NewData As String, Response As String)

    If so delete or rename the other sub.
    Francois

  5. #5
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery - update combo boxes (Access 2003)

    No, I've checked and there's definitely only this one that begins with that.

    Anything else it could be?

    Thanks.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery - update combo boxes (Access 2003)

    According the name of the sub, the name of the combo is BLTBL_City_Town
    Private Sub BLTBL_City_Town_NotInList(NewData As String, Response As String)

    and then you use another name in the undo
    cbocitytown.Undo

    Is this the problem ?
    Francois

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery - update combo boxes (Access 2003)

    Another problem that you have is that you have spaces in your table name and field names. You have to surround them with squared brackets.
    strSQL = "INSERT INTO [bltbl city town] ([BLTBL city town]) VALUES (" & Chr$(34) & NewData & Chr$(34) & ")"
    Francois

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

    Re: Requery - update combo boxes (Access 2003)

    The second argument should be of type Integer, not String:

    Private Sub BLTBL_City_Town_NotInList(NewData As String, Response As Integer)

    Moreover, table and field names with spaces in them should be enclosed in square brackets, as indicated by Francois.

  9. #9
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery - update combo boxes (Access 2003)

    Thanks again.

    Have done all that and the message box question and adding to the list is now working. However, if i click 'no' in response to the question, the following error appears:

    Runtime error 424, Object required.

    How exactly should i enter the field name.undo? With square brackets, _ between each word or both?

    Another thing that happens is that if you click on an item in the list accidentally and then clear the combo box and enter new text, when the msg box asks you if you'd like to add to the list, the text it tries to add is the item from the list you clicked on first + the new text you entered, even though the combo box now only shows the new texted you typed. Is there any way of resolving this?

    Thank you.

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery - update combo boxes (Access 2003)

    You don't undo the field but the combo. Use:
    Me.BLTBL_City_Town.Undo
    Francois

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

    Re: Requery - update combo boxes (Access 2003)

    Use either BLTBL_City_Town.Undo or [BLTBL City Town].Undo - both should work.

    I don't experience the quirk you mention - NewData is just the text entered by the user.

Posting Permissions

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