Open form to specific record

  • Thread starter Thread starter Madmax via AccessMonster.com
  • Start date Start date
M

Madmax via AccessMonster.com

I also am having problems opening a form to a specific record. I have a
single table that because of the amount of data I enter on two different
forms. On form 1 I have a command button to open form 2. On form 1 I have a
name field that is extracted from table A. Form 1 also has a component
number which in combination with the name field is a unique index to extract
data from table B. There is a 1 to many relationship between table A and
table B.

When I get to form 1 I can use the Navigation Button to walk through the
various table 2 entries. When I am on a particular component I may want to
access form 2 that corresponds to form 1. However when I open form 2
supplying both the name and component number parameters I always end up on
form 2, component 1 entry and have to then Navigate to the corresponding
component record. I have been trying to resolve this problem for some time
and have searched the Web, but just can't seem to find an answer to my
problem. Below is the code I used to open form 2.

Dim stFrmName As String
stFrmName = "Add_Edit_TestInformation" '= Form 2 Name
Dim openargs As String
Dim openfilter As String
openfilter = "[TestCaseNumber]= """ & [TestCaseNumber] & """" ' = Name
openargs = "[TestCaseNumber]= """ & [TestCaseNumber] & _
""" AND [TestCaseComponentNumber]= " & [TestCaseComponentNumber] ' =
Component Number
'MsgBox (openargs)
If Me.RecordsetClone.RecordCount > 0 Then
If Not (IsNull([TestCaseComponentNumber])) Then
DoCmd.OpenForm stFrmName, , , openfilter, , , openargs
End If
End If

As I was typing this post, the thought came to me that maybe I should not be
supplying the openfilter parameter?
I should probably also point out that I am a newbie and I am trying to
support and existing product that someone else constructed. Thanks in
advance for any suggestions.
 
Madmax via AccessMonster.com said:
I also am having problems opening a form to a specific record. I have a
single table that because of the amount of data I enter on two different
forms. On form 1 I have a command button to open form 2. On form 1 I
have a
name field that is extracted from table A. Form 1 also has a component
number which in combination with the name field is a unique index to
extract
data from table B. There is a 1 to many relationship between table A and
table B.

When I get to form 1 I can use the Navigation Button to walk through the
various table 2 entries. When I am on a particular component I may want
to
access form 2 that corresponds to form 1. However when I open form 2
supplying both the name and component number parameters I always end up on
form 2, component 1 entry and have to then Navigate to the corresponding
component record. I have been trying to resolve this problem for some
time
and have searched the Web, but just can't seem to find an answer to my
problem. Below is the code I used to open form 2.

Dim stFrmName As String
stFrmName = "Add_Edit_TestInformation" '= Form 2 Name
Dim openargs As String
Dim openfilter As String
openfilter = "[TestCaseNumber]= """ & [TestCaseNumber] & """" ' = Name
openargs = "[TestCaseNumber]= """ & [TestCaseNumber] & _
""" AND [TestCaseComponentNumber]= " & [TestCaseComponentNumber] ' =
Component Number
'MsgBox (openargs)
If Me.RecordsetClone.RecordCount > 0 Then
If Not (IsNull([TestCaseComponentNumber])) Then
DoCmd.OpenForm stFrmName, , , openfilter, , , openargs
End If
End If

As I was typing this post, the thought came to me that maybe I should not
be
supplying the openfilter parameter?
I should probably also point out that I am a newbie and I am trying to
support and existing product that someone else constructed. Thanks in
advance for any suggestions.
Click to expand...
 
Sorry, bumped the send key......

if you want form two to open to ONE record (but, then no navigation will be
able to occur on form2, you can use:


Dim stFrmName As String
stFrmName = "Add_Edit_TestInformation" '= Form 2 Name
Dim openargs As String
Dim openfilter As String

openfilter = "TestCaseNumber = '" & me!TestCaseNumber & "'" & _
" and TestCaseComponentNumber = " & me!TestCaseComponentNumber

docmd.Openform strFrmName,,,openfitler

The above is all you need. Note how the filter will set 2nd form to ONE
record. Also, it is not clear TestCseComponentNumber is a "text" field, or a
actual number as defined in the table. If it is a number, then you need to
surround it with quotes. So, you would use:

openfilter = "TestCaseNumber = '" & me!TestCaseNumber & "'" & _
" and TestCaseComponentNumber = '" & me!TestCaseComponentNumber
& "'"

Note that I am suing a single ' quote, as it find them easier to put in..but
they are hard to see/read in the above.

However, it is possible that you want to filter the 2nd form to casenumber,
and ALLOW navigation, but also send the 2nd form to the correct component
number. (as you can see, this is a bit more complex of a problem). Post back
if you need the 2nd case....
 
I will try what you have given me so far as an intrim solution. But what I
really need is the second case you described.

Thank you kindly for your reply Albert.

Madmax
 
I will try what you have given me so far as an intrim solution. But what I
really need is the second case you described.

Thank you kindly for your reply Albert.
Click to expand...

Great, just a side note:
If it is a number, then you need to
Click to expand...
surround it with quotes. So, you would use:

The above should read the opposide:
number type fields no quotes
text type fields you need quotes

Ok, the 2nd solution means that we have to filter to "all" of the
components, and then MOVE TO the correct record we want.

So, lets restrict the form to all of the "test case numbers"

We would use:

Dim stFrmName As String
stFrmName = "Add_Edit_TestInformation" '= Form 2 Name
Dim openargs As String
Dim openfilter As String

openfilter = "TestCaseNumber = '" & me!TestCaseNumber & "'"

docmd.Openform strFrmName,,,openfitler

Of course, the above means then we have to "navigate" to the correct record.
A workaround is to "pass" what record to "move to" in the forms on-load
event.

So, lets pass this value to the form via the OpenArgs (that is what it is
for). So, the filter remains as above, but we use

docmd.Openform strFrmName,,,openfitler,,,me!TestCaseNumber

Now, we have to "move" to the correct test case number.

So in our form Add_Edit_TestInformation "on-load event" we go:

if isnull(me.OpenArgs) = false then
' a case number was passed, lets move to it
dim r as dao.recordset
set r = me.REcordSetClone
r.findfirst "TestCaseNumber = '" & me.OpenArgs & "'"
me.BookMark = r.bookmark
end if
 
I tried your first suggest and it did work in getting to the correct
component number, but of course I could not position forwards or backwards.

I tried your second suggestion but had some problems with the findfirst
statement. When I enter the single quote in front of the & me.OpenArgs it
takes that as the start of a comment. Listed below is what I put in:

This is for the onClick event:
Dim stFrmName As String
stFrmName = "Add_Edit_TestInformation"
Dim openargs As String
Dim openfilter As String

' Note TestCaseNumber is acutally a string not really a number
' TestCaseComponentNumber is an integer

openfilter = "[TestCaseNumber]= """ & Me![TestCaseNumber] & """"

If Me.RecordsetClone.RecordCount > 0 Then
If Not (IsNull([TestCaseComponentNumber])) Then

' I made the openargs pass TestCaseComponentNumber because I
thought
' this is the value were trying to get to the second screen?

DoCmd.OpenForm stFrmName, , , openfilter, , , Me!
[TestCaseComponentNumber]

End If
End If

Here is what is in the Form_Open Sub of the second form:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Restore
End Sub

Here is what I put into the Form_Load Sub on the second form:

Private Sub Form_Load()
If IsNull(Me.openargs) = False Then
' a component numer was passed, so lets move to it
Dim r As dao.Recordset
Set r = Me.RecordsetClone
r.FindFirst "TestCaseNumber= " & Me.openargs & ""
Me.Bookmark = r.Bookmark
End If
End Sub

The results of these changes is that I am back where I was, I get to the
second form but I am at the first component record instead of the component
record I was on at the first form.

Thank you for taking the time to work with me. Please let me know what I
have done wrong.
I will try what you have given me so far as an intrim solution. But what I
really need is the second case you described.

Thank you kindly for your reply Albert.

Madmax
Sorry, bumped the send key......
Click to expand...
[quoted text clipped - 27 lines]
number. (as you can see, this is a bit more complex of a problem). Post back
if you need the 2nd case....
Click to expand...
Click to expand...
 
After reading what I typed I noticed that on the Form_Load code I had not
transposed the TestCaseNumber to TestCaseComponentNumber, when I made that
change it now works. T H A N K Y O U!

I tried your first suggest and it did work in getting to the correct
component number, but of course I could not position forwards or backwards.

I tried your second suggestion but had some problems with the findfirst
statement. When I enter the single quote in front of the & me.OpenArgs it
takes that as the start of a comment. Listed below is what I put in:

This is for the onClick event:
Dim stFrmName As String
stFrmName = "Add_Edit_TestInformation"
Dim openargs As String
Dim openfilter As String

' Note TestCaseNumber is acutally a string not really a number
' TestCaseComponentNumber is an integer

openfilter = "[TestCaseNumber]= """ & Me![TestCaseNumber] & """"

If Me.RecordsetClone.RecordCount > 0 Then
If Not (IsNull([TestCaseComponentNumber])) Then

' I made the openargs pass TestCaseComponentNumber because I
thought
' this is the value were trying to get to the second screen?

DoCmd.OpenForm stFrmName, , , openfilter, , , Me!
[TestCaseComponentNumber]

End If
End If

Here is what is in the Form_Open Sub of the second form:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Restore
End Sub

Here is what I put into the Form_Load Sub on the second form:

Private Sub Form_Load()
If IsNull(Me.openargs) = False Then
' a component numer was passed, so lets move to it
Dim r As dao.Recordset
Set r = Me.RecordsetClone
r.FindFirst "TestCaseNumber= " & Me.openargs & ""
Me.Bookmark = r.Bookmark
End If
End Sub

The results of these changes is that I am back where I was, I get to the
second form but I am at the first component record instead of the component
record I was on at the first form.

Thank you for taking the time to work with me. Please let me know what I
have done wrong.
I will try what you have given me so far as an intrim solution. But what I
really need is the second case you described.

Thank you kindly for your reply Albert.

Madmax
Sorry, bumped the send key......
Click to expand...
[quoted text clipped - 27 lines]
number. (as you can see, this is a bit more complex of a problem). Post back
if you need the 2nd case....
Click to expand...
Click to expand...
 
Madmax via AccessMonster.com said:
I tried your first suggest and it did work in getting to the correct
component number, but of course I could not position forwards or
backwards.
Click to expand...

Well, that means there is likely only "one" component number. Remember, the
"where" clause is simply going to restrict WHAT RECORDS the form loads. If
only one record is loaded, then how can we move, or navigate?

At this point, I not sure if I lost you, or you don't realize that we are
using the "where" clause to simply tell what records will be loaded into
that form.

As a further note, we better get the "first" case working. It is kind of
jumping the gun by not getting our code to first work, and then go head deep
into trying the 2nd case. (you create a double mess).

So, we should step back, and work on getting the form to load up all of the
test cases.

It stands to reason that if we want to "browse" the components in a given
test case, we simply filter the 2nd form by testcase, and then we are able
to "browse". Once we get that working, then lets add the additional code to
"move" to a particular component number within the test case.
I tried your second suggestion but had some problems with the findfirst
statement. When I enter the single quote in front of the & me.OpenArgs it
takes that as the start of a comment. Listed below is what I put in:
Click to expand...

Ok, we are also crossing wires here on the use of quotes.

So, lets try making the form load all of our components for a give test
case. We will pass the open args, but no have any code in the on-load event
until we get the basic "where" to restrict the records we want.

Dim stFrmName As String
stFrmName = "Add_Edit_TestInformation"
Dim openargs As String
Dim openfilter As String
dim q as string

q = """"

