Append TABLE

[BorisGomel]

Hi ,

I have a small issue and i hope you help me with it .I have a db with 5 large wokring tables .Each tables are appended to final tables that all report are run , forms connected and so on .The problem is that there are many users and sometimes the same datas updated a few times and I have to delete it from final table and sometimes there 1000 and more records .How can I limit any user to append table with the same records only one times not a few.

Really thanks for advise.

Best Regards.

[June7]

The real question is why are you using intermediate tables, why not data entry direct to the final table, why shuffling records? You would need to use VBA code to check if a record already in final table and then permit the append only if record not existing.

If you really must have separate tables, an alternative to moving records might be a UNION query of the tables then base reports on the UNION. There is no wizard or designer for UNION, must type into the SQL View window of the Query Designer.

[BorisGomel]

Thank you June 7 for your input .THE problem is the major file of db IS LOCATED on server of company .I have designed this db and first I set the append query in large macro .The macro has many queris and the final output is excel file whcih we use to process info into accounting software(ORACLE in this case) .When the macro run query , it is always something forgetten to be inputted in first EXCEL spreadshhet which datas are uploaded off into db. Then we reraun the macro again , but the append was done in the first run .I moved the append query and made separated macro just to appeend .But , there are a lot of users and depsite the message box saying "Make sure it is final append" , they append the table a few times and i have to delete all double rows.I am trying to progarm into command box(click procudere) VB code that would check for dates and if there is the same date are about to be appended and if so it cancel it or anyhting that checks data in loop for double .I am new in VB so any head ups , would be vey helpfull .

Thanks a lot .

ReGARDS .

[June7]

Show the code for analysis. You mentioned a loop structure. I expect that within that loop or just before entering the loop you could do either DLookup to check the final data set for existing record or SQL query for records matching criteria. I use DLookup a lot for this.

[BorisGomel]

Here we go .

I am thinking that I know what date I need to append so that i have created A new form so far it is for my using , and i have placed two text box one called txtDate for input date and second called txtClaimNumber and also placed command box in here is the code in "click" procudire:

Me.txtClaimNumber = DLookup("[ClaimNumberQB]", "CHECKSPRINTEDQB2011", "DateQB= Me.txtDate")


Where:
txtClaimNumber-text box on the form for showing claim number for specific date
ClaimNumberQB-is field in table (its name )
CHECKSPRINTEDQB2011-table name
DateQB-name of filed in the table(CHECKSPRINTEDQB2011) date which I am checking
txtDate-text box on form where I input date which I want to check

So I know the dates to append and if the dlookup will return value so that I don't need to append , the table was appended already.
The problem the dlookup is not working .
VB debug is :"Run Tim error"2001'
"You cancleed the previous operation"

I don't know what is it . IT Sems the code fine , but it doesn't work

Please June 7 advise me how caN i fix the code.

Thank you very much in advance.

BEST REGARDS.

[June7]

The txtDate textbox is like a variable, must concatenate variables into the filter argument of DLookup, otherwise the textbox name is a literal string within the quotes and is the value handled by the DLookup. Same syntax as the WHERE clause of SQL statement.
Me.txtClaimNumber = DLookup("[ClaimNumberQB]", "CHECKSPRINTEDQB2011", "DateQB=#" & Me.txtDate & "#")
Text values must have apostrophe delimiters, dates use # symbol.

[BorisGomel]

Thank you very much June 7 .The cod is working !!! Will mark it as solved .

The Best Regards!!