Triggering a data input form in Access

[jimfrmla]

I need to automatically trigger a data input form from an combo box when the value entered does not match anything pre-existing in the table.

Example.

The form ties a shipper number field which is linked to a shipper table with full shipper information. When keying a shipper number in the shipper number field, if that number doesn't exist in the shipper table I need it to trigger the launch of an input form so that the new shipper information can be put in the shipper table.

 

[PHV]

Take a look at the LimitToList property and NotInList event of the ComboBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jimfrmla]

I have tried several interations using the response to this and couldn't get it to work. Perhaps I am typing something wrong. I will explain in detail with actual table and form names below.

Starting form name = "Orderdetail1A"

Field name of Combo box = "Ship to Number1"

Name of table that the combo box field Ship to number1 is referencing = "Ship to"

Name of Form I want to open when you type a value that is not in the list = "Ship to"


Private Sub Ship_To_Number1_NotInList(NewData As String, Response As Integer)
Criteria = "[Ship to]='" & Me![Ship to] & "'"

If IsNull(DLookup("Ship to Number", "Ship to" ,Criteria))
Then DoCmd.OpenForm "Ship to", acNormal
End If

End Sub

Any assistance would be greatly apprieciated

Thanks

 

[misscrf]

you can also try this:

Private Sub cmbCandTypeFK_NotInList(NewData As String, Response As Integer)
Dim Msg, Style, Title                  ''''<<<<<<,Mystring
Msg = "The text you entered is not in this list, would you like to add it now?" ''''<<<
Style = vbYesNo    ' Define buttons.
Title = "Type or listing must be maintained"    ' Define title.
Response = MsgBox(Msg, Style, Title)            '''''<<<<<<<


If Response = vbYes Then    ' User chose Yes.
    Me.Undo                                     '''''<<<<<
   DoCmd.OpenForm "frmCandidateType", acNormal, , , , acDialog ' Perform some action.
'   DoCmd.OpenForm "frmCandidateType", acNormal, , , acFormPropertySettings, acDialog ' Perform some action.
''''Else    ' User chose No.                     '''''<<<<<
''''    DoCmd.Requery 'Perform some action.     ''''<<<<<

Me.Requery                           '''' <<<
Me.cmbCandTypeFK.Requery                        '''''<<<<

End If
Exit_CmdSaveCand_Click:
    Exit Sub

Err_CmdSaveCand_Click:
    MsgBox Err.Description
    Resume Exit_CmdSaveCand_Click
    
    End Sub

this is code for the not in list event of a combo. It can only be used if the combo is set to limit to list as described above. This code asks the user if they want to add to the list. You can always just put the docmd.openform in there if you like.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[jimfrmla]

Thanks but I figured out what to do to get the form to pop up. In the notinlist event I only put the 2 statements below and eliminated everything else.

DoCmd.OpenForm "Ship to"
DoCmd.GoToRecord , , acNewRec

It opens the form to a blank record but what I would like to do now is trigger the black record to open up but with the value appearing in the first input field that triggered the ship to form to open in the 1st place.

example:

I typed in 03G866 in the "Ship to Number1" field of my start form. This is a combo box that compares this number to the "Ship to" table. This number is not in the "Ship to" table so the "Ship to" form opens up for input. I would like for the 03G866 to automatically appear in the 1st field of the "Ship to" form when it opens up for input. I'm sure this is something simple. If anyone knows how to do this I would apprieciate it.

Thanks in advance.

 

[PHV]

Take a look at OpenArgs

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244