Can anyone help me?

[Simon Wong]

I know the tips of Adding/removing value to List Box. However, I don't know how to save all my value in list box bound to a table/queries. Is there any tips to do so?(please explain in details because I'm still new to VBA coding part!!!!)

[Access Guy]

COPY OF COMMENT BY PS
------------
Behind the combo box, in the 'On Not in List' Event, create area behind the combo box, create a subroutine cboVendor_NotInList(NewData As String, Response As Integer), and include this code: (NOTE: this is code from Access 2000 Developer's Handbook, Volume I and I use it and it's great!)

The table and field that I use is tblVendors.VendorName. You will have to modify this code to include your table name and the field name. Try it. Hope this helps!


Private Sub cboVendor_NotInList(NewData As String, Response As Integer)

' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert (Sybex)
' Copyright 1999. All rights reserved.

' Amended on 03/06/2008
' EOP Budget and Fiscal database - VISA Statement
' Add new items to the table/field: tblVendors.VendorName

Dim strMsg As String

#If USEDAO Then
Dim rst As DAO.Recordset
Dim db As DAO.Database
#Else
Dim rst As ADODB.Recordset
#End If


strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"

If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Vendor") Then
Response = acDataErrDisplay

Else

#If USEDAO Then
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblVendors")
#Else
Set rst = New ADODB.Recordset
rst.Open _
Source:="tblVendors", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTableDirect
#End If


rst.AddNew
rst("VendorName") = NewData
rst.Update
Response = acDataErrAdded
rst.Close

End If

End Sub

[Simon Wong]

Hi, thx for your reply. Your tips of "NotInList" is useful to save data in Combo Box that bound to table/queries. However, it can't be use in list box. Do you have any ideas to do so?

[Simon Wong]

Yo! I've figured it how to use either Combo Box/text box to save all value I type in it. Suprisingly, it bound to table/queries. Now, I'm facing another problem : How come all my values that save in table can't display in the list box? And it'll dissappear everytime I try to reopen the form. Can anyone tell me why?

[Simon Wong]

Thx to people that give me tips on my discussion. I've figured another way to add/remove value in list box. At the same time, it bound to table/queries.

I'll just roughly tell how my another way works:-
1. First, I create a List box that bound to table/queries in fom1
2. Then, I create a command button(Add value)
3. Basically this button is to open another form(from2) that contains bound text box.
4. Then, on form2's "on close" property. I put this code "Forms![Form Name]!ListBox.Requery" to requery the list box in form1.

That's what I've figured out after so many hours... hehe