Anyone out there, need help with Design

[Brad_A]

I apparently lost the person helping me so I will start over here in hopes of
getting some help from someone that is more familiar. Once I do it once, I
learn it, but this is my first time for setting up a database with access.

I have 7 facilities... I want each facility to have a simple form to enter
data with. I want each facility to only have access to their own facility's
database. I want each of these facilities information placed into a master
database to run reports, queries, etc. for analysis.

Thus, I want the table Main_DB (the main database) to contain the informaton
for tables Facility1 through Facility7. How do I do this? All fields are
the same in every database. Please use the names of Facility1-7 when writing
code so I can better understand it.

 

[Lynn Trapp]

Brad,
I think I posted something in your original thread but Brett was doing a
fine job of working with you so I didn't interrupt. Basically, you need to
only have one field that stores the facility name (Facility1, Facility7,
etc.) and use queries to make that information available to the users at
each facility. That way, all the information is available to whoever also
needs to run reports for ALL or Multiple facilities.

 

[Brad_A]

Yeah, Brett stopped responding for some reason. I need a step by step on how
to do this.

 

[Brad_A]

BTW, I do have a DB that has each facility name as a column field. I just
need a step by step no how to get it all connected and how to restrict access
so that each facility only gets access to their own information.

 

[Lynn Trapp]

Brad,
I think you misunderstood me. You don't want a separate field for each
facility. Rather you need to have ONE Field -- let's call it Facility. You
will need to enter the appropriate facility number in that field. Then, to
make it possible for Facility1 to see only their data, the simplest thing to
do is create a separate Frontend that has a query similar to the following
as the recordsource for their data entry form.

SELECT Field1, Field2, Field3, .... FieldN
From YourTable
Where Facility = 1;

Start off with giving that a try and then post back if you have further
questions.

 

[Brad_A]

I have the Facility Name as a pull down option so each field column heading
is the facility name in the "Facility" database. Where exactly do I enter
that code at? I have one main DB, and a seperate form for each facility.

 

[Brad_A]

I am getting all kinds of parameters. Not doing me any good.

 

[Lynn Trapp]

I have the Facility Name as a pull down option so each field column
heading

is the facility name in the "Facility" database.

That's the problem. You should not be storing data in your field names.
Naming your fields Facility1, Facility2, Facility3, etc. is doing just that.
You need ONE column named Facility and then store the data in that column
appropriate to the Facility. Thus, a record might look like this:

Field1 Field2 Field3 Facility
Jack Smith Salesman 1
Dave Jones Programmer 2

 

[Lynn Trapp]

I am getting all kinds of parameters. Not doing me any good.
Brad,
You have to keep in mind that I can't see your database. It's impossible for
me to know what is causing the prompts for parameter input without seeing
the SQL statement behind your queries.

 

[Brad_A]

Okay, I will describe the whole thing.

I have a master database with 10 fields. So, for an example use Field1,
Field2, etc. One of the fields is facility, in which there are seven. It is
done with a pull down menu that receives its data from the Facility database,
which has the 7 facility names (Facility1, Facility2, etc.). This form
needs to be easy to fill out.

I want to send the database to each facility (or host over internal
network). I want them to fill in their data, and form information about the
facility individually as well as all facilities.

The only database that I have made is of the simple one user variety. I am
trying to learn how to do one that requires 7 users to have seperate
databases, yet one big one for me. The best way to learn is to get a step by
step so that I fully understand what each step is doing and its purpose.

 

[Lynn Trapp]

I have a master database with 10 fields. So, for an example use Field1,

Field2, etc. One of the fields is facility, in which there are seven. It is
done with a pull down menu that receives its data from the Facility database,
which has the 7 facility names (Facility1, Facility2, etc.). This form
needs to be easy to fill out.

That sounds pretty good then. For the Facility field, create a combobox with
the following rowsource (change fieldnames appropriately):

Select FacilityName
From Facility;

Then the users will need to select that for each record they enter.

 

[Brad_A]

Where exactly is the

Select FacilityName
From Facility;

placed? Where would I enter that information?

 

[Lynn Trapp]

in the RowSource property of the combo box.

 

