Prevent Subform from Moving to New Record 1

[BikeToWork]

I have a main form with a subform in it. The subform table has a one to one referential integrity (cascase updates, deletes) relationship to the main table based on the autonumber ID from the main table. The subform is linked to the main form on this ID field. What I want is for users to enter one record in the subform and have the focus exit the subform when they tab out of the last field on the subform. I want the subform to stay on that record and set the focus to the next field on the main form. Instead, what the subform is doing is cycling to a new record in the subform when the tab is pressed on the last field. Is there any way to handle this? Thank you in advance for help.

 

[Ray1127]

Yes, in the Lost Focus Event of the last field on the subform set the focus to the main form. It's been awhile since I've done this so I don't remember the exact syntax but you should be able to figure this out. Last time I did this was in Access 2000 so if you have a different version there may be a better solution.

 

[kjv1611]

First, if you don't want new records to be added via the subform, turn off the "Allow Additions" option under the Data tab in the subform's properties.

Next, using the LostFocus property, use code to send the Active selection where you want it to go, as suggested by Ray1127.

Post back if you need further help. Give it a try, and see how far you get..

 

[PHV]

You may also consider the Cycle property of your subform.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya BikeToWork . . .

Curious ... why the break in data entry in the mainform? This is like broken logic flow. Typically all data is entered in the mainform ... then the subform.



See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[BikeToWork]

The setup of the forms and subforms is a little unorthodox because the form data has a one to one relationship with the subform data. What I did is break up a huge monolithic table with too many fields into one big table with 150 fields and several smaller, related tables which share a one to one relationship to the data in the main form. I got around the tab from the last record in the subform moving the subform to a new record by setting the cycle property of the form to "current record." AceMan, you are probably right about the logic flaw but now users can only get out of the subforms back to the main form with the mouse, not keyboard. Thank you for the advice on this forum.

 

[MajP]

Since there is really no such thing as a one to one relationship in database design (unless you are compartmentalizing for security or performance reasons)

What I did is break up a huge monolithic table with too many fields into one big table with 150 fields and several smaller, related tables which share a one to one relationship to the data in the main form.

I interpret the following to be "I had one monolithic non-normalized table with too many fields that I converted into one non-normal 150 field table and a couple more non-normal smaller tables"

This sounds like you went from one bad design to an even worse design. Breaking a non-normal table into smaller non normal tables does not improve the design. When the skeleton (your table design) of the database application is bad it causes workarounds like this and non-traditional user input.

I have developed hundreds of large databases and only in very rare cases have ever had a table over 30 fields and never over one hundred. There is always exceptions, but I would say you need to redesign the tables first. This will make everything easier in the long run.

 

[BikeToWork]

MajP, thanks for the comments. I know you are right about the non-normalized database design but I'm not sure how to break up the monolithic 150 field table. The fields in this table are used in eight different data entry forms whose data needs to be together for querying and reporting purposes. The person who originally designed the database had a separate table for each form with many of the same field names repeating in each table. She planned to combine the data via union queries for reporting. Also, those separate tables for each form had up to 90 fields in each of them. How does one go about normalizing such a database design? Any advice is most welcome.

 

[PHV]

Have alook here:

http://www.r937.com/Relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

You have to determine if it is worth it first. If this is something that is used often, you want to add functionality, you are still developing, etc. Then you should go back to the drawing board and fix it.
If it is a one time shot and there are no resources to put against it, then it may not be worth the effort and the band aids are good enough because it may require a complete redesign.

So assuming there is resources and interest to put the work in to fix it.

Look at PHVs post on relational design. The fact that the tables were designed to support a form, tells me there are major problems. You design forms and reports to support the tell design not the other way around.

If you tell us about the type of data maintained, the current tables and fields, we can work you through a proper table design to support your needs.

Then you can use different normalizing queries to push the data from your old tables into the new design.

I guarantee you will end up with more tables, with far fewer fields, more records, less empty fields.

You will go from short, wide, sparsely populated tables to narrow, long, densely populated tables.
(where long = more records, wide = more fields)

 

[BikeToWork]

Unfortunately, due to the restrictions in place where I work, I am unable to upload the structure of the table so I can only cut and paste it and the fields don't line up. Sorry about that. This is the field list of the megatable with 156 fields in it. Many of the numeric fields get their values from lookup tables. I have already "normalized" it to a certain extent by replacing the multivalued fields with related tables. If you have any ideas on how to break up and normalize this table, please share.

