Recordset Not Updateable - From Query to Table

[Heatshiver]

Actually, that's a great suggestion. I had thought of this at first, but stayed on my current path.

I will give it a shot and get back to you.

Thanks.

 

[RainLover]

Please check that timing first.

It is important if I am correct that it is 30 seconds.

 

[Heatshiver]

As an append query or make table query it runs for far longer than 30 seconds.

 

[RainLover]

Thanks for that.

It proves my thought is incorrect so we can forget that.

 

[Heatshiver]

It's ok, thanks for trying though!

 

[RainLover]

I am not going anywhere. Just saying that my thought about the 30 Seconds is not the problem.

Lets see how your import into a local table works.

 

[Heatshiver]

Ok, so here is where I am:

I got the query to run properly by date and produce the necessary values. I have two different approaches I am working on with success:

1) Have an invisible form that first produces a value and is carried over to the subform fields to commit to the table.

2) Using VBA in the main form so that when the Days update the query runs and sends a value to the subform.

Neither of these seems to be working. I can get the values to show on the form, so the query is not the issue. And I get no errors from the code itself. Here is my code for the form and the subform. I have inserted the Requery as the date does not seem to change properly without it:

[Forms]![frmT6].Requery
[Forms]![frmDiscSum]![fsubDiscSum].Form.[Vol Cut Disc from Cuttings Dryers] = [Forms]![frmT6]![WellSummary-T6]
[Forms]![frmDiscSum]![fsubDiscSum].Form.[Ave OOC Wgt%] = [Forms]![frmT6]![Ave OOC]

Here is my code for just VBA on Day Change event on the main form:

DoCmd.OpenQuery "qryVolCutAOOCTotal"
[Forms]![frmDiscSum]![fsubDiscSum].Form.[Ave OOC Wgt%] = qryVolCutAOOCTotal.[Ave OOC]
DoCmd.Close acDefault, "qryVolCutAOOCTotal"

Any suggestions why they can't be carried over? At least if I had an error I'd have a point of reference to work from...

 

[RainLover]

When posting code go to Advanced and use the Code Tags.

[Forms]![frmT6].Requery
[Forms]![frmDiscSum]![fsubDiscSum].Form.[Vol Cut Disc from Cuttings Dryers] = [Forms]![frmT6]![WellSummary-T6]
[Forms]![frmDiscSum]![fsubDiscSum].Form.[Ave OOC Wgt%] = [Forms]![frmT6]![Ave OOC]

You have no notes to explain what is going on and why.
You have started with a Requery. Why? What is its purpose. If it has none then delete it.
frmT6 appears to be a poor name. It does not say much.
You have spaces and special characters in the names of your Fields. This is allowed but not advised. It can cause problems.
Why do you want one Open Form to obtain its Values from another Open Form. Why would you not get the values direct from the Table.

In summary this code does nothing but fill two fields and I don't know why.

Here is my code for just VBA on Day Change event on the main form:

DoCmd.OpenQuery "qryVolCutAOOCTotal"
[Forms]![frmDiscSum]![fsubDiscSum].Form.[Ave OOC Wgt%] = qryVolCutAOOCTotal.[Ave OOC]
DoCmd.Close acDefault, "qryVolCutAOOCTotal"

How do you know which Record in the Query you will get the Data from. Is this a Calculated Value. Try using the query to display the info you want and then use that in the Form's Control, Control Source.

I feel as though I have not really been of much help.

Is it possible to post a cut down version of this Database? It really would help. I keep feeling that your Table Designs are largely wrong.

 

[Heatshiver]

When testing the date, it did not seem to change on the form without a Requery, but I planned on just placing code in the criteria of the query to grab directly from the form.

The name T6 really was just for testing and is actually the name of one of the queries. I do plan to change this once everything is working as expected.

The reason I have not directly placed the queries into the form is because this is only one of about 9 queries that produce values, and because of their complexity, not all of them can fit into the one form.

I will try to produce a cut down version of what I am dealing with tonight and post it.

Thanks for the help.

 

[RainLover]

After this

[Forms]![frmDiscSum]![fsubDiscSum].Form.[Ave OOC Wgt%] = qryVolCutAOOCTotal.[Ave OOC]

Add

Me.Dirty = False

It might work but I am not sure.

 

[RainLover]

If you could post that Demo in Access 2003 it would be nice.

You should always use correct name from the beginning. It help you and also others like me who may be looking at it.

 

[Heatshiver]

@RainLover - Sorry for the delay, will upload a condensed version tonight.

@Pat Hartman - Thanks for the advice. I am not very good in the aspects of ADO or DAO, but I would like to try your method.

What I am attempting to do is run a query based on a date, then transfer the values of certain fields (created in the query) to fields in a table.

If I understand correctly, I open a recordset in my database and refer to one of the columns in my query. How do I get this to then be moved into the field on my form, or into a table if that's easier?

Thank you both for all the help. This has been a nightmare situation!

 

[Heatshiver]

After much trial and error, I was able to almost fully accomplish what I wanted... Almost.

I ended up making subforms for the separate queries. Then adding those to the main form. I can now get the values to show and change when the date on the main form changes. I then used the following code to add them to the table:

Dim Tbl As DAO.TableDef
Dim Fld1 As DAO.Field
Dim DB As DAO.Database
Set DB = CurrentDb
Set Tbl = DB.TableDefs("tblDiscSum")
Set Fld1 = Tbl.Fields("[Ave OOC Wgt%]")
Fld1.DefaultValue = [Forms]![frmDiscSum]![fsubAOOCTotal].Form.[Ave OOC]
Set DB = Nothing
Set Tbl = Nothing

There are more fields, but for the sake of simplicity, I just listed the first.

My last problem is that I can only seem to commit one record to the table. I believe this has to do with having no foreign key present in the committed row. I am unsure how to remedy this... If I try to add the table [I want to commit to] to the main form (to use its foreign key), I get an error when opening the main form.

Again, I know I shouldn't be storing calculated values, but for now, I need to. Is there any solution for adding a foreign key? I was thinking of somehow just adding a foreign key through VBA, but since it is connected to an autonumber primary key, I don't think it will work...

Any help would be much appreciated!