Pass value from form to calling form (1 Viewer)

[tcarnahan]

I have Form A that is bound to tbl_Member (MemberId, LastName, FirstName, and some other fields).

On Form A is a combobox [cbo_Selector]. The combobox shows LastName and FirstName from tbl_Member, but when a name is selected, it returns the member's ID to Form A and causes that member's record to show in Form A (along with other data pertinent to the member). This works fine until I type a name into the combobox that does not exist in the underlying table.

I use the NotInList event of the combobox to pop up another form (Form B) in the DataEntry mode. Form B is bound to the same table as Form A. I enter the LastName, FirstName, and exit Form B. What I am trying to do is to have Form B pass back the new MemberId to Form A and specifically to add that record's data to the combobox on Form A.

I have tried several things to accomplish this. When Form B updates, the table has the new member added, but when I try to requery Form A or the combobox, I get errors.

My main question is how do I communicate to the combobox in Form A the information from the newly added record?

Thanks,
Tom

 

[pbaldy]

Sounds like the same issue:

http://www.access-programmers.co.uk/forums/showthread.php?t=276805

 

[tcarnahan]

Paul,

Thanks for the quick response. That solves part of my problem. The other part is this:

Both forms are bound to the same table. When I close Form B, not only do I need to have the combo-box refreshed, but I need the main form (Form A) to display the new record so that the user can add additional information and associate the record to other data in other tables.

I wasn't sure if I needed to go the RecordsetClone route with Form A to bring the new record up in Form A or if there was a better way to do it. Would you have an example? I am assuming that everything you have shown me or will show me gets executed from Form B, correct?

Some additional info on Form B: I need a way to undo the record the user creates after they have committed it to the table in case they realize they made a mistake and just want to get out of Form B. Since Form B is used to enter only one record, I was considering having it unbound and then before committing it, have the user choose whether to save or exit without saving. What do you think?

 

[pbaldy]

If they're both bound to the same table, why not just go to a new record on the first form and not bother with the second? In any case, could this be adapted to do what you want?

http://www.baldyweb.com/Bookmark.htm

 

[tcarnahan]

That would work if I wasn't using a combo-box to navigate. The user types in the first several characters of the last name they are looking for in the combo-box on the main form. On Change, if the name exists, I have code that goes to that record on the main form. If that name does not exist, I use the NotInList Event of the combo-box to tell the user that the name does not exist and asks them if they would like to add it. If they say no, nothing happens. If they say Yes, it opens the second form in Data Entry mode.

Your example was not quite what I had in mind since the record does not exist at the time the second form loads.

I guess I could go right into the data entry mode on the main form. I tried this but is did not give me a new record. The main form stayed on the existing record:

DoCmd.OpenForm Me.Name, acNormal, , , acFormAdd
Set rst = Me.RecordsetClone
rst.AddNew

 

[pbaldy]

You'd use GoToRecord to go to a new record on the first form.

If you stayed with the 2-form method, I meant that link to be used (with tweaks) going from the second form back to the first. At that point the record does exist.

 

[tcarnahan]

Here is my code in the main form. I got rid of the second form:

Private Sub cbo_Selector_NotInList(NewData As String, Response As Integer)
    
    'Suppress the default error message.
[COLOR=Red]1[/COLOR]    Response = acDataErrContinue
    
    ' Prompt user to verify if they wish to add a new value.

[COLOR=Red]2[/COLOR]    If MsgBox("The resident: " & UCase(NewData) & vbCr & " is not in list. ADD New Resident?", vbYesNo + vbQuestion, "Not in List") = vbYes Then
        
        'Go to new record:
[COLOR=Red]3[/COLOR]        DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
        
[COLOR=Red]4[/COLOR]       MsgBox "Add new LAST NAME", vbInformation + vbOKOnly, "Add NEW Member"
        
[COLOR=Red]5[/COLOR]        Me.cbo_Selector = Null
        
[COLOR=Red]6[/COLOR]       Me.txt_Last_Name.SetFocus
        
[COLOR=Red]7[/COLOR]    End If

End Sub

 

[pbaldy]

Happy to help!

 

[tcarnahan]

Paul,

I spoke too soon. My code (above) goes into an endless loop. I added the red line numbers so that I could describe what is happening.

When I start to type a name into the combo-box [cbo_Selector], names are supplied in the box until the control holds a name not in the table. I press Tab. The code executes in the NotInList event for cbo_Selector. It progresses down and informs me that the name is not in the list and "Do I want to add it?". If I say "no", it exits. If I say "yes", when it gets to Line 3 (go to new record), execution jumps back to the top of the code for the event.

Sometimes if I say "yes" and it has gone through the loop several times (with me selecting "yes" each time), when I select "no" to get out of the loop, execution drops down to the bottom, but before exiting, I get the error "cannot go to record" and execution jumps back to Line 3, at which point I have to kill execution to get out.

I am not sure if I changed something or not ... I think not, but it worked yesterday.

Any idea what might be going on?

Thanks,
Tom

 

[pbaldy]

The not in list event would typically be used for a different source table, so this use of it is a little off the map. I wonder if putting

Me.cbo_Selector.Undo

helps? If not, my first thought would be to abandon the not in list event and add to the code you probably have in the after update event.

 

[tcarnahan]

Thanks Paul!

The combo-box undo did the trick!

 

[pbaldy]

No problemo!