Designing a db

Hi everyone,

Having learnt a little about the basics whilst creating my first db I
am now about to start creating a good one!

I am about to sit down with a large piece of paper and plan it all
properly but I have some questions about logistics I wondered if anyone
could help with.

1. Can I put command buttons on a table or query?
For example I am going to have a table/query with a list of customers
(over 1000). I would like the user to be able to select the customer
and then click a command button to do an action relating to the
selected record.

Further to this - if buttons are possible is it possible to lock them
to the top of the page so when the user scrolls they are always
visible? (I had thought about custom buttons on the toolbar but copies
of this db are going to be distributed to many users and I have a
feeling these buttons will not be visible if the db is installed & used
on another system).

2. Is it best to never double up data in tables - I believe I should
never need to store data twice in two seperate tables if I can link
them - is this the most efficient way to design?

3. I would like to flag to the user when a customer becomes due a
visit. Is there a way to do this in a similar way to Outlooks
reminders? I can't see that using reminders in outlook would be
efficient as there could be 100 due customers per month.

Many thanks for your help in advance, I am so determined to create this
correctly that I really want this clear before I even touch access!

re: Designing a db

Keri,

Congratulations on putting your first DB to bed. I had just started on
Google Groups when you started on that DB and I believe I helped you
once or twice.
keri wrote:NO! You want to have nothing to do with mucking about in a table or a
query. All interaction from the user should be done using Forms. If I
understand your situation correctly then you could use the Continuous
Forms view on your form. This will list all the records one after the
other. Then in the form header you could put your buttons.
Close your eyes and imagine that you were entering data into the
database that you are designing. If you see yourself typing in the
same set of things, like, first name, last name, address over and over
again then you should split that information off into another table.

I suggest reading this:
http://www.prestwood.com/aspsuite/kb...asp?qid=100040
If each customer is related to an employee and that employee's email
address is sotred within the database then it is possible to get Access
to send emails to the employees responsible, but the how of this thing
is off in the future, for now, know that each Customer must be related
to an employee with an email address to be able to use this feature.
I applaud you on making this statement, and I hope that you will adhere
to that because there's nothing worse than getting a decent distance
into development and then finding that your model is not sustainable.

Cheers,
Jason Lepack

re: Designing a db

keri wrote:
No. Command buttons can be placed on a form.
You could use a form or subform to present the data as a datasheet or
continuous form. You could then set the Dbl-Click event to do whatever
you desire. You could put a button on continuous form in the rows but
that might look sloppy.
It is best to keep all similar data in 1 table if you can. You can
merge two tables together with similar fields with a union query. The
problem with a union query is that the data is not updateable. A union
query would be good to present data from 1 or more tables but open
another form to edit the record. You could have a table that contains
current data and another that contains archived data. The table
structures are the same. So you might want to pull data that meets a
criteria from both tables and present it as a recordset.
You might have a field called DueDate. You can then filter the tables
based on the duedate.

re: Designing a db

You already have some great answers..so, I just add a bit:
As others said, you NEVER want to allow users direct access to a table. It
makes things very difficult for the user.

On the other hand, you can most certainly have buttons in a table like
format if you use what is called a continues form.

Take look at the following screen shots, and you can see several have a
"button" in the grid display that when clicked on
will launch the form.

http://www.members.shaw.ca/AlbertKal...icles/Grid.htm

Sure, once again..take a look at some of the forms. Really you have a form,
buttons on the form, and then also place "grids" of data into those forms.
So, once again, you REALLY want to get away from thinking in tables...and
think in forms.

Yes, you custom buttons and CUSTOM menus do travel with your apcpation.
Since every other application you purchased for the last 15 years had menu
bars..why not yours? No need to try and re-write 15 years of computer
history, and throw out a menu bar. Every single application you likely used
has a menu bar...and all of a sudden you plan to not use this MOST familiar
feature. Menu bars take extra work, but the results are MORE WINDOWS LIKE
application, and a MORE USER friendly apcpation. I have a bunch of menu bar
screen shots, and EXPLAIN the use of how a "windows" like interface makes
your apcpation far easier to use and learn....

