Would like to have combobox accept name not in list and update list.?? 1

[Guest_imported]

I have a combobox that gets its info from a query that is based on a table (I am using the query to ensure that the info in the combobox is in alphabetical order)

When a user selects a name from the combobox list, the name is stored in the table.

I need the combobox to be able to accept having a new name entered into the list and then update the list to reflect this change without having to close down/reopen the form to update the query.

Can this be done(easily)?

Thanks in advance for any help

 

[Durkin]

If you have a table called Table3 and your combo box is called combo2, the following code should do what you need.

Private Sub Combo2_AfterUpdate()
If IsNull(DLookup("TestField", "Table3", "TestField = '" & Me.Combo2 & "'&quot) Then
CurrentDb.Execute "INSERT INTO TABLE3 VALUES ('" & Me.Combo2 & "')"
Me.Combo2.Requery
End If
End Sub

Make sure that the 'Limit to List' property of combo2 is set to 'No'. If you are using ADO then the 'CurrentDB.Execute' will be 'Connection.execute'. Also if you need to insert more than one field into the table the SQL will need to be modified. But I'm sure you know this already.;-) Durkin
alandurkin@bigpond.com

 

[Umbane]

I have some forms which do the same and I use this code, see if it helps.

Private Sub City_NotInList(NewData As String, response As Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available City Name"
strMsg = strMsg & "@Do you want to add the new City Name to the current list?"
strMsg = strMsg & "@Click Yes to link or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?&quot = vbNo Then
response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Cities", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!City = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
response = acDataErrContinue
Else
response = acDataErrAdded
End If
End If
End Sub


Umbane

 

[Guest_imported]

Thanks for the response! I appreciate the time you have taken to assist me.
Durkin: after putting more thought into it, I decided to expand my task a little more so the code you provided wouldn't quite do the job with my new ideas. Thanks a lot though.

I ended up using a variation of what you wrote Umbane.

 

[jbento]

Hi Umbane,
I used your Not In List Example, and I get a "type mismatch" error. I am using an Access 2000 database. My table that holds the values is called "Project" and I also created a query from this table, just to do sorting of the values, and it is called "qry_Project". Here is the code I am using, and I'll point out where the code is highlighting the error:

Private Sub ProjectName_NotInList(NewData As String, response As Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Value List"
strMsg = strMsg & "@Do you want to add the new Value?"
strMsg = strMsg & "@Click Yes to add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?&quot = vbNo Then
response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("qry_Project", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Project = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
response = acDataErrContinue
Else
response = acDataErrAdded
End If
End If
End Sub

The line that is highlighting the error, is the following:
Set rs = db.OpenRecordset("qry_Project", dbOpenDynaset)

Can you please tell me why I am getting this error? Any help will be greatly appreciated. Thanks so much.

Jerome

 

[jfhewitt]

Here is a copy of a memo I sent my daughter who was having a similar problem. I might help you.

This routine will let you add data to a table on which a combo box is based, on the fly. For example, the "bates" table contains numbers and a name. To add a new name to the table while entering data on the form, this routine is attached to a command button (command5) which opens a dialog box for entry of the new name, then adds the new name to the existing table "bates" in the field "name". The combo box must be based upon the table "bates", and "name" must be selected as the listbox field. The same routine can be used to add data to any table in the selected database and recordset.



Private Sub Command5_Click()

Dim varName As Variant 'variable must be declared
varName = InputBox("Enter New Name&quot

Dim db As Database 'The "AddNew" function works with a recordset, so these
Dim rst As Recordset 'settings are required to make it work.
Set db = CurrentDb
Set rst = db.OpenRecordset("bates", dbOpenDynaset)
With rst
.AddNew
!name = varName
.Update
Me.NameBox.Requery 'may not be essential sine this is a table not a query
End With 'probably not a bad idea to include just in case to use
End Sub 'a query rather than a table

 

[jbento]

All,
I found out what the problem was with "Umbane's" code. You have to put DAO.Recordset as a reference, and make sure that the DAO 3.6 is chosen in the references area of your database. The code works fine after I did this.

The second line of code should read like this:
Dim db As Database, rs As DAO.Recordset

Thanks.

 

[jbento]

I think you could use the following to refresh/requery:

me."combo_box_name".requery

Try that.

Jerome

 

[SQLGirl]

Hi,

I am using a combo box which gets its values from a predefined set of values in the VB code. I have the Limit To List property set to 'Yes' as i don't want users to enter any other values, however when you click on the box, it is still possible to type free text inside. The code produces an 'invalid list value' error and will not let the new value be saved in the form, but i was wondering if there is any way of disabling this behaviour ???

Thanks.

 

[miletracker]

I Have tried the above From Umbana with no results but no errors either only "Pick an item from the list"

 

[miletracker]

I copied the following from Umbana
which works but when I OK or cancel with a blank field access shows an error in code at the rs.update line
can anyone add code to prevent this error so the msgbox will exit gracefully I am using Access2000
Thanks for all the help so far
, miletracker


Private Sub City_NotInList(NewData As String, response As Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available City Name"
strMsg = strMsg & "@Do you want to add the new City Name to the current list?"
strMsg = strMsg & "@Click Yes to link or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?&quot = vbNo Then
response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Cities", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!City = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
response = acDataErrContinue
Else
response = acDataErrAdded
End If
End If
End Sub
Thanks again to everyone Miletracker