INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Not In List find Item in combobox and run afterupdate code

VBA Not In List find Item in combobox and run afterupdate code

(OP)
Thought this would not be tricky, maybe overlooking something. (Hope I'm using the bound terminology properly)
I created a unbound combobox in the header of a form and code in the after update event adds new record using values from all the cols in the combobox. This works well for the bound field. I thought I would extend the capability without having to create another combobox or button on the form, for cases where the user knows the eventcode (another field in the combo) rather than the acro (bound field in the combo), I put code in not in list event which does a dlookup to find the acro based on the eventcode. I tried to get that value back into the combobox and rerun using that, didn't work --combobox remained blank. I was successfully able to pass the eventcode to the after update directly (commented out), which worked for that piece of data, but since the other fields are derived from the combobox and the combobox was not set to the acro, the other fields are null.

CODE -->

Private Sub cboAcro_NotInList(NewData As String, Response As Integer)
'If entering an event code rather than acro,
'try to search for that instead
'20170310
    If Not IsNull(DLookup("Acronym", "Courses_Data", "[EVENTCODE] = " & "'" & NewData & "'")) Then
        Me.cboAcro = DLookup("Acronym", "Courses_Data", "[EVENTCODE] = " & "'" & NewData & "'")
        Me.cboAcro.Requery
        'Call DisplayProduct(NewData)
    End If
    
End Sub 

RE: VBA Not In List find Item in combobox and run afterupdate code

Since you have an "unbound combobox", wouldn't it just be:

CODE

Private Sub cboAcro_NotInList(NewData As String, Response As Integer)
'If entering an event code rather than acro,
'try to search for that instead
'20170310
    If Not IsNull(DLookup("Acronym", "Courses_Data", "[EVENTCODE] = " & "'" & NewData & "'")) Then
        Me.cboAcro.AddItem DLookup("Acronym", "Courses_Data", "[EVENTCODE] = " & "'" & NewData & "'")
        'Me.cboAcro.Requery
        'Call DisplayProduct(NewData)
    End If
    
End Sub 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: VBA Not In List find Item in combobox and run afterupdate code

(OP)
Hi Andy, I didn't consider using additem because when I read about it, I thought that adds an item to the list. I am not adding an item to the list as it already exists. Are you saying that the add item will find the existing item? I'm trying to use the combobox for double duty so it's only a not in list event from the standpoint that what I type in is not in the searchable part:

Sample data in the combo:

ABC | 123 | How to find a value | 03/10/2017
ACD | 124 | How to lose a value | 03/10/2017
...
...
ZZT | 212 | Find the last value | 10/03/2017



For example, let's say I type in "A" Then "C", it will select the row containing ACD. Then the after update runs and takes the data from Column(1), Column(2), Column(3) and appends to data table.

Second example, let's say I know the code 212, but I don't know that its letter is ZZT. I type 212 this fires the not in list event, however, since 212 is already there I wouldn't be adding it, but need to get that to be the selected Item and then execute the after update code to pull the Column(x) data elements.

If not doable,I guess I'll try creating a recordset to gather the data in order to replicate what the after update code does while inside the notinlist event.

RE: VBA Not In List find Item in combobox and run afterupdate code

(OP)
After doing some more searching, seems that NotInList won't do what I'm after. Saw another site that suggested the Beforeupdate. http://www.fontstuff.com/mailbag/qaccess03.htm
In order to use that, it said LimitToList must be set to No and when I did that, Access refused to change it. Even after I changed the bound col to a visible field per the message. So gave up on that for now.

One of the sites referenced this link as the solution for limittolist=no, but clicking on it came up blank
http://www.microsoft.com/communities/newsgroups/en...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close