Prompt to update table

[tjr222]

I have tables called "Orders", "Inventory", "Materials" and the three are related with Inventory being the joing table of a many to many relationship.

The database is used to keep track of orders and I have a material lookup so the user can easily select from what has been ordered. The problem is that when a user enters something that is not in the lookup table "Materials" as an entry, Access displays a message "a related record in table 'materials' is required."

After having explained this what I would like to do is instead of that message by access, have my own message that prompts the user if they would like to add their entry to the material table.
Then conflict would no longer exsist and the new material would be with all the others in the lookup table. Please let me know how I could go about attempting this.

 

[Tim K.]

What don't you let the user to enter the data only in a form? So you can use a combo box to list only stored items by setting the combo box Limit To List property to Yes and add the message you want to show on On NotInList event like this.


Private Sub YourControlHere_NotInList(NewData As String, Response As Integer)
MsgBox "This item is not available. Please select only existing items", vbOKOnly, "Oops!!"
Response = acDataErrContinue
End Sub

 

[tjr222]

Thanks for the response, however, I don't think I was clear enough on my problem. When the user enters a material that is not in the combo box (which is looked up from the material table) instead of saying that there is a conflict and this cannot happen, I would like for the new material to be added to the combo box. This way the user can continue recording the order with no problem. And they wont have to open the 'materials table' and enter in the new material everytime it is not there. Hopefully I made myself a little clearer and hopefully you can still help. Thanks again.

 

[tjr222]

Thanks for the response, however, I don't think I was clear enough on my problem. When the user enters a material that is not in the combo box (which is looked up from the material table) instead of saying that there is a conflict and this cannot happen, I would like for the new material to be added to the combo box. This way the user can continue recording the order with no problem. And they wont have to open the 'materials table' and enter in the new material manually everytime it is not there. Hopefully I made myself a little clearer and hopefully you can still help. Thanks again.

 

[Tim K.]

Sorry. Here's the code to do want you really want.

Private Sub acPlaceType_NotInList(NewData As String, Response As Integer)
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb

Response = acDataErrContinue
If MsgBox("This item is not available yet?" & vbCrLf & "Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to a table tblDiscipline.

With dbs
Set rst = .OpenRecordset("tblPlace")
rst.AddNew
rst!PlaceName = NewData
rst.Update
rst.Close
.Close
End With

Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
End If

End Sub

 

[Tim K.]

Oops!