Unanswered: Two-field NotInList event for combo not working
First post, so hopefully I'll get some "beginner's" luck, although actually my question is a little above "beginner's" level...
I have a combo-box that lists the different Parts stored in our warehouse. Each part consists of a part-number and a part-name; hence the Parts table has idPart (an autonumber PK), PartNumber, and PartName. For the combo-box, I'd like to have it key off of the idPart (the first, hidden row) and then have it display both the PartNumber and the PartName. For the drop-down, I was wanting the PartNumber and PartName to be shown in separate columns, but for the text portion of the combo-box, have it list both (eg "1234 | My Part"). Thus my record source is:
SELECT idPart, PartNumber & " | " & PartName, PartNumber, PartName FROM Parts
and the column widths being 0"; 0.02"; 1"; 2"
(so the combined PartNumber / PartName column is "hidden" in the drop-down portion, but is used for the text portion of the combo-box)
Because of the structure I had provided for displaying both the PartNumber and PartName, I thought I could provide a slick way of adding the two fields for a new record in the Parts table: have the user type in the new part in the same way, parse the position of the '|' character, and add both to the same INSERT statement. So I wrote the following NotInList event:
Private Sub cboPart_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboPart_NotInList
Dim cn As ADODB.Connection
'Dim rs As ADODB.Recordset
Dim strPartName As String, strPartNum As String, strPartEntry As String
Dim longDelimPos As Long
Set cn = Application.CurrentProject.Connection
' Parse entry into business unit's name and number
strPartEntry = CStr(NewData) ' Change Variant to String
longDelimPos = InStr(strPartEntry, "|")
strPartName = Left$(strPartEntry, longDelimPos - 1)
strPartNum = Mid$(strPartEntry, longDelimPos + 1)
' Insert data into the table
cn.Execute ("INSERT INTO parts (PartName, PartNumber) " & _
"VALUES ('" & strPartName & "', '" & strPartNum & "')")
' Finish form stuff
'Get the id used in the prev. INSERT
' Set rs = cn.Execute("SELECT @@IDENTITY AS LastID;")
'longTempID = rs!LastID
'Me.cmbLocation.Value = rs!LastID
'NewData = CStr(rs!LastID)
'MsgBox "Last ID: " & NewData
Response = acDataErrAdded
'Set rs = Nothing
Set cn = Nothing
Response = acDataErrContinue
However, I am running into a problem (as you can see with some commented "fixes" I was attempting). The record is getting entered into the database fine. The problem, however, is that at the end the item in the list is not getting selected and the focus doesn't move to the next control.
Normally acDataErrAdded causes the combo to get requeried, the new item is selected, and the focus moves. I don't think it is a problem with setting the Response param. to this value; at the line "Response = acDataErrAdded", the combo-box is getting requeried correctly, and the new entry is now available. However, this item does not get selected and the focus doesn't leave this control. Rather, what the user typed in remains in the text portion of the combo-box, the drop-down portion opens without the new item being selected, and the focus remains with the combo-box control. If the user tries to Tab to the next control, another record is made with the same part-number and -name. The only way for the user to go to the next control is to actually use the drop-down list to select the item they had just created.
I'm thinking the problem may have to do with my funky record source query. As a result, I have tried getting the last autonumber used for inserting this record and setting the combo-box's value to this (hence all of the commented out code). I have also tried rewriting the data so that it exactly matches the 2nd row of the query.
So if anyone has any thoughts... either to fix the NotInList code that I am currently using, or trying a completely novel workaround. I guess the only caveat to the workaround would be that I don't want to open a new form to enter in the data; I already have a button that will do this if they want to fully populate the record for a new part. (The parts table actually has a few more fields than what I mentioned; the 3 I spoke of are the only required fields.) I'm just looking for a quick way for the user to enter in a new part without having to mess with a completely new form, etc.
I figured out what my problem was... In my record source, I had a space on either side of the bar (eg "1234 | My Part"), but when I was parsing the PartNumber and PartName in the NotInList event, I was only finding the bar's position. So Left$ to the bar's position-1 includes the space character just before the bar; likewise for mid$, the position+1 included the space after the bar. Thus the part-number / -name in the table was getting an extra space. When the combo-box got requeried, the concatenated column for the new item now had two spaces before and after the bar (eg "1234__|__My Part"; EDIT: argh! can't show two spaces!). Hence what they entered would still not match the list, so the new value wouldn't be selected. And because it still didn't match the list, tabbing out of it wold once again trigger the NotInList.
So to fix it, all I needed to do was trim$ each of my strings before inserting them into the table (I want to Trim$ instead of using position+-2 because I cannot guarantee that my user will also include a space before and after the bar).
Amazing how frustrating a simple space can be. Especially when you can't (literally) see the problem!
It's one of the things that I have always been taught regarding databases: don't use data as a primary key (unless size constraints would truly cripple the database). A primary key by definition must always be unique and cannot be null. True, the part number may satisfy both conditions; the problem is that I cannot 100% guarantee that it would always do so.
Case in point: another of our databases (written by some other engineer) had a table keyed off of the customer's asset tag number. The idea was that 1) the asset tag number was incremental so each would be unique and 2) each unit we built required an asset tag. Thus it seemed that this piece of data would be perfectly suitable as the primary key.
Lo and behold, a few years later, another piece was added to our contract with the customer; they wanted us to ship update kits to the units that were already built and sent to the customer locations. These kits had much of the same info. that needed to be tracked as in the previous table. The major exception was that because these were not units, they didn't receive the customer asset tag. Thus we could no longer use the asset tag field as the primary key (since all of the kits would use a null value). So I ended up having to go through everything to change the primary key from the asset tag field to a new autonumber field; meaning all forms, queries, reports, etc. needed to be updated.
For the current warehouse database, there are a few issues that cause me to be cautious about using the part number as the primary key. First it's possible that a part may come in without a part-number (although even if this were to happen, I'd still think that our warehouse would assign some arbitrary part-number to it, so this is a very weak argument). Second, parts are coming in from many different vendors, so its possible that two vendors could assign the same part number to two different parts (again, very unlikely, but possible). Third, we have many different customers using the same part. Because we aren't allowed to mix inventory among customers, each part needs to be specific for a customer. And actually because of this concern, I actually have a customerPartsList table, which matches parts with their customer, so this argument is handled by a different schema. Finally, for each customer, it is possible that a part may have different status. We have parts that are new from the manufacturer, parts that just got returned to us for us to refurbish, and refurbished parts that can be sent out on an order, but still needed to be flagged as "used" for the customer.
Most database management systems (DBMS) have some type of autonumber that can be used as a primary key. And as I stated at te beginning, I've always been taught to do this unless there is a great size constraint to contend with. Since this is in Access, this size constraint is real; however, I'm not really expecting terabytes of data to be stored, and the number of records won't be exceeding the data-type limitation either, so for now I'm using the autonumber as a primary key.