FieldName FieldType FieldSize
2ndPass_DPRevisionsMade 8 8
2ndPass_OpsRevisionsMade 8 8
2ndPass_Pass_DOJNotified 8 8
2ndPass_QAPassDate 8 8
ActivityCLINS 10 255
AF_DateQANotified 8 8
AF_DateQAVendorPulled 8 8
AlternateEmailAddress 10 255
AmtProcessed_GB 4 4
BatesNumbers 10 255
CaseManager 10 255
CaseManagerEmailAddress 10 255
Client_QAVendor 1 1
Coding 1 1
Coding After Scanning 1 1
CodingAfterConversion 1 1
CodingAlerts 1 1
CodingAlerts_NumberOf 4 4
CodingManual 1 1
CodingManual_DateofLastRevision 8 8
Color 4 4
CrossReferenceFile_GEN 1 1
CrossReferenceFile_Other 10 255
DateCoded 8 8
DateContainerEnteredPipeline 8 8
DateContainerReceived 8 8
DateConversionCompleted 8 8
DateExtracted 8 8
DateImageFileCreationCompleted 8 8
DateNotified 8 8
DateOCRCompleted 8 8
DateOriginalMediaReturnedtoPMTeam 8 8
DateProcessingCompleted 8 8
DateScanningCompleted 8 8
DateVendorProcessorQACompleted 8 8
DateWPMediaBurned 8 8
DateWPMediaReturnedtoPMTeam 8 8
DeliveryDate 8 8
DeliveryFormat_MediaType 10 255
DeliveryLocation 10 255
DirectoryStructure 4 4
DirectoryStructure_Other 10 255
DisplayHiddenColumns_Notes 1 1
DisplayTrackChanges 1 1
DocumentBoxes_ReturnToSender 1 1
DocumentReAssembly 1 1
DP_DateExceptionReportsComplete 8 8
DP_DateGAPReportsComplete 8 8
DP_DateLoadedtoDatabase 8 8
Endorsement_BatesNumber 1 1
Endorsement_PhraseLabel 1 1
EndorsementBatesNumber_SpecifyFormatting 10 255
EndorsementPhraseLabel_SpecifyFormatting 10 255
Endorsing_BatesNumber 1 1
Endorsing_FL 1 1
Endorsing_PhraseLabel 1 1
EstimatedVolume 10 255
EstimatedVolume_Documents 4 4
EstimatedVolume_Pages 4 4
Fail_DPRevisionsMade 8 8
Fail_OpsRevisionsMade 8 8
Fail_ProcessorReworkDate 8 8
Fail_QAFailDate 8 8
Fail_ReworkAFQADate 8 8
Fail_ReworkProcessorQCDate 8 8
FileInventory 1 1
FileType_JPG 1 1
FileType_Other 1 1
FileType_Other_Specify 10 255
FileType_PDF 1 1
FileType_TIFF 1 1
FirstPageCoded 10 255
GrandJuryMaterial 1 1
ImageFileConversion 1 1
ImageFormat 4 4
ImageType 10 255
InsertSlipSheet 1 1
LastPageCoded 10 255
MaterialShipDate 8 8
Media_ReturnToSender 1 1
Metadata_DateMerge 1 1
Metadata_LoadFile_TextFiles 10 255
MetadataFields_AdditionaltoExtract 10 255
MetadataFields_TargetDatabaseFormat 10 255
MethodOfTransmission 10 255
MethodOfTransmission_Other 10 255
NameofDPContact 4 4
NumberDocsCoded 4 4
NumberImagesConverted 4 4
NumberImagesCreated 4 4
NumberImagesEndorsed 4 4
NumberImagesOCRd 4 4
NumberImagesScanned 4 4
NumberOfImageCDs 10 255
NumberOfOCRDiscs 4 4
NumberofPDFDiscs 4 4
NumberofSets 10 255
NumberPagesCoded 4 4
OCR 1 1
OCROptions 4 4
OCRRollUp 1 1
OCRRollUpOptions 4 4
Operations_VendorSelection 4 4
Orientation 4 4
OriginalDocumentBoxes_ReturnToSender 1 1
OtherShippingInstructions 10 255
PaperBoxes_ReturnToSender 1 1
Pass_DPRevisionsMade 8 8
Pass_OpsRevisionsMade 8 8
Pass_Pass_DOJNotified 8 8
Pass_QAPassDate 8 8
PDF 1 1
PDFFileNaming 10 255
PDFType 4 4
PhraseLabels 10 255
PhysicalConnectors_FL 4 4
PostItNotes 4 4
Print_NumberOfSets 4 4
Print_Options 4 4
Print_Packaging 4 4
Print_PaperType 4 4
Print_Schedule 4 4
Printing 1 1
ProjectName 4 4
ProjectSupervisor 4 4
RemoveSpreadsheetPrintRanges 1 1
Resolution 4 4
ScanFolder 4 4
Scanning 1 1
SecurityClearance 4 4
SlipSheetVerbiage 10 255
SourceContainer 10 255
SourceFormats 10 255
SourceMaterialComments 10 255
SourceMedia 10 255
SpecialActivityCLINS 1 1
SpecialInstructionsOrComments 12 0
SubmissionDate 8 8
SystemDesignManual 1 1
SystemDesignManual_DateofLastRevision 8 8
TrainingSession_Client 1 1
TrainingSession_Vendor 1 1
Turnaround 10 255
Turnaround_Expedited 10 255
UnhideHiddenSpreadsheet 1 1
Vol_Full 10 255
Vol_Number 10 255
Vol_Prefix 10 255
WorkOrderID 4 4
WorkOrderNumber 10 255
WorkOrderProcessingStatus 10 255
WorkOrderProcessingStatusNotes 12 0
WorkOrderSubmissionStatus_Operations 4 4
WorkOrderSubmissionStatus_PMTeams 4 4
WorkOrderType 4 4
WorkProductBoxes_ReturnToSender 1 1

 