http://www.members.shaw.ca/AlbertKal...erFriendly.htm

Note later on the above article how menu bars were used in such a way that
users could likely "guess" were the feature is..and likely 9 out of 10 times
with ZERO training will find the correct option...

You as a general rule are only limited by your imagination. For a source of
inspiring just look at any application running on your pc to get ideas.

You can see some more screen shots of an access appcation here:

http://www.kallal.ca/ridestutorialp/index.html

The above is all ms-access, and no special 3rd party tools were used....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

re: Designing a db

Thanks everybody for your answers.
I should have learnt that by now if I explain what I want my database
to achieve you will all help with the best way of doing this. I am
still struggling with part of my design and have narrowed it down to 2
ways of doing this so.....

My user needs to be able to add an appointment or record a call or
perform another action they have made with customers.
I have a customers table which holds over 1200 customers. I want a
simple way for my users to perform an action on the customer.

Choice 1.
The user opens a form (to record an appointment or a call or another
action) on which there is a button which opens the customers table.
They can then search this table via any field and select the customer
they want. This would take them back to the form with the customer
fields populated from the selection on the customer table.

Choice 2. The user opens the customer table and selects the customer
they want to record an appointment or call with. They can then click a
button on the toolbar that will open the form (eg appointments) and
populate the customer fields with the customer details from the
selection in the accounts table.

I can't decide which option is best to use or even which is possible!

Thanks everyone.

re: Designing a db

keri wrote:I might consider creating a form/subform to find a customer. 1200
customer records is getting a bit large for a combo dropdown and most
likely your customer set is going to do nothing but grow.

In search form header provide a method to search for the customer. Is
it necessary to search on every field? Most likely not. You would most
like search on name, address, phone number, customerid, acct number, or
ssn or something that makes your search result unique. You could
provide a listbox, option group, or dropdown to select the search field.
In a text box, allow the op to enter the item to search for. Some
records may be difficult to find. You may have partial information to
search for a customer and require a more "manual" find. So you may want
to provide a filter. You might want to filter on state or city or area
code to limit the list. So you might select to filter on city and enter
"Dallas" and only customers in Dallas are presented.

The subform would contain, either as a datasheet or continuous form, the
fields you would want to display when finding the customer. You could
provide a Dbl-Click event that selects the customer.