[Brad_A]

Is this in the main_db, form design, or some quiery? Where is the combobox?
I have the combobox in seven form names right now. When I open Facility1
Form, it automatically places Facilitiy1 name there. The problem is I can
flip through the entered informaiton from other facilities and see the
information.

 

[Lynn Trapp]

Brad,
The way you describe it, you probably don't want to use a combobox at all.
You should only have ONE form and the recordsource behind that form should
be a query that limits what is seen to the specific facility a user is at.
You will need to create a type of log on form that has the user identify
initially which facility he or she is at -- you might even add password
protection to it, giving each facilty it's own unique password.

 

[Brad_A]

Lynn,

Is it possible to get detail no how to do all of this? At least how do I
code and password protect the queries? What query type should I use and what
is the code for it? I apologize for not understanding, but this is my first
time, and I haven't been through the system and database design classes yet.
However, I need to set up this database, and learn/understand it for future
use as well.

Trying to make sure I have all of the right parts:
1. One password form that will signal the facility that is logged on - I
have no idea on how to do this
2. One entry form (would already have facility name filled in for quicker
and easier entry)
3. One main database that holds all of the information
4. Queries - how many, what type, etc. do I need?

Looks like 1 and 4 are the areas in which I need detailed help.

Regards,
Brad

 

[Lynn Trapp]

Is it possible to get detail no how to do all of this? At least how do I

code and password protect the queries? What query type should I use and what
is the code for it? I apologize for not understanding, but this is my first
time, and I haven't been through the system and database design classes yet.
However, I need to set up this database, and learn/understand it for future
use as well.

Brad,
I actually think that I will do better to "teach you how to fish" than to go
fishing for you. It looks to me like you need to get to that database design
class or, in lieu of that, get a copy of Database Design for Mere Mortals by
Michael Hernandez. You can also search the Microsoft website, or do a google
search, for "database design".

1. One password form that will signal the facility that is logged on - I
have no idea on how to do this

The simplest way to do this would be to have a simple form with 2 textboxes
and 2 command buttons (OK and Cancel). You would put code similar to the
following in the Click event of the OK button.

Select Case Me.txtFacilityName
Case "Facility1"
If Me.txtFacilityPassword = "SomePassword" Then
DoCmd.OpenForm "YourFormName"
Else
MsgBox "You do not have permission to log on"
End If
Case "Facility2"
If Me.txtFacilityPassword = "SomeOtherPassword" Then
DoCmd.OpenForm "YourFormName"
Else
MsgBox "You do not have permission to log on"
End If
........Add Case statements for all the facilities and then this
Case Else
MsgBox "You Must enter a valid facility Name"
End Select

2. One entry form (would already have facility name filled in for quicker
and easier entry)

Just base this form on a query where the criteria for the facility field is
the value in the Log on screen in txtFacility

3. One main database that holds all of the information

To be accurate, the "database" is the entire file that you have open, while
a "table" is what you are referring to.

4. Queries - how many, what type, etc. do I need?

That depends on what you need to do.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

 

[Brad_A]

What is the Me.txt?

Do you mean txtFacility.Text on that Select Case? Shouldn't
txtFacility.Text be assigned to a variable.

I am a person that learns by doing it. I am learning as I am adding. It
just seems hard because some of your instructions just say "do this" without
providing the instruction to do it.

I created the password form. Just need to tweak the coding based on the
questions above.

Regards,
Brad

 

[Lynn Trapp]

What is the Me.txt?
By default, Access gives a textbox control a name such as Text1 or the name
of a field it is bound to. To make it more readable I use the convention of
adding "txt" to the front of the name of any textboxes. Therefore, my
recommendation was that you would rename the textbox (on the Other tab of
the property pallette) to txtFacilityName. "Me" is a shortcut way of
referring to the Form object.

Do you mean txtFacility.Text on that Select Case? Shouldn't
txtFacility.Text be assigned to a variable.

The .Text property is the default when referring to a textbox and, thus, you
don't need to add that. You can simply reference it by typing
Me.txtFacilityName. In other words, Me.txtFacilityName is exactly the same
as Me.txtFacilityName.Text.