Add a combo box to select from a list

[Sonny101]

Hello

I have created a very basic form but I want to add the following capabilities.

1- Basically in place of just a text box for the technician name field, I want to add a combo box, which looks up a certain set of predefined names and adds that name for a particular entry and will store that entry for that name in the table, and an easy method to add names to that predefined names( in the combo box) in the future. Plus I should be able to use the search tool for this field to look for a certain name in multiple entries.

2- For that particular name I want to plot a sort of graph which plots the hours worked over a certain period (keep in mind that there would be multiple entries for that name).

So any ideas on how I could accomplish this? Any help would be appreciated


Regards

PS I have attached the form I have been working on(The form I have attached doesnt have a table to it, because the one with the table was too big to upload)

[June7]

Database without data to test and analyze is useless. Why not delete records for posting db?

1. combobox RowSource list can be restricted. Use NotInList event of combobox to add new records to the lookup table 'on the fly' http://www.databasedev.co.uk/not_in_list.html

2. I expect this would be possible but I need data

[Sonny101]

OK so basically I understand the code, but and made a few modifications as necessary such as replacing "tblBookCategories" to "Table1"(which is the name of my table), and the name of the combo box, but I couldn't understand what "strBookCategory" is. Plus I dont know how to link the code for combo box to a certain field in my table (Technician Name). Or is it done through linking it with "Control Source" in properties?

[June7]

strBookCategory is fieldname - the field to save the NewData value to.

Table1 is not an informative name.

A combobox is a data control and can be bound to a field with the ControlSource property.

[Sonny101]

"Private Sub cboBookCategory_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Name...")
If i = vbYes Then
strSQL = "Insert Into tblBookCategories ([strBookCategory]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
"

This was the code on the example.

- so lets say the field name in the table is Technician Name, so that would change to strTechnicianName ? doesn't str stand for string? because the field "Technician Name" is in default text category
- Can you elaborate on that? what do I need to change it to lets say my table name is Table1

[June7]

If the field name is [Technician Name] then that is what you use in place of strBookCategory. Just as you use Table1 in place of tblBookCategories. The str and tbl prefixes were naming conventions used by the code author.

The Sub declaration line will be named for whatever the name of your combobox is.

[Sonny101]

"
Private Sub Combo291_NotInList(NewData As String, Response As Integer)


Dim strSQL As String
Dim i As Integer
Dim Msg As String


'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub


Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"


i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Name...")
If i = vbYes Then
strSQL = "Insert Into Table1 ([TechnicianName]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
"
Wrote this code and got the error (attached), WITHOUT first linking the ControlSource property as blank, next did it with ControlSource property to "Table1.Technician Name" got the same error followed by the generic error of the "text you eneterd in not the item on the field."

I have uploaded the form with the table behind it, if you could look at it it would be great

https://app.box.com/s/fdkcc3jiblaaa9dwpmvr


PS Not a typo I have just made sure

[June7]

This db could have been zipped and attached to post in forum.

Not finding any combobox on either form. The code you posted is not in the db.

There is only one table in the db. Why no table for technicians?

[wes9659]

Hello
I looked over your project and there was a typo you had TechnicianName in string but not on actual table. I changed them completely so easier for you to review. I have attached your update at link below of project. https://www.dropbox.com/s/j93lg8h997...boBoxFix.accdb this forum has taught me so much. My biggest enemy has been simple typos Good Luck

[wes9659]

Hi Sonny

I did one last update to the message boxs that appear lol wife caught it Thought it looked better and we all know a happy wife = a happy life here is link to new one
https://www.dropbox.com/s/hk4bxgzy4g...x%20Update.mdb

[Sonny101]

Hi Sonny

I did one last update to the message boxs that appear lol wife caught it Thought it looked better and we all know a happy wife = a happy life here is link to new one
https://www.dropbox.com/s/hk4bxgzy4g...x%20Update.mdb

Thanks for the effort man, though cannot open it as I have ancient version of access(2003) . If you could just convert it, that would be great

[Sonny101]

This db could have been zipped and attached to post in forum.

Not finding any combobox on either form. The code you posted is not in the db.

There is only one table in the db. Why no table for technicians?

Yeah sorry attached the wrong file

[June7]

What you have is a single table that is for data and also the source of the technicians listed in combobox dropdown. Conventional structure would be another table for Technicians where the technician is a unique record and TechnicianID would be saved into data table. It is Technicians table that code in the NotInList event would add record into. With this one table structure the NotInList event is basically useless. Just set the combobox LimitToList property to No and user can enter whatever they want. The combobox is an aid for the user to select existing technician but if technician not in the list, they just enter the new name. However, the new name will not show in the list until form is reopened or run code to requery the combobox. So have code in combobox AfterUpdate event: Me.Combo291.Requery

The combobox RowSource would be: SELECT DISTINCT [Technician Name] FROM Table1 ORDER BY [Technician Name];

If you decide to use a lookup table for technicians then we can talk about the NotInList event code.


Form RecordSource can simply be: Table1