best way to update a combo box after adding a new record

[Sym]

I have lots of combo boxes in my forms that are mostly limit to list as I want the people entering the data to actually add the full details of a client or supplier or whatever instead of just typing the information in over and over again. if the person/client/supplier is not in that list I have a button that will pop up a form so they can add a full new record but I need it so when they add the new record it will show up in the combo box in the intial form once it has been saved and closed without having to also close that initial form and reopen it or manually refreshing it.

right now I am using an if statement on the save button on my popup form that looks at what form is open and if that form is open then it refreshes that form after the save and closes which works fine but adding this to every form and combo box combination is very tedious, so I thought I would ask here, what is the best way to update combo boxes after and new record has been added via another popup form? there is probably a much simpler way than than the one im using.

 

[spikepl]

Use the not in list event and teh appropriate parameters (look it up in the docs) the property where you can specify which form to open to update the combo data.

Also, use uppercase, spacing, punctuation and SHORT sentences to make your input readable and comprehensible.

 

[Solo712]

I have lots of combo boxes in my forms that are mostly limit to list as I want the people entering the data to actually add the full details of a client or supplier or whatever instead of just typing the information in over and over again. if the person/client/supplier is not in that list I have a button that will pop up a form so they can add a full new record but I need it so when they add the new record it will show up in the combo box in the intial form once it has been saved and closed without having to also close that initial form and reopen it or manually refreshing it.

right now I am using an if statement on the save button on my popup form that looks at what form is open and if that form is open then it refreshes that form after the save and closes which works fine but adding this to every form and combo box combination is very tedious, so I thought I would ask here, what is the best way to update combo boxes after and new record has been added via another popup form? there is probably a much simpler way than than the one im using.

I have developed a method of passing the value lists among combos on different forms, where the edits of one is shared among all the others. Basically, after each update the row source string is exported as a text file and in each on_current event it is (re-)imported. For editing, I let the user use the Access combo editor setting (ie. not limiting the list). The advantage over the NotOnList method(s) apart from the visibility across forms is that you can edit the Access supplied list and delete from it, not just add to it. It's simple and efficient. I am not using this on items that are critical from the integrity perspective so I am not worried, but you would need to put some additional code if you want it bulletproof in a multiuser setting.

Here are the two routines exporting and importing the source string. You will notice that the transfers are byte-by-byte because it does not work at the string level.

Public Sub ExportValueList(mtct As String, fname As String)
     '
     'mtct holds the Rowsource string of the control
     'fname is the name of the destination text file
     '
     Dim iFile As Integer, llen As Integer, oo As Integer
     Dim sFileName As String
 
     sFileName = Application.CurrentProject.path & "\" & fname & ".txt"
      iFile = FreeFile
      llen = Len(mtct)
      Open sFileName For Output As iFile
          Write #iFile, llen
          For oo = 1 To llen
              Write #1, Asc(mID$(mtct, oo, 1))
          Next oo
      Close iFile
End Sub
' ---------------------------------------------------------------------------------
Public Function ImportValueList(fname As String) As String
    '
    '  function returns the Rowsource string if it was previously saved
    '  in the fname file
    '
    Dim iFile As Integer, flen As Long, oo As Integer
    Dim sFileName As String, ss As String, M As Byte
    ss = ""
    sFileName = Application.CurrentProject.path & "\" & fname & ".txt"
    flen = FileLen(sFileName)
     iFile = FreeFile
    If Dir(sFileName) <> "" Then
       Open sFileName For Input As iFile
           Input #iFile, flen
          For oo = 1 To flen
             Input #iFile, M
             ss = ss + Chr(M)
          Next oo
       Close iFile
    End If
    ImportValueList = ss
End Function

Best,
Jiri