Lookup field on form - how to update with new values

[Faith90189]

I have a lookup field that contains values I’ve entered. This field is accessed on a form. Very often, I will need to add items to my value list. I open the table and manually enter the new value in the value list for that field. Here’s my problem: the new value does NOT display in the list for this field on my form unless I delete and re-add the field. How do I get around this? Is there a “refresh” property or something I’m not seeing??

 

[randy700]

If I read your post correctly, you are using a value list for your listbox/combobox. This will never update unless you either retype the entries or create code that adds a new entry to the list.

Why not change the record source type to table/query and set the record source to the field in question. As such, the listbox/combobox will update whenever you add a new item to the table.


Randy

 

[Faith90189]

A friend suggested using a Requery action in the "On Update" event for the properties for this field.

What I want to do is to be able to add, right from the field on the form, a new entry without the user having to exit data entry, open the table, and add the value.

Is this possible?

 

[randy700]

Are you using a combo box or a list box?
What is the record source type?


Randy

 

[Faith90189]

It's a lookup field.

 

[PHV]

Use a ComboBox and play with the LimitToList property and the NotInList event procedure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[randy700]

Sorry, but none of the controls in my toolbox are called lookup field. Do you have a special ActiveX control?


Randy

 

[Faith90189]

Well, it's actually a text field, but on the "Lookup" tab I've selected ComboBox.

 

[randy700]

And the record source and record source type?

Randy

 

[Faith90189]

I'm not sure what you're asking. The field is called "DocName," and it's in a table called "Main Table New.

 

[Faith90189]

I found the following information. I re-created it, but it still doesn't work. Is something wrong with the procedure?

Private Sub DocName_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
'Return Control object that points to combo box.
Set ctl = Me!DocName
'Prompt user to verify they wish to add a new value.
If MsgBox("Document name is not in the list. Do you want to add it?", vbOKCancel) = vbOK Then
'Set Response argument to indicate that data
'is being added.
Response = acDataErrAdded
'Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
'If user chooses Cancel, suppress error message
'and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub

 

[PHV]

What are the RowSourceType and RowSource properties of the DocName combo ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Faith90189]

OK, I know I sound like an idiot . . . but how do I determine the RowSourceType (which I think is "value list") and the RowSource properties?

 

[randy700]

Right click on the control (in design view). Select properties. The row source and row source type are defined on the data tab.


Randy

 

[Faith90189]

Row source type = value list
Row source = all the values I entered for this field, in quotes, and separated by semicolons.

 

[randy700]

Try inserting a break point in your code (press F9 while the cursor is on the line you want to code to stop on). Modify your code by inserting the command shown in red here...

ctl.RowSource = ctl.RowSource & ";" & NewData
Debug.Print ctl.RowSource
Else

You can "step through" your code after it stops at the break point by pressing the F5 key once for each line of code. When it gets to the Debug command, check the Debug window to see if the control source is correct. If necessary, you can open the Debug window by pressing Ctrl-G.



Randy

 

[Faith90189]

OK, this is just not working, so I'm changing my approach. I've created a new table that contains the values I need. I've created a form to use for data entry to this table. Now, what I want to do is to create a macro (or command button or SOMETHING) that will open this form and either allow a user to select from the list OR add a new value. Any hints about how to do this? I've created an OpenForm macro and used the Edit mode. I'm not sure on which event in the properties for this field to attach the macro.

I REALLY appreciate your help!