Digital Workshop

Stephen,

I hazard a guess that you are inserting a record somewhere between blanking the variables and entering new values. For example, if you where to blank the variables on the goto page two button and then have an insert record on the page two show, this would add the blank record.

Post the ILM and I'll check if you don't find it.

Mack

Mack

A couple of things prevent insertion of a blank record: only starting from the Review&Update page, using the blank button to navigate to the New Records page, and not using the add button at all.

So, this may not be a problem, since runtime navigation, unlike preview, can go through a setting of the variables to "" and I suspect the add button on the New Records page can be eliminated, sine the insert and return button works fine.

Let me know, since, especially, the add button may have a needed place in a pub?

On to updating/changing a record, next?

Kind Regards,

Hi Stephen,

Am in Vista at the moment, so will check the file in a while. On to Access 2K7. Yes it ribbon time

It really is rather different. I forgotten that I had used it before, but had block the memory out!!!

Here's a brief how to:

1. Load Access
2. Select the New Blank Database
3. Enter Name and location
(A blank table called Table 1 should fill the main pane)
The ID column is automatically created for you. If you click on this, and the DATASHEET ribbion is selected - then the 3rd block should display AutoNumber as the Datatype and the Unique checkbox is ticked. I don't think that this value is vital for Opus, it just very good DB design. If you are going to use a Primary Key to link your tables, this could be replaced with another unique number such as the Social Security or use both)

