update a query in a form

[etcrier]

i have a query input to a form. the query is made up of 2 other queries. I want to update a field in a table with the current time that the form is displayed and update a time field in a table that is the basis for the query input.

how do you update a table field from a fomr when the input to the from is a query not the actual table?

hope that clear.

thanks

 

[Jtorres13]

I think you can do a macro with the SetValue action. SetValue allows you to change the value to a field in a table. use the NOW() function to capture the date and time the fom is opened and trigger the macro from the OnOpen event of the form that opens. I think...

 

[Remou]

Are you saying that the query is not updateable and therefore you cannot include the time field in the query? If so, you can use an update query:

[tt]strSQL="Update tblTable Set TimeField=#" & Now() _
& "# Where ID=" & Me.ID
CurrentDB.Execute strSQL[/tt]

 

[etcrier]

let me explain a little better.
I have a form that is an invoice type of form. I have a print key that prints this from onto a 1 page hardcopy. the nature of the data makes printing a form necessary instead of a 1 page report. One of the fields on this form is the time the form is displayed for printing. the input data is so complicated it takes 4 queries from 3 tables to come together into and display this form. So all input to the form is from queries not tables. one field in the query is the time the form is displayed. I have a text field on the form that displays the "time()" just fine but I need to get that time written back to one of the tables via the query.

BTY this is not SQL but mdb database. the update must come from the display of the form since they may reprint it and adjust this time again. BTY it a ready mix company and they ticket or invoce must be printed witin 90 minutes of the delivery. which is why all this is necessary.


thanks

 

[Remou]

So all input to the form is from queries not tables. one field in the query is the time the form is displayed. I have a text field on the form that displays the "time()" just fine but I need to get that time written back to one of the tables via the query.

A query is built on tables, so even if the immediate input is from queries, the data is stored in tables. If the control (forms have controls, not fields) that displays Time is bound to the correct field in the query, the table to which this field belongs will be updated, if the query is updateable: not all queries are, hence my question.

The database engine for Microsoft Access is Jet and it understands SQL (Structured Query Language), which is why Tek-Tips has a forum:
Microsoft: Access Queries and JET SQL Forum: forum701
Where you will find many examples.

You can run SQL through VBA code, which is what I suggested above:

[tt]strSQL="Update tblTable Set TimeField=#" & Me.TimeField _
& "# Where ID=" & Me.ID[/tt]
CurrentDB.Execute strSQL

You should substitute the italicized names with the correct names for your database, if you intend to use this method.

[tt]CurrentDB.Execute strSQL[/tt] will run the SQL immediately, without warnings. [tt]DoCmd.RunSQL strSQL[/tt] will run the SQL with warnings.

It is usually better to store Date / Time rather that just Time, therefore I suggested Now() in my example, which returns both.

I hope this is clearer.

 

[TheAceMan1]

How are ya etcrier . . .

To tell if the [blue]recordsource[/blue] of a [blue]form or query[/blue] is [blue]updateable[/blue] have a look at the AddNew button

, if it has that disabled look

then the recordset is uneditable!

See Ya! . . . . . .

 

[etcrier]

thanks
I checked the addnew button and it is NOT in focus so update is not an option. the queries are real complicated so i am thinking i do not have a direct tie to the table ?? just to give you an overview of the query. this application is for a ready mix company. the ticket form i am doing is what the driver takes to the job. the designer made the form with typical heading stuff but the detail is very unusual.
the current load shows in detail but all previous loads for the same day only are in a subtotal amount. I found no way to list just the latestload and summerize the rest in a total. i did a query to summerize the previous loads and combined it with a query for the current load then added the query for the customer and job detail. al this feeds the ticket form in question.

the sql code remou suggested is a little over my head since i have never added any code to access.
i understand the code just not where to put it and what field property to set. i have not had real ggod luck with event handleing in access.

thanks all of you for your great help.

 

[TheAceMan1]

etcrier . . .

I forgot to add a caveat in my post:

Note: for the proper indication in forms the [blue]Allow Additions[/blue] property has to be set to [purple]Yes[/purple]. If set to [purple]No[/purple] the visual effect is the same as [blue]disabled![/blue] . . .

