Help Please! MS Access 2000, Anyone? 3

[Nikki2454]

I have created a Students Form where you enter FName, MName, LName, StudentID, password. When I create a new student how can I pass the studentID to another form in order to add new information. I currently have it set up to filter, but that doesn't work with a new record. What am I doing wrong? Here is the code from the Students Registration Form to the Intervention Form.

----------------------------------------------------------
Private Sub cmdintervention_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
If IsNull(Me.StudentID) Then
MsgBox "Please enter a valid user name.", vbExclamation, "Error"
Me.StudentID.SetFocus
Exit Sub
ElseIf IsNull(Me.password) Then
MsgBox "Please enter a valid password.", vbExclamation, "Error"
Me.password.SetFocus
Exit Sub
End If
rs.Open "SELECT * FROM Students WHERE(StudentID = """ & Me.StudentID & """ AND Password = """ & Me.password & """)", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rs.EOF Then
MsgBox "Please Save Your Information!", vbExclamation, "Error"
Me.StudentID.SetFocus
Exit Sub
End If
stDocName = "Intervention"
stLinkCriteria = "[StudentID]=" & "'" & Me![StudentID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , StudentID
DoCmd.Close acForm, "frmStudents", acSaveNo
End Sub

------------------------------------------------

Please Help.

 

[VBslammer]

Set the DefaultValue property for the StudentID textbox using the form's OpenArgs:

Private Sub Form_Load()
  If Not IsNull(Me.OpenArgs) Then
    Me!txtStudentID.DefaultValue = Me.OpenArgs
  End If
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[Nikki2454]

VbSlammer-

Hey I tried that. Output from StudentID textbox = #Name?

 

[vbLew]

Another Way:

Is to create Module, name it something like Properties.

In the module you would write code that would look something like this:
'-----------------Module Code---------------
Private mStudentID as Long

Public Function SetStudentID(newNum as long)
mStudentID = newNum
End Function

Public Function GetStudentID() as long
GetStudentID = mStudentID
End Function
'-------------End of Module Code ---------

So in the first form, you would save the state of the student ID by calling the SetStudentID Function, like this:

SetStudentID( Me.StudentID)

And in the second form, you could remeber the state of the student ID by calling GetStudentID(), like this:
Dim MyID as long
MyID = GetStudentID()

 

[Nikki2454]

my studentid is a string
will this still work?

 

[GingerR]

In trying VBSlammer's method, did you change your Docmd.OpenForm line for a new student? To something like this (no more criteria, and open in ADD mode):

DoCmd.OpenForm "Intervention", , , ,acFormAdd, , Me.StudentID

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

If the ID is a string, try this:

Private Sub Form_Load()
  If Not IsNull(Me.OpenArgs) Then
    Me!txtStudentID.DefaultValue = "=" & Chr(34) & Me.OpenArgs & Chr(34)
  End If
End Sub

Also, make sure the textbox doesn't have the same name as its controlsource (ambiguity issue).

VBSlammer

"You just have to know which screws to turn." - Professor Bob