Goto record in from based on the contents of a String

[jetbuster]

For my BIT Im doing a small database in Access and im trying to open a Form to a specific record using info entered by the user.

the String BNetID gets set(I think) when a user enters it on the previous page
Private Sub Form_Load()
If Not IsNull(BNetID) Then
' Goto page with BNetID
Else: MsgBox "To access this page you did not Log In, You will be given the first record only", vbOKOnly, "No Login was achieved"
'That colon keeps being added by Acess2010 so I assume it needs to be there
End If
Ive never done VBA before so documentation on anything more than 3-4 lines of code will be greatly appreciated
Thanks in advance

[BrianMH]

I can't see the code you have leading up to this. Personally I would figure out what record you wanted to open prior to loading the form and then use something like.

DoCmd.OpenForm "FormNameHere", , , "[FieldtoFilterOnHere] = BNetid

[jetbuster]

Thanks for the speedy response but doesn't that line just open a form and filter the one field to show BnetID and that's it? or does the rest of the record jump to the same record?

[jetbuster]

Ill just give you what I have so far, It is working from the KB example

Option Explicit
Option Compare Database
Private intLogonAttempts As Integer

Private Sub Form_Open(Cancel As Integer)
'On open set focus to Username
Me.txtBattleNetID.SetFocus
intLogonAttempts = 0
End Sub

Private Sub txtBattleNetID_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub

Private Sub cmdLogin_Click()

'Check to see if data is entered into the BattlenetID text box

If IsNull(Me.txtBattleNetID) Or Me.txtBattleNetID = "" Then
MsgBox "BattleNetID is a required field.", vbOKOnly, "Required Data"
Me.txtBattleNetID.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password text box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "Password is a required field.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
'Checks the value of txtBattleNetID to see if it exists
If Me.txtBattleNetID.Value = DLookup("BattleNetID", "tblEmployees") Then
'Should Check the value of txtPassword to see if its in the same line entry as txtBattlenetID but it doesn't and I don't know how
If Me.txtPassword.Value = DLookup("User_Password", "tblEmployees") Then

DoCmd.Close acForm, "frmLogon", acSaveNo
'substitute correct name if using
'form other than frmLogon
'Should it be DoCmd.OpenForm "User_Info", , , Me.txtBattleNetID.value or do I need to store it?
DoCmd.OpenForm "User_Info", , , Me.txtBattleNetID

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If
Else
MsgBox Me.txtBattleNetID.Value + " BattleNetID Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtBattleNetID.SetFocus
End If

'If User Enters incorrect password 5 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts = 5 Then
MsgBox "Toomany Incorrect attempts. Please contact a Store assistant.", vbCritical, "Access to Access is Restricted!"
Application.Quit
End If

End Sub

If Me.txtBattleNetID.Value = DLookup("BattleNetID", "tblEmployees") Then
Should return true on all the usernames I enter but It only runs for the first result in tblEmployees.

Then when
DoCmd.OpenForm "User_Info", , , Me.txtBattleNetID runs when the above one works, I think it needs to be stored before I use it

[hansup]

'Checks the value of txtBattleNetID to see if it exists
If Me.txtBattleNetID.Value = DLookup("BattleNetID", "tblEmployees") ThenShould return true on all the usernames I enter but It only runs for the first result in tblEmployees.


Look at Access' help topic for DLookup(). Its optional 3rd argument is Criteria, which is used to "restrict the range of data on which the DLookup function is performed". When you don't supply Criteria, DLookup operates on the full domain, which in your case is tblEmployees, and returns the first BattleNetID found in that domain.

You could continue to use DLookup and supply Criteria to limit the domain to only one txtBattleNetID. However, consider DCount() instead.

Dim strCriteria As String
' if BattleNetID is numeric data type ...
strCriteria = "BattleNetID=" & Me.txtBattleNetID.Value
' if BattleNetID is text data type ...
'strCriteria = "BattleNetID='" & Me.txtBattleNetID.Value & "'"
If DCount("BattleNetID", "tblEmployees", strCriteria) > 0 Then

[jetbuster]

Awesome works perfectly now thank you very much, I think I may be here for a bit the database isn't finished yet :P

[hansup]

OK, there is a lot of ground to cover when you're starting with VBA, but it looks like you're off to a good start.

One detail I noticed is this ...MsgBox Me.txtBattleNetID.Value + " BattleNetID Invalid. Please Try Again"

If Me.txtBattleNetID.Value is Null, that expression would be equivalent to ...
MsgBox Null... which should throw error #94, 'Invalid use of Null'.

Even if that possibility is unlikely, you should avoid it by using the ampersand instead of the plus sign for concatenation. MsgBox Me.txtBattleNetID.Value & " BattleNetID Invalid. Please Try Again"Null ampersand "some string" returns "some string".

Null plus "some string" returns Null.

[BrianMH]

Thanks for the speedy response but doesn't that line just open a form and filter the one field to show BnetID and that's it? or does the rest of the record jump to the same record?

Not it opens the record based on that filter for that field but it is the whole record.

[jetbuster]

OK, there is a lot of ground to cover when you're starting with VBA, but it looks like you're off to a good start.

One detail I noticed is this ...MsgBox Me.txtBattleNetID.Value + " BattleNetID Invalid. Please Try Again"
If Me.txtBattleNetID.Value is Null, that expression would be equivalent to ...
MsgBox Null... which should throw error #94, 'Invalid use of Null'.

Even if that possibility is unlikely, you should avoid it by using the ampersand instead of the plus sign for concatenation. MsgBox Me.txtBattleNetID.Value & " BattleNetID Invalid. Please Try Again"Null ampersand "some string" returns "some string".

Null plus "some string" returns Null.
Thanks the main language we are using this semester is Java and then its C# next semester but if you want to get any decent marks in our database paper which uses access VBA is pretty necessary, I was showing some of the students and they found the syntax alone pretty dawnting


Not it opens the record based on that filter for that field but it is the whole record.
Thanks I figured it out by playing any way :)

[HiTechCoach]

Thanks the main language we are using this semester is Java and then its C# next semester but if you want to get any decent marks in our database paper which uses access VBA is pretty necessary, I was showing some of the students and they found the syntax alone pretty dawnting


VBA will seam easy compared to C#.