Form query help (1 Viewer)

[why]

I have a table I am importing from an excel file. This table will be deleted and reimported probably weekly/daily. I have created a second table that will have some of the other information. The first table has closed file information such as:

AllFiles_Table

RowID, Company, Closefilenumber, Year of retention, Proposed Destruction Date, Destruction Date, etc.

I have added a second table,

ApproveforDestruction Table,

ID, RowID, AprrovedforDestruction (Yes/No), User, Approval Date.

My plan was to try and create a query I can use for a form to have the fields from the first table and the ApprovedforDestruction visible in the form. Then allow the user to just select Yes/No on that column. However I found when creating the query I really can’t do that because it is going to be an append to the second table.(Second Table is currently empty) Some of the users will literally have thousands of these to edit yes/no, I am trying to make it as simple to do as possible. Right now, I have splitform I was hoping to just add the column (ApprovedforDesrtuction) and allow them to check the checkbox. When they do it would append the 2ndTable. Is there any way to accomplish this? Or how would you recommend setting this up?

I have quite a bit more to do after this but I am hoping I can use a method similar to this for the rest of the forms.

 

[Pat Hartman]

I don't quite get it. How does the permanent table get built? How does it relate to the spreadsheet? Is there a uniqueID that can be used to match the two? What prompts the decision to destroy something?

Once we get to it, we'll talk about a better way than delete/import to handle temporary data if it turns out you actually need that.

 

[why]

I don't quite get it. How does the permanent table get built? How does it relate to the spreadsheet? Is there a uniqueID that can be used to match the two? What prompts the decision to destroy something?

Once we get to it, we'll talk about a better way than delete/import to handle temporary data if it turns out you actually need that.

I am using the db.TableDefs.Delete and a "DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12," in a button to import excel into the table.
Yes it has a unique key RowID is unique.


The second table I just created I can change it but right now it has ID, RowID, DestructionAprroval, User, Date I hope this makes since

 

[arnelgp]

on the Opening of your Form, add code on the Open event so as to add the
Correct RowID to your 2nd Table.
use Update query using Right join on the First table:

private sub form_open(cancel as integer)
dim strSQL as string
strSQL= "Update table2 Right Join table1 On table2.RowID = table1.RowID Set table2.RowID = table1.RowID Where table2.RowID Is Null;"
Currentdb.Execute strSQL
end sub

 

[why]

on the Opening of your Form, add code on the Open event so as to add the
Correct RowID to your 2nd Table.
use Update query using Right join on the First table:

private sub form_open(cancel as integer)
dim strSQL as string
strSQL= "Update table2 Right Join table1 On table2.RowID = table1.RowID Set table2.RowID = table1.RowID Where table2.RowID Is Null;"
Currentdb.Execute strSQL
end sub

Thank you I will try in the morning

 

[Pat Hartman]

I asked 4 questions. You answered 2 and you misunderstood 1 of those. Let's try again.

1. You said you had 1 permanent table and 1 that you constantly replace from a spreadsheet. How does the permanent table get built?

2. How does the permanent table relate to the spreadsheet?

3. Is there a uniqueID that can be used to match the two?

4. What prompts the decision to destroy something?

 

[why]

I asked 4 questions. You answered 2 and you misunderstood 1 of those. Let's try again.

1. You said you had 1 permanent table and 1 that you constantly replace from a spreadsheet. How does the permanent table get built?

2. How does the permanent table relate to the spreadsheet?

3. Is there a uniqueID that can be used to match the two?

4. What prompts the decision to destroy something?

 

[why]

I realized the issue was trying to use a split form. I am trying a continues form with a button to insert into the other table. Thanks for all your help.

 

[Pat Hartman]

If we don't know the details, we cannot offer viable suggestions. Good luck.

 

[why]

If we don't know the details, we cannot offer viable suggestions. Good luck.

Sorry if I sounded Rude that was not my intent at all. I am grateful for the help you did provide.

 

[Pat Hartman]

You weren't rude. I'm trying to help you to get the best possible help. If you know what your problem was, I'm nappy you found it but your response didn't give me the warm fuzzies that you have actually found the best solution. So, if you are still having a problem, please try to answer questions when experts ask them.