NotInList
~~~
Hi Darrell,
Are you using an autonumber primary key in Tbl_Wire_Size ? and storing
a Long Integer Foreign Key in Tbl_Weld_Wire_Chits? If not, then perhaps
you should be. Your RowSource for the combo would be hiding the first
column, which is the numeric key, and showing the second column, which
will be your description.
You can use the NotInList event to add a record to the lookup table
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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 --> RecordID
ControlSource --> RecordID
RowSource -->
SELECT
RecordID,
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)
RecordID 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:
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub ControlName_NotInList( _
NewData As String, _
Response As Integer)
' crystal (strive4peace)
'assumption:
'the combobox controlname is
'RecordID_controlname
'and its first column (hidden)
'is the Autonumber record ID for the source table
'set up Error Handler
On Error GoTo Proc_Err
Dim s As String _
, mRecordID As Long _
, mText As String
' Display message box asking if user wants to add a new item
s= "'" & NewData & "' is not in the current list. " _
& vbCrLf & vbCrLf _
& "Do you want to add it? " _
& vbCrLf _
& "(Check to ensure new entry is correct before proceeding)"
Select Case MsgBox(s, vbYesNo + vbDefaultButton2 _
, "Add New Data")
Case vbYes
'if NewData needs to be parsed,
'separate 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 & "';"
'--------------------------------------------------------
'if data is text
s = "INSERT INTO Tablename(SomeName) " _
& " SELECT '" & NewData & "';"
'--------------------------------------------------------
'if data is numeric
'
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
'assume SQL to add was ok
Response = acDataErrAdded
Case Else
Response = acDataErrContinue
End Select
Proc_Exit:
Exit Sub
Proc_Err:
'NOTE: replace ProcedureName with the name of your procedure
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"
Resume Proc_Exit
'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WHERE
- Tablename is the name of the lookup table
- SomeName is the name of the field with the text or numeric values to
display
- RecordID is the name of the Primary Key field in the table you are
adding to, and the Foreign Key in the table that the form is based on
- ControlName is the NAME property of the control on your form with RecordID
Warm Regards,
Crystal
remote programming and training
*
have an awesome day
*
~~~~~~
Learn Access on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal
~~~~~~
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
~~~~~~
