Solved Append Currently Viewed Record Only

[LtWorf33]

I have a form that is viewing records of a linked database. I have a button that runs an Append Query, to add that record to a different table. The problem is that the query appends all records. I only want to it append the currently selected/viewed record. The solution seems to be to specify a WHERE clause, using the PK, but being a linked table, there is no PK, nor can I edit/designate one. Any ideas?

 

[theDBguy]

Hi. Welcome to AWF! Can you post the SQL statement of your APPEND query?

 

[Gasman]

Why would a linked table preclude a PK existing?
You would need to specify the PK of the record you are viewing or at least something that is unique to that record?

 

[Ranman256]

all tables should have a uniqe key. So there is no unique identifier at all in the table?
can you add one? do you have permission to add an AUTONUM? or get an admin to do so?

if not, then youd have to pull enough fields to act as one. the most notorious is a Person. Not every person has a unique key so use:
FirstN
LastN
Addr
City
St
Zip

even those may not do it.

 

[LtWorf33]

Hi. Welcome to AWF! Can you post the SQL statement of your APPEND query?

Thank you! I am happy to find this community and am looking forward to getting more involved.

INSERT INTO tbl_VendorRequests ( email_sender, subject, date_rec, description )
SELECT tbl_UnsortedEmails.From, tbl_UnsortedEmails.[Normalized Subject], tbl_UnsortedEmails.Received, tbl_UnsortedEmails.Contents
FROM tbl_UnsortedEmails;

 

[LtWorf33]

Why would a linked table preclude a PK existing?
You would need to specify the PK of the record you are viewing or at least something that is unique to that record?

The table is a 'linked table' that is linked to an Outlook folder. The properties are set and cannot be edited. And I do not see any reference to a PK in the existing properties. It's highly probably I am missing something, but thus far I have not been able to see/edit and property/field/PK in the linked table.

 

[theDBguy]

Thank you! I am happy to find this community and am looking forward to getting more involved.

INSERT INTO tbl_VendorRequests ( email_sender, subject, date_rec, description )
SELECT tbl_UnsortedEmails.From, tbl_UnsortedEmails.[Normalized Subject], tbl_UnsortedEmails.Received, tbl_UnsortedEmails.Contents
FROM tbl_UnsortedEmails;

Okay, assuming your form is a single view form, you could try changing your query's SQL statement to something like this:

INSERT INTO tbl_VendorRequests ( email_sender, subject, date_rec, description )
VALUES (Forms!FormName.From, Forms!FormName.[Normalized Subject], Forms!FormName.Received, Forms!FormName.Contents)

Hope that helps...

 

[Gasman]

I seem to recall that each item in an Outlook folder has a unique reference?
Someone earlier was trying to save emails to a DB.?

 

[LtWorf33]

all tables should have a uniqe key. So there is no unique identifier at all in the table?
can you add one? do you have permission to add an AUTONUM? or get an admin to do so?

if not, then youd have to pull enough fields to act as one. the most notorious is a Person. Not every person has a unique key so use:
FirstN
LastN
Addr
City
St
Zip

even those may not do it.

I believe I have fields that would qualify as a unique identifier. The table is linked to an Outlook folder, so the time/date received would be best suited as a unique identifier. Being a linked table I do not have the ability to specifiy the PK, and the only solutions I have seen for my problem seem to revolve around using the PK in a WHERE clause. Is it possible to just use a unique field in this way even though it is not a PK? What would the SQL statement look like? I am still a newbie.

 

[Gasman]

Date and Time are unlikely to be unique.

Anyway theDBguy has offered a solution?

 

[LtWorf33]

I seem to recall that each item in an Outlook folder has a unique reference?
Someone earlier was trying to save emails to a DB.?

I thought the same thing, but I haven't found that answer. I was searching for a messageID or a conversationID. After more investigating, I realized that I wasn't even able to choose which fields to link when setting up a 'monitored' table this way.

 

[LtWorf33]

Date and Time are unlikely to be unique.

Anyway theDBguy has offered a solution?

Testing now. Will update.

 

[LtWorf33]

The solution worked and is exactly what I was looking for. Thanks a ton for your assistance. I'm guessing I will be coming back to this forum often as I develop this system more and more. Thanks again.

 

[theDBguy]

The solution worked and is exactly what I was looking for. Thanks a ton for your assistance. I'm guessing I will be coming back to this forum often as I develop this system more and more. Thanks again.

Hi. You're welcome. Glad we could assist. Good luck with your project.