Full Version: Adding New Values To A Table Using A Combobox
UtterAccess Forums > Microsoft® Access > Access Forms
kwh
Hi there,
I have a form called fStaffboth which show a database listing of all the staff at the top and then specific information for each staff member on the bottom.
The main additional piece of information is training information. Each staff member can take many classes of different types on different dates. Or they can take the same class twice (2 different dates).
I have already pre-populated the training type table with the relevant training types. I would like to build the training dates table as staff training is entered. Many staff members can take one class on the same day. I don't want to pre-populate this data.
I am not sure how to do it though. When I attempt to change the limit to list property of the combobox I get an error.
"Cannot set the limit to property to No right now.
The first visible column, determined by the ColumnsWidth property isn't equal to the bound column.
Adjust the ColumnsWidth property first and then set the LimitTo property"
The combobox is bound to the first column which has the training date ID but there are 2 columns in the combobox. First is the training date id (width = 0" since I don't want the user to see this) and then the training date (width= 1").
I'm not sure what I need to adjust to make this work.
Can you help me?
thanks
karen
pere_de_chipstick
Hi Karen
Are you trying to add new items that are not currently in the list to the combo boxes row source table?
If so, try the Not In List Event (This post in the UA code archive may help)
kwh
Yes that is exactly what I want to do. I'll try your code and let you know if I have any additional questions.
Thank you
karen
strBean
If you have prepopulated a table with training dates, and you have set this table as the RowSource of the combo, why do you need to allow users to enter values that aren't in the list?
The error happens because if you're allowing users to enter new values in a bound combo, they have to be entering the value that goes into the field. If the first column is an ID field and the user is typing a date, where will the database get the value for the ID field?
If you want a Table Row Source for the combo, and you want users to be able to add values, AND you want to hide the bound column, you should just put a button or other control on the form for that purpose, and use DoCmd.RunSQL to insert the new record...
kwh
It is not possible to know all of the training dates that a user will need. The trainingdateID is an autonumber field. The table just has the ID and the date.
If the trainingdateid was unhidden is there a way to get it to use the autonumber feature?
Is the AddNewToList a standard command in access?
I'm still a bit confused on how to make this work.
strBean
Does the table with the stored training dates have other fields in it besides the date and the ID?
kwh
No. The trainingdateID is used in another table that contains the specifics about the training. Date is just one of them.
pere_de_chipstick
Hi Karen
The 'AddNewToList' is not a standard Access function, but a function you need to add to a code module.
With the Navigation Pane open and with the standard Access Ribbon, use the
Ribbon Tab: Create; Group: Macros and Code; Command: Module
This will open the VBA editor window, where you can paste the complete "Public Function AddNewToList" code
When pasted, use the command Menu: Debug, Command: Compile [your db name] to debug the code and save the module using the 'Save' Icon, naming it as (e.g.) modComboNILCode
hth
strBean
I think I discovered what was confusing me about your question.
For what you're doing, I think you need to leave the LimitToList property set to
kwh
Thanks Bernie,
I am still a little confused. The code
Private Sub cboCities_NotInList(NewData As String, Response As Integer)
Response = AddNewToList(NewData, "tblCities", "City", "cities")
End Sub
Why do you need City and Cities (singular and plural)?
I have to use this in another place in my db as well so I might have more questions later.
thanks
karen
pere_de_chipstick
Hi Karen
#39;City' singular is the field name in the table 'tblCities' to which the NewData will be added
The plural is there simply to make the message grammatically correct; in the AddNewToList code there is a message box:
CODE
strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
                 "Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
                 "(Please check the entry before proceeding)."

