MS Access 2000 - One form to open 4 tables

[Ross]

I am not an Access programmer, only user. I have a project that requires
Access 2000 only. I need to have a form with two fields (street, city)
trigger opening four other forms for tables that have different field names
but same street - city data.
I have an employee that needs to evaluate thru four sets of data with the
same street - city key fields.

TYIA

Ross

 

[Jeff Boyce]

Ross

"How" depends on "what", and I'm not sure I understand your underlying data
structure well enough to offer specific suggestions.

"... for tables that have different field names but same street - city
ata" -- What does this mean? Can you give an example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ross]

One table has fields 'addr_sn' and 'addr_city'
another table has 'str_name' and 'city_name'
another table has 'tn_st' and 'tn_city'
the last has table 'g_street' and 'g_city'

all four tables should have 'mostly' the same street data
we need to input partial "street" data and "city" (city could be blank, and
expect a return for all cities in each of the tables) to return all the
records that have "*valley*" in them for example, in each of the tables.

Thank you Sir

 

[Jeff Boyce]

Perhaps I'm under-caffeinated today, but I'm still having difficulty
visualizing. The field names aren't providing me enough information about
what is actually being stored in those fields.

However, based (only) on the field names, it looks like you have more than
one table with the same basic information (e.g., street, city). This may be
how you'd handle your situation ... if you were limited to using a
spreadsheet.

Access is a relational database ... what are the entities and relationships
for your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ross]

If I could send pictures, I might be more helpful to you.
I have been playing with the Northwind and attempting to accomplish what we
need and am getting close.

What I want is: One Form with 3 objects, 2 of which are the variable data
that will be used for the queries that open 4 similiar tables. The third is
just a button that says "Go Open / or Update the 4 forms".

I have this going in some test environment:
Private Sub OpenChildForm()

DoCmd.OpenForm "Ross_Orders1"
DoCmd.OpenForm "Ross_Orders2"
DoCmd.OpenForm "Ross_Orders3"
DoCmd.OpenForm "Ross_Orders4"

If Not Me![ToggleLink] Then Me![ToggleLink] = True

End Sub

I also need to run the:
Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![Ross_Orders1].DataEntry = True
Else
Forms![Ross_Orders1].Filter = "[CustomerID] = " & """" &
Me![CustomerID] & """"
Forms![Ross_Orders1].FilterOn = True
End If

End Sub

for each form independently.

Unfortunately, in my learning environment I am using Northwind with Wizard
created form from Customer. It has customer Id and a 'Go' button that opens
the tables. The 'linked' forms are related tables so when rec1 goes to rec2
the one table form changes.

That is about as far as I have gotten all day.

Thank you so much for taking the time with my post at all.

Ross

 

[Jeff Boyce]

You've described the form, and "how" you are trying to do something.

It all starts with the data, and I don't understand your data yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I could send pictures, I might be more helpful to you.
I have been playing with the Northwind and attempting to accomplish what
we
need and am getting close.

What I want is: One Form with 3 objects, 2 of which are the variable data
that will be used for the queries that open 4 similiar tables. The third
is
just a button that says "Go Open / or Update the 4 forms".

I have this going in some test environment:
Private Sub OpenChildForm()

DoCmd.OpenForm "Ross_Orders1"
DoCmd.OpenForm "Ross_Orders2"
DoCmd.OpenForm "Ross_Orders3"
DoCmd.OpenForm "Ross_Orders4"

If Not Me![ToggleLink] Then Me![ToggleLink] = True

End Sub

I also need to run the:
Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![Ross_Orders1].DataEntry = True
Else
Forms![Ross_Orders1].Filter = "[CustomerID] = " & """" &
Me![CustomerID] & """"
Forms![Ross_Orders1].FilterOn = True
End If

End Sub

for each form independently.

Unfortunately, in my learning environment I am using Northwind with Wizard
created form from Customer. It has customer Id and a 'Go' button that
opens
the tables. The 'linked' forms are related tables so when rec1 goes to
rec2
the one table form changes.

