openargs on NotInList

[maxmangion]

i have a form frmSongs and it has a field (cboSinger). when a value is inputted which is notinlist, the following code executes. the code works fine, except for the part of the openargs in the docmd.openform. Can someone guide me what i have wrong in this pls:

Private Sub cboSinger_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboSinger_NotInList
If MsgBox(NewData & " is not in List" & vbCrLf & "Would you like to Add it to the List", vbQuestion + vbYesNo, "Warning ...") = vbYes Then
DoCmd.OpenForm "frmSingers", , , , acFormAdd, acDialog, OpenArgs:="[Singer] = '" & Me.Singer.Value & "'"
Response = acDataErrAdded
Else
Me.cboSinger.Undo
Me.cboft.SetFocus
Response = acDataErrContinue
End If
Exit_Err_cboSinger_NotInList:
Exit Sub
Err_cboSinger_NotInList:
MsgBox Err.Description & Err.Number, vbExclamation, "Warning ..."
Resume Exit_Err_cboSinger_NotInList
End Sub

Thank you!

 

[adam_fleck]

You might need to put some code on frmSingers

If you want frmSingers to open with the new singer's name already in place, you could try this -

Change your DoCmd.OpenForm so that it reads like this:

DoCmd.OpenForm "frmSingers", , , , acFormAdd, acDialog, NewData

Then put this code on the Form_Load event for frmSingers

Private Sub Form_Load()

    If Me.OpenArgs <> "" Then
        Me.singer = Me.OpenArgs
    End If

End Sub

This will make the new singer appear in the singer field on frmSinger.

 

[maxmangion]

thank you very much that did the trick.

 

[maxmangion]

by the way, how can i pass the current value of a field to the newly opened form rather than passing NewData ?

e.g.

your example which works fine
DoCmd.OpenForm "frmSingers", , , , acFormAdd, acDialog, NewData

now
DoCmd.OpenForm "frmSingers", , , , acFormAdd, acDialog, Me.cboSinger
then in the onLoad
if me.openargs <> "" then
me.Singer = me.openargs
end if

does not work ?

thank u

 

[maxmangion]

i've managed to solve the problem ... thx!

 

[adam_fleck]

Good stuff

How did you solve it?

Me.cboSinger.Text

I think the reason "Me.cboSinger" didn't work is that this tries to send a numeric value as the OpenArgs string, which causes an error. Not sure.

 

[maxmangion]

actually the problem was that in the new form i had to pass the singerid and not the singer, i was just referencing the wrong field, and since the datatype was different, that was the problem.

anyway thank you very much for help, because i've managed to do it, thx for your help with the NewData.

Thanks!