table not populating (1 Viewer)

jedder18

Just Livin the Dream!
Local time
Today, 05:06
Joined
Mar 28, 2012
Messages
135
have to put my Access hat on this week.
I created a table (tblsvctimes)
I created a query(qrysvctimes) using table and created 2 fields using calculations to generate hrs and minutes
I use this query in a subform linked to mainform by svcticketID.
I populate the subform in couple different clients.
Show up fine on the subform...
Table is not populating.

This should be so simple? But, I've been working in Crystal for the past couple months and can't remember stuff and feel like a complete idiot as I've created 10 new db's last year using same scenario...

Help please and Thanks.
:banghead:
 

Mark_

Longboard on the internet
Local time
Today, 05:06
Joined
Sep 12, 2017
Messages
2,111
I created a query(qrysvctimes) using table and created 2 fields using calculations to generate hrs and minutes

If you have calculated fields in your query the query cannot be used to update the table.

If you need to "generate hrs and minutes", do so with unbound controls on your form to display these calculated values. This will allow your query to update the underlying table.
 

jedder18

Just Livin the Dream!
Local time
Today, 05:06
Joined
Mar 28, 2012
Messages
135
Ok. so I removed the fields in table and qry and form. Still not populate.
Changed control source of subform to just the table.
nada.
What in the world?
I hate that this is besting me.
 

plog

Banishment Pending
Local time
Today, 07:06
Joined
May 11, 2011
Messages
11,613
Can you upload your database?
 

jedder18

Just Livin the Dream!
Local time
Today, 05:06
Joined
Mar 28, 2012
Messages
135
I'm thinking not...hippa violation.
Wish I could...
 

Mark_

Longboard on the internet
Local time
Today, 05:06
Joined
Sep 12, 2017
Messages
2,111
When we ask if you can upload, we are looking for a stripped version that only has test data that reproduces the problem.

Oddly, many times when you make a stripped down version with test data, the problem seems to disappear....
 

plog

Banishment Pending
Local time
Today, 07:06
Joined
May 11, 2011
Messages
11,613
Honestly, with this issue, we don't even need test data. Give us the form, the query and the table without any data.
 

jedder18

Just Livin the Dream!
Local time
Today, 05:06
Joined
Mar 28, 2012
Messages
135
I will work on that today.
The db has been in use for yrs so lot's of data to be stripped if I went that route.
I'm adding this new subform.
Thanks for all the help.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:06
Joined
Feb 28, 2001
Messages
27,001
Your question is basically "Why can't my form (or query) update?"

Look through Allen Browne's list of causes for non-update. See if ANY of them resemble what is going on.

http://allenbrowne.com/ser-61.html

One other question, since you also tried the table itself: Does the involved table have a multi-valued (lookup) field? That is the same thing in a table as Allen's "sub-query" question for queries.
 

jedder18

Just Livin the Dream!
Local time
Today, 05:06
Joined
Mar 28, 2012
Messages
135
yes it does have a lookup table....
I will definitely read up on this.
Thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:06
Joined
Feb 28, 2001
Messages
27,001
Ah, the insidious LOOKUP field strikes again.

The general reason you can't update something (query OR table) is because Access wants updates to be either atomic (unique target record) or clearly generic to some level (multiple records based on an inclusive WHERE clause). If you have no WHERE clause, the implied all-encompassing WHERE that isn't there can't act as a filter to make records unique.

For queries, when there is a function (intrinsic, aggregate, or user-defined) in a field, if you try to update through the query, the value associated with the function-field cannot be written to any particular record. After all, there is no record with that field name. (I.e. VAL(X) as a query field isn't the name of a table field.) Where the query is a JOIN of two (or more) tables and you didn't specify in the query how to select a specific JOIN row, again there is ambiguity as to where to write the record.

The problem with LOOKUP fields in a table is that they do not provide a clear and singular target record either. By implication, the lookup field is actually a sub-table that is somehow intrinsically linked to the parent table. So it is like the sub-query case that Allen lists as a no-no.

That is why on this forum we tend to recommend against use of a LOOKUP field. That ambiguity of target will kill updates in a heartbeat. More trouble than it is worth, to be honest.
 

jedder18

Just Livin the Dream!
Local time
Today, 05:06
Joined
Mar 28, 2012
Messages
135
that doesn't make sense about the lookup field. I have numerous db's with drop downs with no issues.
 

Mark_

Longboard on the internet
Local time
Today, 05:06
Joined
Sep 12, 2017
Messages
2,111
Can you make a sample that reproduces the problem?

If you can, upload it and we will see if we can identify the issue.
If you can't, that means there is something else going on other than your query/table/form.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:06
Joined
Feb 28, 2001
Messages
27,001
I have numerous db's with drop downs with no issues.

Yes, but how many of them attempt to update records that involve the drop-downs?

IF the drop-down field appears in the list of fields to be updated AND you don't specify a value by which to select the sub-field, you should have problems. If you wrote a query that DID NOT TOUCH the LOOKUP field at all, it might work.
 

static

Registered User.
Local time
Today, 12:06
Joined
Nov 2, 2015
Messages
823
The recordset is updateable otherwise the form would be locked. So something is being updated.

Perhaps a record other than the expected record is being updated? Check Master Child fields of the subform.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:06
Joined
Feb 19, 2002
Messages
42,981
Look at the ControlSource for the control you think is bound to a table field? Does it start with an = sign? If it does, it is not bound and therefore will never update the underlying table. If the control is bound to a field that is calculated by the query, again, it will never be saved to the table.
 

jedder18

Just Livin the Dream!
Local time
Today, 05:06
Joined
Mar 28, 2012
Messages
135
So this is a DUH moment.
My table is populating.
When I run the query, there are 750 rows.
the first 730 rows are blank because I didn't link with anything worthwhile, LOL! so it just gave me the autonumbers to blank data.
The bottom 20 are being filled in with my data. Even with the lookup field.
I didn't bother to scroll to the end.
Thanks for all the help.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:06
Joined
Feb 19, 2002
Messages
42,981
Creating a minimum number of required fields in addition to the primary key will keep you from creating "empty" records. You probably also need to modify your forms so that you do your validation in the form's BeforeUpdate event and also make sure that you are not dirtying a record with code that the user never fills in.
 

Users who are viewing this thread

Top Bottom