Originally Posted by
Micron
Your code only seems to add data to one field
rstVnds![Vendor_no] = NewData
I resolved my issue, but for the sake of clarity, I'll try to explain once again.
I have two tables - AddContracts and AddVendors. AddContract form:
AddVendor form:
There is Vendor FK in the Contracts. The Add Contract form besides contract-related fields has also fields from the Vendor table (a combo and a textbox), so you can choose an existing vendor for a new contract. The textbox is just for user interface purposes, you do not enter data in there, it is only to display the vendor name corresponding to the number chosen in the combo. So the order of actions is as follows: Open AddContract form -> enter new contract details - > choose vendor from the list (combo) -> if vendor is in the list textbox displays its name, if not in the list - AddVendor form opens up where you enter Vendor name and contact details (the new vendor number is transferred from the combo through OpenArgs). When AddVendor form is closed the combo and the textbox in the AddContract form gets populated with the new vendor number and vendor name.
So the way I resolved this is:
Code:
Private Sub VenSvRecrd_Click()Dim VenNm As String
On Error GoTo VenSvRecrd_Click_Err
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
Me.Visible = False
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If
VenSvRecrd_Click_Exit:
Exit Sub
VenSvRecrd_Click_Err:
MsgBox Error$
Resume VenSvRecrd_Click_Exit
End Sub
Code:
Private Sub Combo225_NotInList(NewData As String, Response As Integer)Dim oparg As String
Dim dbsProc As DAO.Database
Dim rstVnds As DAO.Recordset
Dim intAnswer As Integer
Dim cbo As Control
Dim rst As DAO.Recordset
Dim ff As String
On Error GoTo ErrorHandler
intAnswer = MsgBox("Add " & NewData & " to the list of vendors?", _
vbQuestion + vbYesNo)
If intAnswer = vbYes Then
oparg = NewData
Set dbsProc = CurrentDb
Set rstVnds = dbsProc.OpenRecordset("Vendors")
rstVnds.AddNew
rstVnds![Vendor_no] = NewData
rstVnds.Update
rstVnds.Close
DoCmd.OpenForm "AddVendor_Form", , , , acFormEdit, acDialog, oparg
ff = Forms!AddVendor_Form!Vendor_title
Response = acDataErrContinue
Me.Text207 = ff
Response = acDataErrAdded
DoCmd.Close acForm, "AddVendor_Form"
Else
Response = acDataErrDisplay
End If
Set rstVnds = Nothing
Set dbsProc = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
Basically after the new vendor details are added in the AddVendor form, I do not close it, but just hide it and then catch the new vendor title in the AddContract form and manually insert it as value into the text box without doing a requery. This suits my needs because the correct vendor name is already in the table, it's just does not appear in the dataset. Any requery before the NotInList event finishes makes the event fire up again and again. English is not my first language so please bear with me, my way of describing things might not be the clearest