Adding Records to a Combo Box

[ElCid]

Hi all,

I have a data entry screen that requires the user to select a assessment type from a combo box that gets its values from a separate table. So far i have 3 Assessment types, these are Quality, Manufacturing and Technical.

If the assessment type they want is not is the combo box list. I want them to be able to type in the assessement type in the combo box and have it saved to the assessment type list so that in future it will be availible directly from the combo box.

Any ideas on how i can do this as it's giving me a head ache....

Many thanks in advance.

El Cid

 

[indesisiv]

Try doing a search

Try doing a search and you will come up with something like this.

Try this one

 

[Haymarket]

Not sure how you want to do this in terms of look, however I have used the not in the list event for the combo box to display an error message that says the item is not in the list and says if you would like to add, double click on the field. Then an event for double click opens a form which lets them add the relevant data. Finally, the close button then refreshes the combo box and lets them choose from the list. If you want, you can then set the combo box field to be the entry you have just added.

If you want to go down that route, and don't know how to do it, reply to this forum and I will write out the code I have used in the past.

Phil

 

[ElCid]

Thanks

I managed to sort out the issue.

Instead of having the list in a separate table i used a query that took the information from the field i wanted to add the record to. When the records get entered they will now populate the combo box over time.

Many thanks.

I would be interested to see how you would do it using the not in list.

Thanks,

El Cid

 

[maxmangion]

take a look at this example by SJ

 

[ctieppo]

Please show how to set combo box to new value

If you want, you can then set the combo box field to be the entry you have just added.

If you want to go down that route, and don't know how to do it, reply to this forum and I will write out the code I have used in the past.

Phil

Could you please post the code to do this? I'd like to do this, a user has requested it. Thanks!

 

[Haymarket]

Not sure how automatic you want this to be - the code indesisiv has hyperlinked below is very good.

In my case, the combo dropdown only gave a name, of course you want them to add address and phone number etc or other relevant data, then you want them to add more than just the drop down entry, so you need them to open a form.

So if, you would prefer an error message which gives them instructions, or opens a new form which they can then enter then you can use the notinthelist event.

Private Sub cboname_NotInList(NewData As String, Response As Integer)
'The undo entry clears what they typed in to the combo
Me.cboname.Undo
'The Message Box gives them the instructions you want them to follow
MsgBox "This name is not in the database, choose one that is or Click on Add New Button"
'This clears the Access default error and returns to the drop down for them to choose an entry in the list
Response = acDataErrContinue
End Sub

Of course you could just put an open command for another form, with a refresh combo comand on close and a form.cboname = me.id to put the entry you've just created in to the combo.

I have done that by giving an instruction above to double click on the field to add a new entry and it opens a new form.

You choose.

 

[ctieppo]

I already have my database working to open a new form where they can add a new sub and lot number. I just needed to know the logic in that last part of your response, (form.cboname = me.id) in order to have the original screen populate with the new value when they return. Thanks.
CRT

 

[Haymarket]

Ah well that being the case, the best way to do that is create a close button on the form with this code:

Private Sub OKfrmAddManagersPopUp_Click()
  On Error GoTo Err_OKfrmAddManagersPopUp_Click
 
   'make sure current edits in the current form are saved to the table
   Me.Refresh
   'refer to a combo on the sub-form of a form
   With Forms!frmTeam.ChildTeamDetails.Form.cboManager
      'requery the combo so newly added record appears there
      .Requery
      'set the value of the combo
      .Value = Me.ManagerID
   End With
   'close the form
   DoCmd.Close
Exit_OKfrmAddManagersPopUp_Click:
    Exit Sub

Err_OKfrmAddManagersPopUp_Click:
    MsgBox Err.Description
    Resume Exit_OKfrmAddManagersPopUp_Click
End Sub

In this case there is a Manager Combo in a sub form of a form called team, so if the Manager is not in the list, they create one by opening a form. They then enter his details - address - phone etc.

When it closes, it does a requery of the combo, then sets the ID of the combo to be the same as that of the form you are in.