Not In List to open form at specific record ?? (1 Viewer)

[fibayne]

Hi
I have this code from the following link http://www.access-programmers.co.uk/forums/showthread.php?&p=694123#post6941 which works really well , I would however like if possible to be able to open frmClientMain after the table has been updated with the new client at the new clients record so I can add further info about the client, I tried adding the line below which starts >>> but frmClientMain opens showing the last Client added ?? any suggestions how to get the form to open at the newly added client ??

Thanks for any help ...cheers
Fi

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

On Error GoTo cboSurnameFA_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Client " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add them to the list now?" _
, vbQuestion + vbYesNo, "Acme Life Assurance PCC Plc")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblClientMain([SurnameFA]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Client has been added to the list." _
, vbInformation, "Acme Life Assurance PCC Plc"
Response = acDataErrAdded
>>>'DoCmd.OpenForm "frmClientMain", acNormal, "", "[ClientMainID]=[Forms]![frmApplicantDetails]![ClientIDFK]"
Else
MsgBox "Please choose a Client from the list." _
, vbInformation, "Acme Life Assurance PCC Plc"
Response = acDataErrContinue


End If
cboSurnameFA_NotInList_Exit:
Exit Sub
cboSurnameFA_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboSurnameFA_NotInList_Exit
End Sub

 

[ezfriend]

You can do something like

    DoCmd.OpenForm "frmClientMain", , , , , , "GoToNewClient"

in your frmClientMain load event, you can do something like

Private Sub Form_Load()

    If Me.OpenArgs = "GoToNewClient" Then

        'The last client should be your newly added client.
        DoCmd.GoToRecord , , acLast

    End If

End Sub

Please note that this will only work if you did not sort your form by anything else but the client id (AutoNumber field)

 

[fibayne]

Hi EZfriend and thanks for your reply ..I have tried your suggestion and it seems to be almost there, the new client is added to the list and frmClientMain opens at the new client but the msg box 'The Client is not currently listed.... ' keeps opening and when yes is selected adds a new client then goes back to the message box, any ideas why this would be ??
thanks again for your help...cheers Fi