silly question

[equilib]

Sorry for a very simple question - but I spent far too many hours playing around with it now (both with macros and vb) and I could really do with some help.

How do I run a query from a form, example from after update. What is the vb code I should use?

Thanks,
Petter

 

[boblarson]

Sorry for a very simple question - but I spent far too many hours playing around with it now (both with macros and vb) and I could really do with some help.

How do I run a query from a form, example from after update. What is the vb code I should use?

Thanks,
Petter

What kind of query? Select, Append, Delete, Update?

 

[equilib]

Actually three different queries (in different forms): select, append and update.

 

[boblarson]

Actually three different queries (in different forms): select, append and update.

Okay, a Select query does not need to be run. It can be opened to be displayed, or used as a record source for a form (the preferred method), etc.

For Append and Update queries you can use

CurrentDb.Execute "YourQueryNameHere", dbFailOnError

 

[georgedwilkinson]

Is this on a bound form?

 

[equilib]

Sorry, I may be a bit out or my leauge here.

Bob: what do you mean by using the select query as a record source for a form? What does that mean and how do I execute it?

George: What do you mean by bound form?

What I'm trying to do is to move data from table1 to table2 given a few criterias.
As soon as I open table2 I want to: A) append all new entrys in table1 into table 2
B) update any entries in table2 where they have been changes in table1.

I figured select, append and update queries would do the trick. However, I don't know how to execute them automatically on launch of table2. Further - the append query keeps adding identical values to table2 (ex ID1, ID1). Not really what I had in mind. Any ideas?

 

[RossWindows]

Sounds like all the data updates are flowing one way, so to speak.
If I understand correctly, anything that happens in Table1, you eventually want it to update to Table2 as well, but not the other way around.

Is that accurate?

I don't know how to execute them automatically on launch of table2

I'm pretty sure you can't hook into the open event of a table.
If you have a form bound to the table, you can hook into the open event of the form.

 

[equilib]

Sounds like all the data updates are flowing one way, so to speak.
If I understand correctly, anything that happens in Table1, you eventually want it to update to Table2 as well, but not the other way around.

Is that accurate?

I'm pretty sure you can't hook into the open event of a table.
If you have a form bound to the table, you can hook into the open event of the form.

You are totaly right Ross - it is a one way flow - but I can't get it to work. I have a two forms one for each table (new info to be added in table2). Can you be a little bit more explicit about what you mean by "hook into the open event of the form"?

Thanks

 

[RossWindows]

Sorry, I guess I edited my post while you were already reading it...

What are you ultimately trying to accomplish? What's the big picture?

 

[RossWindows]

"hooking into an event" is programming jargon - sorry about that.
It's just a figure of speech meaning "When this event happens I want this other stuff to happen too."
In Access, when a table is opened, there is no way to make other things happen automatically. "you can't hook into the open event of the table"

I have to go, but I'll try to finish this post in about 1/2 hour.

 

[equilib]

It's for scientific research.
I have a large raw data set (table1) that I want to keep clean and not do any changes to. This raw data will continue to grow.

I want to select and export the observations that are of interest to me to a new table (table2). Then I want to add observations to this data by using a form.

Exporting the data to table2 should not duplicate already exported posts. However, there must be a possibility to update the existing posts in table2 if errors are encountered in table1 (they will then first be corrected in table1).

 

[equilib]

"hooking into an event" is programming jargon - sorry about that.
It's just a figure of speech meaning "When this event happens I want this other stuff to happen too."
In Access, when a table is opened, there is no way to make other things happen automatically. "you can't hook into the open event of the table"

I have to go, but I'll try to finish this post in about 1/2 hour.

Thanks a lot. No I understand that nothing is automatic. I was looking for a way to execute a "selection" query "after update" of the form related to table1 and then run a append query upon launch of table2.

 

[RossWindows]

Ok. I understand a bit more now.
Let's start from the beginning.
How does the data get to you? .csv format, external database?

What I'm getting at here is: The data must be coming from some place and it is constantly changing.
The easiest way to do what you're saying is get in touch with the people who manage that data, and ask them to create an audit trail and dump that data daily to you. So that way, every day you are getting a file with only those things that have been added or changed in some way.

Say you get an audit file like this:

ID, Type, Field, OldValue, NewValue
1, Change, someField, 0, 1
2, NewRecord, someField1, ,10
2, NewRecord, someField2, ,15
2, NewRecord, someField3, ,9.9

You can create queries that say
"Anything that says Change, perform that change on Table2"
"Anything that says NewRecord, loop through the fields given and add those values to their respective places in Table2"

 

[equilib]

