Command Button: Opening Form Search Limit

[DianaS]

Hello,

I have created a command button inside Form1. When the button is clicked,
it opens Form2 and only displays records that match the ID# displayed in
Form1. This is great for my purposes, but I would also like to have the
capability to search for other ID numbers in Form2. I cannot do this--I can
ONLY view records with the matching ID# in Form2. Is there a way to adjust
visual basic code (or something else) so that the button pulls up records
with only the matching ID#, but also allows users to find other ID#s by using
CONTROL+F function in the ID# field?

Thank you,
Diana

 

['69 Camaro]

Hi, Diana

Is there a way to adjust
visual basic code (or something else) so that the button pulls up records
with only the matching ID#, but also allows users to find other ID#s by using
CONTROL+F function in the ID# field?

Yes. Assuming that you've used the Wizards to create the forms and buttons
for you, then Form2 is a bound form opened with a filter on the primary key
of the record.

In Form2, create a new button and place code such as the following in the
button's OnClick( ) event:

Private Sub FindAnotherBtn_Click()

On Error GoTo ErrHandler

Me.FilterOn = False
Me!txtScoreID.SetFocus
RunCommand acCmdFind

Exit Sub

ErrHandler:

MsgBox "Error in FindAnotherBtn_Click( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & "Error #" & _
Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

.... where "FindAnotherBtn" is the name of the button on Form2 and
"txtScoreID" is the name of the text box containing the primary key of the
record you'd like to search for. The "Find And Replace" dialog window will
pop up using the default settings for "Find" on the user's computer, allowing
the user to type the "ScoreID," or primary key of the record sought. The
form will then jump to that record or the first record that meets that
criteria.

If you are not using a bound form for Form2, then other code may be
necessary. If so, then please post the code that opens and filters this form
and we can provide assistance in altering it to suit your needs.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[Ken Sheridan]

Another approach would be, rather than filtering the second form, to sort it
so that the records for the ID# you've selected appear first. Lets assume as
an example that the second form is bound to a Contacts table and you want to
sort by the LastName column. To open the second form use the following code:

DoCmd.OpenForm "Form2",OpenArgs:= lngID

where lngID is a variable holding the ID# whose rows you want to locate:

In the second form's Open event procedure put code which sets its
RecordSource property to an SQL statement which includes an expression in its
ORDER BY clause to sort the selected ID# rows first by returning 1 for that
ID#, 2 for the rest:

Dim strSQL As String

If Not IsNull(Me.OpenArgs) Then
strSQL = "SELECT * FROM Contacts ORDER BY " & _
"IIF([ID#] = " & Me.OpenArgs & ", 1, 2), LastName"
Me.RecordSource = strSQL
End If

In the second form all the records for the selected ID# will appear first,
sorted by last name within that ID#, followed by the rest, again sorted by
last name. You will then be able to navigate to any other ID# using the
built in Find facility or any other method.

There are more sophisticated ways to use the OpenArgs mechanism to control
the behaviour of the second for; for instance by passing named arguments to
it so that you can then process these selectively, but the above would be
sufficient in your case.

 

[DianaS]

Hi,

I like your solution, and am trying your suggestion. It looks like it would
work except for one problem. The name of the table that is linked to form2
has a "space" in it: "MVC Schedule".

I inserted the codes you suggested with the modifications to field names and
file names. I then went back to form1 and tried clicking the form button.
Upon clicking, I get an error message to the effect that the JET database
does not recognize table name "MVC". Clearly, a table called "MVC" is
searched for instead of "MVC Schedule". Is there a way to get the program to
recognize table names with spaces? I tried quotation marks around the table
name, but this did not work.

Thank you,
Diana

Another approach would be, rather than filtering the second form, to sort it
so that the records for the ID# you've selected appear first. Lets assume as
an example that the second form is bound to a Contacts table and you want to
sort by the LastName column. To open the second form use the following code:

DoCmd.OpenForm "Form2",OpenArgs:= lngID

where lngID is a variable holding the ID# whose rows you want to locate:

In the second form's Open event procedure put code which sets its
RecordSource property to an SQL statement which includes an expression in its
ORDER BY clause to sort the selected ID# rows first by returning 1 for that
ID#, 2 for the rest:

Dim strSQL As String

If Not IsNull(Me.OpenArgs) Then
strSQL = "SELECT * FROM Contacts ORDER BY " & _
"IIF([ID#] = " & Me.OpenArgs & ", 1, 2), LastName"
Me.RecordSource = strSQL
End If

In the second form all the records for the selected ID# will appear first,
sorted by last name within that ID#, followed by the rest, again sorted by
last name. You will then be able to navigate to any other ID# using the
built in Find facility or any other method.

There are more sophisticated ways to use the OpenArgs mechanism to control
the behaviour of the second for; for instance by passing named arguments to
it so that you can then process these selectively, but the above would be
sufficient in your case.

Hello,

I have created a command button inside Form1. When the button is clicked,
it opens Form2 and only displays records that match the ID# displayed in
Form1. This is great for my purposes, but I would also like to have the
capability to search for other ID numbers in Form2. I cannot do this--I can
ONLY view records with the matching ID# in Form2. Is there a way to adjust
visual basic code (or something else) so that the button pulls up records
with only the matching ID#, but also allows users to find other ID#s by using
CONTROL+F function in the ID# field?

Thank you,
Diana

 

[Ken Sheridan]

Wrapping the table name in square brackets should do the trick; [MVC
Schedule]. This applies to other types of objects as well; fields, controls

 

[DianaS]

Hi,

Thanks, that worked. I am having another issue as well. I have created a
third form (form3) with a command button to open form2. In your example, you
wrote a general code so that when form2 is opened, it first opens the record
with the matching ID#, then organizes by LastName.

How can I get this new command button in form3 to open form2 by LastName
(let's assume this is a unique field--there are no duplicate entries in
"LastName"--actually, for me, this field is a unique number)? I could use
the same code you used, but that would mean changing the way the command
button in form1 opens form2. Is there a way to get command buttons in form1
and form3 to open form2 in unique ways?

Thanks,
Diana

Wrapping the table name in square brackets should do the trick; [MVC
Schedule]. This applies to other types of objects as well; fields, controls

Hi,

I like your solution, and am trying your suggestion. It looks like it would
work except for one problem. The name of the table that is linked to form2
has a "space" in it: "MVC Schedule".

I inserted the codes you suggested with the modifications to field names and
file names. I then went back to form1 and tried clicking the form button.
Upon clicking, I get an error message to the effect that the JET database
does not recognize table name "MVC". Clearly, a table called "MVC" is
searched for instead of "MVC Schedule". Is there a way to get the program to
recognize table names with spaces? I tried quotation marks around the table
name, but this did not work.

Thank you,
Diana

 

[Ken Sheridan]

Diana:

As I mentioned there are more sophisticated ways of using the OpenArgs
mechanism, so what you can do is pass a named argument to the form so it can
then select which way to open. First add the following module (which I
produced a few years back based on an earlier module by my former colleague
Stuart McCall which passed a simple delimited argument list) to your
database. Note that when you paste this into a blank module you should first
delete the two lines which Access automatically inserts at the start of a
module:

'module starts'
Private Const OFFSET As Long = 127
Private Const ASSIGNOP As String = "=="

Function Arg(buffer, idx) As Variant

If IsNumeric(idx) Then
i& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
i& = InStr(i&, buffer, ASSIGNOP) + 2
Else
i& = InStr(1, buffer, idx) + Len(idx) + 2
token$ = Chr(Asc(Mid(buffer, InStr(1, buffer, idx) - 1, 1)) + 1)
End If
Arg = Mid(buffer, i&, InStr(i&, buffer, token$) - i&)

End Function

Function Argname(buffer, idx) As String

i& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
Argname = Mid(buffer, i& + 1, InStr(i&, buffer, ASSIGNOP) - (i& + 1))

End Function

Function ArgCount(buffer) As Long

ArgCount = Asc(Right(Chr(OFFSET) & buffer, 1)) - OFFSET

End Function

Sub AddArg(buffer, Argname, argval)

If Len(buffer & "") = 0 Then buffer = Chr(OFFSET)
If IsNumeric(Argname) Then Argname = ArgCount(buffer) + 1
buffer = buffer & Argname & ASSIGNOP & argval & Chr(Asc(Right(buffer,
1)) + 1)

End Sub

Sub AddArgList(buffer, ParamArray Tokens())

For i& = 0 To UBound(Tokens)
AddArg buffer, i& + 1, Tokens(i&)
Next

End Sub
'module ends'

Once you have this in your database you can make use of it to open the form
in either way. So to open the form with the selected ID# first as I
described in my last post you'd do something like this:

Dim args As String

' add named arguments
AddArg args, "OpenMethod", "OpenByID"
AddArg args, "Value", 42

DoCmd.OpenForm "Form2", OpenArgs:=args

To open it and go to a selected Lastname do this:

Dim args As String

' add named argument
AddArg args, "OpenMethod", "OpenByLastName"
AddArg args, "Value", "Sheridan"

DoCmd.OpenForm "Form2", OpenArgs:=args

I've used the constants 42 and "Sheridan" as the values here but in reality
you'd use variables to which you'd assigned values. In the Open event
procedure of Form2 you'd now use a Select Case statement to determine how to
open the form:

Dim rst As Object
Dim strSQL As String
Dim args As String
Dim i As Integer

args = Me.OpenArgs

Select Case Arg(args, "OpenMethod")
Case "OpenByID"
strSQL = "SELECT * FROM Contacts ORDER BY " & _
"IIf([ID#] = " & Arg(args, "Value") & ", 1, 2), LastName"
Me.RecordSource = strSQL
Case "OpenByLastName"
strSQL = "SELECT * FROM Contacts ORDER BY Lastname"
Me.RecordSource = strSQL
Set rst = Me.Recordset.Clone
rst.FindFirst "LastName = """ & Arg(args, "Value") & """"
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "No matching record for LastName: " & Arg(args,
"Value"), _
vbInformation, "Warning"
End If
End Select

When opening the form by LastName the records will be sorted by LastName and
the form will go to the first record, if any, which matches the value passed
to it.

 

[Ken Sheridan]

Diana:

It just occured to me that you might want to open the form without passing
arguments to it, so check for OpenArgs being Null by amending Form2's Open
event procedure as follows:

Dim rst As Object
Dim strSQL As String
Dim args As String, i As Integer

If Not IsNull(Me.OpenArgs) Then
args = Me.OpenArgs

Select Case Arg(args, "OpenMethod")
Case "OpenByID"
strSQL = "SELECT * FROM Contacts ORDER BY " & _
"IIf([ID#] = " & Arg(args, "Value") & ", 1, 2), LastName"
Me.RecordSource = strSQL
Case "OpenByLastName"
strSQL = "SELECT * FROM Contacts ORDER BY Lastname"
Me.RecordSource = strSQL
Set rst = Me.Recordset.Clone
rst.FindFirst "LastName = """ & Arg(args, "Value") & """"
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "No matching record for LastName: " & Arg(args,
"Value"), _
vbInformation, "Warning"
End If
End Select
End If