Return ID value of inserted record, and populate text box

[gsnidow via AccessMonster.com]

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

 

[Douglas J. Steele]

See whether it makes a difference using

Dim rsSTUDENT_ID As ADODB.Recordset

 

[gsnidow via AccessMonster.com]

Thanks for the quick reply. Well, now I am getting a different error, "Data
type mismatch in criteria expression", so I guess that is progress. Could
that be because the value of STUDENT_ID being returned is number, and
txtSTUDENT_ID is text?

See whether it makes a difference using

Dim rsSTUDENT_ID As ADODB.Recordset

Greetings everyone. I am using Access 2003. I am using a popup form to
run

[quoted text clipped - 41 lines]

Me.txtStudent_ID = (rsSTUDENT_ID("STUDENT_ID"))
Me.txtUser.Requery

 

[gsnidow via AccessMonster.com]

Ok, the problem is that I am trying to compare STUDENTS.stu_birthday, which
is a date field, to txtDOB, which is text. So, now my problem is, how to I
convert this line...

'"AND s.stu_birthday = '" & Me.txtDOB.Value & "';"

In TSQL I would want 'AND s.stu_birthday = CONVERT(SMALLDATETIME,me.txtDOB,
101)'

Can you tell me how to do this with VB? Thanks for the help.

Thanks for the quick reply. Well, now I am getting a different error, "Data
type mismatch in criteria expression", so I guess that is progress. Could
that be because the value of STUDENT_ID being returned is number, and
txtSTUDENT_ID is text?

See whether it makes a difference using

[quoted text clipped - 5 lines]

 

[gsnidow via AccessMonster.com]

Ok, not used to querying in Access. I got it to work by changing single
quotes to #...

"AND s.stu_birthday = " & "#" & Me.txtDOB.Value & "#" & ";"

Anyhow, it works, thanks for the help Doug.

Ok, the problem is that I am trying to compare STUDENTS.stu_birthday, which
is a date field, to txtDOB, which is text. So, now my problem is, how to I
convert this line...

'"AND s.stu_birthday = '" & Me.txtDOB.Value & "';"

In TSQL I would want 'AND s.stu_birthday = CONVERT(SMALLDATETIME,me.txtDOB,
101)'

Can you tell me how to do this with VB? Thanks for the help.

Thanks for the quick reply. Well, now I am getting a different error, "Data
type mismatch in criteria expression", so I guess that is progress. Could

[quoted text clipped - 6 lines]

 

[Douglas J. Steele]

In case any user of the database has his/her Short Date format set to
dd/mm/yyyy, you're better off using

"AND s.stu_birthday = " & Format(Me.txtDOB.Value, "\#yyyy\-mm\-dd\#")

(note that the semi-colon at the end is optional)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok, not used to querying in Access. I got it to work by changing single
quotes to #...

"AND s.stu_birthday = " & "#" & Me.txtDOB.Value & "#" & ";"

Anyhow, it works, thanks for the help Doug.

Ok, the problem is that I am trying to compare STUDENTS.stu_birthday,
which
is a date field, to txtDOB, which is text. So, now my problem is, how to
I
convert this line...

'"AND s.stu_birthday = '" & Me.txtDOB.Value & "';"

In TSQL I would want 'AND s.stu_birthday =
CONVERT(SMALLDATETIME,me.txtDOB,
101)'

Can you tell me how to do this with VB? Thanks for the help.

Thanks for the quick reply. Well, now I am getting a different error,
"Data
type mismatch in criteria expression", so I guess that is progress.
Could

[quoted text clipped - 6 lines]

Me.txtStudent_ID = (rsSTUDENT_ID("STUDENT_ID"))
Me.txtUser.Requery