We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Access GoToRecord based on Two Criteria - VBA

Medium Priority
4,401 Views
Last Modified: 2013-11-28
Hello,

I have a form with a VBA event. I have two text boxes with values that I have copied into strings. The records are stored in the values: val1, val2. They are strings with names that directly correspond to row entries in the the table.

The code I am trying to use to go to the record when the command button is pushed is:

 Me.RecordsetClone.FindFirst ("[StudentName] = " & val2 And "[ClassName] = " & val1)
 Me.Bookmark = Me.RecordsetClone.Bookmark

The table is called GradeDB. There are many fields in this table including two, one that says StudentName, one that says ClassName. When I debug the VBA it shows the correct text strings for val1 and val2. Even though both show up in the same row it throws an error message of - type mistmatch.

To boil the question down, I want to either be able to use gotorecord or findfirst with an expression containing two criteria.

Thanks!
Comment
Watch Question

what you have may work, but for text like:

 Me.RecordsetClone.FindFirst ("[StudentName] = " & val2 And "[ClassName] = " & val1)

you would have to (i would) surround your variables with chr(34)

 Me.RecordsetClone.FindFirst ("[StudentName] = " & chr(34) & val2 & chr(34) & " And [ClassName] = " & chr(34) & val1 & chr(34))


--I'm also not 100% sure your syntax was right on this line between val2 and the work "And",,,, there is no break between your variable and string,,, problem see I yes.


-John

Author

Commented:
Hello,

I will try that, thanks for the advice. I think perhaps using SQL would be the way to go. I want the Access form to automatically go to the record based on the val1 and val2 criteria. I am planning on disabling the navigation bar at the bottom.

One other possibility, which also doesn't work, is:

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [GradeDB] WHERE [ClassName] = val1 And [StudentName] = val2")

 rs.Close
if you're trying to call a recordset to variable like you would a cursor in sql based upon two fields,,,, do this:


-it's also possible within the loop i have here to set a
for i = 0 to rst.Fields.Count -1

msgbox rst.fields.item(i).Name & ": " & rst.Fields.item(i).value


next i


-Good luck

-John
dim rst as ADODB.Recordset
Dim strSQL$
 
 
      strSQL = "SELECT * FROM [GradbDB] WHERE [Classname] = " & chr(34) & val1 & chr(34)
      strSQL = strSQL & " AND [StudentName] = " & chr(34) & val2 & chr(34)
      strSQL = strSQL & ";"
 
        set rst = new adodb.recordset
            rst.open strSQL, currentproject.connection, _
                     adopenstatic, adlockreadonly
 
          if rst.RecordCount > 0 Then
             While not rst.EOF
 
                'Perform actions upon recordset here as it loops through:
                'to reference fields syntax would be:
 
                 'MsgBox rst.Fields.item("MyField").value
 
                rst.MoveNext
             Wend
           end if
          rst.Close
      set rst = nothing

Open in new window

Author

Commented:
Hello,

Still not getting the desired results. Is there a way to do it using docmd.gotorecord with the SQL statement, or, without the SQL statement? The chr(34) do not seem to be helping and are still throwing errors.

I would like to keep the code as simple as possible. It seems like it should be able to be done with two or at most three lines of code.

I simply want the form to make the record with the two criteria to be navigated to so that there is no need for the navigation bar. I already have logical if statements in so it won't execute this code unless it exists. I did that with a Nz(dcount) statement and if/else statements.

Thanks!
'note: val1 and val2 are global variables already set by this point
 
count = Nz(DCount("*", "[GradeDB]", "[ClassName] = val2 And [StudentName] = val1"), 0)
 
'This above checks to see if the record exists
 
If count = 0 Then
 
	' record doesn't exist so insert it into the table with the two criteria val1, val2
   
   Dim mysql As String
   mysql = "INSERT INTO [GradeDB] ([StudentName], [ClassName]) VALUES (val1,val2);"
   DoCmd.SetWarnings (False) 'turns off warning to add record
   DoCmd.RunSQL mysql 'runs 
   DoCmd.SetWarnings (True) 'warnings back on
 
 
' THIS IS WHERE THE FORM NEEDS TO NAVIGATE TO THE NEW RECORD JUST CREATED
 
Else
    
	'IF IT GETS HERE IT MEANS THE RECORD ALREADY EXISTS. CODE NEEDED TO NAVIGATE TO IT
 
End If
 
End Sub

Open in new window

My Appologies, I misunderstood what you were trying to accomplish.

Here's how i'd do it......

me.form.recordsource = "SELECT * from gradeDB where [studentname] = " & chr(34) & val1 & chr(34) & " AND [ClassName] = " & chr(34) & val2 & chr(34) & ";"

me.form.requery
doevents

another approach:

-if you've already got the recordsource set to "GradeDb"  then you can probably just set the filter property and turn the filters on.
(you'll probably have to have the fields you're filtering on inculded in the recordset and/or in the form window.

like this:

me.filter = "studentname= '" & val1 & "' AND classname= '" & val2 & "'"
me.filteron = true

-John

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Hello,

Great, that was the magic bullet. Thanks a million! I have spent, literally, days trying to go through the GoToRecord, RecordSet, etc., all to no avail! I had an intuition it was something quite simple but you definitely provided three days work of frustration into one simple answer!

Thanks a million!
Joseph

Author

Commented:
Thank you very much! That was the magic bullet that saved me hours of work!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.