collate historic data (archive)

[bobsyeruncle]

Hi all,

I have a form which contains name/address of businesses with a subform button for more information.

The subform contains date of last visit, a yes/no tick box and a short comments box.

What i need is to keep a record of all visits automatically after the subform is updated. The end result being that i can have a button for visit history that will show alll the previous visits that business has received along with the comments section.

I am a noob so any help would be great.
Many Thanks

 

[jzwp22]

If the subform is bound to a table, then when data is entered in the subform, it will be saved to the table. If you go to the table, do you see the data you entered?

You say that you are using a button to open the subform. In what mode are you opening the subform? Can you supply the code that runs behind the button?

 

[bobsyeruncle]

If the subform is bound to a table, then when data is entered in the subform, it will be saved to the table. If you go to the table, do you see the data you entered?

Yes

You say that you are using a button to open the subform. In what mode are you opening the subform? Can you supply the code that runs behind the button?

Can't answer that at the minute .... i'm sitting at home enjoying a steak pie and won't be back to work till mornin!

 

[vbaInet]

.... i'm sitting at home enjoying a steak pie and won't be back to work till mornin!

Mmmm lol lovely!!!

 

[jzwp22]

If you are not seeing previously entered records in the subform, then it tells me that you are probably opening the subform in data enty mode (data entry property set to yes). This opens the form ready for the entry of NEW records. To show previously entered records and still have the ability to add new records, just set the data entry property to no.

I am assuming that your table structure is correct and that the table that holds the visit information is properly joined to the table that holds the businesses. Something along these lines:

tblBusiness
-pkBusinessID primary key, autonumber
-txtBusinessName

tblBusinessVisits
-pkBusVisitsID primary key, autonumber
-fkBusinessID foreign key to tblBusiness
-VisitDate
-YourYesNoField
-YourCommentsField

 

[bobsyeruncle]

Here is the code behind the button as asked for

Private Sub cbuPROGRESS_Click()
On Error GoTo Err_cbuPROGRESS_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmPROGRESS"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cbuPROGRESS_Click:
Exit Sub
Err_cbuPROGRESS_Click:
MsgBox Err.Description
Resume Exit_cbuPROGRESS_Click

End Sub

You have lost me with the foreign key (as i said i am a noob) ... everything else is set up as you suggest. I have set up a relationship between the two tables.

 

[bobsyeruncle]

Here is a screenshot of the relationship with my two tables open and the main form in the background.

I have removed/hidden some data etc.

hope this helps

 

[jzwp22]

If you have joined the two tables you are joining via the primary key field of the main table with its corresponding field in the detail table. That corresponding field is called the foreign key. Sorry for not explaining the database jargon more fully.

Your code does not specify how to open the form, so I have to assume that the Data Entry property was set at the time the form was designed. When you open the form for a record where you have already entered detail records (the visits), is the subform always blank? If so, then the Data Entry property is set to yes. You can change it by opening the subform in design view and look under the data tab for the Data Entry property and change it to no. Close and save the subform. Open you main form and click on you subform button and you should see all the detail records related to the record shown in the main form.

 

[bobsyeruncle]

Data Entry is set to no. I have never overwritten the records before, so i tried that just now but when i open the subform all i get is the new record details and if i open the table itself there is only the new record.

This is a relatively new DB, to be honest i am a complete noob with access and have only managed to build this through this website and a lot of trial and error.

I think the subform is set up incorectly so i am going to zip the DB and email it home so i can play with it tonite ... the best way to learn is to get stuck in!!

Thanks for your help and don't be surprised if there are a few crys for help later!

 

[jzwp22]

Looking at the Word document you attached, you have some problems with your table structure that need to be corrected. Now, once you change your table structure, any forms, queries or reports you created before the change will become useless. So, I would recommend starting with a clean slate.

Just focusing on the tables you have in the relationship window, can you provide the list of all fields in the two tables: tblMain and tblProgress? Do you have any other tables that were not shown in the relationship window?

