Goto record in Parent form by searching criteria in Subform

[CDF]

Hi,

I need to search for criteria found in a subform which when found then
changes (goes to) the record in the parent form.

I've got 2 tables (tbl_Customer & tbl_Vehicle) linked by a Customer_ID
field with a 1:Many relationship (1 customer (Customer_ID (PK)) can
have many vehicles (Customer_ID (Fk))).

On the Customer Form there's a subform showing the customers Vehicle/s
in list form.

Each vehicle has a VIN (Vehicle ID No) field and Registration field
both of which are unique to each vehicle in the vehicle table.

I would like to search for a customer (while in the customer form) by
VIN or Rego (I will have a button for each on the Customer Form).

What code do I need to put in the properties for these buttons??

I'd prefer a pop up window (or form) requesting the information
instead of Access' 'Find & Replace' box.

What's the best way to go about this?

I'm not a programmer but have an understaning of the VB code if told
what and where to put it.

Thanks in advance.

 

[strive4peace]

Hi CDF (what is your name?)

put an unbound combobox in the header of the Customer form -- the MAIN
form, not the subform

Name --> FindVIN

RowSource -->
SELECT Customer_ID, VIN
FROM tbl_Customer INNER JOIN tbl_Vehicle
ON tbl_Customer.Customer_ID = tbl_Vehicle.Customer_ID
WHERE (VIN Is Not Null)
ORDER BY VIN;

ColumnCount --> 2
ColumnWidths --> 0;2
ListWidth --> 2

the trick is to set the column width of Customer_ID to zero -- so it is
what is stored, but it is not displayed. When a user picks a VIN, they
are actually picking a customer <smile>

I also like to change list rows (how many rows show up when you drop a
combo since 8 is not very many

ListRows --> 24

~~~

make the following function in the code behind your form:
'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "SomeID = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field (Customer_ID in your case
-- so you will have to make that change -- otherwise, no editing is
necessary), which is in the RecordSource of the form. This code assumes
your primary key is a Long Integer data type (autonumbers are long integers)

then, on the AfterUpdate event of FindVIN, this would be the code in
your [Event Procedure]
'~~~~~~~~~~~~~~~~~~~~
FindRecord
'~~~~~~~~~~~~~~~~~~~~

this works because the code refers to ActiveControl -- not a specific
control name, so if you are in the AfterUpdate event of FindVIN, that is
your active control -- slick!

~~~
You can use the same analogy to make a combobox that lists Registration
.... or anything! Remember that the Rowsource for a combo can come from
anywhere -- it can pull from multiple tables or only use one ... just
make sure that the first column is the primary key ID of the table you
want to search (and that field is part of the RecordSource for the form
you are searching).


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CDF]

Hi Crystal,

I haven't tried this yet as i'm not near the file, but from what i've
read, are you saying to put a combo box on the main form?

If so, this isn't viable as there are going to be...thousands!! of
vehicles in the list...lots of scrolling.

I need a 'search box' to pop up from the customer form that searches
the vehicle table then just 'gos' to the correct customer on the
customer form.

Does that make sense??

Regards

 

[strive4peace]

Hi Rony,

once you get the basic code in place and understand how the search is
done, the idea can be refined to use a popup form as you desire ... one
step at a time

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CDF]

Thanks Crystal.

I now see where you're going with this. I've put the code in and it
works, but I have one question.

Why if/when I choose an item in the list does the combo box show
nothing even though it goes to the correct record in the main form?

Is it because of this line?
'clear the choice to find
Me.ActiveControl = Null

If so, what is the purpose of clearing the choice? (or am I one step
ahead of you again??)

If it's got something to do with the search function, what's next?

Regards

 

[strive4peace]

Hi Rony,

the criteria currently is -->
WHERE (VIN Is Not Null)

if you are seeing blank lines, that means that VIN has a zero-length
string, or a string with only spaces. Try this for criteria instead -->

