Cascading Data Entry??

[m_d]

I have a table for idenifying fields, with one subtable for data fields.

The data table has 5 fields with multiple records for each record in the info table.

I need the data entry to run so that for field one, when you hit enter instead of going to the next field it goes to the next record in the same field. To accomplish this I have 5 subforms, one for each field.

The problem is when you switch to the next field the data continues, rather than starting back at the first record.

ie

1
1
1
2
2
2
1
---1
---2
---1
---2
---1
-------5
-------8
-------4
-------5
etc..



what I need the table to look like is

1--1--5
1--2--8
1--1--4
2--2--5
2--1
2
1

Thanks

 

[Pat Hartman]

Welcome aboard. Sorry, you lost me. I don't have a clue what you are trying to do. Can you describe a business purpose. I don't see how you get from one set of data to the other.

 

[m_d]

Thanks.




A case of meat has several retail packages each labeled with a weight. The case also is labeled with a weight. I audit random cases to ensure all these weights are accurate.

My info table would have vender, date, purchase order, product description, and 5 fields for case labels. The data table has 5 fields holding the retail weights, each field is a different case. The number of retail packages is variable from one item to another.

The overall structure is working, reporting looks nice, its just that when the retail weights are entered, the second case continues in a new record rather than starting at the first record, and I have to go and manually fix it in the table.

The only way I could find to set up the data entry form, is to use a subform for each case so that when you hit enter it starts a new record rather than jumping to the next case. When I do start a new case though, I need the table to start back at the first record.

If this is still confusing I'll try posting screenshots.

 

[m_d]

[edit]

uploaded mdb rather than the screen shot.


Thanks

 

[Mike375]

What do the numbers mean in each table. Are those on the right the weights and those on the left a label number.

It is very hard to follow but on the surface it appears there is entry method problem.

Perhaps it would be best for you to make a copy of the DB, remove what is not relevant to the problem, compact then Zip and attach to a post.

 

[m_d]

Awesome Mike375, was just working on that as you posted. See the above... help.mdb

 

[Mike375]

I just had a quick look as I am dizzy from my own stuff.

The main form is a one record no new records.

Is there a reason for having a subform for each of the 5 fields in question on the second table.

Are the field/data details entered manually on the full DB for the second table or directly in the subform.

It appears (and from your description and illustrations) in earlier postings that you are entering say 5 bits of information for subform 1 (label1) and thus creating 5 records and then starting on subform2 at what would be record 6 for for subform 2 (label 2) and so on.

 

[Mike375]

One thing I meant to mention is that having a subform for each field is not like one form. While the display looks like a row or a record it is not a record.

Each subform is a different form. Being a subform only means you have placed the form on another form and you have said to Access...Please show me all the records in the table/query that supports the subform where FieldABC = Field XYZ on the main form.

If the subform is just one form then if you were to sort A to Z for a field then the whole record moves but that will not happen with what you have.

 

[m_d]

Ok, I'll run through an example case study.

I buy 100 cases of chicken from The Chicken Farm. Each case has say, 7 chickens, 8 if they're small.

For a given case I would want to track all the information I need related to that particular order. For the audit, lets say the chickens weigh the following

1.56, 1.84, 1.36, 1.44, 1.67, 1.62, 1.70 (stored in retaillabel1)

This adds to 11.19, but the case label shows 11.38. I'm short .19



I'll need to track several cases to show The Chicken Farm they are consistantly shorting me, and charge them for it.

The report will show details for the cases checked, and the net +-

Everything works, except the data entry.



The reason I need the redundant subforms is for the data entry. If I use a single datasheet, or a continuous form for all 5 fields (or cases), after hitting enter on 1.56 focus goes to the next case, rather than staying on one case to enter 1.84. With a subform for each case, focus stays on the one case until you force focus to the next.

I'm quite open to completely changing the input form. I'd rather not change anything else as reporting works nicely, once data is in the right place.

I should also mention I'm not afraid of coding.whatever makes it work.

 

[Mike375]

1.56, 1.84, 1.36, 1.44, 1.67, 1.62, 1.70 (stored in retaillabel1)

This adds to 11.19, but the case label shows 11.38. I'm short .19