That is about as far as I have gotten all day.

Thank you so much for taking the time with my post at all.

Ross

Perhaps I'm under-caffeinated today, but I'm still having difficulty
visualizing. The field names aren't providing me enough information
about
what is actually being stored in those fields.

However, based (only) on the field names, it looks like you have more
than
one table with the same basic information (e.g., street, city). This may
be
how you'd handle your situation ... if you were limited to using a
spreadsheet.

Access is a relational database ... what are the entities and
relationships
for your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ross]

I simply would like to know how to have one form with 2 fields - (1) a
partial street name, and (2) maybe a partial city name (this field could be
blank) ... and then a 'Go' button as it were to take the 'street' data and
present the query 4 times to 4 separate unrelated tables and display the
results.

I.E. the input form could have 'valley' for Street input, and nothing for
the City field. Code would then take 'valley' and present it to the first
table with a query saying 'Give me all records where record_field:st_name
contains var_field:Street', the second query would be 'Give me all records
where record_field:addr_sn contains var_field:Street', the third and forth
the same but with the correct record_field name for that table.

the output could be:
Table1 Table2 Table3
Table4
VALLEY ST VALLEY ST SW VALLEY AVE RIVER
VALLEY RD
SW VALLEY AVE SW VALLEY AVE VALLEY RD N RIVER VALLEY
RD

Ross

You've described the form, and "how" you are trying to do something.

It all starts with the data, and I don't understand your data yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I could send pictures, I might be more helpful to you.
I have been playing with the Northwind and attempting to accomplish what
we
need and am getting close.

What I want is: One Form with 3 objects, 2 of which are the variable data
that will be used for the queries that open 4 similiar tables. The third
is
just a button that says "Go Open / or Update the 4 forms".

I have this going in some test environment:
Private Sub OpenChildForm()

DoCmd.OpenForm "Ross_Orders1"
DoCmd.OpenForm "Ross_Orders2"
DoCmd.OpenForm "Ross_Orders3"
DoCmd.OpenForm "Ross_Orders4"

If Not Me![ToggleLink] Then Me![ToggleLink] = True

End Sub

I also need to run the:
Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![Ross_Orders1].DataEntry = True
Else
Forms![Ross_Orders1].Filter = "[CustomerID] = " & """" &
Me![CustomerID] & """"
Forms![Ross_Orders1].FilterOn = True
End If

End Sub

for each form independently.

Unfortunately, in my learning environment I am using Northwind with Wizard
created form from Customer. It has customer Id and a 'Go' button that
opens
the tables. The 'linked' forms are related tables so when rec1 goes to
rec2
the one table form changes.

That is about as far as I have gotten all day.

Thank you so much for taking the time with my post at all.

Ross

Perhaps I'm under-caffeinated today, but I'm still having difficulty
visualizing. The field names aren't providing me enough information
about
what is actually being stored in those fields.

However, based (only) on the field names, it looks like you have more
than
one table with the same basic information (e.g., street, city). This may
be
how you'd handle your situation ... if you were limited to using a
spreadsheet.

Access is a relational database ... what are the entities and
relationships
for your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One table has fields 'addr_sn' and 'addr_city'
another table has 'str_name' and 'city_name'
another table has 'tn_st' and 'tn_city'
the last has table 'g_street' and 'g_city'

all four tables should have 'mostly' the same street data
we need to input partial "street" data and "city" (city could be blank,
and
expect a return for all cities in each of the tables) to return all the
records that have "*valley*" in them for example, in each of the
tables.

Thank you Sir

:

Ross

"How" depends on "what", and I'm not sure I understand your underlying
data
structure well enough to offer specific suggestions.

"... for tables that have different field names but same street - city
ata" -- What does this mean? Can you give an example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not an Access programmer, only user. I have a project that
requires
Access 2000 only. I need to have a form with two fields (street,
city)
trigger opening four other forms for tables that have different
field
names
but same street - city data.
I have an employee that needs to evaluate thru four sets of data
with
the
same street - city key fields.

