Having combo box value travel automatically to new form after update

[jimfrmla]

I had a problem figuring out hot to trigger a form opening up from a combo box when the value didn't exist in the subject table. 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 typed value appearing in the first input field that triggered the notinlist event to open the 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]

You have to play with the NewData parameter of the NotInList event procedure and OpenArgs.

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've been playing with the NewData parmameter and I still can't get this to work. Below is the code I have tried so far. I'm very new to Access and VB programming. Any suggestions would be greatly apprieciated.

Thanks in advance.

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

strMsg = "'" & NewData & "' is not in the Ship to table" & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to enter this Ship to number and record in the Ship to Table?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to retype the Ship to or No to continue with '" & NewData & '"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new ship to") = vbNo Then
GoTo Line1:

Else
Set db = CurrentDb

GoTo Line2:
Line1:
DoCmd.OpenForm "Ship to", acNormal
DoCmd.GoToRecord , , acNewRec

Line2:
DoCmd.OpenForm "Ship to", acNormal
DoCmd.GoToRecord , , acNewRec
Me![Ship to Number] = Me!'" & NewData & "'

End If


End Sub

 

[jimfrmla]

I'm still not able to get this to work. I'm new to access and visual basic. I know that the NewData parameter works because the value entered pops up on the prompt part of this code just fine. I just can't figure out how to pass the value to the first field of the blank record when it pops up in the new window. Any help with this is greatly appreciated.

Thanks

 

[jimfrmla]

Perhaps I should explain this better. I am attempting to have a value from one form once entered into a combo box that is not in the Ship to table then trigger the opening of another data entry form so it can be entered into the ship to table and have the value entered into the 1st form that triggered the notinlist event to open the data entry form to travel to the first field of the form.

1st form is - Shipments
combo box with notinlist event - Ship to number

2nd form is Ship to Form1
combo box field in 2nd form - Ship to number

So far nothing I tried works for transfering the value (New ship to number) from the 1st form when the notinlist event opens the 2nd form.

I've tried using the newdata parameter and openargs but nothing works. I must be doing something wrong but i'm not sure what.

Any assistance would be greatly apprieciated.

Thanks