Write "Edit List Items.." data back to original form.

[ngahm]

Sorry. Nothing happens.

[ssanfu]

I use the "Not In List" event of the combo box. Look in help or "The Access Web" at http://access.mvps.org/access/forms/frm0015.htm.

If you use the not in list event, you can open a form that allows you to add data to more than one field. Then when you close the add data form, you can insert the PK value of the new entry into the combo box on the main form. Access takes care of refreshing the fields.

Seems a lot simpler that editing the combo box in form view. Just my 2 cents.......


(sorry aytee111, this went under the wrong post )

[aytee111]

In this database there is a table and a form. I have them both open. I add a new record to the table and when I click on the down arrow on the form the new record shows up. What I am trying to illustrate that the combo box has the new value every time, so when you close your second form and come back to this one and click the down arrow the new value will be there.

[ngahm]

Perhaps I was not clear. The new entry that I make into the form opened when I, 1.right click on the combobox entry line and then 2. go to "Edit List Items..." form, does indeed appear (and always has appeared) in the drop down list of the combobox of the original form when I go back to it. I can then go to it, click it and it is accepted as the valid entry for this record. What I am trying to do is to have this new record automatically appear in the entry field of the combobox upon exiting secondary data entry form.

[aytee111]

Oh my, I have certainly been on the wrong track! Yikes. I'm scared to say anything now!
I did this - I populated a value into the combo box when I closed form 2. I didn't use a real value, just a string. But you can put in your real vale: Forms!Form1!Combobox="123". Do you need multiple columns?

ssanfu - fee free to jump in any time - we're struggling here!

[ngahm]

My Combobox shows one field, the "description" (text) field. Not shown are: 1. the foreign-keyfrom the primary table of the first form, or 2. the unique primary key from the table used in the lookup form.

[aytee111]

I think you should go back to my idea of starting a new thread! Sorry if I have wasted your time.

Here is a very long way around:
- have a hidden value on form1
- on close of form 2 set it to the new value
- change the row source of the combo box with a WHERE to match that value and requery the combo box on mouse down
- onclick event change the row source back to what it was

[ngahm]

Here is an expression that I have placed in the OnClose line of the secondary form brought up from the combobox of the first form =[Forms]![frm_ModifyFilms]![FilmTypeNo]=[FilmType_Key]. "frm_ModifyFilms" is the first form; "FilmTypeNo" is the foreign key in this form; "FilmType_Key" is the primary key in the secondary form. This does not work. If I substitute a value (say 22) forthe "FilmType_Key", it still does not work.

[ngahm]

Sorry, my last overlapped with your last. I'll look at your latest suggestions. Perhaps you have some idea why the expression in my last does not work.

[aytee111]

Maybe because of the multiple values required in the combo box.

[ssanfu]

Maybe because of the multiple values required in the combo box.

Let's start with the combo box on the form [frm_ModifyFilms]. What is the row source type of [FilmTypeNo]... "Table/Query" or "Value List"?

What is the row source?

What do you mean by "multiple values required in the combo box"???

Is this a "Look up Field" in the table?

[ngahm]

Hello ssanfu:
Row source for FilmTypeNo is "FilmType_Lookup" which is a lookup query using a table consisting of just two fields; 1."FilmType_Key", which is an autonumber, and 2. "FilmType", which is a text field.

I am not certain what aytee111 was referring to as "multiple valuesrequired in the combobox".

[aytee111]

I don't know if anyone else is going to read this. I think it is time to start a new thread. Like I say, I don't use lookup fields on tables as they create background procedures and take over my forms!

[ngahm]

Thanks aytee111. It looks like ssanfu is working on it. I'll wait a while and, if nothing more happens, I'll consider starting a new thread. Again, thanks for all your effort.

[ssanfu]

I think you should be using the "Not in List" event. The "Edit List Items..." (AFAIK) is to edit a "Value List", not to add items to a "Table/Query".

In the property dialog box for the combo box, set the "Limit to List" property to "Yes". On the event tab, change the "On Not in List" to [Event Procedure], then click on the ellipsis (...) to open the code window.

Paste in the following, changing the blue words to your names:
(From "The ACCESS WEB" - http://access.mvps.org/access/forms/frm0015.htm)

' This code was originally written by Dev Ashish. 
' It is not to be altered or distributed, 
' except as part of an application. 
' You are free to use it in any application, 
' provided the copyright notice is left unchanged. 
' 
' Code Courtesy of 
' Dev Ashish 
'
'Private Sub Combo0_NotInList(NewData As String, Response As Integer)  '<<=do not add this line. It should already be added with your combo box name
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strMsg As String

   strMsg = "'" & NewData & "' is not an available Job Site Name " & vbCrLf & vbCrLf
   strMsg = strMsg & "Do you want to add the new Job Site ?"
   strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."

   If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Job Site?") = vbNo Then
      Response = acDataErrContinue
   Else
      Set db = CurrentDb
      Set rs = db.OpenRecordset("Job_Sites", dbOpenDynaset) 'look up table name
      On Error Resume Next
      rs.AddNew
      rs!FilmType = NewData  '<<= look up table text name
      rs.Update

      If Err Then
         MsgBox "An error occurred. Please try again."
         Response = acDataErrContinue
      Else
         Response = acDataErrAdded
      End If

   End If

   rs.Close
   Set rs = Nothing
   Set db = Nothing
'End Sub    '<<=do not add this line. It should already be added

Then enter a name that is not in the look up table. When you leave the combo box, it should ask you if you what to add the new value. At least that is what happens in A2K .