By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Access Combobox Match found?

TheSmileyCoder
Expert Mod 100+
P: 2,321
I have a combobox on my form, which has its MatchRequired set to false. Basicly this means you can write new selections into the combobox. The rowsource of the combobox selects (distinct) all previous entries of that field in the underlying table.

The aim is no try and make what people write into the field uniform, but not limiting them to a pre-defined list.

My problem is that if a user types a new value that doesn't exist in the underlying query, I need to requery the combobox before it becomes available for the next record entry.

I would like to know in the Combobox before_Update event, whether or not a match was found, and if not, requery the combobox in the afterupdate event.
Sep 3 '12 #1
Share this Question
Share on Google+
4 Replies


zmbd
Expert Mod 5K+
P: 5,287
TheSmileyCoder ...
Is it possible to use the "notinlist event" for what you are trying to do...

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
This is for V2003; however, is still valid in V2010

-z
Sep 3 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
As I read your post I initially thought *Facepalm* but then I realised that the "not in list" event only fires if the combobox is set with "Limit to list" as true. To maintain a list in the way you suggest I would need to add the value to a seperate table, in which case its true I could limit it to list, and have the not in list event add the value to the table. However I was hoping to not have to add an extra table.


I also soon after realised that it is no use to requery the value in the combobox's afterupdate event, since the value will not yet have been written to the table, it doesn't get written to table until the whole record is saved.
Sep 3 '12 #3

zmbd
Expert Mod 5K+
P: 5,287
TheSmileyCoder

This is a modification of some code I found some years ago, see if it works for you.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cbo_exampleupdatelist_NotInList(NewData As String, Response As Integer)
  5. Dim z_ctl As Control
  6. Dim z_YesNo As Integer
  7. '
  8. 'Setup to handle the control
  9. Set z_ctl = Me.cbo_exampleupdatelist
  10. '
  11. 'Verify that the user is entering a new value:
  12. z_YesNo = MsgBox("The value:" & vbCrLf & NewData & vbCrLf _
  13.     & "Is not currently in the list." & vbCrLf _
  14.     & "Would you like to add it?", vbQuestion + vbYesNo + vbDefaultButton2, _
  15.     "Verify Data Entry")
  16. If z_YesNo = vbYes Then
  17.     ' Set Response argument to indicate that data
  18.     ' is being added.
  19.     Response = acDataErrAdded
  20.     ' Add string in NewData argument to row source.
  21.     z_ctl.RowSource = z_ctl.RowSource & ";" & NewData
  22. Else
  23.     'If user chooses Cancel, suppress error message
  24.     ' and undo changes.
  25.     Response = acDataErrContinue
  26.     z_ctl.Undo
  27. End If
  28. End Sub
  29.  
-z
Sep 3 '12 #4

zmbd
Expert Mod 5K+
P: 5,287
Here's another nice set of code...
http://msdn.microsoft.com/en-us/library/office/ff845736

I must have used something from one of the Inside&Out books as my code is fairy close to one listed in the link.

-z
Sep 3 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.