Updating 2 tables in a Macro (1 Viewer)

Cedarguy

Access developer wannabe
Local time
Today, 01:11
Joined
May 8, 2012
Messages
39
Hello,

I have a form with a macro that updates certain fields on it when I press a button.

I want to make a related update to another table in the same macro.

How do I do that?

Thanks
 

Minty

AWF VIP
Local time
Today, 08:11
Joined
Jul 26, 2013
Messages
10,353
Run an update query on the other table?

Sorry if that sounds a bit flippant, but that is all you need to do.
 

Cedarguy

Access developer wannabe
Local time
Today, 01:11
Joined
May 8, 2012
Messages
39
Amazing!! Thank you so much for the simple solution you provided; it worked like a charm; I don't see the scale symbol that you referred to; Thanks again
 

Cedarguy

Access developer wannabe
Local time
Today, 01:11
Joined
May 8, 2012
Messages
39
Thanks, I found it; however, it only appeared for Minty after I posted a reply
 

Cedarguy

Access developer wannabe
Local time
Today, 01:11
Joined
May 8, 2012
Messages
39
Hello again,

Same question but now for an "append" instead of an "update".

I set up all the fields in TempVars in the macro and call a query to add a new record to the other table but nothing happens.

What am I missing or can I even do this?

Thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:11
Joined
Sep 21, 2011
Messages
14,037
Run the query manually first.?
 

Cedarguy

Access developer wannabe
Local time
Today, 01:11
Joined
May 8, 2012
Messages
39
Thank you; I tried that with values codes in the query and a got a blank datasheet; it seems as though the record being appended must exist first; please clarify this confusion for me; thank you.
 

Mark_

Longboard on the internet
Local time
Today, 01:11
Joined
Sep 12, 2017
Messages
2,111
An append query adds a new record to a table. Are you sure you are providing valid values for the append? If this is dependent on another table, make sure you've save the original record FIRST.
 

Cedarguy

Access developer wannabe
Local time
Today, 01:11
Joined
May 8, 2012
Messages
39
Thank you.

Yes the values are correct; I've double-checked.

In the form for table 1, I execute a macro via a button;
The macro sets up the tempVars for the values for table 2 needed by the query and then opens the query;
The query is set up to append a record to table 2 using the temp variables set up by the macro.

Nothing happens; with "Action Queries" in Options checked, I see a blank datasheet.

When I execute the query directly with hard-coded values, I get the same result.

What am I missing?

Thanks
 

Mark_

Longboard on the internet
Local time
Today, 01:11
Joined
Sep 12, 2017
Messages
2,111
Can you post the SQL for the query? Also, are you sure you are not trying to duplicate a value in an index that disallows duplicates?

If you hard code the values in a query and the query does the append but doesn't add the values, something is very off.
 

Cedarguy

Access developer wannabe
Local time
Today, 01:11
Joined
May 8, 2012
Messages
39
Here's the SQL:

INSERT INTO task ( Who, What, Completed, Comment, Bypass )
SELECT Task.Who, Task.What, Task.Completed, Task.Comment, Task.Bypass
FROM Task
WHERE (((Task.Who)=2476) AND ((Task.What)=1) AND ((Task.Completed)=Yes) AND ((Task.Comment)="No Answer") AND ((Task.Bypass)=Yes));

A record with these values does not exist in table "Task".

Thanks
 

Mark_

Longboard on the internet
Local time
Today, 01:11
Joined
Sep 12, 2017
Messages
2,111
INSERT INTO with a SELECT normally grabs from one table to insert into a second table. You are using the task table for both your source and destination.

What table are you trying to select from and what table are you trying to insert into?
 

Cedarguy

Access developer wannabe
Local time
Today, 01:11
Joined
May 8, 2012
Messages
39
I see; that's the problem then; I'm trying to add a record to table 2 (Task) while I'm in the form for table 1.

What I'm trying to accomplish is to add a task record by clicking a button on the form for table 1.

Is there a way I can do this?

Thanks
 

Mark_

Longboard on the internet
Local time
Today, 01:11
Joined
Sep 12, 2017
Messages
2,111
What is the table you want to add FROM?
Or put another way, where is the data coming from?
 

Cedarguy

Access developer wannabe
Local time
Today, 01:11
Joined
May 8, 2012
Messages
39
I'm creating the data on the fly by using temp vars
Thanks
 

Mark_

Longboard on the internet
Local time
Today, 01:11
Joined
Sep 12, 2017
Messages
2,111
The the SQL syntax you are looking for is

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
 

Cedarguy

Access developer wannabe
Local time
Today, 01:11
Joined
May 8, 2012
Messages
39
Thanks Mark,

I'm not well versed in sql or vba; the suggested sql is similar to what my query has.

What I gather from what you've stated earlier is that an append only works if the sourse is another table; did I get that right?

I am attempting to mimic the import function in access; I frequently use it to import records to append to the task table.

Any thoughts?

Thanks
 

Mark_

Longboard on the internet
Local time
Today, 01:11
Joined
Sep 12, 2017
Messages
2,111
You won't mimic the import function as the import function very specifically looks at another table (the SELECT statement) to return the values it will group add.

You will instead need to replace the entire "SELECT/FROM/WHERE" block with a VALUE list. This is much easier as you already have the values saved.

Where I have
VALUES (value1, value2, value3, ...);

You would replace each 'value' with the matching variable you already have for Who, What, Completed, Comment, and Bypass.

I don't use macro's so I can't tell you exactly how to change the macro. If you convert from a macro to VBA you would be able to make the change rather easily.
 

Cedarguy

Access developer wannabe
Local time
Today, 01:11
Joined
May 8, 2012
Messages
39
IT WORKED!!

Thanks Mark!

So I'm still using TempVars in the macro to set up the values then opening the Query where the Field values are being substituted with TempVars.

To set up the right select statement I followed your suggestion to code the sql statement and it worked beautifully.

Many thanks, Sir.
 

Users who are viewing this thread

Top Bottom