Echo Method

[StefanScheidegger]

Can you help on this?

I have a form where users may enter data. Right after the input, MS Access should update a field in a query without actually showing the query table.

So far I tried the following with the help of a Macro.

Echo False
Openquery

Unfortunately, MS Access opens the query although I put "Echo" to false. Does anyone know what I am doing wrong? Is there a solution with an easy VBA code?

Stefan

 

[PaulBricker]

The Echo action won't help in this situation. Echo keeps the screen from being repainted, not hiding open Objects. To do what you want in a query, I would suggest you change the macro to this

Echo with the Echo On set to No
OpenQuery
Minimize 'that will keep the query from being visible.

Then do what you need and close the query.

Paul

 

[danvlas]

Access should update a field in a query
Wrong... Fields are always updated in a table
But this is pure theory.

Now I understand what you need. You want to update a field without displaying it. Two ways to do it:
1. Create an update query to change the data in the field. An update query is not 'visible' on the screen and doesn't display the updated field.
Your macro should be:

SetWarnings No
OpenQuery yourUpdateQuery
SetWarnings Yes


2. Open a recordset in VBA, based on the SQL statement of the query:


Access 2k/XP:

Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String

strSQL = "Select WhateverField From WhateverTable Where WhateverCondition"

Set conn = CurrentProject.Connection
rst.Open strSQL, conn, adOpenDynamic, adLockOptimistic
rst.Fields("FieldToUpdate&quot = YourValue
rst.Close
conn.Close
Set rst= Nothing
Set conn=Nothing


Access 95/97

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select WhateverField From WhateverTable Where WhateverCondition"

Set db = CurrentDb
Set rst = db.OpenRecordset strSQL
rst.Edit
rst.Fields("FieldToUpdate&quot = YourValue
rst.Update
rst.Close
Set rst= Nothing
Set db=Nothing

This will work properly if your SQL statement returns one record: the one to be updated. If it doesn't, you'll have to locate the correct record by using FindFirst...

The code should be inserted between
Sub Whatever()
and
End Sub,
where Whatever() is the event procedure that best suits your needs (Form_AfterUpdate, Form_AfterInsert and so on)

Although the first method is easier, keep in mind that the update query can be run from the database window. The second method will only run when necessary (when the event occurs).

Good luck
Daniel Vlas
Systems Consultant
danvlas@yahoo.com