The reason I need the redundant subforms is for the data entry. If I use a single datasheet, or a continuous form for all 5 fields (or cases), after hitting enter on 1.56 focus goes to the next case, rather than staying on one case to enter 1.84. With a subform for each case, focus stays on the one case until you force focus to the next.

So 1.56, 1.84, 1.36, 1.44, 1.67, 1.62, 1.70 represents an entry in a field for 7 records like in

1.56...Null...Null...Null...Null
1.84
1.36
1.44
1.67
1.62
1.70

Other 4 "columns" filled in at the same time.

I would not be happy with the five forms becasue of potential mix up of records.

What about standard continuous and you put an little macro or code on Lost Focus Event which is GoToNextRecord and GoToControl. You woud need a different GoToControl for each column so as to keep it in the column.

You could probably also include in the Lost Focus Event and IF Then whereby if the control is Null then GoToFirstRecord and GoToControl. That would be for when you got to the last entry in the column. The first part of the lost focus would take you to the next record (but in the same control) and when yoy hiy enter again because there was no entry it would take you to the top of the second column and so on.

There are undoubtedly all sorts of potential for table structure changes but you appear to want to retain what you have and make entry better.

 

[m_d]

I hadn't thought of macro's, I'll give it a try.

I agree, that 5 subforms as it is now is rather inelagant. If I can get your suggestion to work I won't even need subforms, that would be great.

 

[Mike375]

I hadn't thought of macro's, I'll give it a try.

I agree, that 5 subforms as it is now is rather inelagant. If I can get your suggestion to work I won't even need subforms, that would be great.

It is not a case of being elegant but rather you are trying to data on one record while doing it in 5 different forms.

The basic maco ideas I gave you will allow you to navigate OK. They are very simple.

But your basic problem (and perhaps this a necessity of the business, I don't know) is you are treating a column as a record. It would be similar to giving someone the LastName, FirstName, Street and Suburb for 5 people and instead of entering each person's details on a new record you were to enter the LastNames first and forming 5 records and then entering the FirstNames etc

 

[Pat Hartman]

your problem is really that the database is not normalized. I modified the database to show what it should look like. You can add editing if you want to compare the total as the detail section is being built to the expected totals in the header.

Notice that the sample forms I created require NO code to sync them or do the totalling you just type the weight and tab. You are not limited to a fixed maximum number of packages per case either.

 

[m_d]

Maybe this will help some, I've attached a sample report.

Your file looks much cleaner, structure wise, than what I have currently, but I don't see how to start a new case. The end report will show up to 5 cases for the audit. To make table structure changes I would have to redo the report.

---

As for the macro, using gotocontrol on lostfocus works great up until you want to switch to the second case. because that triggers the lostfocus macro, and are now stuck in that control forever.

Treating a column as a record is presenting complications, but it just seems to make sense for the data I need to store.

--

maybe I should just use 5 different tables, one for each case...

 

[Mike375]

As for the macro, using gotocontrol on lostfocus works great up until you want to switch to the second case. because that triggers the lostfocus macro, and are now stuck in that control forever.

Just click in the next column and that column has the same sort of macro.

If you have the sort of DB structure you have then it is a case of doing this sort of stuff.

I don't think redoing that Report would rank as a huge issue.

 

[Mike375]

This illustration might help you see that what you have is not unique. In other words normalisation (it does not need to be extreme, I am middle of the road with it) and various other aspects of a DB apply to what you have.

buy 100 cases of chicken from The Chicken Farm. Each case has say, 7 chickens, 8 if they're small.

I have 100 policy holders and each policy holder has 7 policy benefits but some have 8 policy benefits.

For a given case I would want to track all the information I need related to that particular order. For the audit, lets say the chickens weigh the following

1.56, 1.84, 1.36, 1.44, 1.67, 1.62, 1.70 (stored in retaillabel1)

This adds to 11.19, but the case label shows 11.38. I'm short .19

For a given policy holder I want to track all the information I need that relate to the 7 policy benefits.

The 7 premiums are etc and they add up to $4315.16 but the insurance company notification is showing $4335.26

The point being that while a policy holder with 7 policy benefits looks nothing like a case with 7 chickens, from an Access perspective there is not much difference.

 

[Pat Hartman]

MD - using the normalized structure that I posted, you would use a crosstab query to create the sample report.