TYIA

Ross

 

[Jeff Boyce]

Ross

I suspect that your data is organized more like a spreadsheet than a
relational database. I've been asking about the data because you (and
Access) will have to work overtime to overcome data that is not
well-normalized (i.e., Access isn't optimized for 'sheet data).

If "normalization" and "relational database" are not familiar terms, plan on
spending some time working your way up the learning curves if you want to
get the best out of Access.

That said, look in Access HELP (and on-line) for "UNION" queries. Based on
what I understand so far, this might (?MIGHT?) apply.

By the way, "4 separate unrelated tables" doesn't sound like a relational
database. How is it that you have potential addresses in 4 different
tables?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I simply would like to know how to have one form with 2 fields - (1) a
partial street name, and (2) maybe a partial city name (this field could
be
blank) ... and then a 'Go' button as it were to take the 'street' data and
present the query 4 times to 4 separate unrelated tables and display the
results.

I.E. the input form could have 'valley' for Street input, and nothing for
the City field. Code would then take 'valley' and present it to the first
table with a query saying 'Give me all records where record_field:st_name
contains var_field:Street', the second query would be 'Give me all records
where record_field:addr_sn contains var_field:Street', the third and forth
the same but with the correct record_field name for that table.

the output could be:
Table1 Table2 Table3
Table4
VALLEY ST VALLEY ST SW VALLEY AVE RIVER
VALLEY RD
SW VALLEY AVE SW VALLEY AVE VALLEY RD N RIVER
VALLEY
RD

Ross

You've described the form, and "how" you are trying to do something.

It all starts with the data, and I don't understand your data yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I could send pictures, I might be more helpful to you.
I have been playing with the Northwind and attempting to accomplish
what
we
need and am getting close.

What I want is: One Form with 3 objects, 2 of which are the variable
data
that will be used for the queries that open 4 similiar tables. The
third
is
just a button that says "Go Open / or Update the 4 forms".

I have this going in some test environment:
Private Sub OpenChildForm()

DoCmd.OpenForm "Ross_Orders1"
DoCmd.OpenForm "Ross_Orders2"
DoCmd.OpenForm "Ross_Orders3"
DoCmd.OpenForm "Ross_Orders4"

If Not Me![ToggleLink] Then Me![ToggleLink] = True

End Sub

I also need to run the:
Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![Ross_Orders1].DataEntry = True
Else
Forms![Ross_Orders1].Filter = "[CustomerID] = " & """" &
Me![CustomerID] & """"
Forms![Ross_Orders1].FilterOn = True
End If

End Sub

for each form independently.

Unfortunately, in my learning environment I am using Northwind with
Wizard
created form from Customer. It has customer Id and a 'Go' button that
opens
the tables. The 'linked' forms are related tables so when rec1 goes to
rec2
the one table form changes.

That is about as far as I have gotten all day.

Thank you so much for taking the time with my post at all.

Ross


:

Perhaps I'm under-caffeinated today, but I'm still having difficulty
visualizing. The field names aren't providing me enough information
about
what is actually being stored in those fields.

However, based (only) on the field names, it looks like you have more
than
one table with the same basic information (e.g., street, city). This
may
be
how you'd handle your situation ... if you were limited to using a
spreadsheet.

Access is a relational database ... what are the entities and
relationships
for your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One table has fields 'addr_sn' and 'addr_city'
another table has 'str_name' and 'city_name'
another table has 'tn_st' and 'tn_city'
the last has table 'g_street' and 'g_city'

all four tables should have 'mostly' the same street data
we need to input partial "street" data and "city" (city could be
blank,
and
expect a return for all cities in each of the tables) to return all
the
records that have "*valley*" in them for example, in each of the
tables.

Thank you Sir

:

Ross

"How" depends on "what", and I'm not sure I understand your
underlying
data
structure well enough to offer specific suggestions.