' Note TestCaseNumber is acutally a string not really a number
' TestCaseComponentNumber is an integer

openfilter = "TestCaseNumber = " & q & Me!TestCaseNumber & q

If Me.RecordsetClone.RecordCount > 0 Then
If Not (IsNull([TestCaseComponentNumber])) Then

' I made the openargs pass TestCaseComponentNumber because I
' thought
' this is the value were trying to get to the second screen?

' good, hen we got "many" TestCaseNumber records (as our filter)
' and then we will "move" to the particular TestCaseComponentNumber...right?

DoCmd.OpenForm stFrmName, , , openfilter, , ,
Me!TestCaseComponentNumber

End If
End If

Ok, at this point, does the above code open the form with the correct
TestCases
(lets not worry about the code "moving" to the particular
TestCaseComponentNumber in our form...but lets just ensue it opens up the
form
with the correct "records" for a given TestCaseNumber.

Does the above work now?

Can we navigate to the several records correctly?

Once we get it working, then we can add to the forms on-load event:

Private Sub Form_Load()

dim q as string
q = """"

If IsNull(Me.openargs) = False Then
' a component numer was passed, so lets move to it
Dim r As dao.Recordset
Set r = Me.RecordsetClone
r.FindFirst "TestCaseNumber = " & q & Me.openargs & q
Me.Bookmark = r.Bookmark
End If
End Sub

As mentioned, if testCase number is defined as a number field, then you
would go:

r.FindFirst "TestCaseNumber = " & Me.openargs
 
Back
Top