Combo Box - List Problem

[pd2222]

Hi,

I'm not sure if I can explain this properly or if it can be done but I'll give it a go.

I have a combo box with a list based on a Query. I also want the user to add new names if they so wish but if they do so, I want a message box to pop up asking if they wish to add this name to the list (yes/no).

I have writen code for the NotInList event. Is this correct? If so, please help me with my existing code below:

Private Sub Bid_Manager_NotInList(NewData As String, Response As Integer)
Dim strMessage As String
Dim ctl As Control

strMessage = "Do you wish to add '" & NewData & _
"' to the list?"

If Confirm(strMessage) Then
??????????
Else
DoCmd.CancelEvent
End If

Thanks

Keza

 

[Eldaria]

You could open another Form where the user could enter the New information... and then refresh the Combo.
Eldaria

http://www.eldaria.net

That was my 25cent** of opinion.

** Inclusive Intrest, tax on interest, Genral tax, Enviromental tax, Tax, and tax on intrest, tax on fees, tax on tax, and other Various taxes and fees.

 

[braindead2]

there is a FAQ under access general discussion that covers this.

 

[Sameal]

I would just provide a MsgBox() within an If statement for the user confirmation. If the MsgBox() returns vbYes then do the combo box addition, if it returns vbNo then Goto your Exit_Routine label.

 

[pd2222]

Sameal

Thanks for replying. Do you mean have a messagebox inside an 'If' statement as I have above (original message)? If so, how do I then do the combo box addition?? This is the part that I am confused on.
Thanks again
Confused Keza

 

[Sameal]

I don't ever use bound controls in my Access databases so the way I add new entries to a combo box is a little different then if you use bound controls.

It is possible in Access to set the AllowNewEntries property to TRUE and let Access automatically add these entries to the table, but this is ONLY if you are using bound controls and you set everything up a certain way.

In my case I use DAO (Database Access Objects) to add records to the table that supplies the combo box.

If MsgBox("Do you want to add?", vbQuestion + vbYesNo, _
"Confirmation&quot = vbYes Then

Dim rsRecordset as Recordset
Dim sSql as String

'This SQL statement pulls all TableID and RecordName
'from tblExample that match the criteria: TableID = 0
'which will return 0 records. Doesn't matter we are
'adding not editing.
sSql = "SELECT TableID, RecordName FROM tblExample_
WHERE TableID = 0"

Set rsRecordset = CurrentDb.OpenRecordset(sSql)

With rsRecordset
.AddNew
.Fields(1) = cmbExample 'Set text from combo
'control into RecordName
'field of tblExample.
.Update
End With

Set rsRecordset = Nothing
End If

 

[pd2222]

Hi Sameal,

Thanks for your reply - I have tried your code but I'm being a bit thick here. TableID? What do you mean by TableID do you mean the primary key (e.g. EnquiryID) or do you mean just 'TableID' without substitution?

At the moment when I run the code I get the error message:
'Run time error 3061
Too few parameter.Expected 1'
I am assuming this is to do with the TableID? Can you help?

Thanks
Keza