Intelligent Combobox

[thisisntwally]

I have a combobox, populated from a lookup table and a button which opens a form to add new values for the combobox.

Heres what i would like to do (users idea, not mine, apparently clicking the add button might be too confusing for some old fogies)

If the user types a value which is not in the list (dcount?) open the addnew form with the new value. maybe something like

on lost focus
if dcount(me.cboVALUE, lkupValue, me.cboValue = lkupvalue!Value) = 0 then
DoCmd.openForm (addValue,,,)

a. Do you think this can be done?
b. If so, How can i go about passing the value from Me.cboValue to addValue.txtValue?



also if you are an expert maybe you could take a look at this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=135141

 

[rainman89]

there is a not in list option. have u looked that up?

 

[CraigDolphin]

You need to search for information about the notinlist event for combo boxes. I'm sure you will find many examples that could be adapted to your needs.

Basically, the notinlist event fires whenever the user inputs something not in the drop down list. The 'newdata' string is whatever the user typed into the combo.

Simply pass this string to the 'add new' form as the openarg variable and use this to populate the relevant control on the 'add new' form.

Post back if you get stuck.

 

[thisisntwally]

hot buttery nipples, that sounds right up my alley. Thanks guys!

Now if i could only get a grip on that validation function....maybe next week

 

[rainman89]

mmmmmmm buttery nipples

 

[thisisntwally]

ok this is pretty sweet. Im having a little problem though

im doing this in form1:

Private Sub cboPurchaseOrder_NotInList(NewData As String, Response As Integer)
DoCmd.openForm "addPO", , , , , , NewData
End Sub

and this in addPO:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
  Me.txtPO = Me.OpenArgs
End If
End Sub

The problem is that i have a requery for the cbo on got focus, to keep it current. This seems to cause major problems, whether i choose to add the new Value or choose to cancel.

Private Sub cboPurchaseOrder_GotFocus()
Me.cboPurchaseOrder.Requery
End Sub

any ideas whats going wrong here?

 

[thisisntwally]

ah, it seems i should have searched 2118 error before i posted that. I believe i have THE SOLUTION.

 

[thisisntwally]

kinda.
im doing this as such:

Private Sub cboPurchaseOrder_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to add this item to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.openForm "addPO", , , , , , NewData
Response = acDataErrAdded
Else
MsgBox "Please choose something from the list.", vbInformation
Response = acDataErrContinue
End If
End Sub

this should as i understand it requery when acDataErrAdded is run.
after adding the new recordfrom addPO, the cbo is not refreshed, and i get prompted a second time to add this item to the list. at this point, if i choose yes access realizes there is a duplicate record, disallows adding a new record and the cbo is refreshed.

As i understand it, acDataErrAdded is running before the new entry has been added, and it is only when the response is run a second time that the list gets refreshed. As a requery will not work, what can i do to update this list?

edit: hrmmm i found a post from Rural Guy that said that the progress above should be paused while the addPO form is open, and that adDataErrAdded will run after addPO closes. so what the heck is going on here.

 

[thisisntwally]

heh. after a continuous loop made me start over, i tried this again, this time with ruralguys code from here: http://www.access-programmers.co.uk/forums/showthread.php?t=117314&highlight=list+requery

while it doesn't seem too different from what i was doing, it did the trick. methinks it was the acformadd or acdialog. or some mismanaged event procedure.

anyways...If you're out there, THANKS RuralGuy!