Just check the property to be sure . . .

See Ya! . . . . . .

 

[etcrier]

allow additions is set to yes but I cannot edit anything

thanks

 

[Remou]

A query is not so difficult. Open a new query window and add the table that you want to update to the window. Change the query to an Update query.

Under the time field that you want to change, on the Update To line, type (except with the proper names for the words in italics):

[tt]Forms![Name of your form]![Name of your time Control on the form][/tt]

Under the field that has the unique ID, on the Criteria line, type (except with the proper names for the words in italics):

[tt]Forms![Name of your form]![Name of your ID control on the form][/tt]

You can test this query to see if it is working properly, but to do this you must have the right form open. Remember that update queries change your data, so it is best to practice with a back-up. When you have it working the way you want, you can run this query from a button or from an event on the form.

 

[etcrier]

Remou,
OK let me verify what you said.
this is a new query not an additonal table to the existing form in question, but a new query. and set it to an update query. add the code as you said above.

Forms![CODticket]![text213]
!!!!on the update line of new query for the field "time" of the load table. BTY the load table is fed thru a query to the ticket form. as outlined in previous Q/A

Forms![CODticket]![ID]
!!!!! this field used for an autonumber ticket # and primary key field. I put this on the criteria line of "ID" field

I do have a print form button on the ticket form we have been talking about. DO I add to that print button this new query. the form will then print AND execute this new query.

what event line do you suggest I put the execution or "call" of this new query on the print button. also is there a setting needed to keep the query from any displaying at all so it runs hidden from the screen or is that an issue.

I hope this does it so you can help others
thanks so much for your time.

PS do you use the word "field" and "Control" interchangably.
I noticed sometimes you use control and other time field.

what am i missing.

thanks

david

 

[Remou]

PS do you use the word "field" and "Control" interchangably.
I noticed sometimes you use control and other time field.

I hope not . Forms and Reports have Contols, Tables and Queries have Fields.

You can add

'Turn off warnings for update query,
DoCmd.SetWarnings False
'Run query
DoCmd.RunQuery "QueryName" 
'Turn warnings on again
DoCmd.SetWarnings True

To your print button code. You can post your code, if you run into any problems.

 

[etcrier]

ok
thanks very much

here is the event on the button now. where should i put your code ? before ,after or where.

Private Sub Command124_Click()
On Error GoTo Err_Command124_Click
DoCmd.PrintOut
Exit_Command124_Click:
Exit Sub
Err_Command124_Click:
MsgBox Err.Description
Resume Exit_Command124_Click
End Sub


I'll give it all a try

so
a data element such as "name" which is in a table --- on a form is called a control ?
and in the table that same data is called a field ?

thanks again

 

[Remou]

Try this:

Private Sub Command124_Click()
On Error GoTo Err_Command124_Click

    'Turn off warnings for update query,
    DoCmd.SetWarnings False
    'Run query
    DoCmd.RunQuery "QueryName" 
    'Turn warnings on again
    DoCmd.SetWarnings True

    DoCmd.PrintOut
Exit_Command124_Click:
    Exit Sub
Err_Command124_Click:
    MsgBox Err.Description
    Resume Exit_Command124_Click
End Sub

Dont forget to change "QueryName" to the name of your query.

a data element such as "name" which is in a table --- on a form is called a control ?
and in the table that same data is called a field ?

Not quite. Controls on forms contain data, they are not data in the way that fields are. A control called Name* could contain the field ForeName one minute and LastName the next, after the code has run. Controls should, ideally, be named for the type of control that they are: txtForename, cboCities, lstProducts, cmdClickMe** etc. This can help stop any confusion between fields and controls.

---------------------------------------
* Name is a very bad name for anything, as it is a reserved word.
** There are naming conventions.

 

[rproactive]

This really cleared up a lot of confusion about controls & fields.
Thanks

Is it corrct to say
The property sheets for the forms and controls recordsets are bound to forms (Record Source) and fields are bound to controls (Control Source).

Sorry for the degression

 

[Remou]

rproactive
It is best to start a new thread, otherwise etcrier may get unwanted mail. That being said, I would not say the property sheets for forms and controls are bound to anything, but you may wish to open this up to discussion in a new thread.