"... for tables that have different field names but same street -
city
ata" -- What does this mean? Can you give an example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not an Access programmer, only user. I have a project that
requires
Access 2000 only. I need to have a form with two fields (street,
city)
trigger opening four other forms for tables that have different
field
names
but same street - city data.
I have an employee that needs to evaluate thru four sets of data
with
the
same street - city key fields.

TYIA

Ross

 

[Ross]

Unfortunately pictures cannot be a part of this type of discussion because it
would have saved more than a thousand words for both of us (and several days
of time for me). I seem to be unable to get my objective expressed
appropriately with just words.

We have four databases from four different sources in the county. These
databases are maintained by separate agencies. An 'address' would be the only
thing in each of the database tables that can be associated with each other.
One db may have many records for the same address, or many records with the
same address spelled incorrectly. My employee has the task of viewing each
table side by side (all four at one time) attempting to find errors - one
record at a time - or several at a time, what ever the case may be.

If he could put "main" + "cleveland" in as a search criteria (form) that
would query and display all four ... then he wouldn't have to go to each
table display property and change the "street" and "city" fields to 'Like
"*main*" ' and ' Like "*cleav*" ' to get each one to display.

The databases cannot be related because of mispellings, so searches based on
'subsets' of a field make the task much easier.

I am simply trying to setup an environment that would be easier for him to
put the criteria in once, push a button and all four tables display the
result.

Ross

Ross

I suspect that your data is organized more like a spreadsheet than a
relational database. I've been asking about the data because you (and
Access) will have to work overtime to overcome data that is not
well-normalized (i.e., Access isn't optimized for 'sheet data).

If "normalization" and "relational database" are not familiar terms, plan on
spending some time working your way up the learning curves if you want to
get the best out of Access.

That said, look in Access HELP (and on-line) for "UNION" queries. Based on
what I understand so far, this might (?MIGHT?) apply.

By the way, "4 separate unrelated tables" doesn't sound like a relational
database. How is it that you have potential addresses in 4 different
tables?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I simply would like to know how to have one form with 2 fields - (1) a
partial street name, and (2) maybe a partial city name (this field could
be
blank) ... and then a 'Go' button as it were to take the 'street' data and
present the query 4 times to 4 separate unrelated tables and display the
results.

I.E. the input form could have 'valley' for Street input, and nothing for
the City field. Code would then take 'valley' and present it to the first
table with a query saying 'Give me all records where record_field:st_name
contains var_field:Street', the second query would be 'Give me all records
where record_field:addr_sn contains var_field:Street', the third and forth
the same but with the correct record_field name for that table.

the output could be:
Table1 Table2 Table3
Table4
VALLEY ST VALLEY ST SW VALLEY AVE RIVER
VALLEY RD
SW VALLEY AVE SW VALLEY AVE VALLEY RD N RIVER
VALLEY
RD

Ross

You've described the form, and "how" you are trying to do something.

It all starts with the data, and I don't understand your data yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I could send pictures, I might be more helpful to you.
I have been playing with the Northwind and attempting to accomplish
what
we
need and am getting close.

What I want is: One Form with 3 objects, 2 of which are the variable
data
that will be used for the queries that open 4 similiar tables. The
third
is
just a button that says "Go Open / or Update the 4 forms".

I have this going in some test environment:
Private Sub OpenChildForm()

DoCmd.OpenForm "Ross_Orders1"
DoCmd.OpenForm "Ross_Orders2"
DoCmd.OpenForm "Ross_Orders3"
DoCmd.OpenForm "Ross_Orders4"

If Not Me![ToggleLink] Then Me![ToggleLink] = True

End Sub

I also need to run the:
Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![Ross_Orders1].DataEntry = True
Else
Forms![Ross_Orders1].Filter = "[CustomerID] = " & """" &
Me![CustomerID] & """"
Forms![Ross_Orders1].FilterOn = True
End If

End Sub

for each form independently.

Unfortunately, in my learning environment I am using Northwind with
Wizard
created form from Customer. It has customer Id and a 'Go' button that
opens
the tables. The 'linked' forms are related tables so when rec1 goes to
rec2
the one table form changes.

