Continuous Form Not Updating Tables Correctly (1 Viewer)

JessicaGomo

Registered User.
Local time
Today, 03:22
Joined
Feb 26, 2015
Messages
21
I have a continuous form that is using a select query to populate all of the bound controls. The drop downs and text boxes are displaying the values from the correct places. But, the form is not updating in the places I expected. Any information/help would be greatly appreciated!

The select query that is being used to populate, combines information from 3 tables.
  1. 'TimeEntries' table (the one that I want records to be updated in). This holds employee ID, and numeric values for Project Name and Project Task.
  2. 'ProjectInfo' table (nothing should be updated here). This table holds other details about the project (ex. Project Name) that is used to populate a combo box.
  3. 'ProjectTasks' table (nothing should be updated here either). This table holds details about project task (ex. Task Description) that is used to populate a combo box.
The issue I am having is that when records are updated on the form, they are adding records to all 3 tables that are a part of the query, instead of just updating the time entries table.

Record Source (form) = Select Query "EmployeeTimeEntry"

Thanks in advance. :confused:
 

plog

Banishment Pending
Local time
Today, 02:22
Joined
May 11, 2011
Messages
11,613
Forms shouldn't be based on queries. They should be based on tables. So, since you want to update TimeEntries, that should be the data source of the form.

For combo boxes, they can be still be populated with any data source you want. You can still have them feed of their respective tables.
 

JessicaGomo

Registered User.
Local time
Today, 03:22
Joined
Feb 26, 2015
Messages
21
Ok, that makes sense then as to why all three tables from the query were being updated. I now have the correct control table, but am having trouble correctly populating the combo boxes. Can the combo boxes be populated with values from the query instead of the numeric values they are writing to the control table?
 

plog

Banishment Pending
Local time
Today, 02:22
Joined
May 11, 2011
Messages
11,613
Can the combo boxes be populated with values from the query instead of the numeric values they are writing to the control table?

Yes, combo boxes can be based on any table/query. However, they should usually save the primary key of whatever datasource you use, which typically means the value they save is numeric. You may not have the right relationship between your tables if you are saving a text value of one table into another table.
 

JessicaGomo

Registered User.
Local time
Today, 03:22
Joined
Feb 26, 2015
Messages
21
Thank you for your replies thus far, I think I am making progress. My intention is to display a text description in the combo box, but save a numeric value to the table. With my rowsource using a select statement to display the values from the query, I receive an error when i try to make a selection. The error says that the value is the wrong type for that field... any suggestions on what to look at?
 

RainLover

VIP From a land downunder
Local time
Today, 18:22
Joined
Jan 5, 2009
Messages
5,041
Forms shouldn't be based on queries. They should be based on tables. So, since you want to update TimeEntries, that should be the data source of the form.

For combo boxes, they can be still be populated with any data source you want. You can still have them feed of their respective tables.

Plog

I have no idea why you feel this way. I use queries for both Forms and Reports. Never a problem.

Sometimes I convert to SQL and hide it behind a Form/Report. This is good security when converted to mde. No one can get at it then.

But as far as day to day design work, a query is perfectly correct.
 

plog

Banishment Pending
Local time
Today, 02:22
Joined
May 11, 2011
Messages
11,613
I use queries for both Forms and Reports. Never a problem.

But can you give an instance where basing a Form on a query is more beneficial than on a table? I have never come across such an instance (Reports, for sure, but my statement was about forms).

Add to that, the people coming here for advice are a few steps over their heads anyway, I think that rule is a good one. It ensures they know exactly where the data is coming from and going to when they implement a form. It also helps them understand the purpose of normalization: they can see their individual tables as distinct data objects when they need to manipulate the data (forms), and how all those data objects can come together when they need to report on data (reports).
 

JessicaGomo

Registered User.
Local time
Today, 03:22
Joined
Feb 26, 2015
Messages
21
Thank you all for the feedback. The data updates correctly for me with the table as my control source, the query gave me unexpected results. I was able to correctly populate the combo box values with the row source property. But when I select something from the combo box I get an error about invalid type...
 

plog

Banishment Pending
Local time
Today, 02:22
Joined
May 11, 2011
Messages
11,613
...an error about invalid type...

Like I said before, you are probably trying to store the text of the combo box into the numeric field of the underlying table. Search this forum for help on this. What you can do is make the combo box show the text value, but store the numeric value like your table wants.
 

RainLover

VIP From a land downunder
Local time
Today, 18:22
Joined
Jan 5, 2009
Messages
5,041
Thank you all for the feedback. The data updates correctly for me with the table as my control source, the query gave me unexpected results. I was able to correctly populate the combo box values with the row source property. But when I select something from the combo box I get an error about invalid type...

Jessica

The Combo Box is a tool that for one allows you to display something different to what is or is going to be stored in the table. As plog has stated this should be a Number, Primary Key. When saved in the table it is no longer a Primary Key it now becomes a Foreign Key in the Table.

The record source from where you get this data to store in the Table is usually in a separate table of its own.

You store a Number from the Combo's Record Source, its (Primary Key) and store that number only. In the future when you select the number a whole bunch of other information comes with it simply by linking these Record Sources together.
 

