Find and Go To

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form where I would like to type in a loan number into my "Find_Loan"
field, then click a command button next to this field to search for that
record. If the record exists, I want to go to it, if it doesn't, then I'll
know I can create a new one. For example, I want to enter the information for
loan number 11111 into my database via a form, but first I want to make sure
loan number 11111 doesn't already exist. I will have thousands of loans so I
don't want to have to do an edit, find type of thing, I'd like to just type
in the loan number, hit the command button and then be brought to the record
or told it isn't there.
 
NotIT said:
I have a form where I would like to type in a loan number into my
"Find_Loan" field, then click a command button next to this field to
search for that record. If the record exists, I want to go to it, if
it doesn't, then I'll know I can create a new one. For example, I
want to enter the information for loan number 11111 into my database
via a form, but first I want to make sure loan number 11111 doesn't
already exist. I will have thousands of loans so I don't want to have
to do an edit, find type of thing, I'd like to just type in the loan
number, hit the command button and then be brought to the record or
told it isn't there.
Click to expand...

In the header of your form add an unbound TextBox labelled "Go To..." ( or
similar) and in it's AfterUpdate event have code...

Me.Filter = "LoanNumber = " & Me.TextBoxName
Me.FilterOn = True

If a record with the loan number you entered exists, it will be shown. If
not the form will automatically show a blank "New Record" ready to fill out.
 
Sorry, you're over my head, what is the "Me" in this expression? I've never
worked in code, it's always been expression builder or Macro?
Thanks!!!
 
NotIT said:
Sorry, you're over my head, what is the "Me" in this expression? I've
never worked in code, it's always been expression builder or Macro?
Thanks!!!
Click to expand...

Me is a shorthand that refers to the class object where the code is running. So
if the code is in a form's module then Me is the same as Forms!FormName and when
the code is in a report's module then it is equivlent to Reports!ReportName. It
is nice because it is shorter and you can copy code from one object to another
without having to change it.

Just find the AfterUdate property on the {Events} tab of the property sheet and
in the drop-list of choices enter [Event Procedure]. Then press the build
button [...] at the right of the property. That will open the VBA code editor
where you enter the lines from my previous post YTou substitute your actual
control and field names).

Access will already have created the two lines designating the start and end of
the sub-routine. Your code just goes between those.
 
Ok, here is what I have:
Form!QC_Audit = "LoanNumber=" & Form.Find
Form.FilterOn = True
and I get this error:
Run-time error '438':
Object doesn't support this property or method
ARRGGGG!!!! (o:

Please help!!!!!

Rick Brandt said:
NotIT said:
Sorry, you're over my head, what is the "Me" in this expression? I've
never worked in code, it's always been expression builder or Macro?
Thanks!!!
Click to expand...

Me is a shorthand that refers to the class object where the code is running. So
if the code is in a form's module then Me is the same as Forms!FormName and when
the code is in a report's module then it is equivlent to Reports!ReportName. It
is nice because it is shorter and you can copy code from one object to another
without having to change it.

Just find the AfterUdate property on the {Events} tab of the property sheet and
in the drop-list of choices enter [Event Procedure]. Then press the build
button [...] at the right of the property. That will open the VBA code editor
where you enter the lines from my previous post YTou substitute your actual
control and field names).

Access will already have created the two lines designating the start and end of
the sub-routine. Your code just goes between those.
Click to expand...
 
NotIT said:
Ok, here is what I have:
Form!QC_Audit = "LoanNumber=" & Form.Find
Form.FilterOn = True
and I get this error:
Run-time error '438':
Object doesn't support this property or method
ARRGGGG!!!! (o:

Please help!!!!!
Click to expand...

What is that first line? It look nothing like what I posted.

Your code needs to do two things. It needs to set the filter property of
the form to a valid SQL WHERE clause (without the word "where") and it needs
to apply that filter by setting the FilterOn property to True.

The first line should be...
Me.Filter = "FieldName = SomeValue"

To pull (SomeValue) from your "Go To..." TextBox it would be...
Me.Filter = "FieldName = " & Me.TextBoxName

Did you name your TextBox "Find"? If so, change it as that might be a
reserved word in Access.
If your TextBox were named txtFind then your two lines would be...

Me.Filter = "LoanNumber = " & Me.txtFind
Me.FilterOn = True

(assuming LoanNumber is a numeric field)
 
Sorry, I just can't get this for some reason.
Ok, so if my form is named 'QC Audit' and I change look-up/text box to 'Loan
Lookup', then how would I write the code?
 
I just realized why else I might be confused, I thought this expression
worked INSTEAD of the command button - so I JUST have a text box, or would I
still be using both, the command button AND a text box?

Sorry, long time no Access!!!
 
NotIT said:
Sorry, I just can't get this for some reason.
Ok, so if my form is named 'QC Audit' and I change look-up/text box
to 'Loan Lookup', then how would I write the code?>
Click to expand...

If your code uses the "Me" syntax then the name of your form is irrelevent
(that's one of the advantages to using the Me syntax).

If the unbound TextBox that you use to enter the search value into is named
LoanLookup (I never put spaces in object names) and the name of the field
being filtered on is LoanNumber then the code would be (exactly)...

Me.Filter = "LoanNumber = " & Me.LoanLookup
Me.FilterOn = True

Now... you can either run this code in the click event of a CommandButton
that you place next to the LoanLookup TextBox or you can use the AfterUpdate
event of the TextBox itself so the user would just enter the value and press
<Tab> or <Enter> and the code would run. That's entirely up to you.
 
What if I want it to filter on TWO fields, sometimes OUR loan number is
captured, sometimes the Servicer's loan number, so I'd want to be able to
type either of these into the lookup field and know if either are in there.
I have two different fields for our loan number and servicer loan number
 
NotIT said:
What if I want it to filter on TWO fields, sometimes OUR loan number
is captured, sometimes the Servicer's loan number, so I'd want to be
able to type either of these into the lookup field and know if either
are in there. I have two different fields for our loan number and
servicer loan number
Click to expand...

Me.Filter = "LoanNumber = " _
& Me.LoanLookup _
& " OR ServicerLoanNumber = " _
& Me.LoanLookup
Me.FilterOn = True

Did you get the earlier filter working first?
 
Back
Top