Multiple Forms For Field Entry

[Torinjr]

Hi There,

I have a main table called "Data Entry",
It has the fields Type, Supplier, Labour/Plant/Material, Rate (actually a lot more field but these 4 will make this problem simpler)

I have supporting tables for validation into these fields, LookupLabour, LookupPlant, LookupMaterial, each with lots of supporting fields i.e. "Rate", that I want to auto populate through the use of forms that link to the "Data Entry" table.

So basically I currently have a form that user selects "Type", this then runs a macro that opens up a form depending on the selection "Labour", "Plant", "Material". The reason I want 3 separate forms is because not all fields are required to be populated in the "Data Entry" Table for each selection and I want to speed up the data clerks entry ability.

So where I'm getting stuck currently is on these forms, I have a cascading combobox where I select supplier from the preceeding combo box (which is based off the LookupLabour/Plant/Material Tables), however when I select a option I get an error. Control Cannot Be Edited; It's Bound To Unknown Field "Labour/Plant/Material". Basically the next step after this is for the rest of the form to auto poulate based off the selection and the data in the Lookup Tables.

Note that I have no relationships in my tables between these lookup tables and the data entry table (as I didnt know how to make the lookup field for Labour/Plant/Material be validated from multiple tables), nor do I have any ID associated with the query builders on the combo boxes.
Basically I would like some advice on how I should structure this database, whether I should add more fields into the data entry table and create a relationship between each lookup tables and the corresponding fields or if theres a neat little trick I can do so I dont get get the above error and have to redo a lot of work.



Thank you

[Micron]

You should stop now and step back. First, no spaces or special characters in object names - especially like "/". Suggest you adopt some sort of naming convention, plus avoid the use of reserved words for your objects (such as Type). You might think you have a valid reason for 3 forms, but I don't think so. Such an approach often means 3x the work when something needs tweaking. A better method usually is to control what the user can enter data into (or even see) based on a prior selection of some sort.

As for your current issue, you haven't revealed much about the query involved in your form (or even if it's just a table). Two reasons I can think of that will raise this error is trying to use an expression in a bound control, or trying to update a calculated control with a value. It's my belief that (almost) all forms should be based on a query, and that query should be used to test updating/appending before bothering to create a form for it. If the query won't allow any sort of edits, there's no point on building a form because obviously it won't function as needed.

Normally I'd suggest more information is needed, but there are some things about your design that can or soon will be very problematic that I really don't think you should worry about this problem right now. I'd say you have bigger issues. Normalization might be one of them, based on how you've named things. So even if you fix all names and design a query for a new form, your tables structure might not be conducive to ever solving this problem that you now have. I have links on normalization, but there's so much available on the subject, you can easily find something that appeals to you in its presentation style.

EDIT: I can't tell if the first combo or the second is the one generating the error. It would also seem that the bound field can't be "found" by Access. Depending on what you're doing, it may be that it shouldn't be bound in the first place. Likely it can't be found because you've bound it to something that isn't part of the form's record source.

[Torinjr]

Okay Thank You for this advice. I will step way back and have a think about the design and naming conventions.
Okay and I was thinking along this line on hiding entry boxes based off selection, so I will incorporate this into my second attempt.

Novice question if a form is based off a query, can the form then record the entered data into a different table?

It's been a long time since ive used access and Im still coming to grips with the basics.

Cheers

[Micron]

As you probably know, a query can relate many fields from several tables and present them in a data sheet view, which looks like a table itself. IF the query is completely updatable (a term used to mean you can add, edit or delete data) then this condition applies to any field in any table that makes up that query. That's why I said a usable query comes before a form, if for no reason other than it's more efficient to restrict what fields or records are loaded into a form as opposed to processing the whole table when the form loads. Mind you, I don't think everyone subscribes to this line of reasoning.

Things that can prevent a query from being updatable include calculated fields, aggregate functions used in the query (e.g. a Totals query), outer joins and being a Union or Cross Tab query.

EDIT: it can be a bit of a challenge to identify which controls should be shown based on a user's selection if any of those controls are common, but code can be trimmed if you plan well. One way is to iterate over the form's controls, either looking at the Tag property value (you set this) or a set of controls whose names aid in the decision. In the case of using the Tag, you can also create Tag properties with comma separated values and use the Instr function to make the decision for any control. Alternatively, you can use a whole whack of If...Then blocks or Case Select blocks. When you get to that point, it's a new thread topic, which has been covered in this forum multiple times.