[kjv1611]

Wow. That's a lot of fields.

 

[kjv1611]

PHV,

Thanks for posting the relational database design link. Looks like a good read.

 

[MajP]

That format works well. Can you give a description of briefly what it is and how it is used. I am guessing it is a workflow for the production of a document.
Also, if I was to look at this table roughly what percentage of the fields would have values and not null for a completed record? What is the PK?

 

[MajP]

This is more normal than I expected, there does seem to be a whole lot of fields that uniquely identify an item (I think it is a document).
These are some areas that may or not be worth changing. Normalization is an art where sometimes you have to weight the benefits of a more normal design versus the ease of data entry, data display, table creation.

1)There appears to be choices that are designed as multiple fields. Not sure without knowing your process. But assume I have an animal database and animals are mammals, reptiles, amphibians, etc.
I could have yes no fields of IsMammal, IsReptile, IsAmphibian... This is not normal since multiple fields describe the same data. I should have a single field AnimalType with choices of Mammal, reptiel, amphibian. So you may want to look at your yes no fields
If they can be only one choice then consider a single field, If they can be multiple choices consider a linked table.
Assume a document is either a PDF, TIFF, JPG, Other
FileType_JPG 1 1
FileType_Other 1 1
FileType_Other_Specify 10 255 (this field does not uniquely relate to a document but relates to another field in the same table. THat is an idicator of non-normalcy)
FileType_PDF 1 1
FileType_TIFF 1 1

Then the field is a single field FileType whith choices of Jpg, TIF, ...
If it can multiple file types then you may want to link to a child table
tblFileType
documentID_ForeingnKey (relates to a document)
FileType

Here is another possible example. The document may or may not get coding, if it does you have fields that describe the coding and not the document. FIelds that describe another field are signs of non-normalcy. All fields should uniquely describe the item identified by the PK.

Coding 1 1
Coding After Scanning 1 1
CodingAfterConversion 1 1
CodingAlerts 1 1
CodingAlerts_NumberOf 4 4
CodingManual 1 1

so if that is the case then in my mind I have a child coding table
tblCoding
documentID_ForeignKey (relates to a document)
codingPeriod (choices of after scanning or after conversion)
codingManual
codingAlerts
codingAlerts_NumberOF

Note (I broke my own rule here because normalization has to be weighted to the benefits. A coding may or may not have an alert, and the number of alerts relates to an alert and not a coding. If alerts had a lot more properties I would have made an alerts table relating back to the coding table relating back to the document table)

2) There also appears to be a workflow (dates things accomplised) within this table. Depending on your process will really determine if your current design is correct (or better) than I propose. But a more standard design (because it is easier to manage and analyze) is to handle workflows like this.

Create a table default date events that a document will have.

tblDefaultDates
dateEventID (primary key)
documentType (maybe there are different types of documents that have different required dates)
dateEventName ("Date Coded", "Date Container Entered Pipeline", ....)
other fields unique to a date event

Now create a child table that gets records to hold the dates for a document

tblWorkFlow
documentID_foreignKey (relates back to the document)
dateEventID_foreingKey (relates back to a date event)
dtmDate (the actual date goes here)
other fields

So in this case you create a new document. You run an insert query to populate the workflow table with the default set of events. You can add or delete events that are applicable or not applicable.

This gives me several advantages. I can quickly and simply see where the product is in the workflow. I can calculate timing between all events. See if things are occuring in correct order. It becomes way easier to manage this design because when management wants another date tracked then it becomes adding a record to the default table and not redesigning the tables.