UPDATE TABLE FROM UNBOUND QUERY

[redthunder21 via AccessMonster.com]

I have an Unbound Form that uses a query to gather certain data. Once this
data is int he query, I need to update another table. I want to update the 3
fields in the table - Moved = Yes, DateMoved = Today and TimeMoved = Now. I
am new to access and am struggling with the correct way to accomplish this.

My query is TestQuery and the ID's are the same in the query and the table
that I want to update. I can get the every record of the table field Moved
to change to yes, but I only want the RecordIDs from the query to be updated
in the table.

This is the code I have which updates the entire Moved field in the table:
DoCmd.RunSQL "UPDATE ScrapDataBuild SET scrapdatabuild.MOVED = " &
strCategory & ""

Can anyone help me with the code to select the matching ID's to update the
table? I am using Access2003 and I have tried numerous time to use the where
clause with no luck.

Thanks,
Dawn

 

[strive4peace]

Hi Dawn,

Rather than trying to construct the SQL, use the Design view
of the query

1. AddTables --> TestQuery, Tablename
(from the menu --> Query, Show Table)

2. link the query to the table on the ID field -->
click on the ID field of the table and DRAG to the ID field
of the query and then let go of the mouse

3. change the query to an UPDATE query
(from the menu --> Query, Update)

4. put the fields on the grid to update

drag the field from Tablename to the grid and fill in the
UpDateTo cell..

field -->Moved
tablename --> Tablename
UpdateTo --> Yes

field -->DateMoved
tablename --> Tablename
UpdateTo --> TestQuery.DateMoved

field -->TimeMoved
tablename --> Tablename
UpdateTo --> TestQuery.TimeMoved

then, Run!

alternately, you could store DateTimeMoved (since Date and
Time can be stored together) and update that to Now()

It seems that this is not the best logic to accomplish this
task... what is the criteria for TestQuery? Are you using a
temporary table?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

 

[redthunder21 via AccessMonster.com]

Crystal,

I tried your suggestions, but they didn't work. The table that I need to
update is a table that Production Scans the bad inventory into and
categorizes it. On my unbound form, I am asking the following:

Category
Damage Type
Beginning Date
Ending Date
I also have a preview button and a print button on the form.

This buttons run the TestQuery and I can see the results. Based on the
results of the query, I want to update the main data tale with Moved = Yes,
DateMoved = Now and Time Moved = Now.

Is this unbound form really creating a temporary table? I have never used
temporary tables.

Thanks,
Dawn

Hi Dawn,

Rather than trying to construct the SQL, use the Design view
of the query

1. AddTables --> TestQuery, Tablename
(from the menu --> Query, Show Table)

2. link the query to the table on the ID field -->
click on the ID field of the table and DRAG to the ID field
of the query and then let go of the mouse

3. change the query to an UPDATE query
(from the menu --> Query, Update)

4. put the fields on the grid to update

drag the field from Tablename to the grid and fill in the
UpDateTo cell..

field -->Moved
tablename --> Tablename
UpdateTo --> Yes

field -->DateMoved
tablename --> Tablename
UpdateTo --> TestQuery.DateMoved

field -->TimeMoved
tablename --> Tablename
UpdateTo --> TestQuery.TimeMoved

then, Run!

alternately, you could store DateTimeMoved (since Date and
Time can be stored together) and update that to Now()

It seems that this is not the best logic to accomplish this
task... what is the criteria for TestQuery? Are you using a
temporary table?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

I have an Unbound Form that uses a query to gather certain data. Once this
data is int he query, I need to update another table. I want to update the 3

[quoted text clipped - 16 lines]

Thanks,
Dawn

 

[strive4peace]

Hi Dawn,

perhaps I am missing something...

the form you pop up has several records, which you may change

you then want these changed record to update multiple
records in the recordset that your form is based on ... but
you have no way to link the data...

You need a way to relate the popup form to your main table.
BUT you are also breaking normalizaion rules in your main
table by storing the same information on several records...
the best way I can help you is to help you get your sata
properly structured -- then you would have no need to do this.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Crystal,

I tried your suggestions, but they didn't work. The table that I need to
update is a table that Production Scans the bad inventory into and
categorizes it. On my unbound form, I am asking the following:

Category
Damage Type
Beginning Date
Ending Date
I also have a preview button and a print button on the form.

This buttons run the TestQuery and I can see the results. Based on the
results of the query, I want to update the main data tale with Moved = Yes,
DateMoved = Now and Time Moved = Now.

Is this unbound form really creating a temporary table? I have never used
temporary tables.

Thanks,
Dawn

Hi Dawn,

Rather than trying to construct the SQL, use the Design view
of the query

1. AddTables --> TestQuery, Tablename
(from the menu --> Query, Show Table)

2. link the query to the table on the ID field -->
click on the ID field of the table and DRAG to the ID field
of the query and then let go of the mouse

3. change the query to an UPDATE query
(from the menu --> Query, Update)

4. put the fields on the grid to update

drag the field from Tablename to the grid and fill in the
UpDateTo cell..

field -->Moved
tablename --> Tablename
UpdateTo --> Yes

field -->DateMoved
tablename --> Tablename
UpdateTo --> TestQuery.DateMoved

field -->TimeMoved
tablename --> Tablename
UpdateTo --> TestQuery.TimeMoved

then, Run!

alternately, you could store DateTimeMoved (since Date and
Time can be stored together) and update that to Now()

It seems that this is not the best logic to accomplish this
task... what is the criteria for TestQuery? Are you using a
temporary table?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

I have an Unbound Form that uses a query to gather certain data. Once this
data is int he query, I need to update another table. I want to update the 3

[quoted text clipped - 16 lines]

Thanks,
Dawn