In the form footer you could have a couple of command buttons; Select
and Exit. If select, that current customer record is selected (just
like the Dbl-Click. If exit, you abandoned the search.

What happens if the customer does not exist in this table? You can also
have a button in the footer to call a form to add a customer.

When the user presses the find button, the search form could be opened
up as a dialog/modal form. When you select the record, you could stuff
the customer id in the calling form
Forms!CallingForm!CustomerID = Me.CustomerID
and then close the search form. Another method could be if you cancel
the search, you close the search form. If you select a customer record,
you make the form hidden.
Me.Visible = False
and this will return control back to the calling form. This would
continue execturing the code following the code line that opened the
form. You could check to see if the search form is still loaded. If it
is, get the customer id and close the search form.

I would put the find button on your appointments form.

re: Designing a db

Hi and thanks,

I have no experience of subforms so have been reading avidly. My
problem is going to be the search fields - I will need at least 5 - and
how to filter the sub form based on what is entered in these fields. I
do not know how to join two or more filter codes together (sorry for
the terrible language use there but i can't hink of a better way to say
it!).

Further to this (when that part is working) once I have selected the
correct record I presume I will need to refer to this as an active
record or something to use thins information in fields in my main form.

My apologies for seeming a real beginner here but I thought I had
cracked the basics then along comes the use for a subform!

salad wrote:

re: Designing a db

There is a contacts database that lets you record things like calls etc
done....

Take a look at the sample databases here:

http://office.microsoft.com/en-us/te...426031033.aspx

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

re: Designing a db

Is this a suggestion instead of building my own - or is this because
the db contains searchable sub forms?

re: Designing a db

keri wrote:Form/Subform help.

First, create the subform. Use the Form Wizard to build the form. As
you step thru the wizard you'll be asked if you want it to be Columnar,
Tabular, or Datasheet. Select datasheet.

Now select Forms/New/Design. Here you have 3 sections; header, detail,
and footer. Put the search stuff in the header and Select buttons in
the footer. From the toolbar (toolbox icon) select the Subform option.
Drop it into the detail section. There is nothing to link the main
form to the subform so if prompted select None.

Explanation: Let's say you had an order form with order items. The
order form would be the main form. The order items would be the
subform. The link between each form is the OrderID.

How to sort a subform. In your query you can sort the records the way
you want. You can also change the sort via code.
Me.OrderBy = "LastName" 'this sets the field to sort on
Me.OrderByOn = True 'this "turns on" the sort

How to filter a subform. You can filter records out via the
recordsource. You might have a query like
Select * From Orders Where Customer = ""Smith""
You could also have the recoursource be
Select * From Orders
and filter manually via code. Ex:
Me.Filter = "Customer = ""Smith"""
Me.FilterOn = True

To remove a filter
Me.FilterOn = False
To remove a sort
Me.OrderBy = False

The sort/filter methods I did above were for the form you are in and
referencing. If you are setting the filter or sort from the main form
and you want to filter data in the subform you need to reference the
subform. If the main form is called MainForm and the subform is called
SubForm then
Forms!MainForm!Subform.Form.OrderBy = "Customer"
Forms!MainForm!Subform.Form.OrderByOn = True

Albert provide you a link to some Access templates at MS. The reason he
provided the templates link is
1) the work has been done.
2) you can import the form templates into your own database

This does not mean the templates are complete. They are simply a
starting point and you can modify them at will.

BTW, examples of multiple sort fields, multiple filters
Me.OrderBy = "LastName, OrderDate DESC"
Me.Filter = "LastName = 'Smith' And OrderDate #1/1/2007#"

In the sort above, I have 2 sort fields. The OrderDate fields is
ordering in descending order. The filter example is basically the Where
clause of in a SQL statement but it excludes the word Where.
In my first post, I was thinking you'd want to find a customer record in
the appointment/call form. So you'd have a button to find the customer
and return the value of the customerid to the calling form.

Here's a tip. When you create a table, create an autonumber field and
make it the primary key. This key may or may not be sequential so don't
use it for a sequential numbering scheme. As you develop in Access,
you'll be glad you created the table key as an autonumber.

I think that you are still a bit new to Access and need some further
education. I would highly recommend you go to a bookstore and see if
they have any Visual type books. You can go thru them quickly and get
an idea of the environment you are working in. I would also get an MS
Step-By-Step book on Access...if you DO the exercises you'll feel
comfortable with coding events.

If at all possible, learn how to code. Don't bother with macros if you
want to be serious. Study the messages here and at some point all this
stuff will become clear. Also, goto http://www.mvps.org/access.
There's lot of solutions to common problems there. Study them and
you'll get up to speed quicker.
Everybody here was a beginner at one time or another.

You will appreciate subforms, the simplicity of creating and ease of
implementation, if you ever have a problem where you need to present
something like an order and all the associated order items. You simply
need some practice.

re: Designing a db

Thanks for a clear explanation. I am going to try this step by step and
I will come back in ten mins to try and find out what I am still doing
wrong!

re: Designing a db

Ok, dealing with it bit by bit.

Forms created and this code on the after update of my textbox;

Private Sub textname_AfterUpdate()
Me("frm allaccountswithpetals").Form.Filter = "[textname] = [name]"
Me("frm allaccountswithpetals").Form.FilterOn = True
End Sub

filters my form to matching records in the Name field of my subform
from my textname text box.

However;

1. It only works if I type the whole customer name exactly as it
appears, and I cannot use wildcards. Also the case must match.

