writing to table from form

[Joanne]

I am using Office 2003 on WinXP

My database has this table TblMain
2 Columns in table, Name and Location

The form has a cboName and cboLocation

Choices in cboLocation are determined by value in cboName

When the choice I want is not in cboName, I want to be able to put it
in the cbobox and have it write to the table, and then do same in
cboLocation.

I have property 'LimitToList' set to No, and bound col1 for cboName
and bound col2 for cboLocation

In the 'On NotInList' event on cboName I have this snippet of code
that I got from 'The Access Web' (Great place for us newbies)

Private Sub cboName_NotInList(NewData As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMain", dbOpenDynaset)

rs.AddNew
rs!cboName = NewData
rs.Update
End Sub

This lets me type a new Name in the cboName control, but it will not
write the newData to the table. I can't figure it out. I think I have
the properties properly set. After entering the new name, I tab to
the next control, then use navigation buttons to go to the next record
thinking that this will force the write to table, but it does not do
it.

Do I need to put a command button on my form to allow me to write to
the table if I need to add new data? That's the only other thing I
can think of - not really the way I would like to do it but I can if
need be.

As I read thru this note, I am thinking the problem is in the line
rs!cboName = New Data - isn't this failing to tell the app to write to
the table, instead just making the value of the cbobox equal to the
newData? If so, what should it say please?

Thanks for your help on this
Joanne

 

[Klatuu]

Your post is a little confusing, but I think the problem is the names you are
using. You say the field for the name in your table is Name (This is a
reserved Access word and should not be used as a field name, it can confuse
Acces), but in your code, you are using the name of the control on the form.
I don't know why this isn't rasising an error.

rs.AddNew
rs!cboName = NewData
rs.Update

What you want to do in this case it to create a record in the table, then
make that the form's current record. What you have to do is after you write
the data to the table, you have to requery your form because that record is
not in the form's record source yet. Before you do the requery, you have to
know how to get to that record to make it the current record. Here is a
rewrite that should fix it.

Private Sub cboName_NotInList(NewData As String)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO tblMain ([Name]) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Me.frmSubAttributeTable.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Name] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboName.Undo
Response = acDataErrContinue
End If
End Sub