Code to open 1 form using different tables

[Guest]

I have a database that has about 10 tables that are exactly alike but they
are not related in any way. I found code that allows the user to open a form
and do a search on anything in the form, sort by column, add records, delete
records, etc. The problem is that there are modules where the table to use
is hardcoded. I'd like to create one form and pass code to the module that
will tell it which table to use. My main form has buttons that they will
click based on product. I really don't want to create a form for every table
that will have exactly the same fields in it but with different data. Since
I will know which table should be opened when they click the button on the
main form, I would think I could pass that information. I can get the form
name but when I try to pass it, the sort does not work. Here's tpart of the
code from the module:

Public Function BuildFilteredSQL(filtertext As String, filterType As String,
sqltext As String) As String
On Error GoTo Err_BuildFilteredSQL
'Builds a sql statement using the filtertext, filtertype and sqltext
'The filtertext is the string to filter on
'The filterType determines what fields are used to do the filtering on
'Note: these are hardcoded in this routine
'the sqltext determines the base sql query to modify

Dim sql As String
Dim fff As Integer
Dim sqlwhere As String
Dim doneFlag As Integer

sql = sqltext
'good for debugging
'MsgBox sql & "||"

'take off extra spaces and ";" if they exist
sql = RTrim(sql)
If (Right(sql, 1) = ";") Then
sql = Left(sql, Len(sql) - 1)
End If