I will also need to understand the process you are trying to model so that we can create a proper table structure. Can you provide more detail on that?

In the mean time, you may want to look at this site to get a better understanding of database normalization which is critical to creating a successful relational database.

 

[bobsyeruncle]

Thanks for your help.

Here is another quick screenshot with all the detail you requested. I'll have a look at the link you provided.

 

[jzwp22]

In your tblMain you have last and next visit. If you want to track multiple visits for a school, you have a one(school)-to-many(visits) relationship which is handled with 2 tables as follows:

tblSchools
-pkSchoolID primary key field, autonumber
-SchoolName
-Street
-Address
-TownCity
-PostCode
-email

tblSchoolVisits
-pkSchoolVisitsID primary key, autonumber
-fkSchoolID foreign key to tblSchools (this forms the relationship to the appropriate school record, this field must be a long number datatype in order to link to the autonumber field of tblSchools)
-dteVisit (visit date)



Now if the frequency of a visit is fixed, let's say 1 year, you would put that in tblSchools, so now tblSchools looks like this:

tblSchools
-pkSchoolID primary key field, autonumber
-SchoolName
-Street
-Address
-TownCity
-PostCode
-email
-visitfrequency (a long integer value expressed as the number of days)


Now you can calculate when the next visit is due by taking the most recent visit date + visitfrequency I would use the dateadd() function that Access has to do the addition, more on that in a future discussion if needed.

What is rota?

What is the purpose of tblProgress? I see lesson1, lesson2--these are examples of a repeating group which violates normalization rules. If a school has many lessons, we have a one-to-many relationship once again. You'll have to provide more on the lessons. Is each school required to provide the same lessons? Are there more than 2?

A couple general database design items:
Special characters (#,%,&,/,?,\, etc) or spaces should not be used in table or field names.

All joins between tables should be done with numeric fields, text fields are less efficient.

 

[bobsyeruncle]

Rota is the visit frequency ... most schools recieve an annual visit however some are two yearly and some three yearly. So the rota form creates a report for all schools that receive an annual visit or whatever the user wants to query.

Last Visit and Next Visit due are annual representations i.e Last Visit was during 2009/2010 session next visit due during 2010/2011

Each school recieves 2 lessons and the lesson dates are recorded in lesson one lesson two fields.

although set up wrong ... everything worked fine until i realised we need to keep historic records ... then end result being that a user can check back over a number of years, all the data recorded in (Progress) for a particular school or produce a report that shows all visits etc.

Clear as mud?

 

[jzwp22]

Since rota is the visit frequency, you can use that in place of the frequency field I suggested.

With respect to the NextVisit field, you would never store that value in a table since it can be calculated whenever you need it. In general, calculated values are not stored; there are a few exceptions to this, but your case does not appear to qualify.

Since a school has more than 1 lesson, you need a table to hold the related lessons similar to the visits I showed previously.

tblSchoolLessons
-pkSchoolLessonID primary key, autonumber
-fkSchoolID foreign key to tblSchools
-LessonName
-dteLesson (date of lesson)
-NoOfPupils

With the structure I have proposed, the history will be preserved (each new visit in the visit table as well as each new lesson in the lesson table(above) will be recorded as a new record in their respective tables.

 

[bobsyeruncle]

Here is a copy of the original DB with all data removed for data protection.

this was also before i had created the extra table and form for recording progress.

Hope this helps

 

[jzwp22]

I've reworked the table structure based on my previous posts. The DB is attached.

 

[bobsyeruncle]

You are an absolute star!

Thank you so much

 

[jzwp22]

You're welcome.

When you have a one-to-many relationship the best way to represent that in a form is to use a form/subform setup with the main form based on the one side of the relationship which would be your tblSchools. The subform would then be based on the many side of the relationship (tblSchoolVisits).

Post back if you have additional questions.