Problem Cancelling Form that Uses Paramater Query

[bmullin via AccessMonster.com]

Hi,

I have a list type form that shows a list of borrowers. The list form can be
narrowed down based on a full or partial borrower ID number and a full or
partial SSN.

I have paramaters on Borrower ID and SSN as follows:
Like "*" & [Enter as much of Borrower Id as Possible] & "*"
Like "*" & [Enter as much of Social Security# as Possible] & "*"

The user only has to enter one or the other, but can enter both.

The form requeries using a lookup button with the following code behind it:

Private Sub Find_Record_Click()
On Error GoTo Err_Find_Record_Click

Screen.PreviousControl.SetFocus
DoCmd.Requery

Exit_Find_Record_Click:
Exit Sub

Err_Find_Record_Click:
MsgBox Err.Description
Resume Exit_Find_Record_Click

End Sub

Then, they can connect to a main form with more thorough data. It works
great... except if the user presses cancel when they're prompted to enter a
number. If they hit cancel, the next time they press the search button, they
get an error saying that the recordsource, which is 5 fields from the
underlying table, does not exist.

Any thoughts would be appreciated.

Thanks

 

[Allen Browne]

Yes, this is a limitation of using parameters. It sort-of makes sense:
cancel the query, and the form (based on the query) doesn't work correctly.

The solution will be to omit the parameters from the query, and use the
form's Filter instead. This solves not only the problem you have, but also
the fact that anyone without an SSN will be eliminated from your query (i.e.
if the field is Null, it doesn't match the criteria you have.) It's also a
much nicer interface than those popup parameter boxes.

When you say you have a "list type form", I think you mean Datasheet view?
If so, switch it to Continuous Form view. You can lay the text boxes out
side by side so it looks similar to Datasheet but you get to use the Form
Header and Form Footer section too.

Now add a couple of unbound text boxes to the Form Header section, where the
user can enter the [Borrower ID] and/or the SSN to match. Let's name them
txtBorrower and txtSSN.

You will end up using code like this to apply the filter:

Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then Me.Dirty = False 'Save any edits

If Not IsNull(Me.txtBorrower) Then
strWhere = "([Borrower ID] Like ""*" & Me.txtBorrower & "*"") AND "
End If
If Not IsNull(Me.txtSSN) Then
strWhere = strWhere & "([SSN] Like ""*" & Me.txtSSN & "*"") AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
'No criteria: show all records
Me.Filter = False
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = False
End If

For a downloadable example of how to do this with different field types,
see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a list type form that shows a list of borrowers. The list form can
be
narrowed down based on a full or partial borrower ID number and a full or
partial SSN.

I have paramaters on Borrower ID and SSN as follows:
Like "*" & [Enter as much of Borrower Id as Possible] & "*"
Like "*" & [Enter as much of Social Security# as Possible] & "*"

The user only has to enter one or the other, but can enter both.

The form requeries using a lookup button with the following code behind
it:

Private Sub Find_Record_Click()
On Error GoTo Err_Find_Record_Click

Screen.PreviousControl.SetFocus
DoCmd.Requery

Exit_Find_Record_Click:
Exit Sub

Err_Find_Record_Click:
MsgBox Err.Description
Resume Exit_Find_Record_Click

End Sub

Then, they can connect to a main form with more thorough data. It works
great... except if the user presses cancel when they're prompted to enter
a
number. If they hit cancel, the next time they press the search button,
they
get an error saying that the recordsource, which is 5 fields from the
underlying table, does not exist.

Any thoughts would be appreciated.

Thanks

 

[NetworkTrade]

not sure who designed that cancel button or what it exactly is doing....but
since this seems to be the source of trouble if/when a user uses it at the
wrong time...perhaps your solution is to toggle that button between visible &
not visible at key points in the user's state....

if it is just closing the form....consider making a new button to close form
and see how that goes....

 

[bmullin via AccessMonster.com]

Thanks for you very thorough and comprehensive response.

Unfortunately, due to time, I had to go to kind of a work around.

I basically created a macro that closes then opens (again) the problem form.

I put a DoCmd to run this macro in the error portion of the code behind the
lookup button

Yes, this is a limitation of using parameters. It sort-of makes sense:
cancel the query, and the form (based on the query) doesn't work correctly.

The solution will be to omit the parameters from the query, and use the
form's Filter instead. This solves not only the problem you have, but also
the fact that anyone without an SSN will be eliminated from your query (i.e.
if the field is Null, it doesn't match the criteria you have.) It's also a
much nicer interface than those popup parameter boxes.

When you say you have a "list type form", I think you mean Datasheet view?
If so, switch it to Continuous Form view. You can lay the text boxes out
side by side so it looks similar to Datasheet but you get to use the Form
Header and Form Footer section too.

Now add a couple of unbound text boxes to the Form Header section, where the
user can enter the [Borrower ID] and/or the SSN to match. Let's name them
txtBorrower and txtSSN.

You will end up using code like this to apply the filter:

Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then Me.Dirty = False 'Save any edits

If Not IsNull(Me.txtBorrower) Then
strWhere = "([Borrower ID] Like ""*" & Me.txtBorrower & "*"") AND "
End If
If Not IsNull(Me.txtSSN) Then
strWhere = strWhere & "([SSN] Like ""*" & Me.txtSSN & "*"") AND "
End If

'Chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
'No criteria: show all records
Me.Filter = False
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = False
End If

For a downloadable example of how to do this with different field types,
see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

I have a list type form that shows a list of borrowers. The list form can
be

[quoted text clipped - 36 lines]

Thanks