OnNotInList

[MichaelPReid]

I am using the below code in one of my forms. Three of the combo boxes in the form are populated by data from two related datasheets. I am attempting to create the option of adding new data into the associated data sheet if an entry is not already in the databse.

If I break this code down into its indivdual components, each works perfectly. When I combine the three components (Private Sub Source_NotInList, Sub Byline1_NotInList, Sub Byline2_NotInList) into the below expression, it does not function as it should. Where might I be going wrong?

Private Sub Source_NotInList(NewData As String, Response As Integer)
    Dim iAnswer As Integer

    iAnswer = MsgBox("Item is not currently in list, adding item", _
        vbOKCancel + vbQuestion)
    If iAnswer = vbOK Then
        Me.Source.Value = NewData
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Publication Background")
        rst.AddNew
        rst.Fields("Source") = NewData
        rst.Update
        Response = acDataErrAdded
        
    End If
    
End Sub

Private Sub Byline1_NotInList(NewData As String, Response As Integer)
    Dim iAnswer As Integer

    iAnswer = MsgBox("Item is not currently in list, adding item", _
        vbOKCancel + vbQuestion)
    If iAnswer = vbOK Then
        Me.Source.Value = NewData
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Reporter Background")
        rst.AddNew
        rst.Fields("Byline") = NewData
        rst.Update
        Response = acDataErrAdded
        
    End If
    
End Sub


Private Sub Byline2_NotInList(NewData As String, Response As Integer)
     Dim iAnswer As Integer

    iAnswer = MsgBox("Item is not currently in list, adding item", _
        vbOKCancel + vbQuestion)
    If iAnswer = vbOK Then
        Me.Source.Value = NewData
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Reporter Background")
        rst.AddNew
        rst.Fields("Byline") = NewData
        rst.Update
        Response = acDataErrAdded
        
    End If
    
End Sub

 

[Remou]

Please expand on "does not function as it should".

 

[MichaelPReid]

In the three fields where I have designated the OnNotnList fuction input the entered data into the related table, the data is getting sent to just one tabele instead of the designated location. I have found that everytime I set up one field's properties box to the proper settings, the previously updated combo boxes' are altered so that they do not function properly.

 

[Remou]

What is "Me.Source.Value = NewData" for? You have included it in all three updates, I suspect you meant to change it for the second two, but I think it is unnecessary.

 

[MichaelPReid]

Me.Source.Value = NewData" is the information that was entered into the field. If that information is not in the related database, it should be entered as a new entry.

Can I remedy this by changing it to "Me.Source.Value = NewDataA," "Me.Source.Value = NewDataB," and "Me.Source.Value = NewDataC," respectively? I tried it this way, but I ended up with an error at "rst.Update."

 

[Remou]

It will be entered as new data when the recordset is updated and the line "Response = acDataErrAdded". What the line is doing is saying that the control's value is equal to the control's value in the first procedure, and is equal to the value of the first control in the second two procedures. Comment the line out. NewData is passed to the procedure by the code:

[tt]Private Sub Byline2_NotInList(NewData As String, Response As Integer)[/tt]

You can see how it works by stepping through, you will see that NewData is the new value that was "not in list".

 

[MichaelPReid]

That makes sense. How can I clear out that value so that it is not passed along to the subsequent procedures?

 

[Remou]

You don't have to. It will die naturally at End Sub.

 

[MichaelPReid]

But it does carry over. For example, I can enter "aaaa" into the "Source" field and the result is the proper dialog box and "aaaa" beging entered into the proper sheet. If I move to the next field, Buline1, and input "bbbb," the "Source" field changes to "bbbb" as well. So, somehow the control value is being bounced around. Any thoughts?

I really appreciate all of your help with this.

 

[Remou]

Did you comment out the lines I said to comment out? When you put in this line:
Me.Source.Value = NewData
You said, in code, make the Source equal to what ever I entered in a) Byline1 and b) Byline2.

 

[MichaelPReid]

I updated my code to the below expression. It seems to have done the trick. It seems to have done the trick. Each of the three fields (Source, Byline1, Byline2) will update their related datasheet eachtime a new entry is presented. Byline1 and Byline2 will do this without carring over the inputed value back into Source.

Private Sub Source_NotInList(NewDataA As String, Response As Integer)
    Dim iAnswer As Integer

    iAnswer = MsgBox("Item is not currently in list, click OK to add to database", _
        vbOKCancel + vbQuestion)
    If iAnswer = vbOK Then
        Me.Source.Value = NewDataA
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Publication Background")
        rst.AddNew
        rst.Fields("Source") = NewDataA
        rst.Update
        Response = acDataErrAdded
        
    End If
    
End Sub

Private Sub Byline1_NotInList(NewDataB As String, Response As Integer)
    Dim iAnswer As Integer

    iAnswer = MsgBox("Item is not currently in list, click OK to add to database ", _
        vbOKCancel + vbQuestion)
    If iAnswer = vbOK Then
        Me.Byline1.Value = NewDataB
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Reporter Background")
        rst.AddNew
        rst.Fields("Byline") = NewDataB
        rst.Update
        Response = acDataErrAdded
        
    End If
    
End Sub


Private Sub Byline2_NotInList(NewDataC As String, Response As Integer)
     Dim iAnswer As Integer

    iAnswer = MsgBox("Item is not currently in list, click OK to add to database ", _
        vbOKCancel + vbQuestion)
    If iAnswer = vbOK Then
        Me.Byline2.Value = NewDataC
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Reporter Background")
        rst.AddNew
        rst.Fields("Byline") = NewDataC
        rst.Update
        Response = acDataErrAdded
        
    End If
    
End Sub

 

[Remou]

What can I say? These lines are not necessary. They do nothing for you, they are merely trouble. Remove them or comment them out, so that when you read the code in a year's time you will not woner why they are there. They are dead lines. :-(

Private Sub Source_NotInList(NewDataA As String, Response As Integer)
    Dim iAnswer As Integer

    iAnswer = MsgBox("Item is not currently in list, click OK to add to database", _
        vbOKCancel + vbQuestion)
    If iAnswer = vbOK Then
[b][red]Remove this line:        
'Me.Source.Value = NewDataA[/red][/b]
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Publication Background")
        rst.AddNew
        rst.Fields("Source") = NewDataA
        rst.Update
        Response = acDataErrAdded
        
    End If
    
End Sub

Private Sub Byline1_NotInList(NewDataB As String, Response As Integer)
    Dim iAnswer As Integer

    iAnswer = MsgBox("Item is not currently in list, click OK to add to database ", _
        vbOKCancel + vbQuestion)
    If iAnswer = vbOK Then
[b][red]Remove this line:        
'Me.Byline1.Value = NewDataB[/red][/b]
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Reporter Background")
        rst.AddNew
        rst.Fields("Byline") = NewDataB
        rst.Update
        Response = acDataErrAdded
        
    End If
    
End Sub


Private Sub Byline2_NotInList(NewDataC As String, Response As Integer)
     Dim iAnswer As Integer

    iAnswer = MsgBox("Item is not currently in list, click OK to add to database ", _
        vbOKCancel + vbQuestion)
    If iAnswer = vbOK Then
[b][red]Remove this line:        
'Me.Byline2.Value = NewDataC[/red][/b]
        Me.Byline2.Value = NewDataC
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Reporter Background")
        rst.AddNew
        rst.Fields("Byline") = NewDataC
        rst.Update
        Response = acDataErrAdded
        
    End If
    
End Sub

 

[MichaelPReid]

Understood. Thank you for all of your help.