Add to drop down list and error

[Guest_imported]

Good day,

I am using MS Access 2003 (VBA) running on MS Windows XP.

I created a drop-down combo box using the wizard in a sub form. The associated table has one field [FileLocation] and a primary key. The drop down list contains partial path names for document locations. I have the following code in the AfterUpdate() event procedure.

Private Sub Combo4_AfterUpdate()

Select Case Combo4

Case 1
Me.Combo4 = "C:/pickapath/"

Case 2
Me.Combo4 = "D:/otherpaths/"

End Select

'populate MainForm control
Forms!FrmMain!txtLocation = Me.Combo4

End Sub

I have two issues I can't seem to resolve. The first is that whenever a selection is made, it results in an error message that reads: The value you entered isn't valid for this field". The field is text with 100 size. The partial path name is successfully placed in the control when I select the OK button on the error message. It is also passed correctly to MainForm. How can i get rid of the error message?

second issue: How can I make it so the user can add to the list? I saw the AddItem in the help files but isn't there a way to automate this for the users? I was hoping that if a user types a partial path name that is not in the list, a message box could come up and ask if they wanted to add it. then when they click Yes, it would automatically be added to the list. I searched the Tek-tips for this issue but could not find it.

I am new to Access and VBA so detail and examples are much appreciated. Thank you. I appreciate your help and time.

55

 

[PHV]

1) What about this ?

Private Sub Combo4_AfterUpdate()
'populate MainForm control
Forms!FrmMain!txtLocation = Me!Combo4.Column(1)
End Sub

2) Have a look at the NotInList event and the LimitToList property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Guest_imported]

Thank you, Thank you, Thank you!
I need to tackle the "LimitToList" property next.
Thank you for your time.
55

 

[Guest_imported]

The LimitToList property information is helpful but i'm not quite getting it right. In the On Not in List event procedure I have the following code.

Private Sub Combo11_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
' Return Control object that points to the combo box.
Set ctl = Me!Combo11
' Prompt user to verify they wish to add the new value.
If MsgBox("Add '" & NewData & "' as a new path?", vbOKCancel) = vbOK Then
Response = acDataErrAdded 'Suppress the default error message.

' Set Response argument to indicate that data is being added.
'Open a recordset of the Table TblLocation.

Dim db As Database
Dim rstCombo11 As Recordset
Dim sqlTblLocation As String
Set db = CurrentDb()

sqlTblLocation = "Select * From TblLocation"

Set rstCombo11 = db.OpenRecordset(sqlTblLocation, dbOpenDynaset)

'Add new data with the value that is stored in the variable NewData.

rstCombo11.AddNew

rstCombo11![Combo11] = NewData

rstCombo11.Update

'Inform the combo box that the desired item has been added to the list.

Response = acDataErrAdded

rstCombo11.Close 'Close the recordset
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo

End If

End Sub

Please help me get it working. the following line is highlighted in yellow when i try and run it. There is no error message. just can't figure it out. I don't know what else to try.
rstCombo11![Combo11] = NewData

Thank you. your help and time are much apprecited.
55

 

[PHV]

I'd try something like this:
rstCombo11![FileLocation] = NewData

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Guest_imported]

Thank you PH,

I have not been able to get it to work but I wanted to thank you for your help. I may have to put my new code here if I can't get it running. |

55