2. If I leave textname blank it returns no records in my subform - how
can I change this?

3. How do I now add in a second textbox (which may or may not be blank)
to match another field in my subform?

re: Designing a db

keri wrote:Remember...the filter is exactly like a SQL Where clause...without the
word "Where"

Thus, you can use the word "Like" in the filter. I think there's a
difference in Like between A97 and newer versions. In A97, you could
use *, now it might be %. Anyway
Me.Filter = "FormFieldName Like 'Smith*'"

It's beyond me why case should matter. If you open the code behind the
form is the first 2 lines of the code module the following?
Option Compare Database
Option Explicit
You might be comparing binary or text. In most cases you'll want to
compare with the database option.

'use your text field name, not Me.TextFieldToSearch
If Not IsNull(Me.TextFieldToSearch) Then
'filter statements here
endif
or
If Not IsNull(Me.TextFieldToSearch) Then
'filter statements here
Else
'reset filter if you'd like
Me.Filter = ""
Me.FilterOn = False
endif
How many fields do you need to filter by? OK. Here's what I do...I
might have 2 or 3 fields I'm going to filter/sort by. Usually I have
some combos, checkboxes, etc for filtering. In this example, I will
have Text1...Text2 that I'm filtering on.
Private Sub SetFilter
Dim strFilter As String
If Not IsNull(Me.Text1) Then
strFilter = "FieldName1 = '" & _
Me.Text1 & "' And "
Endif
If Not IsNull(Me.Text2) Then
strFilter = strFilter & _
"FieldName2 = '" & Me.Text2 & "' And "
Endif
If strFilter "" Then
'remove the trailing "And"
strFilter = Left(strFilter,Len(strFilter)-5)
Endif
Me.Filter = strFilter
Me.FilterOn = True
End Sub

Now, in each "search" text box's AfterUpdate event I enter
SetFilter

re: Designing a db

This is my code and it doesn't do anything!GRRRRRR. I can't tell where
I am going wrong but I know my computer may soon be in little bits as I
take out my frustration! I have been trying to do this for over 2 days
and still nothing.

Private Sub TEXTNAME_AfterUpdate()
Call SetFilter
End Sub

Private Sub TEXTOUTER_AfterUpdate()
Call SetFilter
End Sub

Sub SetFilter()
Dim strFilter As String
If Not IsNull(Me.textname) Then
strFilter = "name = '" & _
Me.textname & "' And "
End If
If Not IsNull(Me.textOUTER) Then
strFilter = strFilter & _
"OUTER = '" & Me.textOUTER & "' And "
End If
If strFilter "" Then
'remove the trailing "And"
strFilter = Left(strFilter, Len(strFilter) - 5)
End If
Me.Filter = strFilter
Me.FilterOn = True
End Sub

re: Designing a db

"keri" <keri.dowson@diageo.comwrote in news:1169371817.989551.319940
@q2g2000cwa.googlegroups.com:

One of many possible problems is that you are setting the value of
TEXTNAME and of TEXTOUTER in code. In that case, the After_Update Event
does not occur.

re: Designing a db

Sorry Lyle, I do not understand where in this code I am seeting the
value.

re: Designing a db

"keri" <keri.dowson@diageo.comwrote in news:1169375687.562669.166080
@a75g2000cwd.googlegroups.com:
In this code you are not. I am speculating. How is the value of TEXTNAME
set? Do you click on the control and then type, "Knudsen", or do you, for
example click on "Knudsen" in a Combobox or Listbox? If the first (typing
in the value) is not the way the value of TEXTNAME (and TEXTOUTER as well)
is set, then the AfterUpdate event will not occur.

If this is not the problem, then after you have done whatever you need to
do to set the filter, then perhaps you should check:
Was the filter set but does not work?
or
Was the filter not set?

You could do this with a line of code
MsgBox Me.Filter
after turning the Filter on
or
you examine the form in Design view afterwards. In the Data Tab the Filter
should be shown.

re: Designing a db

I am typing values into the textboxes textname and textouter.

