Pulling a specific record into a form for a user to edit after logging in.

[CaptainCurlyArr]

I'm new to Access and even newer to VBA so please bear with me if my description doesn't quite make sense.



I've been asked to create a system for students to register themselves and then register which modules they would like to take at university.

I've just figured out how to make a log in system that only allows a student to progress to the Registration form if their username and password matches those in the Students table (code below)

To match a real life situation, as well as being able to fill in the form for the first time, I want a student to be able to come back and change their choices later on, but I can't work out how to autopopulate this information/pull their old record information from the login screen. At the moment, the form just opens up blank for a user to enter details from fresh.

I've read around, but nothing seems to make sense to me because I'm so new and my teacher more of a learn for yourself kinda guy then someone who will show you how to do it so any help would be greatly appreciated!!

This is my login code if it helps!


Private Sub Go_Click()

Dim Password As String
Dim msg As String
Dim error As Boolean
Dim fail As Boolean

Static count As Integer

'check if either box is left empty
If IsNull(ESID) Or ESID = "" Then
msg = msg & "Please enter the Username" & Chr(13)
error = True
ElseIf IsNull(PW) Or PW = "" Then
msg = msg & "Please enter the Password" & Chr(13)
error = True
Else
error = False
End If

'warning about not filling in details
If error = True Then
DoCmd.Beep
Call MsgBox(msg & Chr(13) & "Please refer to FAQs for further assistance", vbOKOnly, "Error")
Exit Sub
End If

Me.PWLookup.Value = DLookup("[Password]", "Students", "[Student_ID] ='" & Me.ESID & "'")

'Log in check, allows 3 attempts
If Not Me.PW.Value = Me.PWLookup.Value Then
msg = msg & "The username or password is incorrect"
fail = True
count = count + 1
End If

'Final message
If count >= 3 Then
DoCmd.Beep
Call MsgBox("You have exceeded the number of allowed attempts. Goodbye.", vbCritical, "Error")
DoCmd.Close
count = 0
ElseIf fail = True Then
DoCmd.Beep
Call MsgBox(msg & Chr(13) & "Please refer to FAQs for further assistance", vbOKOnly, "Error")
Else
Call MsgBox("Thank you for logging in", vbInformation, "Success!")
DoCmd.Close
DoCmd.OpenForm "Registration"
End If

End Sub

[pbaldy]

You can use this:

BaldyWeb wherecondition

perhaps after testing to see if there's an existing record with DCount().

[Missinglinq]

Also, note that both count and error are Reserved Words in Access VBA and you should really replace them, as having Variables with the same names may confuse the Access Gnomes! A simply modification to ccount and eerror will do.

Linq ;0)>

[CaptainCurlyArr]

Hey guys,

Firstly thanks for your help - I made the changes to the words as suggested by Linq to avoid any future issues.

As I mentioned, my teacher hasn't explained very much so I have no idea what Dcount() is or how I should use it, would you mind explaining further?

I tried putting in the Wherecondition code as follows:

DoCmd.OpenForm "Registration", , , "student_ID = '" & Me.ESID & "'"

but I keep getting a run-time error '2467' pop up saying "The expression you entered refers to an object that is closed or doesn't exist" - probably something really obvious that I'm doing wrong, can either of you see it?

If it helps I upload a copy of my really awful looking database :P

Thanks again in advance for your help!

[Missinglinq]

Do you actually have a Form named Registration?

Is that the line highlighted when it bombs? Frequently problems will arise after a change is made that aren't actually related by the change. This can happen, for instance, if the change that was made correctes one problem, allowing execution to move on, where it incurs another problem.

Removing confidential data, Zipping up the file and attaching it to a post frequently is helpful for those of us who are trying to help. Also, saving the file as using an earlier file format, such as Access 2007 or, even better, Access 2003, allows more members, here, to run the app.

Linq ;0)>