Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Requery after adding item from another form

Status
Not open for further replies.

niteraven

Technical User
Oct 26, 2006
92
US
Hello All

I have a form (frmNCOE from tbl1) that has a combo box with a record source coming from tbl2 - (item list), that has 4 fields that show in the combo box, then based on the choice the user makes auto populates the combo box, plus the 3 other combo boxes. (This all works fine) When an item is not in the list, a msgbox opens and asks if you want to add an item then a form opens (frmUpdateItems) As acDialog. The following code does not populate the textbox with newdata (what the user has already typed in and found it was not in the list), and when finished adding the item information and close frmUpdateItems, the requery doesn't work, until you choose the item you created then it populates the 3 remaining fields.

Any help or direction is greatly appreciated!

Raven

Code for item list combo box not in list event:
Code:
Private Sub NCItemNo_NotInList(NewData As String, Response As Integer)
    Dim ADL As String
 
    ADL = vbNewLine & vbNewLine
    
    msg = "ITEM NAME IS NOT IN THE LIST!!" & ADL & _
            "WOULD YOU LIKE TO ADD THIS NAME TO THE ITEM LIST?"        
    Style = vbYesNo Or vbQuestion
    Title = "UNKNOWN ITEM NUMBER!"
    
    If MsgBox(msg, Style, Title) = vbYes Then 
        Me.NCItemNo.Value = NewData
        DoCmd.OpenForm "frmUpdateItems", , , , acFormAdd, acDialog, NewData
        
        Response = acDataErrAdded 
        DoCmd.Save
         
    Else
        
        msg = "YOU ARE NOT ADDING ITEM TO THE LIST!!"
        Style = vbInformation + vbOKOnly
        Title = "NOT ADDING ITEM NUMBER!"
        
        MsgBox msg, Style, Title
        
        Response = acDataErrContinue
        Me!NCItemNo.Undo   
    End If
End Sub
Code for Same combo box under change and got focus:
Code:
Private Sub NCItemNo_Change()

    Me.NCItemName.Value = Me.NCItemNo.Column(1)
    Me.Combo20.Value = Me.NCItemNo.Column(2)
    Me.NCCLass.Value = Me.NCItemNo.Column(3)
    Me.Combo32.Requery
    Me.NCCLass.Requery
    Me.Combo20.Requery

End Sub

Private Sub NCItemNo_GotFocus()

    DoCmd.Save
    
    Me.NCItemName.Value = Me.NCItemNo.Column(1)
    Me.Combo20.Value = Me.NCItemNo.Column(2)
    Me.NCCLass.Value = Me.NCItemNo.Column(3)
    Me.Combo32.Requery
    Me.NCCLass.Requery
    Me.Combo20.Requery
    
End Sub

code for FrmUpdateItems form:

Code:
Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

    Dim StrSql As String
    Dim ADL As String
    
    StrSql = "INSERT INTO tblITEMLIST (ItemNumber,ItemName, NCClass, MfgID) VALUES ('" & Me.TXTitemno & "', '" & Me.TXTDesc & "', '" & Me.Combo6 & "', '" & Me.TXTMFG & " '); "
    
    DoCmd.RunSQL StrSql
    
Exit_Command11_Click:
    Exit Sub

Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
    
End Sub
Private Sub Command12_Click()
On Error GoTo Err_Command12_Click

    DoCmd.Close

Exit_Command12_Click:
    Exit Sub

Err_Command12_Click:
    MsgBox Err.Description
    Resume Exit_Command12_Click
    
End Sub

Private Sub Form_Close()

    'DoCmd.Requery ([Forms]![frmNCOE]![NCItemNo])


End Sub

Private Sub Form_Load()

    If Not IsNull(Me.OpenArgs) Then
        Me.TXTitemno.Value = NewData
    End If
    


End Sub

Private Sub Form_Open(Cancel As Integer)

    If Not IsNull(Me.OpenArgs) Then
        
        Me.TXTitemno = NewData
        
    End If

End Sub
 
I fixed it. <Ah ha dummy moment> For anyone reading this - this is what I had to change.
<cannot believe I didn't catch this before>

Code:
Private Sub NCItemNo_NotInList(NewData As String, Response As Integer)
  
    Dim ADL As String
    
    ADL = vbNewLine & vbNewLine
    
    msg = "ITEM NAME IS NOT IN THE LIST!!" & ADL & _
            "WOULD YOU LIKE TO ADD THIS NAME TO THE ITEM LIST?"
            
    Style = vbYesNo Or vbQuestion
    Title = "UNKNOWN ITEM NUMBER!"
    
    If MsgBox(msg, Style, Title) = vbYes Then
        
        Me.NCItemNo.Value = NewData
        
        DoCmd.OpenForm "frmUpdateItems", , , , , acDialog, NewData
        
        Me.NCItemNo.Requery
        Me.NCItemName.Value = Me.NCItemNo.Column(1)
        Me.Combo20.Value = Me.NCItemNo.Column(2)
        Me.NCCLass.Value = Me.NCItemNo.Column(3)
        Me.Combo32.Requery
        Me.NCCLass.Requery
        Me.Combo20.Requery
        
        Response = acDataErrAdded
        
        DoCmd.Save
       
    Else
        
        msg = "YOU ARE NOT ADDING ITEM TO THE LIST!!"
        Style = vbInformation + vbOKOnly
        Title = "NOT ADDING ITEM NUMBER!"
        
        MsgBox msg, Style, Title
    
        Response = acDataErrContinue
        
        Me!NCItemNo.Undo
        
        
    End If


End Sub
 
Ok jumped the gun here - the data the user types into the combo box does not transfer to the frmupdateitems.
Any ideas?
Raven
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top