That is about as far as I have gotten all day.

Thank you so much for taking the time with my post at all.

Ross


:

Perhaps I'm under-caffeinated today, but I'm still having difficulty
visualizing. The field names aren't providing me enough information
about
what is actually being stored in those fields.

However, based (only) on the field names, it looks like you have more
than
one table with the same basic information (e.g., street, city). This
may
be
how you'd handle your situation ... if you were limited to using a
spreadsheet.

Access is a relational database ... what are the entities and
relationships
for your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

One table has fields 'addr_sn' and 'addr_city'
another table has 'str_name' and 'city_name'
another table has 'tn_st' and 'tn_city'
the last has table 'g_street' and 'g_city'

all four tables should have 'mostly' the same street data
we need to input partial "street" data and "city" (city could be
blank,
and
expect a return for all cities in each of the tables) to return all
the
records that have "*valley*" in them for example, in each of the
tables.

Thank you Sir

:

Ross

"How" depends on "what", and I'm not sure I understand your
underlying
data
structure well enough to offer specific suggestions.

"... for tables that have different field names but same street -
city
ata" -- What does this mean? Can you give an example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not an Access programmer, only user. I have a project that
requires
Access 2000 only. I need to have a form with two fields (street,
city)
trigger opening four other forms for tables that have different
field
names
but same street - city data.
I have an employee that needs to evaluate thru four sets of data
with
the
same street - city key fields.

TYIA

Ross

 

[John W. Vinson]

We have four databases from four different sources in the county. These
databases are maintained by separate agencies. An 'address' would be the only
thing in each of the database tables that can be associated with each other.
One db may have many records for the same address, or many records with the
same address spelled incorrectly. My employee has the task of viewing each
table side by side (all four at one time) attempting to find errors - one
record at a time - or several at a time, what ever the case may be.

If he could put "main" + "cleveland" in as a search criteria (form) that
would query and display all four ... then he wouldn't have to go to each
table display property and change the "street" and "city" fields to 'Like
"*main*" ' and ' Like "*cleav*" ' to get each one to display.

The databases cannot be related because of mispellings, so searches based on
'subsets' of a field make the task much easier.

I am simply trying to setup an environment that would be easier for him to
put the criteria in once, push a button and all four tables display the
result.

I'd use an unbound main form with your two textboxes, and four subforms (one
for each of these unrelated tables). The subforms would have nothing in their
Master/Child Link Field.

Base each subform on its own Query using criteria such as

LIKE "*" & Forms!Mainform!txtCity & "*"

on the city, and

LIKE "*" & Forms!Mainform!txtStreet & "*"

on the street. Put code in each textbox's AfterUpdate event to Requery all of
the subforms.

 

[Ross]

Ever been to McMinnville?
I had a Boss some years ago with the same name.

 

[Ross]

I am pretty sure this is going down the right lane for me, but being a
non-access code person, I am not quite certain of the details to accomplish
this.
a. Create an Unbound MainForm that has two Unbound text boxes, ie 'Street'
and 'City'.
b. ? Use the 'Subform/Subreport' tool to place a subform on the MainForm
b. ? Use the 'Subform/Subreport' tool to place a subform for all four
subforms on the MainForm
c. Create a Query that has the "WHERE (((Table_1.ShipName) Like "*" &
[Forms]![Table1]![shipname]) AND ((Table_1.ShipCity) Like "*" &
[Forms]![Table1]![Shipcity]));"
d. ? Create subforms 'Table1', 'Table2', etc to somehow display data from
the input of MainForm Textbox 'Street' and 'City' AfterUpdate event?

Ross

Please accept my apologies for being so short on knowledge of this program.
I have never had the need to create this type of environment before.

 

[John W. Vinson]

