Combo box auto complete?

[zeegoff]

Hi guys I've been trawling the net for hours and cant find anything so I thought I'd try here to see if anyone has a solution for me.

I've just picked up access this week and have designed a form that helps us produce job sheets in my place of work, the form works as it should I'm just trying to make it a little more efficient really. Im trying to find out if there is a way to autocomplete a field? I know autocomplete works with combo boxes but only if you specify either a preset list "listbox" or a table query which shows the data in the current table.

What I want to do is be able to type new data into the drop down, have that info entered into the table and the drop down refresh so the auto complete will work for that client in future records?

Is this at all possible? I'm very much new to access and would appreciate any help.

 

[pbaldy]

Search here on "notinlist" which should turn up code examples. They use the Not In List event of the combo to add the new data to the underlying table.

 

[zeegoff]

Thanks I've gotten a little bit further, found this code and edited it to apply to my combo box and to pull up the right form, I get the "would you like to add" to add message, click yes but then get the same not in list error??

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

On Error GoTo Err_cboDepartments_NotInList

Dim intAnswer As Integer

intAnswer = MsgBox("Would you like to add this value to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "frmDepartments", acNormal, , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Exit_cboDepartments_NotInList:
Exit Sub

Err_cboDepartments_NotInList:
MsgBox Err.Description
Resume Exit_cboDepartments_NotInList

End Sub

 

[pbaldy]

Is the record added at that point by the other form? In my template code (can't find a working db using it right off), I have it like this (the pop up form would only be hidden at this point):

Response = acDataErrAdded
DoCmd.close acForm, strForm

 

[zeegoff]

There's only one form, I'm getting the drop down fields from a "clients" column in my table, and I'm trying to add on the fly to the dropdown? have no programming experience unfortunately!

 

[pbaldy]

Your code is opening a different form, "frmDepartments". That's the one I meant. If you don't need to open a different form, you'd want a different method. A separate form is used when you need to add more than just the value typed into the combo.

 

[zeegoff]

Really appreciate the help, I know its probably not the easiest advice you've given! would it be easier for me to host a copy of the database for you to download? If its not a simple fix its no problem its more of a time saver than a necesity.

 

[pbaldy]

You should be able to post it here:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[zeegoff]

I think i've done this right? Ideally we'd like to be able to enter new clients into the dropdown and have them auto complete if we have to input them again. Any advice or comments good or bad are more than welcome.

Cheers!

 

[pbaldy]

You have the code on the PreparedBy field, not the client field. Is that what you want? I would expect tables to be holding those values. With a single table like this, perhaps all you want is to set the combo's limit to list property to No. That will let the user add a value that isn't already there. You don't at this point have a another table to add the value to, so the limit to list code isn't really relevant.

 

[zeegoff]

Sorry for the delay, I'm over in the UK its just morning over here. I was playing round with an existing drop down it is the client field that we want to get working, its not a problem having another table if thats whats needed but would that mean populating a list manually rather than on the fly?

 

[pbaldy]

I know all about the time difference; my daughter lives in the UK.

In "typical" database design, you would have a separate table for clients, with fields for a client ID, name, address, etc. You might also have a similar table for employees. Then in your transaction table, you would just store the client ID and the employee ID. On your transaction form, you would use combo boxes that got their selections from those other tables, but stored the ID in the transaction table. In that scenario, you could use the Not in List event to add new clients/employees. You can do it the way you have it, it's just less common (and presumes you don't need to store any other info about either).

 

[zeegoff]

I know its pretty simple, I've never used access before about a week ago, no really sure of typical layouts etc. The easiest way to get this working is probably the best? The only reserve I have is we dont want to have to populate the second table manually, ideally we'd like it to add new clients on the fly when entering into the job sheet as some of out clients have very long names, the autofill feature of the combo box could eventually save a lot of time. Hopefully anyway!

 

[josros60]

Listbox auto complete?

I have tab listbox with tag button to select vendor to print file folder label,

how can i make the list auto complete when start typing a letter.

thanks.