the resultant warning message would be (e.g.)
'cities' is a good example as it is not simply the singular with an 's' added
hth
kwh
Ah that makes perfect sense. Thank you.
kwh
It did exactly what I needed and was very simple to implement. I even made a window pop up to enter the data.
Thanks so much
kwh
Sorry one last question. The message box that pops up has yes and no command buttons. That is fine but how do I set the Yes one to be the default button. Right now it has no pre-selected. When you just hit enter it assumes no.
UPDATE:
Ofound the place to change the default button
pere_de_chipstick
Hi karen
Just to confirm, are you using the command format with the optional form parameter
Response = AddNewToList(NewData, "tblCities", "City", "cities", "frmNewCity")
to open a form (here 'frmNewCity') to enter additional data rather than just the city name?
In the line
If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
remove the + vbDefaultButton2
Oprefer to use + vbDefaultButton2 because the user can't 'accidentally' confirm the action - s/he has to deliberately move the focus to the 'Yes' to confirm the addition.
hth
kwh
That helps a lot.
pere_de_chipstick
Hi Karen
Glad I could help, but are you using the Combo box "NotInList" Event or are you using a separate command and input form to enter the 'NewData'?
kwh
I was able to get my staff data to use the module correctly. It opens a form for the user to enter additional data and then successfully updates the staff table and the combo box.
I am having problems when using the module for a date.
Odisplay the date on the form in YY/MM/DD order. It is in the table as MMDDYYYY.
The following is the SQL I use to change how the date is displayed.
SELECT tblTrainingDates.TrainingDateID, Format([tblTrainingDates.TrainingDate],"yy/mm/dd") AS TheTrainingDate
FROM tblTrainingDates
Order to get my staff data to use the module correctly. It opens a form for the user to enter additional data and then successfully updates the staff table and the combo box.
am having problems when using the module for a date.
Odisplay the date on the form in YY/MM/DD order. It is in the table as MMDDYYYY.
The following is the SQL I use to change how the date is displayed.
SELECT tblTrainingDates.TrainingDateID, Format([tblTrainingDates.TrainingDate],"yy/mm/dd") AS TheTrainingDate
FROM tblTrainingDates
ORDER BY Format([tblTrainingDates.TrainingDate],"yy/mm/dd") DESC;
I am using the OnNotInList combo box event to trigger the module when the user enters a training date that isn't in the table.
The date is being passed to the module as YY/MM/DD. The module appears to work correctly but when it exits I get the error message "The text entered isn't an item in the list". I'm sure the problem has to do with the way the data is being passed and updated to the database. The update doesn't give an error but it doesn't update the table either which is what is triggering the error message.
Can you tell me how I can modify the code to make this work?
This is the event procedure code:
Private Sub TrainingDateID_NotInList(NewData As String, Response As Integer)
Response = AddNewToList(NewData, "tblTrainingDates", "TrainingDate", "training dates")
End Sub

And the module code is:
Public Function AddNewToList(NewData As String, stTable As String, _
stFieldName As String, strPlural As String, _
Optional strNewForm As String) As Integer
On Error GoTo err_proc
'Adds a new record to a drop down box list
'If form name passed, then open this form to the newly created record
'Declare variables
Dim rst As DAO.Recordset
Dim IntNewID As Long
Dim strPKField As String
Dim strMessage As String

' Display message box asking if user wants to add the new item
strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
"Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
"(Please check the entry before proceeding)."
If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton1, "Add New Data") = vbYes Then
Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
rst.AddNew
rst(stFieldName) = NewData 'Add new data from combo box
strPKField = rst(0).Name 'Find name of Primary Key (ID) Field
rst.Update
rst.Move 0, rst.LastModified
IntNewID = rst(strPKField)
'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID, , acDialog

AddNewToList = acDataErrAdded 'Set response 'Data added'
Else
AddNewToList = acDataErrContinue 'Set response 'Data NOT added'
End If

exit_proc:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Function
err_proc:
MsgBox "Error in Function: 'AddNewToList'" & Chr(13) & err.Description, , "Function Error"
Resume exit_proc
End Function
Thanks for your help.
Karen
pere_de_chipstick
Hi Karen
You need to reformat the date string you pass to the AddNewToList function to be in the format of your system data, MM/DD/YYYY; try:
CODE
Private Sub TrainingDateID_NotInList(NewData As String, Response As Integer)
    Dim dtArray() as String
    Dim strDate as String
    dtArray = Split(NewData, "/")
    strDate = dtArray(1) & "/" & dtArray(2) & "/" & dtArray(0)
    Response = AddNewToList(strDate, "tblTrainingDates", "TrainingDate", "training dates")
End Sub
Air Code (not Tested)
hth
kwh
It worked perfectly. Thank you so much. I am not much of a programmer.
karen
pere_de_chipstick
After a while VBA programming becomes much easier! and there will always be some one here at UA to help if you need.
Good luck with your project.
kwh
Thanks - this group has been invaluable.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.