WHERE (len(trim(nz(VIN,""))) > 0)
OR
WHERE (len(trim(VIN & "")) > 0)

len -- gets the length
trim -- trims leading and trailing spaces
nz -- convert null values to empty string (in this case)
& -- concatenates an empty string to each value to give Nulls a value

you cannot use Len or Trim on Nulls without getting an error

this criteria will make sure that the length of the trimmed string is
greater than zero -- so it will have to contain data besides spaces

~~~
all this does is clear the combo:
Me.ActiveControl = Null
and since it is an unbound control, this is fine

"what is the purpose of clearing the choice? "

there is no need to keep it filled -- the control is not bound and, if
you go to another record using a different method (like the navigation
buttons at the bottom of the form), the information will not correlate
to whatever record is showing

~~~
If you are searching the recordset on another form, change the procedure
name to be specific (like FindRecord_Customer) and, substitute

Me --> forms![formname]

EXCEPT the statements that test IsNull and assign a value to mRecordID

~~~
if you are using the actual recordset to pick the record to find,
naturally, you will not want to set any control to Null

You will also want to have customer_ID IN each record -- so join that
table and put it on your form as a hidden control (Visible --> No) --
can be in the form header or footer. I usually make fill color black
and foreground color white for hidden controls so they are obvious in
the design view.

then, when you assign the ID to mRecordID, pick the Customer_ID control
instead of ActiveControl ...

If not Isnull(me.Customer_ID) then
.... actually, you can limit the records to just those that do have this
filled out -- so there would be be no need to test it

if this is just a search form, and not a form to use for changing data,
make the RecordsetType (Data tab of Form Properties) --> Snapshot

you might then, want to launch the code on the double-click event of
each control displayed in the record

~~~
once you get it working to find the right record in the main form, we
can also make it position the subform record...


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CDF]

Thanks Crystal,

What's the next step in this 'lesson'

 

[strive4peace]

Hi Rony,

by the next step, I assume you mean to position the record in the
subform as well?

modify the code to find a record for the subform:

you can use the same variable, mRecordID -- since it is no longer needed
for the main form. This would go in the IF statement block ONLY if the
mainform record is found:

'~~~~~~~~~~~~~~~~~~~~~~
mRecordID = Me.controlname_with_vehicleID

With forms!formname!subform_controlname.form

'find the first value that matches
Me.RecordsetClone.FindFirst "VehicleID = " & mRecordID

'if a matching record was found, then move to it
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If

end with
'~~~~~~~~~~~~~~~~~~~~~~

WHERE
VehicleID is the fieldname of the vehicleID primary key (assumed to be a
long integer)
formname is the name of the main form you just searched
subform_controlname is the Name property of the subform control

You may need
DoEvents
to make things show up -- only put it in if they do not display immediately

*********************
to make the code easier to read, I did not include an error handler --
but you should put it in.


'~~~~~~~~~~~~~~
'set up Error Handler - 'put this at the top of your program
' -- right after the procedure declaration
On Error GoTo Proc_Err

'...then come the statements of your procedure ...


'then the exit code and error handler statements at the bottom

Proc_Exit:
On Error Resume Next
'close and release object variables if applicable

Exit Sub ' or Function

Proc_Err:

'NOTE: replace ProcedureName with YOUR procedure name

MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
'~~~~~~~~~~~~~~


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CDF]

Thanks Crystal.

This step is not necessary for me but could be helpful for others.

I was thinking that the next step was a pop up search box/form to be
able to search by VIN or Rego instead of scrolling through the combo
boxes???

Are we up to that stage yet?

 

[strive4peace]

Hi Rony,

I gave you that information on 1-24 ..."If you are searching the
recordset on another form, change the procedure name to be specific
(like FindRecord_Customer) and, substitute..."

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CDF]

Sorry Crystal, you completely lost me then. Please bare with me as
I'm not a programmer, but I understand the VB code if given to me in
full.

