A = could we please receive assistance in having the Access 2003 form open with the Analyst_Name in Ascending order, as this sorting does not always work, when the form is opened.
B = When the Choose Sorting at the bottom of the form, this also does not always wok
C = Irregularly the data entered into the field automatically gets changed to “################”
D = When compiling this Access 2003 database we sometimes receive the message “Record Is Deleted” and we have to go back to the previous backup to continue.
Nicole - a couple of observations/questions:
1. If the sort order selected on the form on close is "Analyst Company" then on loading the form this selection is being used on opening the form - as reflected in the control setting it is the default - check the OrderBy property on the form.
2. The ordering function at the bottom of the form - seems to work as expected - are there any scenarios where it does not?
3. There is a query "Analyst_Updating_Q" referenced but not included in the db provided. I have not yet observed the change of Analyst Comments or Memo to "######..." Wondering if this query might have something to do with it.
4. The database design is a single table - it lacks consideration of normalisation. Is this because it comes from a spreadsheet view?
5. Record is Deleted - when compiling? Why are you compiling on a "production" database? You use the term "we" - are others using the application too? Was a record deleted? Do you want to allow any records to be deleted?
6. You have no checks on the quality of the data when a record is added or edited - use the beforeUpdate event on the form to write checks - such as duplicate analyst names.
7. Your PK on the table is the analyst name. yet you have PersID as autonumber - should this be the PK?
A = could we please receive assistance in having the Access 2003 form open with the Analyst_Name in Ascending order, as this sorting does not always work, when the form is opened.
B = When the Choose Sorting at the bottom of the form, this also does not always wok
C = Irregularly the data entered into the field automatically gets changed to “################”
D = When compiling this Access 2003 database we sometimes receive the message “Record Is Deleted” and we have to go back to the previous backup to continue.
In addition to GaP42's comments you are using Lookup fields in your table fields which is a complete NO NO.
Google the Evil's of Lookup Fields in Access Tables
You have an autonumber field set on PerID but you are not using it?
When a field changes to a sequence of #####...##### (however many there are), it suggests that you tried to display a number that needed more digits than were provided in a fixed-format field.
When a field changes to a sequence of #####...##### (however many there are), it suggests that you tried to display a number that needed more digits than were provided in a fixed-format field.
Although in the db provided they are found only in two fields - both of which are long text. May be picking it up from a number field - which may come from the update query that was not provided.
Nicole - a couple of observations/questions:
1. If the sort order selected on the form on close is "Analyst Company" then on loading the form this selection is being used on opening the form - as reflected in the control setting it is the default - check the OrderBy property on the form.
2. The ordering function at the bottom of the form - seems to work as expected - are there any scenarios where it does not?
3. There is a query "Analyst_Updating_Q" referenced but not included in the db provided. I have not yet observed the change of Analyst Comments or Memo to "######..." Wondering if this query might have something to do with it.
4. The database design is a single table - it lacks consideration of normalisation. Is this because it comes from a spreadsheet view?
5. Record is Deleted - when compiling? Why are you compiling on a "production" database? You use the term "we" - are others using the application too? Was a record deleted? Do you want to allow any records to be deleted?
6. You have no checks on the quality of the data when a record is added or edited - use the beforeUpdate event on the form to write checks - such as duplicate analyst names.
7. Your PK on the table is the analyst name. yet you have PersID as autonumber - should this be the PK?
Thank you, GaP42 for your suggestions, which I have tried to follow.
This form seems to be running now with no problems after running the Detect & Repair function,
Item 1. The sort order now is working with our live data.
Item 2. With our live data this function, previously was not working. Could the Detect & repair have fixed the problem?
Item 3. The missing Query is now attached.
Item 4. I tried using the Table Analyzer to split the table, but the program, said that this was not necessary.
Item 5. We have been compiling our databases ever Friday; do you suggest that we stop this function?
Item 6. I am not a programmer and tried to create a “BeforeUpdate” and had no success. Would you please advise me of what the code for this function should be?
Item 7. The primary key in the table has been the same for MANY years, do you suggest removing it and replacing the PK with the Analyst’s Name.
When a field changes to a sequence of #####...##### (however many there are), it suggests that you tried to display a number that needed more digits than were provided in a fixed-format field.
When you have a valid field that suddenly becomes invalid, i.e. when digits get replaced by a string of ###, one of several possibilities exist, but because of their characteristics, I think it is a fixed-space overflow. If you got "####name####" then it would be one of your variables is spelled wrong. If you got "####error####" then there is something wrong with your expression. When you get ONLY the string of #### then you have something that became too wide for the field OR for its format. According to GaP42, the field in question is a LONG TEXT. But in the place where it is displayed, does it have a fixed-length format property?
Good to hear you are making some advances ... stepping back a little:
1. Is 'Detect and Repair" meant to be the "Compact and Repair Database" function? Running the function may fix some issues, but are you keeping a copy of your database before running the function - just in case some corruption is introduced (not expected but ...)
2. You indicate you "Compile" every Friday - do you mean run Compact and Repair, or you open a VBA module and use Debug to compile the code? If the latter, then unless you have changed code during the week then there is no point.
3. Not sure of the inherent smarts of Table Analyser (which is to help with splitting/ normalisation data of structures), however your table is not properly normalised - the database design issues that arise relate to duplication of data and ongoing maintenance of the data. Changing the data stucture will involve substantial rework of your application. Any recommendations here depend upon the scope of your database. eg. You have a code for the field Analyst Company, used in multiple records (mutlple Analysts associated to one Analyst Company). Do you control this list? Do you have any interest in maintaining any other information about the Analyst Company? Is Sector an attribute of the Analyst Company or the Analyst? ...)
4. Is this database application shared (as a single file on a pc ..) or do users have separate copies on their pc? or are you the only user?
5. Re the Primary Key - You currently have Analyst Name as the primary key - it is a text field. Currently you can add names and you must make them unique. However will this always be the case? is it possible you will have two analysts with the same name? What happens if they move to a different company? Will you create a new record or just change the current one (losing info about when they were part of the previous company?) Usually a primary key is an identifier which is assigned by you which will not change as an identity for the record. You have a field PersID - what is it used for? Why do you allow duplicates in the table yet is an Autonumber field? If this were to be the primary key then duplicates are not OK. The Analyst name field could then allow duplicates - you could pick the correct analyst record on the basis of name and company and phone for eg.
6. As stated by mike60smart : Lookup fields in your table fields which is a complete NO NO. Google the Evil's of Lookup Fields in Access Tables
- to illustrate - Analyst_Pos is a case in point. It is defined, with a set of values in the design on the table Research_Analyst_F. if you need to add an new value you must alter the design of the table. if it were managed through a separate lookup table you could add and adjust these on the fly. (this is another illustration of lack of normalisation). See the link here: Evil of lookup tables? | Access World Forums (access-programmers.co.uk)
7. BeforeUpdate
Typical code you could try (repeat IF to End if for each mandatory item on the form) might be:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[Phone Num]) Or [Phone Num] = "" Then ' Comment: Me.[Phone Num] refers to the control on the form not PhoneNum field in the Qry - BTW
MsgBox "No Phone Number - a phone number must be entered", vbInformation, "No Phone Number provided!"
Cancel = True
Me.[Phone Num].SetFocus
GoTo exit_beforeUpdate
End If
exit_beforeUpdate:
Exit Sub
Note this does not check the length or the nature of the characters entered in the PhoneNum field - it is just checking there is something as a required field
Nicole - a couple of observations/questions:
1. If the sort order selected on the form on close is "Analyst Company" then on loading the form this selection is being used on opening the form - as reflected in the control setting it is the default - check the OrderBy property on the form.
2. The ordering function at the bottom of the form - seems to work as expected - are there any scenarios where it does not?
3. There is a query "Analyst_Updating_Q" referenced but not included in the db provided. I have not yet observed the change of Analyst Comments or Memo to "######..." Wondering if this query might have something to do with it.
4. The database design is a single table - it lacks consideration of normalisation. Is this because it comes from a spreadsheet view?
5. Record is Deleted - when compiling? Why are you compiling on a "production" database? You use the term "we" - are others using the application too? Was a record deleted? Do you want to allow any records to be deleted?
6. You have no checks on the quality of the data when a record is added or edited - use the beforeUpdate event on the form to write checks - such as duplicate analyst names.
7. Your PK on the table is the analyst name. yet you have PersID as autonumber - should this be the PK?
Could you please advise me if the controls on our form are wrong, as we have
Order by; Analyst_Company, Analyst_Name on our form?
The ordering function is fine.
The query Analyst_Updating_Q, just opens the field for review.
All data is typed directly into this database.
We, is used to note other volunteers using the same computer.
Yes, we would like to allow records to be deleted.
Could you please suggest the code to check for “duplicate analyst names” as I thought that Analyst_Name with the control “Yes (No Duplicates)” would be sufficient?
Good to hear you are making some advances ... stepping back a little:
1. Is 'Detect and Repair" meant to be the "Compact and Repair Database" function? Running the function may fix some issues, but are you keeping a copy of your database before running the function - just in case some corruption is introduced (not expected but ...)
2. You indicate you "Compile" every Friday - do you mean run Compact and Repair, or you open a VBA module and use Debug to compile the code? If the latter, then unless you have changed code during the week then there is no point.
3. Not sure of the inherent smarts of Table Analyser (which is to help with splitting/ normalisation data of structures), however your table is not properly normalised - the database design issues that arise relate to duplication of data and ongoing maintenance of the data. Changing the data stucture will involve substantial rework of your application. Any recommendations here depend upon the scope of your database. eg. You have a code for the field Analyst Company, used in multiple records (mutlple Analysts associated to one Analyst Company). Do you control this list? Do you have any interest in maintaining any other information about the Analyst Company? Is Sector an attribute of the Analyst Company or the Analyst? ...)
4. Is this database application shared (as a single file on a pc ..) or do users have separate copies on their pc? or are you the only user?
5. Re the Primary Key - You currently have Analyst Name as the primary key - it is a text field. Currently you can add names and you must make them unique. However will this always be the case? is it possible you will have two analysts with the same name? What happens if they move to a different company? Will you create a new record or just change the current one (losing info about when they were part of the previous company?) Usually a primary key is an identifier which is assigned by you which will not change as an identity for the record. You have a field PersID - what is it used for? Why do you allow duplicates in the table yet is an Autonumber field? If this were to be the primary key then duplicates are not OK. The Analyst name field could then allow duplicates - you could pick the correct analyst record on the basis of name and company and phone for eg.
6. As stated by mike60smart : Lookup fields in your table fields which is a complete NO NO. Google the Evil's of Lookup Fields in Access Tables
- to illustrate - Analyst_Pos is a case in point. It is defined, with a set of values in the design on the table Research_Analyst_F. if you need to add an new value you must alter the design of the table. if it were managed through a separate lookup table you could add and adjust these on the fly. (this is another illustration of lack of normalisation). See the link here: Evil of lookup tables? | Access World Forums (access-programmers.co.uk)
7. BeforeUpdate
Typical code you could try (repeat IF to End if for each mandatory item on the form) might be:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[Phone Num]) Or [Phone Num] = "" Then ' Comment: Me.[Phone Num] refers to the control on the form not PhoneNum field in the Qry - BTW
MsgBox "No Phone Number - a phone number must be entered", vbInformation, "No Phone Number provided!"
Cancel = True
Me.[Phone Num].SetFocus
GoTo exit_beforeUpdate
End If
exit_beforeUpdate:
Exit Sub
Note this does not check the length or the nature of the characters entered in the PhoneNum field - it is just checking there is something as a required field
Thank you GaP42 for your note.
1 = we copy the data base before running the compact and repair function every Friday, as a matter of routine.
The Analyst Company is only used on this form,
The Sector is an attribute of the Analyst
The database application is on a single computer
We will never have multiple Analysts with the same name as the first Analyst would be GAP41, and if another Analyst had the same name they would be entered as GAP41_2
If the Analyst moves to a new company we delete their history
We have always used an auto number such as PersID for our linking field, do you that that we should remove it?
Thank you for your code.
We will never have multiple Analysts with the same name as the first Analyst would be GAP41, and if another Analyst had the same name they would be entered as GAP41_2
If the Analyst moves to a new company we delete their history
We have always used an auto number such as PersID for our linking field, do you that that we should remove it?
Thank you for your code.
Crystal
The single table provided in the db provided has the structure:
PersID should be the PK, not Analyst_Name.
Analyst_Name, as desired, may be left as Indexed (no duplicates).
PersID as a PK will be unique
- you need to check that in your database that indeed it is unique. (While defined as Autonumber the property Indexed shows Duplicates OK !?)
The other point raised was in relation to Communicate: the Yes/No datatype defines a Lookup field. You may not have an issue with this now, however longer term issues may arise - as Mike60smart suggested: Google the Evil's of Lookup Fields in Access Tables
We will never have multiple Analysts with the same name as the first Analyst would be GAP41, and if another Analyst had the same name they would be entered as GAP41_2
If the Analyst moves to a new company we delete their history
We have always used an auto number such as PersID for our linking field, do you that that we should remove it?
So does that mean you use an assigned code for analyst names rather than the actual name? Not good practice. You could have a field to store a unique AnalystAssignedCode and Analyst_Name to store the actual full name ...
Re PersID - see above. If you are using it to link this table to other data, then this should be your record-identifying Primary Key field. Do not delete it. You need to retain the column and the values to keep those links working.
