Hi. Hope everyone is well.
I'm not sure this is possible so I'm here to find out.
I have a combo box on a form with LimitToList property set to Yes. The data for the list is from a lookup table.
Sometimes the user will select an item from the list but then may need to add a little more info to the field. These little adds don't need to be recorded in the lookup table, i.e. it is particular to that record and may not ever be needed again. So I don't want Access to save it.
I have the Not In List Event working correctly essentially, asking if the I want to add what he's entered to be added to the table. If he chooses no, I'd like to have the field 'temporarily' to not be limited to the list and simply move on to the next field.
If I select No on the MsgBox, the standard message displays saying that I need to pick a value in the list, and drops the list open. I can then move to the next field, so it appears that the LimitToList property = False is working.
When I go back to design mode on the form, the LimitToList property is still set to Yes. I wondered if I need to have my code reset it to True after the temporary change to False.
Hope this makes sense and welcome any alternatives. Code is below.
Thanks,
MIB1019
I'm not sure this is possible so I'm here to find out.
I have a combo box on a form with LimitToList property set to Yes. The data for the list is from a lookup table.
Sometimes the user will select an item from the list but then may need to add a little more info to the field. These little adds don't need to be recorded in the lookup table, i.e. it is particular to that record and may not ever be needed again. So I don't want Access to save it.
I have the Not In List Event working correctly essentially, asking if the I want to add what he's entered to be added to the table. If he chooses no, I'd like to have the field 'temporarily' to not be limited to the list and simply move on to the next field.
If I select No on the MsgBox, the standard message displays saying that I need to pick a value in the list, and drops the list open. I can then move to the next field, so it appears that the LimitToList property = False is working.
When I go back to design mode on the form, the LimitToList property is still set to Yes. I wondered if I need to have my code reset it to True after the temporary change to False.
Hope this makes sense and welcome any alternatives. Code is below.
Thanks,
MIB1019
Code:
Private Sub cboProgram_NotInList(NewData As String, Response As Integer)
On Error GoTo ErrorHandler
Dim dbsMM As DAO.Database
Dim rstProgram As DAO.Recordset
Dim intAnswer As Integer
Dim StationName
StationName = DLookup("CompanyName", "tblCompanies", "Company_ID = " & Me.Parent.Company_ID)
intAnswer = MsgBox("Do you want to add '" & NewData & "' to the program list for " & StationName & "?", _
vbQuestion + vbYesNo)
If intAnswer = vbYes Then
' Add program stored in NewData argument to tblProgramLookup
Set dbsMM = CurrentDb
Set rstProgram = dbsMM.OpenRecordset("tblProgramLookup")
rstProgram.AddNew
rstProgram!Program = NewData
rstProgram.Update
Response = acDataErrAdded ' Requery the combo box list.
Else
Me.cboProgram.LimitToList = False
End If
rstProgram.Close
dbsMM.Close
Set rstProgram = Nothing
Set dbsMM = Nothing
Exit_Handler:
Exit Sub
ErrorHandler:
Resume Exit_Handler
End Sub