Re: Return ID value of inserted record, and populate text box
- From: "Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxx>
- Date: Wed, 12 Aug 2009 09:18:21 -0400
See whether it makes a difference using
Dim rsSTUDENT_ID As ADODB.Recordset
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"gsnidow via AccessMonster.com" <u33475@uwe> wrote in message
news:9a7c664b5809c@xxxxxx
Greetings everyone. I am using Access 2003. I am using a popup form to
run
a SQL command to insert a new record into table "STUDENTS", having PK
"STUDENT_ID", which is an autonumber field. No problems with inserting
the
record. My problem is that I need to return the value of the new
STUDENT_ID
for use back on the form. I did this with a .ADP and SQL Server a while
ago,
so I thought I would simply try to copy, paste, and change object names,
but
it did not work. Mabye because this is .MDB? The popup has fields
"txtFirstName", "txtLastName", "txtDOB", and "txtSTUDENT_ID". I want to
populate txtSTUDENT_ID with the new STUDENT_ID after the insert, and the
below is what works with .ADP and SQL Server, but it does not work for
this .
MDB. When it breaks, the line rsSTUDENT_ID.OPEN is the problem, with
.OPEN
highlighted. The error message says "Compile error: Method or data member
not found". So, then I typed it again, so the box upened up after the
".",
and "OPEN" was not an option, but "OpenRecordset" was an option, so I
tried
that, but its asking for "type", and I don't know what it should be. Can
anyone help fix this, or preferably, suggest a simpler method to do what I
need to do? Thank you.
Greg
Dim cn As ADODB.Connection
Dim rsSTUDENT_ID As Recordset
Dim strSQLSTUDENT_ID As String
strSQLSTUDENT_ID = "SELECT STUDENT_ID AS STUDENT_ID " & _
"FROM STUDENTS s " & _
"WHERE s.stu_name_firt = '" & Me.txtFirstName.Value & "' " & _
"AND s.stu_name_last = '" & Me.txtLastName.Value & "' " & _
"AND s.stu_birthday = '" & Me.txtDOB.Value & "';"
Set rsSTUDENT_ID = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsSTUDENT_ID.Open strSQLSTUDENT_ID, cn, adOpenForwardOnly,
adLockOptimistic
Me.txtStudent_ID = (rsSTUDENT_ID("STUDENT_ID"))
Me.txtUser.Requery
--
Message posted via http://www.accessmonster.com
.
- Follow-Ups:
- Re: Return ID value of inserted record, and populate text box
- From: gsnidow via AccessMonster.com
- Re: Return ID value of inserted record, and populate text box
- References:
- Return ID value of inserted record, and populate text box
- From: gsnidow via AccessMonster.com
- Return ID value of inserted record, and populate text box
- Prev by Date: Return ID value of inserted record, and populate text box
- Next by Date: Option Group - Clear Yes/No
- Previous by thread: Return ID value of inserted record, and populate text box
- Next by thread: Re: Return ID value of inserted record, and populate text box
- Index(es):
Relevant Pages
|
