Form1 is built off of Query1.
What I want to do is call a pop-up Form2 from Form1 and inside of Form2 list the next 3 records that are in sequence from the current value of Form1. The values of Form2 of course have to change accordingly with Form1 when navigating through Form1.
For instance...
What I forgot to put in was that the field 'Price' is sorted ascending in the query. Anyway I worked on it a little while and I have this, but how do I correct or handle the errors when I come to the end of the code or query where I only have one or two entries left and not the three that I am asking for?
Code:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Public Function Next3()
Dim A As Double
Dim B As Double
Dim C As Double
Set db = CurrentDb
SQL = "SELECT * "
SQL = SQL & "FROM Query1"
Set rst = db.OpenRecordset(SQL)
If CurrentProject.AllForms("Form1").IsLoaded = True Then
rst.AbsolutePosition = Forms!Form1!Price
rst.MoveNext
Else
End
End If
A = rst("Price")
Debug.Print A
rst.MoveNext
B = rst("Price")
Debug.Print B
rst.MoveNext
C = rst("Price")
Debug.Print C
If CurrentProject.AllForms("Form2").IsLoaded = True Then
Forms!Form2!Text2 = A
Forms!Form2!Text4 = B
Forms!Form2!Text6 = C
Else
End
End If
rst.Close
Set rst = Nothing
End Function
I will work on it again and try to finish it later (like End of Record etc. or what-ever.), just thought I would post this and get opinions and maybe other ideas on how to do this.
Thanks again.
And Pat, I would sincerely like to have ANY book that you have wrote on Access.
A little farther in the process, but heh, this works, but not perfect or very pretty.
Code:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Public Function Next3()
Dim A As Double
Dim B As Double
Dim C As Double
Set db = CurrentDb
SQL = "SELECT * "
SQL = SQL & "FROM Query1"
Set rst = db.OpenRecordset(SQL)
If CurrentProject.AllForms("Form1").IsLoaded = True Then
'rst.AbsolutePosition = Forms!Form1!Price
rst.FindFirst "Price = " & Chr(34) & Forms![Form1]!Price & Chr(34)
If rst.EOF Then
GoTo XYZ:
Else
End If
Else
GoTo ZZ:
End If
If rst.EOF Then
GoTo XYZ:
Else
End If
A = rst("Price")
'Debug.Print A
If A = Forms!Form1!Price Then
GoTo XYZ:
Else
End If
If CurrentProject.AllForms("Form2").IsLoaded = True Then
Forms!Form2!Text2 = A
Else
End If
rst.MoveNext
If rst.EOF Then
GoTo XYZ:
Else
End If
B = rst("Price")
'Debug.Print B
If CurrentProject.AllForms("Form2").IsLoaded = True Then
Forms!Form2!Text4 = B
Else
End If
rst.MoveNext
If rst.EOF Then
GoTo XYZ:
Else
End If
C = rst("Price")
'Debug.Print C
If CurrentProject.AllForms("Form2").IsLoaded = True Then
Forms!Form2!Text6 = C
Else
End If
XYZ:
rst.Close
ZZ:
Set rst = Nothing
End Function
Would you take a look and offer any opinions...
Next step, to work this in with filters vs. parameter query. Preferences?
That statement literally assigns the position of the recordset to the value of Forms!Form1!Price, or I guess assigns the cursor position. As most of you probably already knew...
you should use..
rst.FindFirst (etc.)
Or more to the point or to be more exact in this case:
I did go back and commented out the bad line and inserted the correct line.
I left the original sequence and solution for the bad line incase someone else ran into the same problem, after all that is why I am here... to learn.
Since I am still very new to DAO this was a good learning lesson and the above example actually would work (using the .AbsolutePosition) if your data was in fact in sequence, not just unique identifiers but if all your values were in sequence starting from a value of "1", but in a real situation that will never be that way; a drawback of using the most simplified example to learn.