Ok. I understand a bit more now.
Let's start from the beginning.
How does the data get to you? .csv format, external database?

What I'm getting at here is: The data must be coming from some place and it is constantly changing.
The easiest way to do what you're saying is get in touch with the people who manage that data, and ask them to create an audit trail and dump that data daily to you. So that way, every day you are getting a file with only those things that have been added or changed in some way.

Say you get an audit file like this:

ID, Type, Field, OldValue, NewValue
1, Change, someField, 0, 1
2, NewRecord, someField1, ,10
2, NewRecord, someField2, ,15
2, NewRecord, someField3, ,9.9

You can create queries that say
"Anything that says Change, perform that change on Table2"
"Anything that says NewRecord, loop through the fields given and add those values to their respective places in Table2"

Thanks a lot for your reply.
The data to table1 is entered by a form by another team. A lot of the data is already there but they are adding more. Thus - they will work on the same database and there will thus be no dumping of data in table1.

When my team opens form2 or table2 the additional or changed information should be updated (probably with quearies that runs "on load" - at least that was my idea)

Could the idea with quearies regarding "Change" and "NewRecord" posts still be used even though the data is not dumped in table1?

 

[RossWindows]

I see. So the data in table1 lives it's whole life there (in the same database).
On form2, it's recordsource is table2 correct?
Is form2 a single form or continuous form?

 

[The_Doc_Man]

You have two problems and a misconception to consider.

First, detecting a change.

Second, updating Table 2 from changes (Append, Update) in Table 1.

Third, your comments about executing a Select, Append, Update etc.

When you write a SELECT query and open it, all it does is show you data. I think it was your intent to then somehow do something with the selected data. However, here is the misconception. An append query starts by doing a SELECT of its data source (implicit within the operation) followed by an INSERT of records from the selected source to the selected data repository. Similarly, an update query does a SELECT of its data source (implicit within the operation) followed by an UPDATE of records in the selected repository. You don't need a separate SELECT in either case, though of course you CAN write an APPEND query that takes data from a SELECT query rather than directly from a table.

Your first problem is a way to distinguish various cases in your Table 1 vs Table 2 operation.

(1) Corresponding records match in all significant ways.
(2) Corresponding records differ in some significant way.
(3) Some records in table 1 have no corresponding records in table 2.
(4) Some records in table 2 no longer have corresponding records in table 1.

From your problem description, perhaps #4 cannot occur, but it is a theortically possible result regardless of its practicality. If you cannot make this first delineation, you are stopped before you could ever update table 2.

You also need a place to remember this delination information since trying to do this as a single query isn't going to be possible. Case 2 requires an UPDATE query but case 3 requires an INSERT or APPEND query.

If case #4 can never happen, you might be able to just add a column of short integers to Table 1 and run three queries, in order.

Q1: Reset your indicator column to 3 for all rows of table 1
Q2: In a query that is an inner join between table 1 and table 2 on some suitable PK, reset all indicator columns in the join to 2.
Q3: In a query that is an inner join between table 1 and table 2, set the indicator to 1 if no differences exist between corresponding fields. Here, the WHERE clause is going to contain one sub-expression for each field to be compared, as

UPDATE (SET A.INDICATOR = 1) from TABLE1 A, TABLE2 B WHERE A.X=B.X and A.Y=B.Y etc etc etc

(THIS IS LOOSE SYNTAX...I'm being sloppy on purpose)

When done, the indicator is 3 for any unmatched records that must be appended to table 2 and is 2 for any matched records with one or more differences. The indicator is 1 for all records that are still the same.

Now you can trigger updates or appends as required based on the indicator.

You can do this from a form, but I might start by building all the required queries and running them from a MACRO by hand as a test. Then you can go back later and convert the macro to VBA when you are ready to run the whole shootin' match from behind a form.

 

[equilib]

Yes - table1 data stays there - only grows.
Yes - form2's recordscource is table2
What do you mean by if form2 is a single or continous form?
I suppose it's single - I created it based on the entries in table2 but I'm not sure what a continous form is.

 

[RossWindows]

Here's the difference.

 

[equilib]

Here's the difference.

Aha - thanks - then it is a single form.

 

[RossWindows]

Aha - thanks - then it is a single form.

Okay, so to add to The_Doc_Man's advise.
When you have your queries built, you can run them from your form two ways that I can think of:

When the form is first opened:
Run the queries against the entire table1 and peform any updates needed to the entire table2
You would use the OnOpen event of the form to do this.

Or

When the form opens to a particular record:
Run the queries against that single record in table1 and perform any updates needed to that single record in table2
You would probably use the OnCurrent event of the form for this

Let's discuss the pros and cons of one or the other... or maybe you could both?!