The msg box returns;

NAME ='SMITH' And Outer = 'NR01' And

If I only type one search field I seem to have a trailing and eg.

NAME = 'SMITH' And

Could this be the problem?

re: Designing a db

I have removed the trailing And (this my was fault for commenting
code!)

I think it may be that the filter is not applied to the subform - Do i
need to alter the code so that the filter refers to this rather than
the main form? I am struggling with referring to the subform.

re: Designing a db

"keri" <keri.dowson@diageo.comwrote in news:1169377539.792024.228950
@q2g2000cwa.googlegroups.com:
Open the SubForm in design mode. Set the its HasModule Property to True. If
using Access 97 or earlier it will not have a HasModule Property. Put some
do nothing code in its module if it doesn't have any code already, eg:

Private Sub DoNothing()
End Sub

Then change:

Me.Filter = strFilter
Me.FilterOn = True

to

With Form_NameofYourSubForm
.Filter=strFilter
.FilterOn = True
End With

I am not suggesting that this is an optimal solution. I am just hoping it
will work with what you have.

re: Designing a db

I am so sorry for being hopeless. After doing as you said Inow have
this code;

Sub SetFilter()
Dim strFilter As String
If Not IsNull(Me.textname) Then
strFilter = "name = '" & _
Me.textname & "' And "
End If
If Not IsNull(Me.textOUTER) Then
strFilter = strFilter & _
"OUTER = '" & Me.textOUTER & "' And "
End If
If strFilter "" Then
'remove the trailing "And"
strFilter = Left(strFilter, Len(strFilter) - 5)
End If
With Form_ALLACCOUNTS
.Filter = strFilter
.FilterOn = True
MsgBox strFilter
End With

And my msgbox returns the correct filter terms - it is just not doing
anything with my subform! I can't understand this!

re: Designing a db

"keri" <keri.dowson@diageo.comwrote in news:1169379966.681866.185970@
38g2000cwa.googlegroups.com:
Me neither, but then I haven't worked with subforms for years. Are we
safe in thinking the Subform's recordsource includes fields called Name
and Outer... Argggggggghhhhhh. Form's have a Property named "Name". And
th form's properties are higher in the chain than its filter properties.
So, I would wonder if the SubForm is confused by Name = 'Whatever' as it
knows that its name is 'ALLACCOUNTS'.

It may be kinda hard and time-consuming to change the name of Name to
fldName or something like that in the table but maybe you could modify
the recordsource of the subform to be something like
SELECT Name AS NottoBeConfused, Outer FROM Accounts
and use NotToBeConfused

I think I'm guessing here and I hope someone who deals regularly with
SubForms will jump in soon.

re: Designing a db

I have changed name to accountname to no avail!
Sorry for being a pain!

re: Designing a db

Ha Ha somebody told me I would love working with subforms as they are
quick and easy ........where are they?!!!

re: Designing a db

Thanks to everyone who has helped - I have now managed to cobble this
together so it is working (mostly).

My apologies if you had all presumed I had already done the things I
have now done;

Changed my sub form to continuous and put the search text boxes in the
header of the subform!

Simple as that when you know how. Now onwards to finding my next
problem!

re: Designing a db

keri wrote:

I never trusted Form_ to refer to the instance I am trying
to manipulate. I don't even know if a form being displayed
in a subform control can be the default instance. That
probably sounds like a pile of gobbledy gook, but try
replacing Lyle's With statement:

With Me.subformcontrol.Form

Replace "subformcontrol" with the name of the control
containing the form you are trying to filter.

This all assumes that the text box's and the set filter
function are in the main form. If that's not the case,
please explain in more detail.

Side note: if you try to use the filter property on the main
form or another subform, they will clash as only one of them
can have a filter at a time.

--
Marsh

re: Designing a db

I will try and explain.

