Prev: Looking to automatically re-link tables in Access
Next: Another was to Autonumber?
From: bettybbm on 3 Mar 2010 09:43
I am working on a equipment database that I need to keep all the service
dates in the database. I have a column titled "Date of Last Service." My
problem is when I created the form and type in the date on the form, it
replaces the previous date. I am a fairly new user. Need suggestions on how
to keep all the dates in the database. Should I create a subform? - need
direction. Please help.
--
bbm
From: PieterLinden via AccessMonster.com on 3 Mar 2010 10:07
bettybbm wrote:
>I am working on a equipment database that I need to keep all the service
>dates in the database. I have a column titled "Date of Last Service." My
>problem is when I created the form and type in the date on the form, it
>replaces the previous date. I am a fairly new user. Need suggestions on how
>to keep all the dates in the database. Should I create a subform? - need
>direction. Please help.

Date of Last Service is a derived value... it's

SELECT ObjectID, MAX(ServiceDate) As LastServiceDate
FROM MyTable
ORDER BY ObjectID
GROUP BY ObjectID

So put your Service dates in a separate table... Something like....

ObjectID (FK)
ServiceID (PK)
ServiceDate
ServiceType
ServiceNotes
etc

then join the two tables on ObjectID...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

From: KenSheridan via AccessMonster.com on 3 Mar 2010 11:22
You'll need two related tables, e.g.

Equipment
….EquipmentID
….EquipmentDescription
<and so on>

Services
….ServiceID
….EquipmentID
….ServiceDate
….ServiceDescription
<and so on>

The Equipment table has one row per item of equipment and has EquipmentID is
its primary key, and can be an autonumber. The Services table has one row
per service and EquipmentID is a foreign key, so should be a straightforward
long integer number data type, not an autonumber.

Create a relationship between the two tables on EquipmentID and enforce
relational integrity.

For data input I'd recommend a form based on Equipment and, as you suggested,
within it a Services subform, probably in continuous forms view, linked to
the parent form on EquipmentID. You can if you wish base the subform on a
query which orders the rows by descending date order, i.e. latest first, so
the last service will be at the top of the list in the subform:

SELECT *
FROM Services
ORDER BY ServiceDate DESC;

If you do this, in the subform's AfterInsert event procedure requery it with:

Me.Requery

This will move a new record entered in the subform to the top of the list.

If you are unfamiliar with entering code in event procedures you do this by
opening the subform separately in design view, selecting the form object and
opening its properties sheet if its not already open. Then select the After
Insert event property in the properties sheet. Click on the 'build' button;
that's the one on the right with 3 dots. Select 'Code Builder' in the
dialogue, and click OK. The VBA window will open at the event procedure with
the first and last lines already in place. Enter the line of code between
these two existing lines.

The blank row for entering a new service will always be at the bottom of the
list, however. To save the user having to scroll down the subform to enter a
new service you could if you wish add a 'New Service' button to the subform's
header with the following code it its Click event procedure:

DoCmd.GoToRecord Record:=acNewRec

Ken Sheridan
Stafford, England

bettybbm wrote:
>I am working on a equipment database that I need to keep all the service
>dates in the database. I have a column titled "Date of Last Service." My
>problem is when I created the form and type in the date on the form, it
>replaces the previous date. I am a fairly new user. Need suggestions on how
>to keep all the dates in the database. Should I create a subform? - need
>direction. Please help.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

From: bettybbm on 3 Mar 2010 14:49
I have separated into two related tables. However, several problems. The
equipment ID has letters in it, so must be a text. Therefore, cannot be long
integer number. Next, I created the relationship and enforced relational
integrity, but in order to create the form and subform, need to have a
one-to-many relationship don't I - Only creates a form with all fields. I
like query idea in order to sort by decending. Since linking the tables, I
now see when I open the [+] in equipment, it shows at least two lines for
service dates - which is great! Now, to create the form to input - back to
the first square? Keep me going.
--
bbm


