OnNotInList event

[SamMaynard]

I'm brand new to Access, but am medium accomplished in Excel VBA. So anyway I do understand some programming and I believe the answer to this question will require programming. (if no programming needed, even better)

I have a table called StudentInfo with a field called Major. The Major Field in the StudentInfo table is a combo box that looks up feild values in another table called Major. The two fields of the two tables are related with referential integrity enforced.

This is what I want to do...

...When a user inputs a value in the Major field of the StudentInfo table that is not currently in the Major table I want that new value automatically inserted in the Major table. Make sence?

Any help would be appreciated. Thanks, Sam

 

[Tanis]

Use the On Not In List event of the combo box. Change the Limit to List property to Yes and add this code to the On Not In List event procedure. You will need to change FieldName and TableName to your own.

Private Sub cboFieldName_NotInList(NewData As String, Response As Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not a current name in the list. "
strMsg = strMsg & " Do you want to add it to the list?"
strMsg = strMsg & "@Click Yes to add it or No to retype it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!FieldName = NewData
rs.Update

If Err Then
MsgBox "Error. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub

David

 

[SamMaynard]

I'm having a couple of problems with this code.

1. the dimension db as Database is not recognized. So I changed it to Varient.

2. the set rs expression is "Nothing" and I get an invalid argument message from Access.

Any Ideas?

Thanks, Sam

 

[parry]

Hi Sam, the problem with these errors is because this code requires a reference to the DAO library. In the VB Editor select Tools-References and place a tick against a library called Microsoft DAO 3.6 Object Library.

A sure sign this isnt in your library is where "as database" causes an error.

 

[SamMaynard]

Thanks for the reply. I did as you said and now there is a new issue. With the expression Set rs = db.OpenRecordset(tblCompany, dbOpenDynaset) I get an error message that says can't find my table. The table is there and is spelled correctly? Any Idea. Do I need " " around the table name? Thanks a ton for helping me. Sam

 

[SamMaynard]

PS I put " " around the table name and get a type mismatch error

 

[SamMaynard]

I figured it out. I changed the rs to Varient. That did it. Thanks to all who helped so much. I truly appreciated it. Couldn't have done it with out cha.