MS Access cmdadd button 1

[Nikki2454]

I have setup a login form that passes the username (studentID) to a students form as well as an intervention form. I am trying to pass the username during an add new record command (button) in the intervention form. What happens...when I login it filters the intervention forms to only show the forms that are entered by the user who is logged in. However, when I am logged in and want to add a new intervention it doesn't pass the username and since it is locked it will not allow you to enter a username. When I save a new record the username doesn't show up in the intervention table. The field is null. Any ideas, please help!

 

[VBslammer]

You can set the [tt]Default Value[/tt] property for the username field at runtime (using vba) to the name of the logged-in user when the form loads:

Me!txtUserName.DefaultValue = Chr(34) & CurrentUser() & Chr(34)

VBSlammer

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

 

[GingerR]

Hi there--this is a little confusing, sorry. Not sure if this is the solution, but you could store the Username/StudentID in a text box (perhaps invisible) on a form that is always open (a 'Switchboard'-like form, if you have something like that?), then refer to that text box throughout your database.

I don't understand what this means:

However, when I am logged in and want to add a new intervention it doesn't pass the username and since it is locked it will not allow you to enter a username.

.

Please explain more. Thanks.



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

 

[Nikki2454]

Ginger.

Sorry for the confusion. In view mode I login using a username and password on the login form then select cmdintervention (button) which takes me to the interventions form but filters the records to only return the ones that match my username (from the students table). I have a field on the interventions form that is locked and shows the current logged in user. code:

stDocName = "Intervention"
DoCmd.Close acForm, "frmLogon", acSaveNo
stLinkCriteria = "[StudentID]=" & "'" & Me![StudentID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Now my question is when I want to add a new record (cmdadd button on my intervention form) how do I pass the username(studentid) to the form? This would be passing the username inside the same form but for a new record.

Right now when I am logged in (in view mode looking at my current (filtered) records in my intervention table) and click on the cmdadd button it does not pass the username. Meaning instead of it reading/displaying the locked username the field returns a null value. I tried entering data in the other fields and saving the record to see if it would update and include the username in the intervention table with the current user, however the field was Null.

I hope this helps.

 

[Nikki2454]

VBSlammer.

I tried the code and am now getting this in the username text field

#Name?

Any suggestions?

 

[VBslammer]

If your field is numeric then you can drop the Chr(34) characters. Sometimes you can get the #Name? error when you're trying to reference a textbox that has the same name as the field it is bound to. As a general rule, I try to name my textboxes with a "txt" prefix to avoid that problem.

VBSlammer

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

 

[GingerR]

If I understand correctly:

change your openform line to this:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.StudentID

to have OpenArgs = me.StudentID (just hit commas after stLinkCriteria until it prompts for OpenArgs). This will pass the StudentID to the Intervention form.

Then on the Intervention form, add an unbound text box called txtStudentIDFilter. Then in the OnOpen event add:

Me.txtStudentIDFilter = me.Openargs

Then in your StudentID text box, set the DEFAULT VALUE property to =[txtStudentIDFilter].

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

 

[Nikki2454]

Thanks Ginger!
I was wondering if this same code will work when trying to pass

StudentID to the Interventions Form. Example: When I go to the

Students Registration Form I enter the data then am prompted to save

the data before any other event. When I click on the cmdintervention

button I am taken to the filter intervention form. However since this

is a new student there is not StudentID that passes to the Intervention

Form. I was hoping to be able to pass the StudentID to the

Intervention Form without having a record already tied to that

intervention. Meaning new student to open a new intervention. The

student enters his/her data then tries to enter a new intervention with

their studentID already attached to the intervention form. As stated

early in this thread the StudentID field is locked.

Here is the code that I have currently for the cmdintervention button

on the students 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"
DoCmd.Close acForm, "frmStudents", acSaveNo
stLinkCriteria = "[StudentID]=" & "'" & Me![StudentID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , StudentID

End Sub


Any feedback is welecomed.

 

[PHV]

And this ?
stDocName = "Intervention"
stLinkCriteria = "[StudentID]=" & "'" & Me![StudentID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , StudentID
DoCmd.Close acForm, "frmStudents", acSaveNo

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Nikki2454]

PHV
I tried that. I get the same results that I got from my previous code.

THe problem is (I think) is that I am filtering the interventions table to contian the same studentID as the students Table(so that only the person logged in with a current studentid can see only what they entered and enter only records for themselves). When I enter a new student into the students table, it won't let me add the a new record to the interventions table because it can not find a previous record entered by that student. I have no problem going through the login form and using an existing student for updating/adding the record however when entering a new student my studentID textbox is not passing the StudentID from the Students Form to the Intervention Form.

Do you have any other suggestions?