I am pretty sure this is going down the right lane for me, but being a
non-access code person, I am not quite certain of the details to accomplish
this.
a. Create an Unbound MainForm that has two Unbound text boxes, ie 'Street'
and 'City'.
b. ? Use the 'Subform/Subreport' tool to place a subform on the MainForm
b. ? Use the 'Subform/Subreport' tool to place a subform for all four
subforms on the MainForm
c. Create a Query that has the "WHERE (((Table_1.ShipName) Like "*" &
[Forms]![Table1]![shipname]) AND ((Table_1.ShipCity) Like "*" &
[Forms]![Table1]![Shipcity]));"
d. ? Create subforms 'Table1', 'Table2', etc to somehow display data from
the input of MainForm Textbox 'Street' and 'City' AfterUpdate event?

Sequence rearrangement here for convenience:

1. Create a new Form. Don't select ANY tables. Just use the toolbox textbox
wizard to put two textboxes on the form; name them txtStreet and txtCity.
Leave lots of room on the form. Save the form as frmMain.

2. Create a Query based on each of the four tables that you want to compare,
selecting those fields that you want to see.

Put a criterion on the Street field of

LIKE "*" & [Forms]![frmMain]![txtStreet] & "*"

and on City of

LIKE "*" & [Forms]![frmMain]![txtCity] & "*"

You'll now have four similar queries based on your four tables. Name them
something meaningful to you (e.g. not Query1, Query2 etc.).

3. Create a Form based on each of the four queries, using the forms wizard or
manually. Lay out the controls as you see fit. I'd suggest using Continuous
Form view so you can see multiple records.

4. Open frmMain in design view and drag each of the four new forms onto it.
You'll now have four subforms. Note the names that Access gives them (and
change them to something meaningful if Access' name isn't).

5. Select each of txtCity and txtStreet and view its properties. Find the
AfterUpdate event on the Events tab; select the Code Builder. Access will give
you a Sub and End Sub line; edit it to

Private Sub txtCity_AfterUpdate()
Me!subFirstSubform.Requery
Me!subSecondSubform.Requery
Me!subThirdSubform.Requery
Me!subFourthSubform.Requery
End Sub

and the same for txtStreet.

using the actual subform names.

Save the form.

Now open it and type in a city and street name. You should see the "hits" in
the four subforms.

 

[Ross]

Thank you VERY Much Sir!
If I could press you just a bit further ... Please,
How would you have these 'subforms' Not be in the frmMain, but rather
separate windows to be positioned around the desktop?

Ross

I am pretty sure this is going down the right lane for me, but being a
non-access code person, I am not quite certain of the details to accomplish
this.
a. Create an Unbound MainForm that has two Unbound text boxes, ie 'Street'
and 'City'.
b. ? Use the 'Subform/Subreport' tool to place a subform on the MainForm
b. ? Use the 'Subform/Subreport' tool to place a subform for all four
subforms on the MainForm
c. Create a Query that has the "WHERE (((Table_1.ShipName) Like "*" &
[Forms]![Table1]![shipname]) AND ((Table_1.ShipCity) Like "*" &
[Forms]![Table1]![Shipcity]));"
d. ? Create subforms 'Table1', 'Table2', etc to somehow display data from
the input of MainForm Textbox 'Street' and 'City' AfterUpdate event?

Sequence rearrangement here for convenience:

1. Create a new Form. Don't select ANY tables. Just use the toolbox textbox
wizard to put two textboxes on the form; name them txtStreet and txtCity.
Leave lots of room on the form. Save the form as frmMain.

2. Create a Query based on each of the four tables that you want to compare,
selecting those fields that you want to see.

Put a criterion on the Street field of

LIKE "*" & [Forms]![frmMain]![txtStreet] & "*"

and on City of

LIKE "*" & [Forms]![frmMain]![txtCity] & "*"

You'll now have four similar queries based on your four tables. Name them
something meaningful to you (e.g. not Query1, Query2 etc.).

3. Create a Form based on each of the four queries, using the forms wizard or
manually. Lay out the controls as you see fit. I'd suggest using Continuous
Form view so you can see multiple records.

4. Open frmMain in design view and drag each of the four new forms onto it.
You'll now have four subforms. Note the names that Access gives them (and
change them to something meaningful if Access' name isn't).

