I have an ordering form we use at work. I cannot get the Ordering form to requery or accept the new product after it has been entered.
Here is the code:
Private Sub ProductID_NotInList(NewData As String, Response As Integer)
If MsgBox("The Product " & NewData & " you entered, does not exist yet." & vbCrLf & vbCrLf & "Do you wish to add it?", _
vbQuestion + vbYesNo) = vbYes Then
Response = acDataErrContinue
DoCmd.OpenForm "FrmAddNewProducts", acNormal, , , acFormAdd, , NewData
Response = acDataErrAdded
As soon as I close the Form which I add new products It takes me to my Ordering form and if I try to enter the info again it runs me through the same Msgbox all over again.
I know it just needs to Save or Requery!! It's not though!
Should I enter code in the exit of the other form that would remove what is in the current control, requery, then save? Can you do this to another form while your closing the current form?
If any of this is possible, I would really appreciate the assistance as to what code I should write.
OK, this seems like the "best" solution:
If it's not in the list but you want it to be. Inside the notinlist event just blast the data you want into the table using an update of any kind.
Private Sub HGS_Model_NotInList(NewData As String, Response As Integer)
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strOrderBy As String
Dim strSQL As String
Dim criteria As String
Const msg1 = "The data you entered is not currently in the list."
Const msg2 = "Would you like to add it?"
Const title = "New Entry?"
Const NL = vbCrLf & vbCrLf
If MsgBox(msg1 & NL & msg2, vbQuestion + vbYesNo, title) = vbYes Then
strSQL = "UPDATE [tblPrograms] SET [HGS Model] = """ & NewData & """ WHERE [FD Project] = """ & Me.FD_Project & """"
Response = acDataErrAdded ' confirm record added
Me.[HGS Model].Undo ' clear the entry in the combobox
Response = acDataErrContinue ' confirm the record is not allowed
I think this would work if I just had 1 item to replace.
I need to go to the other form enter the NEW Product, NEW supplier (possibly), enter prices, enter order amount. Then, I need to exit the form and take me back to the original form and enter the new Product I just entered.