Updating my Combo Box

[fantimm]

Ok, basically I have an equipment table, a sale table, and a purchase table. Each piece of equipment can have a sale and a purchase. So when I go into the "Add Sale" or "Add Purchase" forms, there is a combo box so you can select which piece of equipment the sale/purchase refers to. The thing is, usually when adding a sale or purchase it will be for a brand new piece of equipment, so when you go into the sale or purchase forms, there is a button on there which opens the Add Equipment form. So you can then add the equipment, and then close the Add Equipment for and you will be back at the sale/purchase form. The problem is that after you have added the new equipment, it is not always added to the combo box.

I am sure the combo box information needs to just be rechecked. If you open the Add Sale form, then go straight into the Add Equipment, add the equipment, THEN check the combo box, the equipment is there. But if you check the combo box first, it obviously saves the list of data the combo box is meant to show in a cache somewhere, because then it won't update it unless you reload the form.

The combo box is getting the data straight from the table, no query.

I know it is probably a simple problem, so what is the solution?

Also, if possible, after the "Add Equipment" form is closed, is there anyway the combo box could be set to automatically have the new piece of equipment selected? I know you could specifically do it in the "Add Equipment" form, by having a macro run when the form closes that sets the value of the combo box, but sometimes the equipment form will be opened without the sale/purchase forms being open, so it will give an error and say it cannot find the required combo box to set. So the functionality to control this stuff will have to be in the sale/purchase forms, not in the Add Equipment form.

 

[boblarson]

In the close event of the Add Equipment form then you can requery the combo:

Forms!YourFormNameHere.YourComboBoxNameHere.Requery

 

[fantimm]

But if I do that, then it will give me an error message if I close the Add Equipment form and the sale or purchase form is not open.

 

[fantimm]

So I have tried putting a button in the purchase and sale forms which simply runs a requery on the combo box, and that works. After I add some new equipment, if I press the button, the combo box will then be updated. So I definately have to requery the combo box, the only problem is when. So it cannot be in the "On Close" field of the add equipment form, because sometimes I will have that open on its own, and in that case it will give an error when I try to close it because it cannot find the combo box. So the method of updating the combo box has to be done within the form itself. I have tried putting the requery in the "On Click" of the combo box, but that doesn't work. Putting the requery in "On Current" for the form doesn't work either.

Anyone know where I can get it to run the code to requery the combo box?

 

[boblarson]

You can put it in the Click event of the combo box itself.

 

[fantimm]

already tried that boblarson, it doesn't work at all. (see my previous post)

 

[boblarson]

Putting the requery in "On Current" for the form doesn't work either.

Anyone know where I can get it to run the code to requery the combo box?

The requery on click should work so I am 99% sure you don't have the code right to do it as there is no reason why it wouldn't requery at that time. Post your database so I can see what is happening. We've obviously gone to a point where that is needed.

 

[groengoen]

I can see where this guy was coming from. Although the requery on the click event of the combo box does requery, this means that after adding a new record, you have to click on an existing record first before the new record appears in the combo box. One would hope to have the new record there before you click, otherwise it is tricky to explain the necessity of this manouevre to a user. Has anybody come up with an answer yet?

 

[SOS]

I can see where this guy was coming from. Although the requery on the click event of the combo box does requery, this means that after adding a new record, you have to click on an existing record first before the new record appears in the combo box. One would hope to have the new record there before you click, otherwise it is tricky to explain the necessity of this manouevre to a user. Has anybody come up with an answer yet?

Do an Advanced Search on "Not In List" and the user RuralGuy as he has posted several times including how to have Access automatically add to the combo by using something like acErrDataAdded (or something like that).

 

[gemma-the-husky]

I normally do this by

1. opening an add item form
2. suspending processing in the main form UNTIL the additem form closes
3. ONLY then requery the combo box
4. Then it will pick up the new item.

if you dont do step 2, the new item isnt there, and ther requery doesnt find the new item.

you dont really have to worry about whether the popup is open or closed. I find it easier to store any relevant data in global variables, and just close the form.

 

[SOS]

Actually Dave, there's no need to requery the combo if you use the acErrDataAdded (if the new data is added) or the acDataErrContinue if the data is not. It does it automatically for you.

 

[groengoen]

Smeghead: I have used both the acErrDataAdded and the acDataErrContinue in my code but the combo is not requeried.
Gemma/Dave: how do you suspend the processing in the main form or is it just a matter of having the called form modal? Where would you then requery the combo in the main. I have tried in the "on current", "on open", "on load" and "on got focus" with no luck.

here is code for the NotInList:

Private Sub cmbGP_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new Doctor.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Doctors form in data entry
' mode as a dialog form, passing the new company name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Doctor form's Form_Load event
' procedure.
flgRequeryGPCombo = True
DoCmd.OpenForm "frmDoctor", , , , acAdd, acDialog, NewData
End If

' Look for the Doctor the user created in the Doctors form.
Result = DLookup("[DoctorCode]", "tblDoctor", _
"[DoctorCode]= " & Val(NewData))

If IsNull(Result) Then
' If the Doctor was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the Doctor was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
flgRequeryGPCombo = False
End If

End Sub

PS: I wish I could find how to include code properly.

 

[gemma-the-husky]

you can suspend processing with the syscmd function

Function IsOpen(strName As String, Optional objtype As Integer = acForm)
   IsOpen = (SysCmd(acSysCmdGetObjectState, objtype, strName) <> 0)
End Function

then use

docmd.open "newform"

while isopen("newform")
  doevents
wend

(or for a report)
while isopen("anyreport", acreport)
  doevents
wend

you can use this to wait for queries/reports etc also - just use the optional argument as above

--------
i tend to use this, because you dont have to open the popup modal - which means I can move it around, and resize it etc. I tend to do this by clicking a button, rather than using the notinlist events etc. This gives it a more general use thna just in combo obxes.

-------
wrt posting code - you have to go to advanced (at the bottom of the message window) - select the text, and click the code icon (# character)
The PHP tag is also useful as it keeps alignment, and doesnt reduce spacing to single spaces - which helps if you want to post some columnar data