5. Select each of txtCity and txtStreet and view its properties. Find the
AfterUpdate event on the Events tab; select the Code Builder. Access will give
you a Sub and End Sub line; edit it to

Private Sub txtCity_AfterUpdate()
Me!subFirstSubform.Requery
Me!subSecondSubform.Requery
Me!subThirdSubform.Requery
Me!subFourthSubform.Requery
End Sub

and the same for txtStreet.

using the actual subform names.

Save the form.

Now open it and type in a city and street name. You should see the "hits" in
the four subforms.

 

[Ross]

Your suggestions worked wonderfully. Thank you.
One of the 'issues' my Emplyee has with the subforms is after a search and
all show the right info, he would like to hightlight some rows in one
subform, keep them highlighted and highlight some more in each other subform.
I am assuming this would work if the subforms were "outside" of the frmMain?

Ross

Thank you VERY Much Sir!
If I could press you just a bit further ... Please,
How would you have these 'subforms' Not be in the frmMain, but rather
separate windows to be positioned around the desktop?

Ross

I am pretty sure this is going down the right lane for me, but being a
non-access code person, I am not quite certain of the details to accomplish
this.
a. Create an Unbound MainForm that has two Unbound text boxes, ie 'Street'
and 'City'.
b. ? Use the 'Subform/Subreport' tool to place a subform on the MainForm
b. ? Use the 'Subform/Subreport' tool to place a subform for all four
subforms on the MainForm
c. Create a Query that has the "WHERE (((Table_1.ShipName) Like "*" &
[Forms]![Table1]![shipname]) AND ((Table_1.ShipCity) Like "*" &
[Forms]![Table1]![Shipcity]));"
d. ? Create subforms 'Table1', 'Table2', etc to somehow display data from
the input of MainForm Textbox 'Street' and 'City' AfterUpdate event?

Sequence rearrangement here for convenience:

1. Create a new Form. Don't select ANY tables. Just use the toolbox textbox
wizard to put two textboxes on the form; name them txtStreet and txtCity.
Leave lots of room on the form. Save the form as frmMain.

2. Create a Query based on each of the four tables that you want to compare,
selecting those fields that you want to see.

Put a criterion on the Street field of

LIKE "*" & [Forms]![frmMain]![txtStreet] & "*"

and on City of

LIKE "*" & [Forms]![frmMain]![txtCity] & "*"

You'll now have four similar queries based on your four tables. Name them
something meaningful to you (e.g. not Query1, Query2 etc.).

3. Create a Form based on each of the four queries, using the forms wizard or
manually. Lay out the controls as you see fit. I'd suggest using Continuous
Form view so you can see multiple records.

4. Open frmMain in design view and drag each of the four new forms onto it.
You'll now have four subforms. Note the names that Access gives them (and
change them to something meaningful if Access' name isn't).

5. Select each of txtCity and txtStreet and view its properties. Find the
AfterUpdate event on the Events tab; select the Code Builder. Access will give
you a Sub and End Sub line; edit it to

Private Sub txtCity_AfterUpdate()
Me!subFirstSubform.Requery
Me!subSecondSubform.Requery
Me!subThirdSubform.Requery
Me!subFourthSubform.Requery
End Sub

and the same for txtStreet.

using the actual subform names.

Save the form.

Now open it and type in a city and street name. You should see the "hits" in
the four subforms.

 

[John W. Vinson]

Your suggestions worked wonderfully. Thank you.
One of the 'issues' my Emplyee has with the subforms is after a search and
all show the right info, he would like to hightlight some rows in one
subform, keep them highlighted and highlight some more in each other subform.
I am assuming this would work if the subforms were "outside" of the frmMain?

Ummmm... No.

You can't put subforms on the desktop and it wouldn't help anyway.

What's "highlighting"? What will be done with the data in these?

If you don't have control over the tables, it's going to be difficult to flag
the records (not impossible but not all that easy either). I'll think about
it; if you could post back with a bit more description of your process someone
might have another suggestion.