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 for Same combo box under change and got focus:
code for FrmUpdateItems form:
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:
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