"KenSheridan via AccessMonster.com" wrote:

> You'll need two related tables, e.g.
>
> Equipment
> ….EquipmentID
> ….EquipmentDescription
> <and so on>
>
> Services
> ….ServiceID
> ….EquipmentID
> ….ServiceDate
> ….ServiceDescription
> <and so on>
>
> The Equipment table has one row per item of equipment and has EquipmentID is
> its primary key, and can be an autonumber. The Services table has one row
> per service and EquipmentID is a foreign key, so should be a straightforward
> long integer number data type, not an autonumber.
>
> Create a relationship between the two tables on EquipmentID and enforce
> relational integrity.
>
> For data input I'd recommend a form based on Equipment and, as you suggested,
> within it a Services subform, probably in continuous forms view, linked to
> the parent form on EquipmentID. You can if you wish base the subform on a
> query which orders the rows by descending date order, i.e. latest first, so
> the last service will be at the top of the list in the subform:
>
> SELECT *
> FROM Services
> ORDER BY ServiceDate DESC;
>
> If you do this, in the subform's AfterInsert event procedure requery it with:
>
> Me.Requery
>
> This will move a new record entered in the subform to the top of the list.
>
> If you are unfamiliar with entering code in event procedures you do this by
> opening the subform separately in design view, selecting the form object and
> opening its properties sheet if its not already open. Then select the After
> Insert event property in the properties sheet. Click on the 'build' button;
> that's the one on the right with 3 dots. Select 'Code Builder' in the
> dialogue, and click OK. The VBA window will open at the event procedure with
> the first and last lines already in place. Enter the line of code between
> these two existing lines.
>
> The blank row for entering a new service will always be at the bottom of the
> list, however. To save the user having to scroll down the subform to enter a
> new service you could if you wish add a 'New Service' button to the subform's
> header with the following code it its Click event procedure:
>
> DoCmd.GoToRecord Record:=acNewRec
>
> Ken Sheridan
> Stafford, England
>
> bettybbm wrote:
> >I am working on a equipment database that I need to keep all the service
> >dates in the database. I have a column titled "Date of Last Service." My
> >problem is when I created the form and type in the date on the form, it
> >replaces the previous date. I am a fairly new user. Need suggestions on how
> >to keep all the dates in the database. Should I create a subform? - need
> >direction. Please help.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
>
> .
>
From: KenSheridan via AccessMonster.com on 3 Mar 2010 18:15
The fact that EquipmentID is a text column doesn't make a lot of difference.
It can still be the primary key of the Equipment table. It just means it has
to be a text column in both tables. The only difference is that the values
for it will be entered manually into the Equipment table when a new equipment
record is entered rather than being automatically inserted as an autonumber.

It sounds as though you have the relationship set up correctly as a one-to-
many relationship from Equipment to Services, so you should have no
difficulty setting up the form and subform. Create the query for the subform
first; then create the subform as a continuous forms view form. Create an
Equipment form in single form view and then embed the services subform in it,
setting the LinkMasterFields and LinkChildFields properties to EquipmentID.

Ken Sheridan
Stafford, England

bettybbm wrote:
>I have separated into two related tables. However, several problems. The
>equipment ID has letters in it, so must be a text. Therefore, cannot be long
>integer number. Next, I created the relationship and enforced relational
>integrity, but in order to create the form and subform, need to have a
>one-to-many relationship don't I - Only creates a form with all fields. I
>like query idea in order to sort by decending. Since linking the tables, I
>now see when I open the [+] in equipment, it shows at least two lines for
>service dates - which is great! Now, to create the form to input - back to
>the first square? Keep me going.
>> You'll need two related tables, e.g.
>>
>[quoted text clipped - 59 lines]
>> >to keep all the dates in the database. Should I create a subform? - need
>> >direction. Please help.

--
Message posted via http://www.accessmonster.com

 |  Next  |  Last
Pages: 1 2
Prev: Looking to automatically re-link tables in Access
Next: Another was to Autonumber?