• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3829
  • Last Modified:

Access GoToRecord based on Two Criteria - VBA

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!
0
jkeagle13
Asked:
jkeagle13
  • 4
  • 3
1 Solution
 
JohnSingletonCommented:
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

0
 
jkeagle13Author 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
0
 
JohnSingletonCommented:
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

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jkeagle13Author 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

0
 
JohnSingletonCommented:
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
0
 
jkeagle13Author 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
0
 
jkeagle13Author Commented:
Thank you very much! That was the magic bullet that saved me hours of work!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now