Thank you for your help so far. Do you mind going over it again a bit
more clearly for a layman?

Just so we're at the same page, what I've done so far is: put in a
combo box (actually 2) in the Customer Forms Header, one for VIN no's
and one for Rego's. At the moment both are filled correctly and when I
pick an item from either list, it takes me to the correct customer on
the Customer Form.

Now I must explain 2 points;

a) atm in testing mode there are approx 20 vehicles so it's ok
searching for a client by VIN or Rego by clicking the combo boxes and
picking from either list, but eventually there are going to be
thousands of vehicles in the list so this method will be cumbersome.
Therefore I need some sort of pop up search box or form. Do you mind
going over that again? I hope you don't read this the wrong way, but
when you explain it in detail (as above), this is when I get confused.
If you could just please type the VB code and I'll be able to 'catch
on' when I'm 'playing' with the code (as I'm a hands on learner ).
Please don't take that the wrong way. I really appreciate your time
and effort.

b) As each customer will not have too many vehicles listed in the
subform, it's not necessary to "...position the record in the subform
as well" as you went to the trouble of showing. Hopefully someone else
will get use of it when searching this newsgroup as you went to all
that trouble.

Sorry for repeating myself.

Again thanks for your help so far.

 

[strive4peace]

Hi Rony (is that your name?)

you're welcome

"so this method will be cumbersome. Therefore I need some sort of pop up
search box or form."

not necessarily ... lets expand a bit more on the combo box idea in the
form header...

you understand the SQL statements to use as a RowSource for each combo
.... what if we FILTER the SQL so the combos only show a filtered list?

How would you want to filter? By specifying...-
- the first letter ?
- any letters somewhere in the beginning, middle, or end?
- a different field of information?

Decide HOW you want to limit the combo (start with one of them) and we
will help you write code that builds the SQL for the combo based on your
criteria <smile>



Warm Regards,
Crystal

remote programming and training


*
have an awesome day
*

 

[hor vannara]

Thanks Crystal.

This step is not necessary for me but could be helpful for others.

I was thinking that the next step was a pop up search box/form to be
able to search by VIN or Rego instead of scrolling through the combo
boxes???

Are we up to that stage yet?

 

[CDF]

Hi Crystal,

No my name is not Roni (I don't know where you got that, but you
seemed so determined to use it so I let you run with it ). It's
John.

Reading your above made me realise that if I start typing the VIN or
the Rego, it automatically takes me to that record (in the combo box).
If there's more than one VIN or Rego starting with the same
combination of letters/numbers the combo box list starts at what I
typed. If there's only one instance of those combination of numbers/
letters, it fills in the field with that word, so I don't think we
need to filter any further. What you have suggested works fine.

For example if I have 20,000 records in the list and am looking for
the Rego 'Hello', if I start typing H it takes me to the H's in the
list, then He takes me to the He's in the list. If there's no more
He's then as soon as I type Hel, 'helLLO' automatically gets put in
the field.

I didn't pick up on this earlier as there's only 20 vehicles in test
mode at the moment.

Again thanks for your help.

 

[strive4peace]

Hi John,

you're welcome happy to help!

....I don't know where it came from either! I wondered that myself ...
nice to know your real name <smile>

Glad you like this method -- 'find' combos in the form header are so
useful that it is rare I make any form without them!

"He's then as soon as I type Hel, 'helLLO' automatically gets put in
the field."

press the DEL key -- the letters being filled for you are highlighted --
easy to get rid of. It is one more thing to press, but the disadvantage
does not happen nearly as frequently as the advantage! There is a
property that you can set to control this.


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CDF]

Actually Crystal, the letters being 'autofilled' is a useful feature,
not a hindrance, as I can just press enter.

If I'm looking for 'hello' and there are no other hel's in the list,
autofilling with helLO is helpful.

I wasn't 'complaining' about this feature in my above post. I was
praising it.

Thanks for all your help.

 

[strive4peace]

you're welcome, John!!!

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*