Returning value from one form to another

[equestrian]

I have frmEntry with combo box Horse. I have a command button AddHorse that opens another form. When the horse is added in the other form and the close button is used, the user is back in frmEntry. However, I want the horse the user just added to be defaulted in the combo box. As it is, the combo box is blank and the user has to click in the combo box and select the horse. Can anyone point me in the correct direction?

 

[ProgramError]

In the close event of the second form put...

forms!NameOfFirstForm.NameOfComboBox = me.NameofComboBoxOnSecondForm.value

Change the names accordingly.


Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.

 

[Remou]

It might be better to use the NotInList event. However, you can requery the combo from the AddHorse form:

Forms!frmEntry!cboHorse.Requery
'Assumung that the AddHorse form is showing the relevant 
'horse
Forms!frmEntry!cboHorse=Me.txtHorse

 

[TheAceMan1]

How are ya equestrian . . .

[blue] . . . However, I want the horse the user just added [purple]to be defaulted[/purple] in the combo box.[/blue]

Unless you somehow [blue]clear or reset the combobox[/blue], its selection should remain! Check you code for this.

Otherwise, be more specific about [purple]to be defaulted[/purple] . . . and why!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[equestrian]

AceMan,
I was going on the theory that the user would click on the drop down menu and look for the horse in frmEntry. If the horse was not in the list, the user would click on the New button which would take them to frmHorse. Therefore, when the user clicked on the New button, there would be nothing selected in frmEntry. I wanted the horse that was entered on frmHorse to be selected on frmEntry when the user closed the form. I just realized that someone reading my post might assume that frmEntry was the form to enter a new horse. Actually, frmEntry is a form to enter a horseshow entry.

However, the more I thought about it, I realized that the user would instead type the name in the field and hit tab or enter. Therefore, it would make more sense to use the 'on not in list' event. I have done the following code:

In frmEntry On Not In List event

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

    Dim Result
    Dim Msg As String
    Dim CR As String
    
        CR = Chr$(13)
        
        'Exit This subroutine if the combox box was cleared.
        If NewData = "" Then Exit Sub
        'Ask the user if he or she wishes to add a new horse.
        Msg = "'" & NewData & "' is not in the list." & CR & CR
        Msg = Msg & "Do you want to add it?"
        If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
            'If the user chose Yes, start the New Horse form in data entry
            'mode as a dialog form, passing the new horse name in
            'NewData to the OpenForm method's OpenArgs argument.  The
            'Open Args argument is used in New Horse form's Form_Load event
            'procedure.
            DoCmd.OpenForm "frmHorse", , , , acAdd, acDialog, NewData
        End If
        
        'Look for the horse the user created in the New Horse form.
        Result = DLookup("[HorseName]", "frmHorse", "[HorseName]='" & NewData & "'")
        
        If IsNull(Result) Then
            'If the horse was not created, set the Response argument
            'to suppress an error message and undo changes.
            Response = acDataErrContinue
            'Display a customized message.
            MsgBox "Please try again!"
        Else
            'If the horse was created, set the Response argument to
            'indicate that the new data is being added.
            Response = acDataErrAdded
        End If
    
End Sub

In frmHorse On Load event

Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
    'If form's OpenArgs property has a value, assign the contents
    'of Open Args to the HorseName field.  OpenArgs will contain
    'a horse name if this form is opened using the OpenForm
    'method with an OpenArgs argument, as done in the Events
    'form's HorseNum_fk_NotInList event procedure.
    Me![HorseName] = Me.OpenArgs
    End If
End Sub

When I click tab after entering a horse who is not in the table, the frmHorse opens. The data name I entered in frmEntry is in frmHorse. I click on the close button at the bottom of frmHorse and get

run-time error '3078'
The microsoft Jet database engine cannot find the input table or query 'frmHorse'. Make sure it exists and that its name is spelled correctly.'

I click on debug and

Result = DLookup("[HorseName]", "frmHorse", "[HorseName]='" & NewData & "'")

is the problem. Can anyone help me with the syntax?

 

[Remou]

DlookUp looks up tables, not forms.
You should find that:
Response=acDataErrAdded
will work.

Don't forget that forms can be opened using acDialog as the windows mode.

 

[Remou]

Don't forget that forms can be opened using acDialog as the windows mode.

I see you had not forgotten.

 

[Remou]

This is a little less woolly.

Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
   DoCmd.OpenForm "frmHorse", , , , , acDialog, NewData
   'The close button on frmHorse assigns 2 to the tag
   'when all is well and _hides_ the form.

   If Forms!frmHorse.Tag = 2 Then 'acdataerradded
      'Allow for the user having changed the name on 
      'the frmHorse
      Me.HorseNum_fk.Undo
      Me.HorseNum_fk.Requery
      Me.HorseNum_fk.Text = Forms!frmHorse!HorseName

<...>
'All done
      DoCmd.Close acForm, "frmHorse"
<...>

 

[equestrian]

Remou,

Thank you. I used

Response=acDataErrAdded

, and that worked perfectly.