Add an Supplier to Table

[Roger Bell]

I have coded an Event Procedure "OnNotInList" as follows:

Private Sub SupplierID_NotInList(NewData As String, Response As Integer)
Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add new Supplier to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "Supplier", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure supplier has been added

Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Suppliers table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If


End Sub

The form opens, however the Item does not automatically appear in the first
field called Supplier Name. I have done the same for a Suburb table and when
this form opens the Suburb is already there and you can move to type the
relevant PostCode, ZipCode etc

Thanks for any help

 

[strive4peace]

Hi Roger,

Here is some generic NotInList code you can pattern after. If you still
want to open the Supplier form so that more info can be filled out, you
can add that to the code -- once the Supplier record is created, you can
do an OpenForm and filter it

I would add that after

Me.SomeID_controlname = mRecordID

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

on your form, here is an example with the properties you need to set for
a combobox that stores the ID and displays the text

combobox control

Name --> SomeID
ControlSource --> SomeID
RowSource -->
SELECT
SomeID,
SomeName
FROM Tablename
ORDER BY SomeName

BoundColumn --> 1
ColumnCount --> 2

columnWidths --> 0;2
(etc for however many columns you have
-- the ID column will be hidden since its width is zero)

ListWidth --> 2
(should add up to the sum of the column widths)

LinkID will be stored in the form RecordSource while showing you
information from another table...

for the NotInList event of the combobox, here is code behind the form:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Code:


Private Sub ControlName_NotInList( _
NewData As String, _
Response As Integer)

'assumption:
'the combobox controlname is
'RecordID_controlname
'and its first column (hidden)
'is the Autonumber record ID for the source table

Dim s As String _
, mRecordID As Long _
, mText As String

'if NewData needs to be parsed,
'seperate it and
'modify the SQL accordingly

'~~~~~~~~~~~~~~~~~~~~~~~~
'Choose ONE of these code blocks

'--------------------------------------------------------

'if you want to convert to ProperCase
'mText = StrConv(NewData, vbProperCase)

's = "INSERT INTO Tablename(SomeName) " _
& " SELECT '" & mText & "';"

'--------------------------------------------------------

s = "INSERT INTO Tablename(SomeName) " _
& " SELECT '" & NewData & "';"

'--------------------------------------------------------
'~~~~~~~~~~~~~~~~~~~~~~~~

'comment or remove next line after this works correctly
Debug.Print s

CurrentDb.Execute s

CurrentDb.TableDefs.Refresh
DoEvents

mRecordID = Nz(DMax("SomeID", "Tablename"))

If mRecordID > 0 Then
Response = acDataErrAdded

'assuming the first column of the listbox
'is the RecordID, SomeID and is a Long Integer

Me.SomeID_controlname = mRecordID
Else
Response = acDataErrContinue
End If

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*