Append Query

[Tony]

This thing is driving me insane. I have an Append Query
that's supposed to copy a record from one table to another
one, but it's not working.
The tables are identical. In the query I have selected
all the fields, besides the CustomerID (autonumber) field,
to be copied into the other table. It should be a
straightforward process but it's not being cooperative.
When I run the query, I check the other table and the
record wasn't copied. What would you guys need from me to
give me an advice on where to look to solve this issue?
Thanks, Tony.

 

[Ken Snell]

(1) Post the SQL of the query.
(2) Post the table structures.
(3) Post how you are running the query.
(4) Post what messages you get (if any) when you run the query.

 

[Tony]

(1) Post the SQL of the query.
INSERT INTO GrantSelect ( SetupDate, CustomerType,
CompanyName, ContactFName, ContactLName, ContactTitle,
AltContactTitle, AltContactFName, AltContactLName,
Address1, Address2, City, State, ZIP, IntAddress, IntCity,
IntState, IntZip, Country, Phone, Fax, IntPhone, IntFax,
Email, AltEmail, WebSite, Comment, Status, Expire,
Username, Password, Username2, Password2, RefererURL,
RenewalDate, Segment )
SELECT tblGS_Expired_Accounts.SetupDate,
tblGS_Expired_Accounts.CustomerType,
tblGS_Expired_Accounts.CompanyName,
tblGS_Expired_Accounts.ContactFName,
tblGS_Expired_Accounts.ContactLName,
tblGS_Expired_Accounts.ContactTitle,
tblGS_Expired_Accounts.AltContactTitle,
tblGS_Expired_Accounts.AltContactFName,
tblGS_Expired_Accounts.AltContactLName,
tblGS_Expired_Accounts.Address1,
tblGS_Expired_Accounts.Address2,
tblGS_Expired_Accounts.City, tblGS_Expired_Accounts.State,
tblGS_Expired_Accounts.ZIP,
tblGS_Expired_Accounts.IntAddress,
tblGS_Expired_Accounts.IntCity,
tblGS_Expired_Accounts.IntState,
tblGS_Expired_Accounts.IntZip,
tblGS_Expired_Accounts.Country,
tblGS_Expired_Accounts.Phone, tblGS_Expired_Accounts.Fax,
tblGS_Expired_Accounts.IntPhone,
tblGS_Expired_Accounts.IntFax,
tblGS_Expired_Accounts.Email,
tblGS_Expired_Accounts.AltEmail,
tblGS_Expired_Accounts.WebSite,
tblGS_Expired_Accounts.Comment,
tblGS_Expired_Accounts.Status,
tblGS_Expired_Accounts.Expire,
tblGS_Expired_Accounts.Username,
tblGS_Expired_Accounts.Password,
tblGS_Expired_Accounts.Username2,
tblGS_Expired_Accounts.Password2,
tblGS_Expired_Accounts.RefererURL,
tblGS_Expired_Accounts.RenewalDate,
tblGS_Expired_Accounts.Segment, *
FROM tblGS_Expired_Accounts
WHERE (((tblGS_Expired_Accounts.Status)="A"));

(2) Post the table structures.

Not sure what you mean by it...Data Type???

(3) Post how you are running the query.

If cboStatus.OldValue <> "A" And cboStatus.Value = "A" Then
intStatus = MsgBox("Are you sure you want to re-
activate this account?", _
vbYesNo + vbQuestion, "Re-Activate")
If intStatus = vbYes Then
Expire.Value = Now + 365
Comment.Value = Now & ": Reactivated
Account." & vbCrLf & Comment.Value

DoCmd.OpenQuery "q_Append_GSExpired_To_GrantSelect"
DoCmd.Requery

Else
DoCmd.CancelEvent
Exit Sub
End If

(4) Post what messages you get (if any) when you run the

query.
No messages.

 

[Ken Snell]

My guess is that your data change hasn't been saved to the table when you're
running the query, thus there are no records to be appended. Your code runs
the query while the record is still dirty.

Change your code to this (excerpted):


Comment.Value = Now & ": Reactivated
Account." & vbCrLf & Comment.Value\

' new line added to save the data change to the table
Me.Dirty = False

DoCmd.OpenQuery "q_Append_GSExpired_To_GrantSelect"
DoCmd.Requery

 

[John Spencer (MVP)]

First thing I notice is that you have an ALL fields (*) in the source query.

I agree with Ken that you probably need to save the change in the record to get
the update to run on the record. I assume that you don't have more than one
record with a status of "A" in your table. If you do have more than one "A"
status then all the "A" status records will transfer. If you want to transfer
just ONE record, you are going to need to use the primary key in the source
query's where clause to limit this to just one record.

INSERT INTO GrantSelect ( SetupDate, ..., Segment )
SELECT tblGS_Expired_Accounts.SetupDate,
....
tblGS_Expired_Accounts.Segment, * <<<---- Drop the All Records
FROM tblGS_Expired_Accounts
WHERE (((tblGS_Expired_Accounts.Status)="A"))

Modify the WHERE clause to something like:

WHERE tblGS_Expired_Accounts.CustomerID = Forms!YourOpenForm!txtCustomerIDControl

 

[Tony]

Thank you, Ken, I didn't see that...so, If I get the focus
to another control it should also work. What do you think
it's better? The setfocus to another control or the
me.dirty=false? Tony

 

[Ken Snell]

Simply setting the focus to another control will not necessarily save the
record -- unless the control is in a different record or on a different part
of the form (go to subform from main form, or vice-versa, for example). So
go with Me.Dirty = False to be sure.