Data entry form

[djphatic]

Hi

I am looking for some help in creating a form to use for data entry. Here is my situation.

I have the following tables:

tblContractor (pk ContractorNum - autonumber)
tblStandards (pk StandardNumber - text)
tblStandardsType (pk StandardTypeID)
tblStandardsGroup (pk StandardGroupID)
tblStandardResults (pk ResultID - autonumber)

There are 200+ standards in tblStandards with 4 types of standard.

Each record in the tblStandardResults contains the ContractorNum and StandardNumber along with the results/comments.

I would like to be able to create a form that does the following:

- Initially asks the user which StandardType they would like to input (2 different teams focus on different standards)
- Once the user has selected the StandardType they then select which Contractor it relates to using a ComboBox.
- Once the user has selected the Contractor the table is then checked to see if any of the standards have already been inputted for this contractor.
- If there are no records then the form loads to allow the user to input the data, with the records already created and contain the ContractorNumber and StandardNumber already to eliminate the chance of missing standards or inputting them incorrectly.

The first 2 points I can manage myself, the other 2 I am struggling with and I am not sure how I can do it, though I assume it is possible with VB code.

My access skills are rusty as it has been quite a few years since I created a database.

Please could someone point me in the direction or give me a helping hand. Any help is greatly appreciated.

 

[djphatic]

Nobody got any ideas?

 

[JANR]

Your table description is missing vital parts you haven't told and the relationship between these tables. But I will take a stab at it and guesstimate some.

First it seems to me that tblContractor and tblStandards are your main tables and tblStandardResults is the link (junctiontable) between them.
If so then using a main form with a subform based on tblStandardResult is what you need.

The tblStandardsType is from what I can see a lookuptable for tblStandard. (at least that's what I would do)

Now if you create a form with 3 unbound comboboxes.

Combo1 is based on tblStandardsType to get the Type and combo2 is based on tblContractor. Combo3 you can base on tblStandard which is cascaded from the first combo. Now you have the info to insert into tblStandardResults, you need to link the Master/Child property og your subform to:

MasterLink: combo2;Combo3
ChildLink: ContractorFK;StandardFK

With the link in place you only need to enter comments as Access keeps track your keyfields.

I have enclosed a samble db and relationship diagram for you to study.

As for your 3. question of search the table for existing records, I usually use the DCOUNT function to test if records exist.

Hope this helps, i not the give a better description of your tables and relationships.

JR

 

[djphatic]

Hi

Thank you for your response.

The relationship diagram you have posted is how the relationships work for those tables you have mentioned (obviously the tblStandardsGroup also has a relationship to the tblStandards as a one to many).

The form you have created looks like a possible solution though it doesnt solve my final point, of all of those standards being populated so they are created in the table and then whoever is inputting the data can entry the comment and tab down to the next one etc. instead of selecting each individual standard, checking and then inputting.

Hope that makes more sense?

 

[Simon_MT]

I would create a Dialog Search Form that allows the users to questions and end up with an Entry capable Screen in the case of no results.

Essentially you create a Criteria in VB and just fire it at the record set and see what it results are returned. Using wildcards allows to the search form generally or you can be very specific.

Simon

 

[JANR]

Obviosly this form was intended to populate the final table tblStandardResults which is the table you query to get useful information out of your db.

However it is possible to use this to populate the related tables by using the NotInList event of each combobox, by setting til LimitToList property to Yes. This will trigger NotInList if the user types in something thats not part of the dataset and you can do an Insert to these tables.

JR

 

[djphatic]

Please see the db attached (i have replaced the data with generic info).

As you will see, there are various standards in tblStandards - all (except the ones in use) will have a result recorded in tblStandResults to each contractor in tblContractor.

All standards with StandardTypes A, B & D will be inputted by one team. StandardTypes C will be inputted by another team.

The standards can be broken down into seperate groups (tblStandardGroups) and each group could contain standards with types A, B, C & D or a selection of those.

Each record in tblStandResults should either have a result that it has been met, if evidence has been provided or it is not applicable, along with any additional notes the contractor may have provided. The remaining fields in tblStandResults are for follow-on after the initial results have been inputted, an additional form would be created to allow the user to use these fields.

StandardTypes A are optionally and StandardTypes D only apply to some contractors, so some contractors may not provide any evidence of info regarding these standards.

I hope that explains the situation abit better.

So say I am a member of the first team inputting StandardTypes A, B & D and a folder of results has just been put on my desk. I need to input the results as quickly and efficiently as possible so I don't need to look at the folder again and I can pass it onto the next team who will input StandardTypes C (the reason for the 2 teams is because they look at different evidence/info provided in the folder).

I think the best solution would be:

1) a form which loads showing all StandardTypes A, B & D in sequential order and I can work through the list entering any relevant information which will create a record in tblStandResults. The form would have to display ALL the standards and I would only need to scroll up and down, not clicking on various combo boxes to get to a specific standard.

