Using Addnew, recordset to add to field other than Primary(autonumber) 1

[idd]

Ok, so I have looked High and low to find an answer to my problem. I'm fairly new to using recordsets.

Maybe, or more than likely, in the wrong places.

I am an Access 2000 Developer.

I have a form for entering course details with the underlying table

TBL_Course.

The form which is used to enter data is

FRM_Course

On it there is a combo box which is used to display the tutors name. it uses the tutor ID as the bound data for the combo box. The tutor data is sourced from the table

TBL_Tut

What I want
To be able to programmatically add a new tutor to the underlying tutor table of the combo box, when a new name is typed which is not in the list.

The underlying table only has these two fields. Tutor_No (autonumber) and Tut_Name (text)


The problem
It gives me an error message when this code runs on the not in list event of the combo box.

the error is type mismatch, I think its trying to add this newdata to the autonumber field. How do I get it to fill in the autonumber and enter in my text(newdata) as the tutors name.

the code I am using is given below.

-------------------
start of code
------------------------------


Private Sub Tutor_NotInList(NewData As String, Response As Integer)
Dim msg As String
Dim rs As Recordset
Dim db As Database

msg = NewData & " is not a tutor held in the database" & vbCr & _
"Would you like add " & NewData & " to the database" & vbCr & vbCr & _
" Click Yes to Add or NO to Re-Type"

If MsgBox(msg, vbYesNo + vbExclamation, "Unknown tutor name " & NewData) = vbNo Then
SendKeys "{esc}"
Response = acDataErrContinue
Exit Sub
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("TBL_Tut", dbOpenDynaset)
rs.AddNew
rs!Tut_Name = NewData
rs.Update
Response = acDataErrAdded
Set db = Nothing
rs.Close
Set rs = Nothing
db.Close
End If

End Sub



any help would be appreciated.

And if i'm doing the close rs and db thing wrongly could someone let me know. I'm just guessing that it should be the way I have done it i.e.

Set db = Nothing
rs.Close
Set rs = Nothing
db.Close

Thanks in advance

Idd

 

[mp9]

I know you're keen to use recordsets but really, for adding one record to a table, why bother? It would be much easier to just use SQL to perform an INSERT INTO query, something like:

CurrentDB.Execute "INSERT INTO TBL_Tut (Tut_Name) VALUES ('" & NewData & "');"

Note that the above code replaces everything in the ELSE part of your IF-THEN-ELSE loop.

Hope this helps.

http://www22.brinkster.com/accessory/

 

[idd]

MP9,

because I have no idea about how to use the SQL commands in Access, Looking at its use in Queries I get dazzled, seeing inner joins and outer joins and the like thereof.

Anyway i'll try what you've mentioned above maybe that might get me started on using SQL within vba which I have yet to do.

will this resolve my problem though of the autonumber which I don't tell it to add, but assume that it would add it automatically.

I'll give it a try.

Idd

 

[idd]

Oh My Word,

MP9, you are a star, it worked a treat. so simple and yet so effective.
I kept the

response = AcDataErrAdded

line so that it wont give the default error message of not in list.

WOW thats so neat though

1 line replacing 8 lines of code

Heres the proof to show that I appreciated your help, it will hopefully also alert other people to this beneficial reponse from you.


********************************************************
********************************************************
Your vote for mp9 for TipMaster of the Week has been submitted.

Thank you for letting mp9 know this post was helpful.

********************************************************
********************************************************

Idd