Combo Boxes and NotInList 1

[kornork]

Hello,

I have several Combo Boxes that get their values from separate tables. Some of them I have set up to add new items to the list using the method from the FAQ (faq702-4283), and for some of them I have a separate button that opens the relevant form to add a record. My problem is that the combo boxes don't seem to update after the new information is entered into the popup form (and will even prompt me with another message box asking if I want to add a new item, again). I've tried to requery and refresh the combo boxes and the form, and this works sporadically, almost like it takes a while for the underlying table to update. Am I doing something wrong? I've checked the underlying tables, and the new record is there, even though it won't show up in the combo box.

Thanks,
Josh

 

[Steve101]

I would have thought that the requery of the combo would work.

Here are a couple of things you might want to check out:

(a) When the new entry is added, do you make sure that the form which adds the entry to the underlying recordset of the combo, explicitly saves the record BEFORE you requery the combo. Otherwise you could be doing a requery on the 'old' version of the combo's recordsource.

(b) If you exit the application after adding a combo entry, then re-enter it, does the new entry then consistently appear. If so, it would support (a) above.

Hope this might help,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[kornork]

Thanks for your response.

As for your first point, I thought I had the save covered, as I have the form set up so that you save the info, then you close the form (which saves again). Maybe I'm using the wrong command? As for your second point, yes, the new entry does show up after I exit and re-enter.

I'm including my code this time. Maybe I'm requerying wrong, or something else.

In the main form:


'When the Transmission entered is not in the list
Private Sub Transmission_Model_NotInList(NewData As String, Response As Integer)
If NewData = "" Then Exit Sub
If MsgBox("The part you have entered is not in the list. If you are sure this was not " + _
"a typo, do you still wish to add a new part?", vbYesNo, "Warning&quot = vbYes Then
DoCmd.OpenForm "Transmission Information Form", acNormal, , , acFormAdd, acDialog, NewData

Response = acDataErrAdded
Me.Requery
Me!Transmission_Model.Requery
End If

End Sub

In "Transmission Information Form":

'Save changes and finish
Private Sub Save_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub

'Close the Window
Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click

DoCmd.RunCommand acCmdSaveRecord
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close

Exit_Close_Form_Click:
Exit Sub

Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click

End Sub


Thanks for your help,
Josh

 

[Steve101]

OK Josh,

I think I see the problem; it is as you suspect, one of synchronisation. Essentially, when two forms are open concurrently, you can have their respective event code executing at the same time.

Here's what I do to take control of this situation:

(a) Define a global function in the modules section of the database, as follows (I have a module called modUtils, where I park this function):

Public Function HangTen(Con)
'-----------------------------------------------
'Wait around until the current object is closed.
'-----------------------------------------------
While Application.CurrentObjectName = Con
DoEvents
Wend
End Function

This function takes an object name string as a parameter, and does nothing but loop, as long as that object is open. The DoEvents statement allows events such as mouse, keyboard events to be tested for whilst this loop is being executes. Without it, the effect could be to 'hang' the computer.

(b) You now need to invoke this function at the appropriate part in your code. Add the line immediately after the openform line; ie.:

DoCmd.OpenForm "Transmission Information Form", ...rest of line
HangTen "Transmission Information Form"

(c) This will "suspend" execution of the code in the main form, until the transmission information form is closed, at which point the requeries will "fire" in a timely manner.

That should do the trick. You'll also find with this explanation, that your code above has some redundant record save statements; eg. when you close a form, the current record is automatically saved. Suggest you get the above working; then try removing suspected redundant code in a controlled way.

There are other ways to resolve the above problem; eg. you could have put the requery code in the close event of the list box form as an alternative. Take your pick.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[kornork]

Steve,

Your solution is one I would have never thought of, which makes me wonder if I'm doing things a bit backwards on more of a design level (though that's probably a subject for another thread). I had assumed that the code from the first form would halt until the second form was closed, but I guess that is wrong.

I will try this out and let you know what happens (after New Years, of course). Thanks!

Josh

 

[Steve101]

Josh,

You can have several forms all active at the same time; as an exercise, develop two identical forms, each with a button and a text control; when you click the button, get the value of the text control to loop round, displaying the current date time. Include the DoEvents statement in the loop. You should find that when open, each form operates on their own, or at the same time.

Once you understand this concept, it can be useful in a number of situations; your's is just one.

Anyway I'll be away and off the air for a couple of weeks, but I do look forward to hearing how you went when I get back.

Cheers,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[kornork]

Steve,

As usual (at least in my experience with Access so far), the (immediate) problem was not what I thought it was. Upon examination, the code was _never_ advancing past the call to MsgBox, and so my Requery's were never called, and neither was the Response set to acDataErrAdded. I tweaked my message box a little bit, setting it to open in Normal mode (instead of Dialog) and all of a sudden things started working better. With the HangTen routine in there, I don't have any problems at all. It's a useful little function, and I can think of several other places where it will help clear things up.

Thanks for your advice,
Josh