On Not In List Event 2

[rj51cxa]

I have a form "FrmTeamData" in which combo box control "TxtServiceNumber" has the following code in the "Not in List" event:

 Private Sub Service_Number_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Service_Number_NotInList

'Service Number not recognised

Dim intAnswer As Integer

intAnswer = MsgBox("Add Competitor Details?", vbYesNo, vbQuestion)
    If intAnswer = vbYes Then
        DocName = "FrmCompDetails"
        DoCmd.RunCommand acCmdUndo
        DoCmd.OpenForm DocName, acNormal, , , acFormEdit, acDialog
        DoCmd.GoToRecord acDataForm, DocName, acNewRec
        DoEvents
        Forms(DocName).Controls("TxtServiceNumber").Value = NewData
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If

Exit_Service_Number_NotInList:
    Exit Sub
    
Err_Service_Number_NotInList:
    MsgBox Err.Description
    Resume Exit_Service_Number_NotInList:

End Sub

I enter a service number in the combo box and, if it is not listed in TblCompDetails, I get the message box. What I then want is for the number I have entered in the combobox to appear in the "TxtServiceNumber" control on FrmCompDetails, so that I don't have t type it in again.

I used the code that was suggested by PHV in Thread 705-1278130 but this does not work and the form opens with a blank control.

Could someone please take a look at my code and see where it is going wrong.

Thanks a lot
John

 

[SgtJarrow]

I think your code problem lies in this line:

DoCmd.OpenForm DocName, acNormal, , , acFormEdit, acDialog

The acDialog parameter STOPS the rest of your code from running until the DocName form is closed.

I would look at passing the NewData to the form as a OpenArg. Take a look at the DoCmd.OpenForm in the help files.

Basically you change the above quoted line to something like:

DoCmd.OpenForm DocName, acNormal, , , acFormEdit, acDialog, NewData

Then, in the open event of the DocName form, you fill the text box....like:

Me.TxtServiceNumber = Me.OpenArgs

Then you take this line out of your calling code:

Forms(DocName).Controls("TxtServiceNumber").Value = NewData

That should point you in the right direction.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[rj51cxa]

Sorry mstrmage1768, I think I am missing something. I modified the code as follows but the following happens:

When I type an unknown number into the combo box on the main form, I get the message box (OK) but as soon as I respond Yes, the number in the combo box disappears and I get a message saying "no record".

The code now looks like this:

Private Sub Service_Number_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Service_Number_NotInList

'Service Number not recognised

Dim intAnswer As Integer
Dim TxtServiceNumer As String

intAnswer = MsgBox("Add Competitor Details?", vbYesNo, vbQuestion)
    If intAnswer = vbYes Then
        DocName = "FrmCompDetails"
        DoCmd.RunCommand acCmdUndo
        DoCmd.OpenForm DocName, acNormal, , , acFormEdit, acDialog, NewData
        StrTxtServiceNumber = Forms!FrmTeamData.OpenArgs
        DoCmd.GoToRecord acDataForm, DocName, acNewRec
        'DoEvents
        'Me.TxtServiceNumber = Me.OpenArgs
        'Forms(DocName).Controls("TxtServiceNumber").Value = NewData
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If

Exit_Service_Number_NotInList:
    Exit Sub
    
Err_Service_Number_NotInList:
    MsgBox Err.Description
    Resume Exit_Service_Number_NotInList:

End Sub

I have also modified the open event in the DocName form as you suggested.

I would appreciate a little more help with this one please.
Best Regards
John

 

[SgtJarrow]

My sample I built has the following:

Form1 is contains a combobox. The NotInList event has this:

    If MsgBox("'" & NewData & "' is not in the list.  Do you wish to add it?", vbYesNo, "Add New?") = vbYes Then
        DoCmd.OpenForm "Form2", , , , , acDialog, NewData
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If

Form2 is unbound and has two fields...txtServiceNumber and txtName. There is one command button.

Code for Form2.OnOpen

    Me.txtServiceNumber = Me.OpenArgs

Code for Command button on Form2

    Dim strSQL As String
    strSQL = "INSERT INTO TABLE2 VALUES ('" & Me.ServiceNumber & "', '" & Me.txtName & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    DoCmd.Close acForm, Me.Name

If that doesn't help, please describe you table layouts, form layouts and show me all the code you have so I can work on a sample based on your info.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SgtJarrow]

FYI...I found an old sample I have that uses a bound form to collect data. If you would like that, let me know.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[rj51cxa]

Sorry Robert, still no joy. I did as you suggested and placed a command button on Form2 (FrmCompDetails). Then, I entered an unknown number in the TxtServiceNumber combobox on FrmTeamData . The Message box came up and, when I pressed the Yes button I received a Run-time error message:

Run-Time Error '-2147352567 (80020009) You can't assign a value to this object.

I clicked debug and the code for the OnOpen event on FrmCompDetails appeared - highlighted in yellow.

I closed the code and then exited the original form (FrmTeamData) and received the following message:

The text you entered isn't an item in the list. Select an item from the list or enter text that matches one of the listed items.

The set-up is as follows:

The main form (FrmTeamData) is based on one table (TblCompetitor). In this form, the combobox TxtServiceNumber AfterUpdate event looks up the entered number in a second table (TblCompDetails) and, if the service number is in that table, extracts the competor's name and initials and enters them into TblCompetitor.

If the service number is not recognised, the message box appears and, when Yes is pressed, FrmCompDetails opens. This form is based on TblCompDetails and has three text boxes - TxtServiceNumber, TxtName and TxtInitials. These three are completed, the form is then closed and the new details appear in FrmTeam Data.

What I would like to happen is that, having entered the unrecognised service number on FrmTeamData, the message box appears, followed by FrmCompDetails which opens with the unrecognised service number in the TxtServiceNumber text box. The name and initials can then be entered, the form closed and the full details will then appear in FrmTeamData. This way, the service number is only entered once.

The codes I have so far are as follows:

1 FrmTeamData - TxtServiceNumber Not in list:

Private Sub Service_Number_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Service_Number_NotInList

'Service Number not recognised

Dim intAnswer As Integer

intAnswer = MsgBox("Add Competitor Details?", vbYesNo, vbQuestion)
    If intAnswer = vbYes Then
        DocName = "FrmCompDetails"
        DoCmd.RunCommand acCmdUndo
        DoCmd.OpenForm DocName, acNormal, , , acFormEdit, acDialog, NewData
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If

Exit_Service_Number_NotInList:
    Exit Sub
    
Err_Service_Number_NotInList:
    MsgBox Err.Description
    Resume Exit_Service_Number_NotInList:

End Sub

2 FrmCompDetails - On Open:

Private Sub Form_Open(Cancel As Integer)
Me.TxtServiceNumber = Me.OpenArgs
End Sub
[code]

3   FrmCompDetails - New Command Button:

[code]
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim strSQL As String
strSQL = "Insert into TblCompDetails ('" & Me.TxtServiceNumber & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


    'Screen.PreviousControl.SetFocus
   ' DoCmd.FindNext

Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click
    
End Sub
[code]

For ease of use, I would prefer not to have to use the command button but if this is the only way, so be it.

I hope this explains the set-up more clearly and that it will help you to resolve my problem.

If you think that your example would help, my email address is john@jdraper.net.  If it would help you further, I could make an extract of my database and email that to you.  Please let me know.

Best Regards
John

 

[PHV]

I'd use the Load event instead of the Open to assign a value to a control ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rj51cxa]

Hi PHV, that was the hint I needed. I changed the code over and it all worked.

Thanks very much to both you and mstrmage1768 for your help in resolving my problem. Stars all round.

Best Regards
John