#Name? Unknown Field

[williamson1979]

Hello,

I have a form with 1 unbound combobox and 2 unbound textboxes "I'll add a few more sets if I can get this working". I have 1 table "tbl_testingB"; the field names are listed in the code below. My problem is that when I make a selection in the combobox I get the #name? error "control can not be edited, its bound to a unknown field". The unknown field changes based on the combobox selection. Any help is appreciated.

Thanks

Private Sub cbo_equip_Change()
    If cbo_equip = "Loader" Then
        Me.txt_equiphours.ControlSource = "EquipmentHoursLoader"
        Me.txt_equipfuel.ControlSource = "EquipmentFuelLoader"
    ElseIf cbo_equip = "Skid Steer" Then
           Me.txt_equiphours.ControlSource = "EquipmentHoursSkidSteer"
           Me.txt_equipfuel.ControlSource = "EquipmentFuelSkidSteer"
    ElseIf cbo_equip = "Water Truck" Then
           Me.txt_equiphours.ControlSource = "EquipmentHoursWaterTruck"
           Me.txt_equipfuel.ControlSource = "EquipmentFuelWaterTruck"
    ElseIf cbo_equip = "Wash Down" Then
           Me.txt_equiphours.ControlSource = "EquipmentHoursWashDown"
           Me.txt_equipfuel.ControlSource = "EquipmentFuelWashDown"
    ElseIf cbo_equip = "Other" Then
           Me.txt_equiphours.ControlSource = "EquipmentHoursOther"
           Me.txt_equipfuel.ControlSource = "EquipmentFuelOther"
    Else
    End If
End Sub
 

Looking around the web I had tried adding the below info with no luck.

'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset("tbl_testingB", dbOpenDynaset)
'Dim frm As Form
'DoCmd.OpenForm "frm_testing"
'Set frm = Forms!Testing
 

[twinnyfo]

Haven’t we been through this before? Serious table design issues with this form.

[williamson1979]

Hey Twinny… It separate's the it from the primary table you helped me on. However what Im trying to do works but only if I hide bound textboxes on the form. I was thinking surely there is a way to not have to hide bound boxes.

[williamson1979]

And I'm just trying to learn how to do some things. So I just made a table and a form to practice.

But on the part you'd helped me on I did take what Id learned from you and remove calculated numbers from the tables etc.

[twinnyfo]

Yes, but you are still using ten fields when you need only two and you’re still playing around with changing control sources on your controls. The design is just not the way you want to build anything.

And, it seems like you are still expanding your DB, so the more you continue down this path, more complicated and the more unwieldy it becomes. I cannot recoomend any solution to this thread other than to recommend a structural change to your tables.

[williamson1979]

I think I understand what you are saying. So Field Hours; Field Fuel. So if I were to do that then all types of equipment would save to the same fields? I'd also have to add a 3rd Field Equipment.

[williamson1979]

Ok. Instead of having multiple fields I now have 3. But I still have 5 choices of equipment. They are all bound to the same fields so if I change 1 they all change to match. This is the part I don't get and why I just create individual fields.

[williamson1979]

This is what I've reworked so far. If you would look to see if its structured properly and efficient Id appreciate it. Twinny I'm still not sure how exactly how to incorporate this with my main form so that the fuel and aggregate portions send the correct data for the daily calculations.

[twinnyfo]

So you tblEquipment is on the right track, as well as tbl_equipmentsub. The key here, is that these two tables must be related to each other.

To do this, you can build a relationship between these two tables during the regular design view of your tables. The general process is this: design and build all of your subordinate tables first. For example, lets say your table tbl_equipment lists all the equipment, when it is used, how long and how much fuel they use on that date (this is just my guess of what you are tracking here, but let's go with it for now). Becuase you have different types of equipment to track, your equipment field is a subordinate table, so you make that table before you ever start with your tbl_equipment table--although I would recommend a more meaningful set of names for your tbales: tblEquipUsage and tblEquipment.

Now, let's go further (I explain this just so you understand the principle). Let's say that your equipment table (listing all your equipment) not only has skid steers, but also has different makes or models of skid steers. Some are Bobcats, some are John Deere. So you would create your table tblEquipMftr first, to list all the different equipment manufacturers.

The reason I add this is because, depending upon what you are tracking, you may want to rethink your equipment table, as well. So, if you are only tracking your costs for production, and you don't care how much usage you are getting out of a particular piece of equipment, then you are pretty close to what you need now.

However, let's say you wanted to track how much usage you get out of each particular piece of equipment, then your equipment list, rather than being simply "Loader", "Skid Steer", etc., might be "BC S550-1", "Bobcat", "Model S550". Why "BC S550-1" and "Model S550"? One lists simply the model, but if you have seven of them, you need to keep track of which one. Then, when your table is set up like this, you can tell your boss, "Hey, we're getting a lot of work out of our S550's, but almost no work out of our end loaders. Maybe we don't need so many end loaders and we can recoup some money on their sale...."

Now, some might say that this is over-complexifying things, but it is really just the opposite. We are breaking things down into their simplest parts. That way you can use these parts later (or choose not to use them). However, I digress.

Once you complete your lists of Equipment. save and close that table. Open the Equipment usage table in design view. In the Data Type column for your EquipmentDescript field, click the dropdown and select Lookup Wizard. Select "I want the lookup field to get values from another table or query". Click Next. Select tblEquipment (the renamed table) and add the EquipmentID and EquipDesc to the selected fields. Click Next. Sort by EquipDesc. Click Next. Make sure "Hide key column" is checked. Click Next. Make sure "Enable Data Integrity" and "Restrict Delete" are checked. Click Finish. (You may get a couple "error" messages when you finish because the DB is replacing bad text data with good numerical data. You'll have to adjust the data in the table accordionly.

You may already know how to do that. This tutorial is for any readers who don't know how to do that.

Now---remember how we set up your previous forms? I can only imagine that your new form set up will be remarkably similar.....

Hope this hepps!

[williamson1979]

Hey, on equipment I don't really need tp be so specific but using what you wrote I could add a field for its assigned number "same principle". Far as adding date stamps to everything; I don't really want that but I was trying to think of a way I could join everything into a daily report.

I do like the way the data is cleaner and easier to query this design so far but it makes it more complex "connecting" the data for someone as myself that is learning on the fly.

Thanks

I'm also trying to recreate some of the subforms you went over on my previous layout for the aggregate. I think I'm missing something, maybe its hidden sql or something but I think I'm close.

[twinnyfo]

I do like the way the data is cleaner and easier to query this design so far but it makes it more complex "connecting" the data for someone as myself that is learning on the fly.

It can. But as you understand your structure better, you will be able to execute those reporting options easier over time, and you will have greater flexibility in the long run.

[williamson1979]

I really like the way the data is stored but can I have the calculations form request the data from those tables or a query? Example: tblEquipment records where the fuel was used and amount; tblreport has the fuel tank starting amount, received amount so it needs to know fuel used from tblequipment or the ending amount. So without putting 2 similar but different items together in 1 table how do I get all the numbers to my main calculation form? Sorry for all the questions.

[williamson1979]

I tried to do as you stated on the equipment also adding the 3rd table. I did not get any error messages though. Maybe I did something wrong.

[williamson1979]

So Twinny something like below is what you wee saying I think. How would I tie all three together properly? If I get this figured out then I believe my aggregates or any tracking I added would fit in this design. Thanks


Tables
--------------------------------------------------------
Equipment
tblEquipId EquipID EquipNum
tblEquipType EquipTypeID EquipType
tblEquipUsage EquipUsageID EquipDate EquipHours EquipFuel

Queries
----------------------------------------------------------
Equipment
qryEquipSum Date Type Number Hours Fuel

Form
----------------------------------------------------------
frmEquipmentsub "display datasheet, current record only"

Date:Date() EquipType:Ldr EquipNum:L73 EquipHours:600.3 EquipFuel:50

[twinnyfo]

I’ll try to look at this tomorrow

[williamson1979]

Thanks.

Question 1 - Basically what I posted prior. A main table "usage" linked to a table "type equip" and also linked to a table "equip num" with relationships to keep integrity, cascade update correct?


Question 2 - Being as I have tables to store the equipment type and equipment number is there a easy way to add new from the combobox. I do have some vba code but requires the table to be open.


Question 3 - Can the appearance of the datasheet view be modified? It opens enough space for 3 times the fields I actually have so basically much wider than needed.


Question 4 - Once I complete all my table sets such as Equip, Aggregate and so on how do I "join" the tables or join them in a query so that my report generates everything from the same date. I know you used some vba but I didn't really understand how it worked.

[williamson1979]

OK Twinny, I deleted the last upload. This one is a good bit further along. Its still incomplete as far as some tables I need to add and some vba for my tank conversions.

If you remember my old DB I guess I had been going the route of a spreadsheet with some DB functions but now that I'm trying to do it as you suggest I feel stuck at the point of this upload but also optimistic about the increased versatility I was able to add so far.

I'm thinking everything that needs to be included in my daily report will have to be pulled together in a query and calculated there but when I tried to test that I got a error about unrelated tables. I'm also confused on how I need to get the modified aggregate information to a report since it now consist of multiple rows rather than the 1 long row.

Thanks

[twinnyfo]

Question 1 - Basically what I posted prior. A main table "usage" linked to a table "type equip" and also linked to a table "equip num" with relationships to keep integrity, cascade update correct?

I think you're starting to get the hang of things. This is a process, so try not to get discouraged. With your tblEquipNum, I recommend restructuring:

tblEquipment
EquipID      AutoNumber, PK
EquipNumber  Identification of Equipment (as you have it now)
EquipType    FK to tblEquipTypes

Then in tblEquipUsage, all you need is EquipID.

Question 2 - Being as I have tables to store the equipment type and equipment number is there a easy way to add new from the combobox. I do have some vba code but requires the table to be open.

I'm not sure what you mean (exactly). Are you asking if there is an easy way to add a piece of equipment so that you can use it on your form?

Yes - You could manually add it, but that's no fun. If a user can't find a piece of equipment from the drop down, have an "Add Equipment" button that brings up a small form which allows the user to add a piece of equipment. Then after they are done, it refreshed your Combo Box--this is s discussion for another thread.

Question 3 - Can the appearance of the datasheet view be modified? It opens enough space for 3 times the fields I actually have so basically much wider than needed.

1) I'd have to see what you are talking about because I'm not sure I understand.

2) As a general rule, I avoid datasheet view as much as possible. There can be several good uses, but I prefer to use form view so I can control what does into my DB.

Question 4 - Once I complete all my table sets such as Equip, Aggregate and so on how do I "join" the tables or join them in a query so that my report generates everything from the same date. I know you used some vba but I didn't really understand how it worked.

1) Question for another thread, but...

2) I get the impression that on each date "something" is occuring, such as, you view each date as a "production run" perhaps? If this is the case, then the "master table", if you will, would be something like tblProduction. Then, both the Aggregate and Equipment tables would have an FK to the ProductionID. Then you only have to record the date of the production run--all aggregate/equipment used for that ProductionID would be on that same date.

So................. to make a long story short, this shows the importance of planning out your DB very well before you start building it. This is not your fault, as you've probably never designed a DB from the ground up before. I think all of us who have been self-taught have learned these same principles over time. However, on the road of self-instruction, the main rule to always keep in mind is, "You'll never get it right the first time." As long as you understand that any "mistakes" you are making are out of inexperience, and you continue to learn from those previous attempts, all is good.

As I mentioned before, I think you are starting to move along the right road. This is a journey--not a destination. I continue to learn every day.

Hope this hepps!

[williamson1979]

If you could open up what I uploaded last. See if I’ve made any critical errors please. Also I have PK bit no FK?

[twinnyfo]

I did open it up and my last response are my recommendations. I didn't explore the forms at this point, because I want to get the tables right first.

[williamson1979]

Ok. So the FK connects the tables? The filler, pg and aggregate tables need to share for each days Production. The maintenance table is just a extra set I added that is ok alone. I will have to add the second pg table, natural gas, environmental and production summary “this shows total tons and hours etc the othe tables need for calculations “.

So how do I create this fk and do I randomly just connect the tables that need to be in the loop or is there a order I need to follow.

Also access changed my short text lookup fields to number fields?

[twinnyfo]

Also access changed my short text lookup fields to number fields?

That is your foreign key!

Here is the idea: Your tables have a primary key--this is to identify specific records. Why is this important? Let's say you have a table of poeple. You have 7 people named, "John Smith". How do you differentiate them if you want to find a particular record? You use the primary Key. So, "John Smith #1" might have a primary key of 234567 and "John Smith #2 of 345678. They may never know what their primary key is, and generally speaking not even you really care what that number is. It is only a way to uniquely identify a record.

Now, let's say you have another table for Work Hours. You have to keep track of People and number of work hours. In that table, you want to be able to differentiate betwixt "John Smith #1" and "John Smith #2", so you use their primary key to identify them as the worker. This is considered a "foreign key" because it is a key (even though it is a primary key in its own table) that is foreign to that table. Get it?

This is why it changed your text fields to numbers--because it is saving the primary key in that field, not the actual text.

So how do I create this fk and do I randomly just connect the tables that need to be in the loop or is there a order I need to follow.

We described this way back in Post #9.

You would never want to "randomly" connect tables--but I know that was not your literal intent.

I'm still not sure we've answered your question. Still not sure if we can. I think structurally, we just need to get you up and running first.

[williamson1979]

Ok so master tblProduction would work. So it stores the date. The others if linked do not need if I understand correctly. Would I be correct in each material “type” say aggregate, pg binder, fillers, natural gas etc have their own tables; they are all part of the process or product but in every day paper filling they’d be separated. They would only be joined for a report that summarizes what amounts are added, subtracted and what percent of final product.

[williamson1979]

Thanks again. I know I’m asking a lot of questions but as you said first get the table structure correct. So I ask the above to be sure it can all be neatly joins in the master table. Question; once joined to master do I need forms for each material type? I’m fine if I do. The only thing that bugs me is say my aggregate and fuel forms. Each have multiple rows each day so without the split form I don’t know how to make it show what’s already been entered. So if that’s a issue I’d rather address it now before getting further along.

[twinnyfo]

Would I be correct in each material “type” say aggregate, pg binder, fillers, natural gas etc have their own tables; they are all part of the process or product but in every day paper filling they’d be separated. They would only be joined for a report that summarizes what amounts are added, subtracted and what percent of final product.

If I understand how your db is designed, this is correct. I have no idea what those other terms refer to, but, if they are all separate and distinct functions within a production cycle, then yes, I think this design would work.

Also, keeping this is your hip pocket for future design ease, if you have a "Production" with different, unique aspects of that one production, you can very easily use a Tab Control on your main form for Production with a separate Page on the Tab Control for each aspect of production.

Are you beginning to see how "making things more complex" by "breaking things down into their simplest form" is actually easier in the long run? It may not seem all that clear to you now. But, over time you will appreciate much of this design.

[twinnyfo]

The only thing that bugs me is say my aggregate and fuel forms. Each have multiple rows each day so without the split form I don’t know how to make it show what’s already been entered.

This is where subforms in continuous mode (remember your previous thread) do wonders. For each of your tables that you will have multiple records, you will need to design a subform that captures each record. But, in continuous-form view. This allows you to add as many or as few records as needed. Use your Aggregate form as an example: you could have as few as one aggregate or as many as ten.

Hope we are moving forward with your understanding. I think you are getting there--simply because the nature of your questions seems more directed and focused. I'm not getting a "deer in the headlights" response.

[williamson1979]

Thanks, Ill go over your latest replies and try to put the responses to work. Could you tell me how to make the FK.

[twinnyfo]

Post #9 - this creates a FK in your table to another table.

[williamson1979]

Are you beginning to see how "making things more complex" by "breaking things down into their simplest form" is actually easier in the long run? It may not seem all that clear to you now. But, over time you will appreciate much of this design.

Yes I can see the benefit. I can see much more flexibility and I think I'm beginning to understand the table structure better. But I'm a bit more uncomfortable now with the forms since its not as easy to put stuff back together or at least it seems that way to me at the moment.
-----------------------------
This is why it changed your text fields to numbers--because it is saving the primary key in that field, not the actual text.

So how do I create this fk and do I randomly just connect the tables that need to be in the loop or is there a order I need to follow.
We described this way back in Post #9.

So I made the FK without realizing it, its nice to know the term now. So my production table Ill make fields to link to my other "primary tables" to tie everything together,
---------------------------------
Google searching I seen a tutorial suggesting to put a date field in each table setting default=Now(). Im assuming that just date stamps everything but I also thought the FK was doing that as well anyway by the PK of the related tables?

[twinnyfo]

Google:
Google searching I seen a tutorial suggesting to put a date field in each table setting default=Now().

this is an effective means for Date-Stamping if you have others making lots of changes to your data (but specifically only when records are added). I don't see that as necessary in your particular case, because (if I understand you project correctly) you are concerned about the date of the production, and you really don't care when people add data to the tables.

I really think you are starting to get the hang of this.

BTW, I had been using Foreign Keys in my tables for close to 20 years before I ever heard the term "foreign key"--such is the ignorance of self-instruction. However, once I heard the term, because I was so familiar with what their function was (without a definition) I immediately understood what they were all about.

Again, this is a learning process. Even though your project will be a product of that process--that is not the end of the process (or at least I hope not). Perhaps someday you will also be an expert here on Bytes (review my earliest posts for proof).

[williamson1979]

this is an effective means for Date-Stamping if you have others making lots of changes to your data (but specifically only when records are added). I don't see that as necessary in your particular case, because (if I understand you project correctly) you are concerned about the date of the production, and you really don't care when people add data to the tables.

Correct, As long as I have a date on the production table that is absolutely fine as long as the related tables all report to that date since each dates entry represents one days production total, used and added material. So I believe I understand since I'll use the FK's on the table production to those material tables it will join them to the productions tables PK thus relate their record to it and its date.

[twinnyfo]

Exactly! One date entry. And that date entry will cover all tables and all records that apply to that one production run.

See! You're getting it!

[williamson1979]

I'm going to work with what I've got so far so before I get much further. I've created the tables and relationships as you've discussed or hope I have anyway.

So the step I'm at is the form. I recall you saying to use a continuous form. That being said I can layout similar to what you first seen when I started trying to figure this out with the form with the tab style layout. For the entries that require multiple entries such as aggregate and equipment they need to be a subform?

[twinnyfo]

Use the same (or similar design) as in your previous thread. I have not givenyou any different advice from previously. The design should be very similar for all your related tables.

[williamson1979]

OK, that's what I'm going with. But with the form set to continuous I got the error: you tried to add a subform in design view, access will reset the property to single form. Is that ok or a issue?

[twinnyfo]

Please explain your process.

This is what you shoudl be doing:

1. Create your main form. Save and Close it.
2. Create your subform (in continuous view). Save and Close it.
3. Edit the Main Form; drag your Continuous Form onto your main form where you want it. Set the Master/Child fields.

Never see the error you've described.

[williamson1979]

I used the wizard to add the subform to my form.

[williamson1979]

But a summary. I created my tables, created my primary form in continuous setting, used the wizard to add the subform then I got the error.

[twinnyfo]

You may have to upload your DB.... I've never had issues before.

[twinnyfo]

But a summary. I created my tables, created my primary form in continuous setting, used the wizard to add the subform then I got the error.

This is your problem. The primary form must be in Single Form view.

[williamson1979]

OK I got the 2 reversed. Let me try it again. Thanks Twinny

[williamson1979]

I've got something out of whack. When I was practicing (multi forms before starting to combine here in this db) this my lookups pulled the information correctly etc. Now the either don't show anything, show the PK autonumber or duplicated my table in the relationship view. Probably something simple

[twinnyfo]

I'll have to take a look at this tomorrow.....

[williamson1979]

Sure thing. I’ll keep looking at it in the meantime and see if I can find my mistake. Seems to be the combo selections in the sub form acting strangely. I can’t see a issue but I don’t know enough yet to see one if it were in my face.

The table it duplicated works but there again it duplicated the table. If it matters I do have two combos pulling off it for shared choices.

[twinnyfo]

Seems to be the combo selections in the sub form acting strangely.

You have to be more specific. I can't fix "acting strangely". How is it acting strangely?

The table it duplicated works but there again it duplicated the table.

What do you mean by duplicated tables? I am clueless at to what you mean here.

If it matters I do have two combos pulling off it for shared choices.

Again--this makes absolultely no sense to me.

I opened your DB and I don't know what you want me to look at. You have stripped everything out of it, so there is no data to work with, so there won't be any records to view or select from.

What, specifically, is the issue?

[williamson1979]

I've resolved the combobox issue in the subform, unsure what was wrong. I deleted and re did works fine now.

Where I really need advice is how to set the relationships from my "Primary" table to all of the subform tables so that all entries use the "Primary" tables ID. This part I still cant get my mind around. As you know all that days data is used throughout the entire report for final calculations. Example: tblProduction (subform) has a ID of 1. Click on the + and tblPlantReport (Primary) has a ID of new as if it doesn't record any entries.


Also I don't understand yet how to get " Some Sum" from subform to the "Primary" form. Example: Subform total tons sold "sum" equals 1000. I need the 1000 to go to "Primary" txtbox Paid Tons so it can be added to "waste" to equal "Total Tons Produced" which is used to calculate "Production Rate". Later in the process "Total Tons Produced" is used be multiple "subforms" to calculate percentages.

I know you mentioned setting master and child in form design but honestly I'm unsure of what fields to link.

[williamson1979]

Hello,
I'm still having a few issues. Hopefully you can look at the uploaded file. I'll list what I notice below. Thanks

tblAsphalt2... All the tables show tblPlantReport with its data except this table and it shows tblPlantReport as if no data was entered.

Also rptPlantReport shows no data. So I've made a error or lack the understanding to use multiple tables in one report.

Since I have broken the db down into shorter tables I may have made errors on setting up the relationships if you could look at that.

I have a few more tables to add but I'd like to just get this part correct before going further. Afterwards I'll get back to trying to get the aggregate subform added and working.

[twinnyfo]

Still totally clueless on what you are trying to do with this DB.

As I've told others on this forum, you can't take a DB that doesn't work, change everything, not knowing what you are changing or why, and then think that it's going to work. Then, asking someone else to try to figure out that mess is impossible.

I stilll think you are trying to fix everything at one time. Take things one step at a time and figure out everything one piece at a time. The slowness with which you approach this project will save you time int he long run.

I can't help you on "everything" because I am more lost with your last DB than I was before.

You need to determine specific issues that you need to fix and understand each step as you go along.

I recognize that learning via forum can be very challenging, but even more so when you don't slow down.

Please, help me out and explain what you are trying to do. Otherwise, I can't offer any useful advice.

[williamson1979]

I’m working on the relationships. All I need to know if I have set those correctly. TblPlantReport is the primary. I’ve set relationships to the related tables directly. The secondary tables I created relationships to the tables that store information they use.

So I just need to know if I have the relationships set correctly because tblAsphalt2 when I click the plus in the table it does not show a record for tblPlantReport but the rest do.

Also my report rptPlantReport shows no data when viewed. So I think I’ve made errors on my relationships.

[twinnyfo]

This is exactly where I am clueless. I have no idea what you are trying to present in your DB nor how you are trying to present it.

I am sure you've made errors in your relationships, as well as potential other structural issues. But, what those are, I am at a loss to determine what these are because everything is jumbled together and there is no clear intent of what you want to capture in your database.

I am not trying to be mean or harsh. But this forum is not for designing and building someone else's project. Break your problem down into smaller parts of what you want to do and work piece by piece. I can't tell you what those pieces are, because it's not my project.

I am glad to take any of those pieces of your project, no matter how small, and work toward a solution. But, the more modifications you've made to this, the more confused I have become on what you are trying to accomplish. And, "confused" is truly the best word I can use to describe what I see.

You will also notice that our discussions have had nothing to do with solving your original problem. As I addressed the specifics of your original problem, instead of working that one issue, you've tried to restructure everything collectively--before you understood the concepts necessary for solving your initial problem.

Again, not trying to be mean or harsh, but this is not a productive method of problem solving. I hope you can appreciate my confusion.