2) a form which loads StandardTypes A, B & D but has a combo box for StandardGroups (from tblStandardGroups). In this situation the user would have to select each StandardGroup from the combo box (which relates to a section in the folder) and those standards with StandardTypes A, B & D would be displayed allowing the user to input the data which will create a record in tblStandResults.

In both situations there would need to be some sort of check to ensure that data is not being duplicated (perhaps it would be better using ContractNumber and StandardNumber as joint primary keys instead of an autonumber).

Both of your solutions so far sound viable but I am not sure how to implement them. Is it possible to show me via the db I have attached?

Thanks for your help so far, much appreciated.

 

[JANR]

In both situations there would need to be some sort of check to ensure that data is not being duplicated (perhaps it would be better using ContractNumber and StandardNumber as joint primary keys instead of an autonumber).

No i would still use the autonumber as a primary key, instead create a multi-field index on ContactNumber and StandardNumber to prevent duplicate entries.

Here is a link on how to do that: http://www.btabdevelopment.com/main...createamultifieldindex/tabid/140/Default.aspx

I haven had a chance to look at your db yet, may take a look later. Others may jump in.

JR

 

[djphatic]

Thanks, I look forward to hearing from you.

 

[djphatic]

Bumping my post and also looking for some info.

I have been considering how I can get the contractor number and standard number to copy into the tblStandResults and create new values.

I have created a make-table query that is based on the contractor number, so the user selects the relevant contractor from a combo box and a temporary table is created that contains all the standard numbers (250+) and the contract number selected from the combo box.

I now need a form based on tblStandResults that is used for data entry and will lookup the values in the temporary folder and use these for the fields ContractNumber and StandardNumber, though I wouldn't want an actual record to be created in the tblStandResults table until the user enters some data related to that record (i.e. after update on specify field).

I hope that makes some sense. Any ideas how I can implement this?

 

[djphatic]

Bumping post - can anyone help me out?

 

[JANR]

I have created a make-table query that is based on the contractor number, so the user selects the relevant contractor from a combo box and a temporary table is created that contains all the standard numbers (250+) and the contract number selected from the combo box.

If you use a temp table so create one and use AppendQuery to fill it, however using a temp table has its drawbacks.

-The table has to be local to everyuser, to prevent interference from other users.
- It bloates your Frontend DB, can be fixed with compacting the db.

I wouldn't want an actual record to be created in the tblStandResults table until the user enters some data related to that record (i.e. after update on specify field).

A form/subform setup will prevent this.

Attached is one way you could do it when using a temptable.

Note I have changed the field StandardTypeID in tblStandards and tblStandardTypes from text to NUMBER. It's easier to work with numbers than text on keyfields.

However you stated this in your first post:

- Once the user has selected the StandardType they then select which Contractor it relates to using a ComboBox.

Shouldn't there be a junctiontable between tblContractors and tblStandardsTypes ?

JR

 

[djphatic]

Hi

Thanks for you response, I will take a look at your example shortly. I am sure it will be of great help to me.

I have thought about my situation again, in regards to your comments of the problems with creating a temporary table. I have created an unmatched query where the user can input the ContractorNumber and then it looks for the unmatched records in tblStandards and tblStandResults.

I am hoping that I can now use this query as a basic of an input form, though I have yet to have a play around. I am sure I will hit some stumbling blocks.

Shouldn't there be a junctiontable between tblContractors and tblStandardsTypes ?

I am not sure why I would need a junctiontable. I only have 1 standard type that is optional the rest apply to ALL contractors. The records created in tblStandResults relating to this standard type will have a record with not applicable as part of the record, so I can filter this out.

 

[djphatic]

I have taken a look at your example, which works well but I am looking for something different in how the form functions.

I have added my forms to the example you provided. Please see frmInitialInputContractor where the user selects the contractor and then standard types via combo box, although the multi click solution you used in your form would be better for the standard types part.

The main form then loads all the missing standards in tblStandResults based on the Contractor selected. The standard number field displayed at the bottom of each record is from tblStandards. The 2 blank fields are ContractorNumber and StandardNumber from tblStandResults. Ideally, these 2 fields would be already populated on load. I have managed to get these fields populated using an OnLoad/Open event

Me.StandardNumber=Me.tblStandards.StandardNumber

And a similar event that gets the Contractor Number from the combobox on the previous form, but this only populates the first record on the form.

I was thinking it maybe best to have an unbound tick box at the end where the user ticks if the record is complete and then the record is created in the table otherwise nothing is entered in the table and if the form was run again it would appear as a missing entry.

Please ignore the filters at the top and buttons (other than CLOSE), as these won't work at the moment.

Again, thank you for your help.