Find Command in subform

[cktia]

Hello. I have a form with a main form (record source tblSewerDescriptions)
and a subform (record source tblWastewaterBackup). The original intent of
this form was to be able to use a find button on the main form to lookup
manhole numbers that were then automatically populated in the subform. The
Sewer Description table doesn't change for the most part. This works. Now,
I've got a request for people to be able to also look up the address of a
sewer backup - this is located only in the subform (tblWastewaterBackup). I
have read that you cannot have a find button on a subform - is this correct?
Any ideas on what I can do to get this to work with both the find button for
the manhole numbers and a find button for addresses? Let me know if you
require more information from me. I appreciate any help! Thanks.

 

[Jeanette Cunningham]

Hi,
Trying to understand what you are trying to achieve.
tblSewerDescriptions and tblWastewaterBackup
I assume that manhole numbers are in tblSewerDescriptions as a Primary Key
and in tblWastewaterBackup as a ForeignKey?
If you put a combobox on the mainform, users select a manhole from the list
and the subform shows the details for that manhole?
Now you want to add the address for the manhole to all the other details for
the manhole?

You could have 2 combos for searching.
Users could use either the manhole combo or the address combo.
Does this sound like it would help?
If so create the second combo for Address on the main form either next to or
above or below the manhole combo.


Jeanette Cunningham

 

[Linq Adams via AccessMonster.com]

I'll leave Jeanette to guide you thru this, since she's started, but I will
address your question:

"I have read that you cannot have a find button on a subform - is this
correct?"

Yes and no! It depends on what view subform is displayed in.On Single View
and Continuous View forms you can do pretty much as you will. But most
subforms are in Datasheet View, which means they look like a table or query
if you run by itself, or an Excel spreadsheet, if you will, and you cannot
add any unbound control (combobox, command button, label, textboxt) to a
Datasheet View form.

Well, actually you can, in Design View, add them, but when you run the form,
they don't appear, mcuh less function! The work around for this is to change
the subform to a Continuous View form, then format it cosmetically so that it
looks like a Datasheet View form.

 

[cktia]

Hello.

The main form (tbleSewerDescriptions) has the manhole numbers, size of pipe,
type of pipe, etc. - this information doesn't change. The subform
(tblWastewaterBackup) is also in a single form format rather than a database
format - this is the data entry form. The reason for the form design being
as it is, is so that when there maintenance crew gets a call that there was a
sewer backup in someone's house - they can fill this form out. We want the
form to be as easy as possible to fill out for them. I actually have a
"find" command button that when pushed brings up a find/replace popup form
and the crew member can look at a map and find what upstream manhole number
is involved with the address in question and type that number in. When they
do that - the downstream manhole number, segment number, line type, pipe
size, etc. is all filled in automatically into the subform. This is
beneficial for us so that there are not errors when typing this information
and it speeds up the data entry process.

Now, they are wanting to be able to look up an address on the subform,
tblWastewaterBackup (there are no addresses associated with
tblSewerDescriptions) - and when they click on or type in the address they
are searching for, it automatically brings up that record on the subform so
that they may add more information to that record, if needed. I have tried
to add a combo box to the subform, tblWastewaterBackup, so that it looks up
the address and date of the backup - I do get the combo box to accomplish
this, but I cannot get it to bring up the record for that address too.
Perhaps there is some code that I need to add to accomplish this?

I hope this essay helped to clarify the intent of the form/subform.
Thanks!

 

[Jeanette Cunningham]

Hi,
the extra info is helpful, however I still have another question, sorry.
The subform is used to add new maintenance calls to the database. Your
find/replace popup does an autofill of info for the new call.
The new combo you added for address is not adding a new call to the
database, it wants to show existing info for address and change any details
in the address?
--open your subform in design view from the database window
--on its property sheet, on the datatab, does it have Data Entry set to Yes?

If the above shows that I understand your setup correctly then I am going to
suggest that you make a copy of your subform and in its name include the
word Edit. This way you will know which subform is for editing the address.
--open the edit subform in design view and set its Data Entry property to no
and its Allow Additions to no
--user selects an address from the combo on existing form
--the code in the after update event for the combo opens the subform that
allows edits at the address that was selected in the combo
--code the after update event for the combo something like this
DoCmd.OpenForm "frmEdit", , , "[ID] = " & Me.txtID & "", , acDialog
replace frmEdit with the correct name of the subform
replace ID with the name of the primary key for tblWastewaterBackup
--the Edit subform will open with the details of the last call to this
address for edits to be made
--opening with acDialog makes the other form unusable while the edit subform
is open
--when the user closes the edit subform you still have your main form for
addding new maintenance calls

If that sounds like what you want, give it a try

Jeanette Cunningham