MS Access + Form with a combobox and subForm + VBA + problem RRS feed

  • Question

  • I have a form, the source is a table named "customer", this form contains a combobox with an event of Update "After update" to switch to selected customer, for this phase everything works and the combo changes to the record what I want.

    I have another table "title" that contains titles of customers (1 to many relationship), this table is the source of a sub - form that I put on the form.

    The two forms are related and when I click on the button in the bottom to move me to next record on the parent form it displays all matching records in the subform.

    But when I try to choose the customer from thee  combobox, it does not work, it gives me the following error:

    Thank you in advance.


    Sunday, September 16, 2012 8:44 PM

All replies

  • Unfortunately as English is my only language, I cannot interpret the error message.

    However, need to know a little more. A copy of your After_Update event code for the ComboBox wil help.

    Is the ComboBox an unbound control? If not then it needs to be. You cannot just change the entire record by changing the value in a bound control.

    The following is a simple example of finding a record with the After_Update event of a combo box and using bookmark to update the display.

    The combobox control is unbound with RowSource set to the fldCustName in the table with the customer name.

    fldCustName is the name of the field in the table.

    cboCustName is the name of the combobox control.

    Note that in the find line of code chr(39) are a single quotes to enclose a string value to be found. Not used if the value to be found is numeric.

    Sub cboCusname_AfterUpdate()
        Dim RS As Object

        Set RS = Me.Recordset.Clone
        With RS
            .FindFirst "fldCustName = " & Chr(39) & Me!cboCustName & Chr(39)
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With

    End Sub

    Regards, OssieMac

    Monday, September 17, 2012 5:52 AM
  • thank's for replying
    this is the file



    Monday, September 17, 2012 12:57 PM
  • My apologies for not getting back to you sooner but I had to go out today and have only just got home and looked at this.

    I downloaded the file OK. I would never have found the problem without it.

    Open titre table in Design Mode and on the General tab of Field Properties set index on cust field to Yes (Duplicates OK).

    I'll stand correcting on this but I think that all fields that are used for linking tables should be indexed. Moving to the next record in the unindexed table is possibly not a problem if the associated linked table is indexed on the linked field. However, if the current record is changed on the table with the indexed linked field, the the problem exists attempting to find the record in the table with the unindexed linked field.

    Regards, OssieMac

    • Edited by OssieMac Tuesday, September 18, 2012 8:31 AM last paragraph explained better
    Tuesday, September 18, 2012 7:04 AM
  • I tested this but it  does not work


    Wednesday, September 19, 2012 4:31 PM
  • I tested this but it  does not work


    OK. You are so right. I downloaded a copy of your file again and as you say, setting the index did not fix the problem. This led me to believe that it might be corruption and somehow with what I did previously the corruption got fixed. However, I believe that I have now found a fix. Have download 3 copies from the site to different filenames ans they all worked with the following fix.

    I copied the form and pasted to a new form name and it worked fine.

    It is likely that you know how to copy the form but just in case to save delaying you again:-

    Close the form.

    Right click the form name and select Copy.

    Right click in the same area and select Paste (Can use default new name with Copy of)

     Right click the original form name and delete.

    Right click the copied form and select rename and rename to original name.

    Regards, OssieMac

    Thursday, September 20, 2012 12:10 AM