access combo box auto update

[t5amec]

I have several combo boxs on a delivery note entry form. In one of the box's it has a list of all the locations the goods are delivered to, but sometimes new locations are added. It seems like a task to keep going in to change the row source when you are entering thousands of lines with hundreds of new locations to remember to add.

How can I make it possible to have the combo box, but be able to add new lines to the row source if they do not appear on the source already?

Make Sense? I hope so! (-:

 

[roltrap]

hi,

you mean you want to add new locations using the combobox? this can be done by using the combobox function NotInList.

regards roltrap

 

[t5amec]

If I do that, it only allows me to input what is ALREADY in the combo box. That can't be right!?!

Where section in the properties, of the combo box, should I put it?

Make Sense? I hope so (-:

 

[mck144]

Make sure limit to list property is set to no.

-Mike

 

[t5amec]

That kinda works, but it doesn't save it on the Location RowSource. So the next time something comes in for that location I have to type it in full again.

Why doesn't it save it, so i can select it another time?

Make Sense? I hope so (-:

 

[fneily]

On the AfterUpdate event of the combobox put:

Me![comboboxname].Requery

 

[t5amec]

I did that and got the following message:

Microsoft Access can’t find the macro ‘Me!Location.’
The macro (or its macro group) doesn’t exist, or the macro is new but hasn’t been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro’s macro group was last saved under.

What does this mean?

Make Sense? I hope so (-:

 

[fneily]

Sorry. I should have been more explicit. Goto design view of your form. Bring up the property sheet ( the button with the hand holding a piece of paper on the top toolbar). Click on your combobox (not the label, the box). Click on the Event Tab of it's property sheet. Click in the box next to After Update. Click on the the button with the three dots (build button). Click on Code builder. Click OK. In between the Sub and End Sub statements type:

Me![comboboxname].Requery

Place your combobox name (found on the property sheet under the All tab, option Name) in [ ] square brackets.

 

[t5amec]

Ok, so that doesn't work either:

1) When i save a record i get this message:
The expression After Update you entered as the event property setting produced the following error: The object doesn’t contain the Automation object ‘Me.’.
* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
* There may have been an error evaluation the function, event, or macro.

2) It still doesn't save the new location in the memory so I can select it rather than typing it in every time.

Below is the code from the code builder, please tell me if you see what I am doing wrong:

Option Compare Database

Private Sub Command24_Click()
On Error GoTo Err_Command24_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command24_Click:
    Exit Sub

Err_Command24_Click:
    MsgBox Err.Description
    Resume Exit_Command24_Click
    
End Sub
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command25_Click:
    Exit Sub

Err_Command25_Click:
    MsgBox Err.Description
    Resume Exit_Command25_Click
    
End Sub
Private Sub Command26_Click()
On Error GoTo Err_Command26_Click


    DoCmd.GoToRecord , , acNewRec

Exit_Command26_Click:
    Exit Sub

Err_Command26_Click:
    MsgBox Err.Description
    Resume Exit_Command26_Click
    
End Sub
Private Sub Command27_Click()
On Error GoTo Err_Command27_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Command27_Click:
    Exit Sub

Err_Command27_Click:
    MsgBox Err.Description
    Resume Exit_Command27_Click
    
End Sub

Private Sub Location_BeforeUpdate(Cancel As Integer)

End Sub

Make Sense? I hope so (-:

 

[mck144]

How did you create the combo box? How many columns are in your combo box both visible and not visible? to view this go to properties for the combo box, under the format tab look at the column count. If the column count is greater than 1, you need to have the 'bound column' to the delivery place and not the identifier. To change this go to the data tab and change the bound column and make sure limit to list is set to no.

-Mike

 

[mck144]

I found a round about way to solve your problem.

Add a text box to your form. If the item is not in your combo box have the user enter it in the text box then use an even to add the item to the list. To prevent mistakes you should use a command button. Here is a sample

Private Sub Command1_Click()
comboboxname.AddItem(textboxname)
End Sub

by default it will be added to the end of the list

There may be some additional pieces to this but this should get you closer.

-Mike

 

[fneily]

This is taken from a form I created. Notice I placed a Requery of the city combobox on both the AfterUpdate for the combobox and the AfterInsert of the form. Just follow this example.

Private Sub city_AfterUpdate()
Me![City].Requery
Me![State].Requery
Me![State].SetFocus
Me![State].Dropdown
End Sub

Private Sub Form_AfterInsert()
Me![City].Requery
End Sub

See, when a combobox is create, it initializes itself with the underlining source, be it a table or query. When you type in something not in the initail list, you must do a Requery, a reinitialization, so the combobox recognizes the new piece of data.

Neil

 

[t5amec]

In view of all the responses with various coding and the like, it is surprising that I still haven't managed to get the thing to work!

Is it possible to write an item into the combo box which isn't already in there, and for it to stay in there so I can select it again, without having to type it all in?

If so, how is it done?


Make Sense? I hope so (-:

 

[KenReay]

Hi

From Access Help

NotInList Event — Event Procedures


To create an event procedure that runs when the NotInList event occurs, set the OnNotInList property to [Event Procedure], and click the Build button .

Syntax

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

The NotInList event procedure has the following arguments.

Argument Description
controlname The name of the control whose NotInList event procedure you want to run.
NewData A string that Microsoft Access uses to pass the text the user entered in the text box portion of the combo box to the event procedure.
Response The setting indicates how the NotInList event was handled. The Response argument can be one of the following intrinsic constants:
Constant Description
acDataErrDisplay (Default) Displays the default message to the user. You can use this when you don't want to allow the user to add a new value to the combo box list.
acDataErrContinue Doesn't display the default message to the user. You can use this when you want to display a custom message to the user. For example, the event procedure could display a custom dialog box asking if the user wanted to save the new entry. If the response is Yes, the event procedure would add the new entry to the list and set the Response argument to acDataErrAdded. If the response is No, the event procedure would set the Response argument to acDataErrContinue.
acDataErrAdded Doesn't display a message to the user but enables you to add the entry to the combo box list in the NotInList event procedure. After the entry is added, Microsoft Access updates the list by requerying the combo box. Microsoft Access then rechecks the string against the combo box list, and saves the value in the NewData argument in the field the combo box is bound to. If the string is not in the list, then Microsoft Access displays an error message.


Remarks

You can create an event procedure for the NotInList event that provides a way for the user to add a new item to the combo box list. For example, you can add a record to the table that supplies the list's values, or add a value to the value list that is the source for the combo box list.

To add a new entry to a combo box list, your event procedure must add the value in the NewData argument to the source of the combo box list. How you do this depends on the type of source the combo box list uses, as determined by the RowSourceType and RowSource properties of the combo box. In the Example in this topic, the event procedure adds the new value to a value list for the combo box.

If you let the user change the value originally typed in the combo box (for example, in a custom dialog box), you must set the combo box value to the new entry entered in the custom dialog box. This saves the new value in the field the combo box is bound to. Set the Response argument to acDataErrContinue, and Microsoft Access will add the new value to the combo box


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK