By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 1,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

#Name? Unknown Field

P: 81
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo_equip_Change()
  2.     If cbo_equip = "Loader" Then
  3.         Me.txt_equiphours.ControlSource = "EquipmentHoursLoader"
  4.         Me.txt_equipfuel.ControlSource = "EquipmentFuelLoader"
  5.     ElseIf cbo_equip = "Skid Steer" Then
  6.            Me.txt_equiphours.ControlSource = "EquipmentHoursSkidSteer"
  7.            Me.txt_equipfuel.ControlSource = "EquipmentFuelSkidSteer"
  8.     ElseIf cbo_equip = "Water Truck" Then
  9.            Me.txt_equiphours.ControlSource = "EquipmentHoursWaterTruck"
  10.            Me.txt_equipfuel.ControlSource = "EquipmentFuelWaterTruck"
  11.     ElseIf cbo_equip = "Wash Down" Then
  12.            Me.txt_equiphours.ControlSource = "EquipmentHoursWashDown"
  13.            Me.txt_equipfuel.ControlSource = "EquipmentFuelWashDown"
  14.     ElseIf cbo_equip = "Other" Then
  15.            Me.txt_equiphours.ControlSource = "EquipmentHoursOther"
  16.            Me.txt_equipfuel.ControlSource = "EquipmentFuelOther"
  17.     Else
  18.     End If
  19. End Sub
  20.  
Looking around the web I had tried adding the below info with no luck.
Expand|Select|Wrap|Line Numbers
  1. 'Dim db As DAO.Database
  2. 'Dim rs As DAO.Recordset
  3. 'Set db = CurrentDb
  4. 'Set rs = db.OpenRecordset("tbl_testingB", dbOpenDynaset)
  5. 'Dim frm As Form
  6. 'DoCmd.OpenForm "frm_testing"
  7. 'Set frm = Forms!Testing
  8.  
1 Week Ago #1
Share this Question
Share on Google+
45 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,054
Haven’t we been through this before? Serious table design issues with this form.
1 Week Ago #2

P: 81
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.
1 Week Ago #3

P: 81
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.
1 Week Ago #4

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
1 Week Ago #5

P: 81
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.
1 Week Ago #6

P: 81
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.
1 Week Ago #7

P: 81
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.
Attached Files
File Type: zip 031419update.zip (92.0 KB, 5 views)
1 Week Ago #8

twinnyfo
Expert Mod 2.5K+
P: 3,054
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!
1 Week Ago #9

P: 81
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.
1 Week Ago #10

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
1 Week Ago #11

P: 81
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.
1 Week Ago #12

P: 81
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.
1 Week Ago #13

P: 81
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
6 Days Ago #14

twinnyfo
Expert Mod 2.5K+
P: 3,054
I’ll try to look at this tomorrow
5 Days Ago #15

P: 81
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.
5 Days Ago #16

P: 81
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
Attached Files
File Type: zip Database_2019-03-17_(2).zip (69.7 KB, 2 views)
5 Days Ago #17

twinnyfo
Expert Mod 2.5K+
P: 3,054
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:
Expand|Select|Wrap|Line Numbers
  1. tblEquipment
  2. EquipID      AutoNumber, PK
  3. EquipNumber  Identification of Equipment (as you have it now)
  4. 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!
4 Days Ago #18

P: 81
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?
4 Days Ago #19

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
4 Days Ago #20

P: 81
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?
4 Days Ago #21

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
4 Days Ago #22

P: 81
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.
4 Days Ago #23

P: 81
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.
4 Days Ago #24

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
4 Days Ago #25

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
4 Days Ago #26

P: 81
Thanks, Ill go over your latest replies and try to put the responses to work. Could you tell me how to make the FK.
4 Days Ago #27

twinnyfo
Expert Mod 2.5K+
P: 3,054
Post #9 - this creates a FK in your table to another table.
4 Days Ago #28

P: 81
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?
4 Days Ago #29

twinnyfo
Expert Mod 2.5K+
P: 3,054
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).
3 Days Ago #30

P: 81
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.
3 Days Ago #31

twinnyfo
Expert Mod 2.5K+
P: 3,054
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!
3 Days Ago #32

P: 81
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?
3 Days Ago #33

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
3 Days Ago #34

P: 81
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?
2 Days Ago #35

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
2 Days Ago #36

P: 81
I used the wizard to add the subform to my form.
2 Days Ago #37

P: 81
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.
2 Days Ago #38

twinnyfo
Expert Mod 2.5K+
P: 3,054
You may have to upload your DB.... I've never had issues before.
2 Days Ago #39

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
2 Days Ago #40

P: 81
OK I got the 2 reversed. Let me try it again. Thanks Twinny
2 Days Ago #41

P: 81
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
Attached Files
File Type: zip ReportDB.zip (41.4 KB, 1 views)
2 Days Ago #42

twinnyfo
Expert Mod 2.5K+
P: 3,054
I'll have to take a look at this tomorrow.....
2 Days Ago #43

P: 81
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.
2 Days Ago #44

twinnyfo
Expert Mod 2.5K+
P: 3,054
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?
1 Days ago #45

P: 81
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.
1 Days ago #46

Post your reply

Sign in to post your reply or Sign up for a free account.