Textbox not populated in NotInList event

[DemonDNF]

A 2 part question.

SQL related with Spec combo box:

SELECT S.Key, S.Spec FROM tblSpecs AS S ORDER BY S.Spec;

NotInList code:

Private Sub cboSpecs_NotInList(NewData As String, _
                               Response As Integer) ' MODIFY SPEC ENTRY.
10  Dim db As Database
20  Dim strSql As String
30  Dim strOldSpec As String
 
'               Retrieve old data for Specs combo box entry.
40  strOldSpec = DLookup("Spec", "tblSpecs", "Key = " & cboSpecs)
 
'               Modify tblSpecs entry for Spec combo box.
50  Set db = CurrentDb
60  strSql = "UPDATE tblSpecs SET Spec = '" & NewData & "' " & _
              "WHERE Key = " & cboSpecs & ";"
70  db.Execute (strSql), dbFailOnError
80  db.Close
 
'               Display process message.
[B][COLOR=darkred]90  txtMessage = "SPEC " & strOldSpec & " CHANGED TO " & NewData & "."[/COLOR][/B]
 
'               Refresh Specs combo box.
100 Response = acDataErrAdded
'               Update debugging fields.
110 txtSpecKey = cboSpecs
End Sub

QUESTION #1 SOLVED!
1) For some reason, txtMessage is not displayed on the form when NotInList is processed. I see it get populated properly in debug mode, but it never gets on the form. But txtMessage gets displayed normally in Add and Delete events.

It seems to do with the way NotInList works because txtMessage is displayed if I use acDataErrContinue. But that's not what I want; it leaves the combobox list open and can't save record if I close the form.

2) DLookup is used to extract the old Spec value to display in the message. I can see the old Spec in debug mode in cboSpec.column, but Listindex is -1 and unusable.

I don't know how to point to the proper record in the list. The only other way I can think of is to loop through the list comparing the Key, and then extract the Spec.

I don't know if it's more efficient to use lookup or loop.
(question #2 continues on post #5)

Robert

 

[DemonDNF]

This is how I use a textbox and combobox to handle add, modify and delete.

Clicking on ADD activates a textbox to add Specs in bulk mode.

txtMessage is updated and displayed after Spec s4 has been added in AfterUpdate event (now ready for next new entry).


Clicking on MOD activates the combobox to modify and delete Specs.

txtMessage does not appear after Spec s1 has been changed to s1a in NotInList event.

(debug shows txtMessage set correctly during NotInList event)

Robert

 

[Cronk]

Are you sure you are debugging the NotInList event?

If you enter a new value in the combo to cause the event to execute, then the value of the combo is not in the table. So how is the DLookup supposed to return a value from a record that does not exist? That is cboSpecs = Null

What is it that you want to achieve when a new value is entered?

 

[DemonDNF]

Are you sure you are debugging the NotInList event?

Yup.

If you enter a new value in the combo to cause the event to execute, then the value of the combo is not in the table. So how is the DLookup supposed to return a value from a record that does not exist? That is cboSpecs = Null

What is it that you want to achieve when a new value is entered?

Reread the post #2, I use a textbox to add new values, the combobox is used only to modify an existing record.


As I was producing the pic above for you, I noticed something weird as I was switching views between VBA debug and the form. txtMessage is displayed on the form until the END SUB and then it disappears?

Robert

 

[DemonDNF]

Woohoo! I figured out question 1; I added msgboxes every time I touched txtMessage and I clear it in cboSpecs_AfterUpdate. D'OH!


So I'm left with question 2; how can I get to the old value in cbospecs.column(1,1) when ListIndex is -1?

Robert