I started out with a main form (frmappointments bound to
tblappointments) in which I added a subform in datasheet view
(allaccounts). The main form had textboxes
textname,textaddress,textouter etc which the user can enter in (minimum
of 1 search criteria, possibly up to 6) search criteria to search the
subform which has fields such as name, address, outer. On the click of
a command button this was supposed to filter the subform to only show
records that matched the search criteria in the main form textboxes.
(The user then wants to select a record in the subform to populate
fields in the main form such as customerID etc etc). Sounds easy but I
couldn't get this to work any which way!

I have not got this code;

Sub filtersubform()
Dim criteria As String


criteria = ""
If Me.Text18 <"" Then
criteria = "[accountname]= '" Like "*" &
[Forms]![frmappointments]![ALLACCOUNTS].Form!textname & "*" & "'"
Else
MsgBox ("no criteria")
Exit Sub
End If

If Me.Textouter <"" Then
If criteria <"" Then
criteria = criteria & " AND "
End If
criteria = criteria & "[outer]='" _
& [Forms]![frmappointments]![ALLACCOUNTS].Form!Textouter & "'"
End If

Me.Filter = criteria
Me.FilterOn = True

Me.Requery

End Sub

Which mainly works. I have changed the subform to continuous and added
the text boxes used as search criteria into the header of the subform.
So the search works, however I cannot use wildcards in any of my search
criteria.

I have tried (for 1.5 days!) to understand referring to a subform and
it is still confusing. (What is the subform CONTROL? I understand
referring to the subform but not the control part of it). I have also
tried to understand the use of ' and " in the criteria string but tha
is still foreign to me too! I hate having code I can't understand and I
hate it when something simple takes a whole weekend!

Ps - not sure what you mean here -

but tryThe name of the control containing the form I am trying to filter!!!???
Is this the main form?


Thanks.

re: Designing a db

Marshall Barton <marshbarton@wowway.comwrote in
news:ahs6r2ha6cbafm524t2f9kkg010jdqqucr@4ax.com:

It has worked for me only every time for about ten years.

re: Designing a db

Lyle Fairfield wrote:

That's good to know, Lyle, but I still have my doubts/biases
about some situations. I just don't see how the default
instance can be used reliably when there are multiple
instances of the form being displayed, either as members of
the Forms collection or as subforms. My attitude is to
address the specific instance so I don't have to worry about
it.

--
Marsh

re: Designing a db

Marshall Barton wrote:If you open a form both as a subform and as a form on its own then the
Form_FormName syntax appears to point to the one that was instantiated first.
If that instance is closed then the reference changes to the next one. That is
admittedly not a common form situation, but it is one where that syntax could
not be used reliably.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

re: Designing a db

keri wrote:

A form object can be displayed in another form only through
the use of a subform control. A subform control is just
another kind of control like a text box control, image
control, etc.

You can select a subform control on the main form by
clicking once in the subform and then view its properties.
The name of form object that will be displayed in the
subform control (when you open the main form) is specified
in the subform control's SourceObject property, which might
be different from the name of the subform control. I just
wanted to emphasize that you need to check that you are
using the subform control's Name property, not the
SourceObject property.

To reference the form object displayed in the subform
control from the main form, you need to use the subform
control's Form property as I said in the suggested With
statement.


Attempting to use all that to answer your question, No, you
are trying to filter the subform, not the main form.

Even though you have managed to get something to work, keep
all this stuff in mind. It will become clearer as you
become more familiar with these things.

--
Marsh

re: Designing a db

I certainly need to read more books before I can understand this!

So, now I have my form working (the continuous was) how can I select
sone of the filtered records to populate fields on my main form? I am
getting confused on this because the fields I need to populate on my
main form are bound to a table.

Think I maybe have just spent far too long on this computer to think
straight!

re: Designing a db

keri wrote:

It might be time to take a little break from this and give
your brain a chance to organize all this stuff.

As for your next issue. It is best if you confine a thread
to a single topic. This is because the people that respond
to one topic may not be the right people to answer a
question on another topic. OTOH, the people that might have
ideas about your second topic are unlikely to monitor a
thread on the first topic.

--
Marsh