4.Click on the Add New Field column and and in the 2nd Ribbon box click the rename icon - call this 'Forename' (You can't use NAME as its a reserved word).
5. In the 3rd Ribbon box, click on datatype and set to TEXT if not already done. If your fields are vital, click the IS REQUIRED check box - BUT you will need to program OPUS to ensure that this is completed before inserting / updating the record or it will cause and error.
6. Repeat this for the AGE and JOB fields.
7. In the 1st cell under FORENAME enter some data - and fill the remain cells.

8. Close the table, it should ask you to rename it. If not, right click on the table in the left pane and select rename.

Mackavi

Stephen,

Sorry, thought there was a file to check. Not quite sure what you're getting at with regards to making the insert work. My understanding of the basic DB pub is this:

1. the next and previous button simply update the DB by overwriting the current query record (displayed on screen before any changes) with what ever is now displayed on screen.

2. The blank / goto P2 button, firstly empties the variables and then goes to P2 ready for data entry. In theory, and probably more practical, you could use another set of variables for the new record page.

3. the Add another record button is MEANT if you want to insert the record on screen and should only be clicked once data has been entered into the displayed fields. At which point it will insert this data as a new record and blank the variables ready for more data. IF clicked before the fields have been field then you will insert a blank record (a simple script would stop this in the final pub).

4. The return to P1 button, will also insert what ever is displayed on screen. Therefore, if this is clicked without fill the fields a blank record will also be inserted. (again a script could prevent this.)

Mackavi

Thanks, Mack

I have a better understanding of how to navigate in Access. Created an accdb DB with 4 names, ages, jobs. Can hold it for now, since adding it to the .imp will require establishing a new connection, changing "Name" to "Forename" etc.

OK. So, how do I set up the .imp to allow the end-user the ability to change an already entered data item? Let's say they filled out a parent's age as 65 and subsequently found out it is 59. How can they re-enter this field (search?), re-enter the corrected age and have it save (over-write) into the correct field for their parent in the DB? Or, after completing the age, they realize it was erronious and they actually don't know the age, and want to delete the wrong age, and for now, leave it blank again.

This is an important feature of the data collection form I am trying to create. The form would be completed by the user for each of 30 + family members, entering a number of fields (name, relationship to index person completing form, age, year of birth, death, if applicable, health conditions, their onset age-there are now 43 fields, more to be added). Some require their research and they will likely need to retrun over and over to fill in blanks or make changes to data. So, the flexibility to both add new records, make changes, possibly delete data in a field (fro example, to re-establish it as blank, if filled in incorrectly, and nothing was actually known).

If you would like to see a rough draft of an Acrobat .pdf version of the data fields, please provide an e-mail address, and I'll sent it.

I am hoping that these features are possible and, with some further help, can be added.

Again, thanks.

Kind Regards,

Slightly confused,

The Publication you are already using would allow you to update a record. For example, the Parent is John Smith. In JS's record, edit the age field and click next or previous.

As for leaving it, this should work the same way, except that Opus treats number variables as 0 by default. It maybe possible to assign a null that work solve this problem. I'll have a play later and see what happens.

Yes, by all means, send the PDF. I've updated my profile to some my email. If you want, also include the ACCDB file and that way we are both working from the same source.

Hi Mack

email on its way with zip folder: .imp, DB, .pdf.

Hope it arrives.

Stephen

Found the problem, an error in my demonstration. When you first create the database query for the ON SHOW, I'd unselected the ID field but these has to be included.

Basically, your SQL should read Select * From Employees. This should allow you to update the fields when you click on the Next / Previous button.

The blank value for the Number fields is a real pain and won't save for any blank datatype ('' or null or undefined). I'd guess that because Opus treats it as a number that it has be a 'Number'.

My solution would be that when a blank is entered into the AGE field that this is converted to a zero before updating / saving and if a zero is read from the database, that this is converted to a blank before displaying. Rather smoke and mirrors I'm afraid. I'll keep looking but this is a problem that has bugged me before.

From the PDF, a couple of Q's;

1. Who is the publication aimed at and what is the max # of records that they are likely to enter?

2. What do you intend on doing with the data that is emailed?

3. There is no unique identifier on the form. Is it going to be a problem if you have 20 John Smith's born 1/1/1950?

As for your BLANK records, the script / insert actions are the wrong way around on the add button. Basically, you've blanked the fields and then inserted them

Mackavi

Hi Mack

Thanks for your reply. I made the changes, and the basic design is now doing what needs to be done: saving, updating.

I can work further with it to adapt it more to what is needed: creating tools to help people collect family health history to share with their physicians. The .pdf was only a draft attempt to organize the info needed to accomplish this. It would be far better to collect the data within an Opus pub and further organize it: for example lists of family conditions, a diagram, etc.

Usually this info is collected for first and second degree blood relatives of the person doing the collecting: parents, children, siblings (including half-siblings), grandparents, uncles, aunts. So, given the varying size of families, this can be 30 or more people. Just creating an adaptable, one-size fits all, form is a challenge. And, right you are about some way to uniquely identify each family member if there is a John Sr. and John Jr. where the Sr. and Jr. are inadvertently left off.

The e-mailing of data is only in the thinking stage as a means to facilitate completing/organizing the info. Security of info would be critical. For example, a family member researching their family health history may want to collaborate with other family members, ask them to fill in some of the "blanks." Encrypted data with family members at each end to open and un-encrypt it into their copy of the program could work to aid this process.

My background is in social work and wellness promotion, so I am quite committed to supporting use of family health history to improve medical care. Presence of health conditions can be a major risk factor for family members and very useful data for physicians in developing monitoring, prevention and treatment.

Further thoughts/solutions about "blank" info fields would be welcome. This type of info is built by a person slowly via research. Guesses can later be confirmed or discarded, so changes/deletions also may be frequent.

Again, thanks for your help.

Kind Regards,

Hi Stephen,

My background is education, programming and most recently databases. From that point, what you want seems pretty straight forward.

My first thought on reading your post would be that you need two databases, one that resides with the collector and one that resides with the completer. In practice this would mean that you may have your 30 completer DBs.

The two reasons for this would be confidentiality - each person or family unit would have their own DB and you simply wouldn't be passing around a 'growing' DB of family tree medical history.

And two, if one big database is passed around there is more chance of erroneous data.

My second thought is good databases have less free text fields. Allowing people to enter what they want tends to lead to pointless data and is much harder to use productively. Give them some free text but use check boxes and drop down lists. This also cuts down on spelling errors.

This might seem daft, but if you want to find the percentage of family members that have diabetes and there are five different spellings of the word, then your search is pointless.

Last bit of advice. It's easier to concatenate data that split data. If you want to known the specific type of diabetes, store this in a separate field to the value of diabetes. This goes for even basic information, such as using different fields for forename, surname, middle name.


Enjoy,

Mackavi

Thanks Mack.

I appreciate the suggestions. I haven't yet gotten my brain around the final design to collect the data. Hearing good ideas as well as cautions are very helpful as I brainstorm how best to create this.

You and Sandy have aided me in quickly getting a working knowledge of databases.

One question: during this learning period, while setting up DSN, I have managed to accumulate quite a number of ODBC connections in the database folder under ProgramFiles>CommonFiles in the C drive. Excepting the one or two that I will continue to need, is it safe to delete the rest?

Kind Regards,

Stephen,

I recall some years back applying for health coverage... lots of questions. If part of your project is facing 'taxomony' of these data nuggets you may get some sense from the existing beauracracies.

Along those lines, have you thought about using radio buttons and check boxes as a way of simplifying user's effort to input, and also keeping the data more uniform? As done in application forms, a 'yes' or other certain answer can open the input to more details.

Best wishes on your project.

Stephen,

The only DSN included with my system are:

dBASE Files
Excel Files
MS Access Database

These don't appear to be connected to any data sources on my machine, but neither are they causing a problem, so I leave them allow. If your are using a work machine with data application then caveats on deleting unknown DNS as this make stop the application functioning.

However, if they are ones you created then you can delete them. Check the configure button to see what the data source is. It might help identify the program using the DSN.


Databases where one of those pieces of software (along with music notation) that I never really got a handle on until ask to design one with a live dataset. I can't really think of any descent sources on database design, but as Opus allows you to execute an SQL query I would recommend this book

http://www.amazon.co.uk/Visual-QuickSta ... 663&sr=8-4

Although it is specifically for the SQL language, I think it gives a great understanding of how a database works even if you are using buttons not scripts to perform the actions.

Mackavi

Thanks Mack

I believe that every one of these was created over the past few days as I experimented setting up DB connections (the file property tabs indicate this as well). Have enough excess overhead on my computer, so will probably delete all but a couple.

Again, thanks.

Kind Regards,