'Build a new WHERE clause based on passed in parameters
If (filtertext <> "") And (filterType <> "") Then
' Select Case filterType
' Case "Customer"
' sqlwhere = "WHERE ((tblCustomer.FirstName Like '*" &
filtertext & "*') OR (tblCustomer.LastName Like '*" & filtertext & "*') OR
(tblCustomer.CompanyName Like '*" & filtertext & "*') OR (tblCustomer.Address
Like '*" & filtertext & "*') OR (tblCustomer.Phone Like '*" & filtertext &
"*') OR (tblCustomer.Mobile Like '*" & filtertext & "*') OR
(tblCustomer.Email Like '*" & filtertext & "*'))"
filterType = "WHERE ((ASHRAEHousings.SerialNumber Like '*" &
filtertext & "*') OR (ASHRAEHousings.Product Like '*" & filtertext & "*') OR
(ASHRAEHousings.ControlNumber Like '*" & filtertext & "*') OR
(ASHRAEHousings.SoldTo Like '*" & filtertext & "*') OR
(ASHRAEHousings.EndUser Like '*" & filtertext & "*') OR
(ASHRAEHousings.SOSelling Like '*" & filtertext & "*') OR
(ASHRAEHousings.SOSellingDestination Like '*" & filtertext & "*') OR
(ASHRAEHousings.ProductCode Like '*" & filtertext & "*'))"
' Case "Resource"
' sqlwhere = "WHERE ((tblResource.FirstName Like '*" &
filtertext & "*') OR (tblResource.LastName Like '*" & filtertext & "*') OR
(tblResource.NameOnSchedule Like '*" & filtertext & "*') OR
(tblResource.Phone Like '*" & filtertext & "*') OR (tblResource.MobilePhone
Like '*" & filtertext & "*') OR (tblResource.Email Like '*" & filtertext &
"*'))"
' End Select
End If

In the above code, ASHRAE Housings is just one of the tables. It's the
filterType that will not worik

Thanks to anyone that can help.

 

[Larry Linson]

Almost certainly, you have an inefficient database design, one that will
continue to cause you difficulties (as you are already experiencing). It
would be far better if you would describe in more detail what you have, what
you are trying to accomplish, and then perhaps someone can offer suggestions
for accomplishing your purpose in a more effective way. I am not the only
one here who is reluctant to help someone go farther down the "wrong path"
which likely will cause them more trouble and which they will eventually
have to "backtrack" and correct, anyway.

Larry Linson
Microsoft Office Access MVP

"doberhausen(AllownoSpam)@bellsouth.net"

 

[Guest]

The reason that all the table designs are the same is because there are
different people in different departments wanting to add and update the
records for product orders that only they are responsible. They don't want
everyone in one large table and have a problem with someone adding or
deleting records that they are not responsible for. Maybe that's bad design.
If so, give me a suggestion or alternative on changing that. Having separate
tables also reduces the size of the table which could be many thousands of
records in just a few months if all product orders were in one table. First
problem is trying to get one form to use different tables for its data
display based on the product button clicked on the main menu. Since all the
tables have the same design, it doesn't seem like it should be so hard just
to display data using "one" form where the only thing that changes is the
data based on the product that they selected when they clicked their
product's button on the main menu. If I get that to work and capture the
name of the table I'd like to pass that name to the code for doing the
filtering when searching. The form has a search ubound box that runs a text
filter procedure that uses the module code that I sent yesterday. Here is
the code for the text filtering that is run when the user types in the text
that they are searching for in the form.

Private Sub txtFilter_AfterUpdate()
Dim newsql As String
Dim lastsql As String

lastsql = Me.ListProduct.RowSource 'hold just in case
newsql = BuildFilteredSQL(Me.txtFilter, "Products", OriginalSQL) 'build
new query
Me.ListProduct.RowSource = newsql
Call RequeryList
If (Me.ListProduct.ListCount <= 1) Then 'revert back if no records
returned
MsgBox "No matching records can be found", vbOKOnly, "Search Failed,
Try again"
Me.ListProduct.RowSource = lastsql
Call RequeryList
Me.txtFilter = LastFilter
End If
Me.ListProduct.Selected(1) = True
If (Nz(Me.txtFilter, "") <> "") Then 'if filtertext is still there then
show "Show all .." button
Me.cmdShowAll.Visible = True
End If

End Sub

Private Sub txtFilter_Enter()
LastFilter = Nz(Me.txtFilter, "")
End Sub

As you can see above "Products" is hard coded which is used in the
BuildFilteredSQL Module. The code was taken from a sample database on the
internet at RPT Software's website on Filtering and Data Entry. It works
great when going against a table name that you can hardcode. Hopefully, this
makes sense. If not, thanks for taking the time to read about my problem.

 

[Bob Quintal]

=?Utf-8?B?ZG9iZXJoYXVzZW4oQWxsb3dub1NwYW0pQGJlbGxzb3V0aC5uZXQ=?=

The reason that all the table designs are the same is because
there are different people in different departments wanting to add
and update the records for product orders that only they are
responsible. They don't want everyone in one large table and have
a problem with someone adding or deleting records that they are
not responsible for. Maybe that's bad design.

Instead of segregating records to a separate table per 'owner', add
an 'owner' column to the one table and filter based on allowing only
the owner to adit and delete records. That's done every day in
hundreds if not thousands of applications.

If so, give me a suggestion or alternative on changing that.
Having separate
tables also reduces the size of the table which could be many
thousands of records in just a few months if all product orders
were in one table.

Access doesn't care how big any table is, the size limit is based on
the total bytes that are used by records and other objects.

First problem is trying to get one form to use

different tables for its data display based on the product button
clicked on the main menu.

Solved handily by filtering the one table.

Since all the tables have the same
design, it doesn't seem like it should be so hard just to display
data using "one" form where the only thing that changes is the
data based on the product that they selected when they clicked
their product's button on the main menu.

"It doesn't seem like it should be so hard....". Well it's a lot
easier to do the design right.

[snipped a lot]

 

[Larry Daugherty]

This is another Larry ....

From what you've written, all of the tables should be merged into one
table. There should be a "Department" field into which the department
name goes. Retrieving data by department is then a matter of setting
that criteria for that field in the underlying query.

It seems that you may have your users actually mucking around in the
*tables*, If you are, that's an invitation to disaster; lots of data
errors by people getting into each other's data by mistake, simple
data errors, typos. etc. Tables provide limited error checking.
Tables are for the storage of data. In spite of some of the many
dysfunctional ways that Microsoft leads newbies into traps, tables
should never even be seen by the users. They shouldn't know about
them or care about them. Further reading seems to indicate that you
provide Forms.

It's up to you as a developer to provide Forms for your users to enter
and massage data and Reports for presentation and printing that data.
Along the way you also need to create Queries and Relationships. Your
users shouldn't have to know anything about them either.

Back to the current issue:

Always before fiddling with the design of your application, *make a
backup*.

You can easily accomplish the merge by starting with an empty table of
the current design and adding the field DepartmentName or something
like that. Then append the data in the existing tables one by one.
You can use the same query to do the whole job but change the value of
DepartmentName to reflect the actual name you want for that table's
data.

Change the design of the existing Form to allow the user to select
which Department's data they want to view and interact with. A
combobox is a very convenient way to do that. I'd suggest one labeled
"Department" in the Header of the Form you've provided for them.

I don't really understand the rest of your issue. You need to post
back with the details of your schema. Also, buttons on a "main menu"
don't really have a context. It's kind of possible to infer what
you're doing in a general way but you need help with specifics. To
get it, you'll have to fill in the details for us. We can't see your
screen nor read all of your code.

Speaking of code, I haven't analyzed yours; there's no context.

HTH

 

[Guest]

This is another Larry ....

From what you've written, all of the tables should be merged into one
table. There should be a "Department" field into which the department
name goes. Retrieving data by department is then a matter of setting
that criteria for that field in the underlying query.

It seems that you may have your users actually mucking around in the
*tables*, If you are, that's an invitation to disaster; lots of data
errors by people getting into each other's data by mistake, simple
data errors, typos. etc. Tables provide limited error checking.
Tables are for the storage of data. In spite of some of the many
dysfunctional ways that Microsoft leads newbies into traps, tables
should never even be seen by the users. They shouldn't know about
them or care about them. Further reading seems to indicate that you
provide Forms.

It's up to you as a developer to provide Forms for your users to enter
and massage data and Reports for presentation and printing that data.
Along the way you also need to create Queries and Relationships. Your
users shouldn't have to know anything about them either.

Back to the current issue:

Always before fiddling with the design of your application, *make a
backup*.

You can easily accomplish the merge by starting with an empty table of
the current design and adding the field DepartmentName or something
like that. Then append the data in the existing tables one by one.
You can use the same query to do the whole job but change the value of
DepartmentName to reflect the actual name you want for that table's
data.

Change the design of the existing Form to allow the user to select
which Department's data they want to view and interact with. A
combobox is a very convenient way to do that. I'd suggest one labeled
"Department" in the Header of the Form you've provided for them.

I don't really understand the rest of your issue. You need to post
back with the details of your schema. Also, buttons on a "main menu"
don't really have a context. It's kind of possible to infer what
you're doing in a general way but you need help with specifics. To
get it, you'll have to fill in the details for us. We can't see your
screen nor read all of your code.

Speaking of code, I haven't analyzed yours; there's no context.

HTH