RainLover

VIP From a land downunder
Local time
Today, 18:22
Joined
Jan 5, 2009
Messages
5,041
Jessica

Just in Case.

You do realise that a Combo can be built with more than one column. Any of these can be hidden. eg

Primary Key Width 0
FirstName Width 2.5
MiddleName Width 0
LastName width 3.5
 

RainLover

VIP From a land downunder
Local time
Today, 18:22
Joined
Jan 5, 2009
Messages
5,041
Thank you for your replies thus far, I think I am making progress. My intention is to display a text description in the combo box, but save a numeric value to the table. With my rowsource using a select statement to display the values from the query, I receive an error when i try to make a selection. The error says that the value is the wrong type for that field... any suggestions on what to look at?


Make sure that your link between the two is via the Primary and Foreign Keys.

I am referring to the Relationships window.

If you create a join elsewhere like in a query you should be following what you have done in the Relationships.
 

RainLover

VIP From a land downunder
Local time
Today, 18:22
Joined
Jan 5, 2009
Messages
5,041
But can you give an instance where basing a Form on a query is more beneficial than on a table? I have never come across such an instance (Reports, for sure, but my statement was about forms).

Add to that, the people coming here for advice are a few steps over their heads anyway, I think that rule is a good one. It ensures they know exactly where the data is coming from and going to when they implement a form. It also helps them understand the purpose of normalization: they can see their individual tables as distinct data objects when they need to manipulate the data (forms), and how all those data objects can come together when they need to report on data (reports).

The attached may help.

Try the last button then start digging.
 

RainLover

VIP From a land downunder
Local time
Today, 18:22
Joined
Jan 5, 2009
Messages
5,041
plog

If you had a table with a person's Name Address, PositionHeld, DOB would this as far as you are concerned all be in the one table or would you split it out into two tables.

If you create two tables which of these Field would go into the new table.

You could call it tblEmployeePositions.

This information could help me to understand your way of thinking.
 

plog

Banishment Pending
Local time
Today, 02:22
Joined
May 11, 2011
Messages
11,613
If you had a table with a...

I hate to be wishy-washy, but it depends on what the database is for--do I need a historical record of positions held? Is it important to the database? If not, 1 table, if so 2. If 2 tables, I put the PersonID, PositionHeld and Start/End Dates in tblEmployeePositions.
 

RainLover

VIP From a land downunder
Local time
Today, 18:22
Joined
Jan 5, 2009
Messages
5,041
I would put POSITION in table two in all situations.

I would do this under the rules of normalisation.

This would then most likely cause me to create a query which would be used as the Record Source for the Form.
 

plog

Banishment Pending
Local time
Today, 02:22
Joined
May 11, 2011
Messages
11,613
Sorry, don't see the benefit. Why a query for the datasource?

If I had that set up, I would have a form for the employee, then a subform on it for positions. Employee form based off Employee table, subform based off Position table. Don't see how a query is the best solution for this.
 

RainLover

VIP From a land downunder
Local time
Today, 18:22
Joined
Jan 5, 2009
Messages
5,041
plog

If a query is not there to feed a form why did Microsoft include them.

plog, this debate is getting out of hand.

Queries are used by all database engines. How you can normalise and design correctly without them is beyond my comprehension.

I am going to leave this thread as I don't want offend.
 

JessicaGomo

Registered User.
Local time
Today, 03:22
Joined
Feb 26, 2015
Messages
21
Thanks all for the information above, I got my form working! I have the table I am updating, setup as the Control Source for my form. I am using a query for the Row Source on my combo boxes, to display a text description but wanted a numeric value to get stored in the table. What I didn't realize was that I also needed to display the column in my combo box that held the primary key (not just the text description). You can set this up to only display the columns you'd like to see. As mentioned above, this I done by updating the column count property (3 in my case) and the column width property to (0,0,1), to only display the text description in the third column. I hope this thread helps someone else with similar issue. Thank you again!
 

plog

Banishment Pending
Local time
Today, 02:22
Joined
May 11, 2011
Messages
11,613
...this debate is getting out of hand...I am going to leave this thread as I don't want offend.

That's a cop out. This "debate" has been nothing but civil. And in terms of internet debates, amazing straightforward--we haven't nitpicked the details out of each other and tried to start tangential debates until we lost focus of the main issue we are discussing.

So far this has been me me trying to gain understanding of your perspective of how I am incorrect. I'm fully open to changing my opinion, in fact I've helped you present your example by accepting your example's assumptions and not derailing your argument by opposing the minor details of how you are trying to get there.

Now here's were we get off track:

How you can normalise and design correctly without them is beyond my comprehension.

Now, I'm not offended by it and this is another debate in itself; but its pretty passive aggressive to say you are closing the argument because of civility and then close with a derisive comment. Especially when you know you haven't provided a good counterexample to my initial point.


Back on topic: I never said queries weren't necessary. I said forms that allow record creations/edits shouldn't be based on queries. An opinion I still hold because you were unable to effectively provide a counter example to it.
 
Last edited:

Users who are viewing this thread

Top Bottom