Append Query help 02-18-08

Can Some Help me out with my Append Query or help me with a better way
to do what im achieving.

table one is Clients and table two is Suppliers.

to each table i have related table client contacts and
SupplierContacts which allows me to have , multiply contacts for each
company.

any way on the associated tables i have a tick field called Buzzsaw
members

and what i want to achieve is to have a table that shows combined
client Buzzsaw members and all the supplier Buzzsaw members

So What i did was created two Append Query's that append to a new
table tbl_Buzzsawmembers

and on the criteria under Buzzsaw member tick = True i so set the Name
field as Indexed No Duplicates so i don't get duplicate records in
table

and the first time i run both append query's they work great. the
problem is the second time i run the append query,  it gives me the
error message that cant update table, cause the name field violation
which i expect it to do as i have no duplicates but it still adds the
new records. what i want it to do.

How do i get round the violation message? do i create a query that
deletes the data in the tbl_Buzzsawmembers and re append all data? if
so how do i do that? or can i just have an ignore error trap?


0
dan
2/18/2008 5:11:12 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
692 Views

Similar Articles

[PageSpeed] 26

On 18 Feb, 17:11, "dan.cawtho...@gmail.com" <dan.cawtho...@gmail.com>
wrote:
> Can Some Help me out with my Append Query or help me with a better way
> to do what im achieving.
>
> table one is Clients and table two is Suppliers.
>
> to each table i have related table client contacts and
> SupplierContacts which allows me to have , multiply contacts for each
> company.
>
> any way on the associated tables i have a tick field called Buzzsaw
> members
>
> and what i want to achieve is to have a table that shows combined
> client Buzzsaw members and all the supplier Buzzsaw members
>
> So What i did was created two Append Query's that append to a new
> table tbl_Buzzsawmembers
>
> and on the criteria under Buzzsaw member tick = True i so set the Name
> field as Indexed No Duplicates so i don't get duplicate records in
> table
>
> and the first time i run both append query's they work great. the
> problem is the second time i run the append query,  it gives me the
> error message that cant update table, cause the name field violation
> which i expect it to do as i have no duplicates but it still adds the
> new records. what i want it to do.
>
> How do i get round the violation message? do i create a query that
> deletes the data in the tbl_Buzzsawmembers and re append all data? if
> so how do i do that? or can i just have an ignore error trap?

Ok So Ive Just Created a Delete Query on the tbl_BuzzsawMembers

and it deletes all the records.

so on my form where i have the button i have created a button called
CmdUpdate

and on the Event Procedure i have the following.

Private Sub CmdUpdate_Click()
DoCmd.OpenQuery "BuzzsawMemberDelete"
DoCmd.OpenQuery "BuzzsawmembersS"
DoCmd.OpenQuery "BuzzsawmembersC"
End Sub

seems to do what i want to acheive

the next problem i have is when i click the button,

the form now says #Deleted on all the records. and i have to either
come out form and go back into it to see the new append Details or run
a filter on form and then clear filter.

is there way when i click the update it run a refresh data at end of
the run requerys?
0
dan
2/18/2008 5:28:26 PM
Hi Dan,

Take a look at docmd.requery - that should take care of it.

Gordon

<dan.cawthorne@gmail.com> wrote in message 
news:43f02eb7-69fa-43df-b656-e8ff1216f3b8@e25g2000prg.googlegroups.com...
> On 18 Feb, 17:11, "dan.cawtho...@gmail.com" <dan.cawtho...@gmail.com>
> wrote:
>> Can Some Help me out with my Append Query or help me with a better way
>> to do what im achieving.
>>
>> table one is Clients and table two is Suppliers.
>>
>> to each table i have related table client contacts and
>> SupplierContacts which allows me to have , multiply contacts for each
>> company.
>>
>> any way on the associated tables i have a tick field called Buzzsaw
>> members
>>
>> and what i want to achieve is to have a table that shows combined
>> client Buzzsaw members and all the supplier Buzzsaw members
>>
>> So What i did was created two Append Query's that append to a new
>> table tbl_Buzzsawmembers
>>
>> and on the criteria under Buzzsaw member tick = True i so set the Name
>> field as Indexed No Duplicates so i don't get duplicate records in
>> table
>>
>> and the first time i run both append query's they work great. the
>> problem is the second time i run the append query,  it gives me the
>> error message that cant update table, cause the name field violation
>> which i expect it to do as i have no duplicates but it still adds the
>> new records. what i want it to do.
>>
>> How do i get round the violation message? do i create a query that
>> deletes the data in the tbl_Buzzsawmembers and re append all data? if
>> so how do i do that? or can i just have an ignore error trap?
>
> Ok So Ive Just Created a Delete Query on the tbl_BuzzsawMembers
>
> and it deletes all the records.
>
> so on my form where i have the button i have created a button called
> CmdUpdate
>
> and on the Event Procedure i have the following.
>
> Private Sub CmdUpdate_Click()
> DoCmd.OpenQuery "BuzzsawMemberDelete"
> DoCmd.OpenQuery "BuzzsawmembersS"
> DoCmd.OpenQuery "BuzzsawmembersC"
> End Sub
>
> seems to do what i want to acheive
>
> the next problem i have is when i click the button,
>
> the form now says #Deleted on all the records. and i have to either
> come out form and go back into it to see the new append Details or run
> a filter on form and then clear filter.
>
> is there way when i click the update it run a refresh data at end of
> the run requerys? 

0
gllincoln
2/18/2008 6:35:17 PM
Reply:

Similar Artilces:

Query Delete and append
I am trying to add information to a table with an append query and that works fine since I duplicated the table. The information comes from a linked XLS sheet, I need to delete the information and replace it every day. When I created a delete query it works fine but when I try to add the information again using the append query it does not work unless I open the query in design view, save it and run it. I have dozens of other queries doing the same and they all work fine. What am I doing wrong? I looks like I am getting a 3349 error but why does it work once and then when I delete the ...

Run macro for each record in a query
Hello All I have a table [practices], which contains details of 'client organisations'. From time to time I need to send an email to certain 'client organisations'. I have a macro called 'send_emails' which uses the SendObject command, which I use to send the emails (a button on a form runs the macro). The emails include a report that is specific to the 'client organisation'. Currently I select the 'client organisation' with a combobox, run the macro, select another 'client organisation', run the macro again, etc. etc. It would be extrem...

Formula Help?
How do I express the following formula? If C1, D1 and E1 equal values in Q1:Q12, then W1=HHH, If C1 and D1 equal values in Q1:Q12 and E1 equal a value in R1:R12, then W1=HHT, If C1 and E1 equal values in Q1:Q12 and D1 equal a value in R1:R12, then W1=HTH, If D1 and E1 equal values in Q1:Q12 and C1 equal a value in R1:R12, then W1=THH, If C1 and D1 equal values in Q1:Q12 and E1 equal a value in S1:S12, then W1=HHC, If C1 and E1 equal values in Q1:Q12 and D1 equal a value in S1:S12, then W1=HCH, If D1 and E1 equal values in Q1:Q12 and C1 equal a value in S1:S12, then W1=CHH, If C1 and D1 e...

Nz Function 10-08-07
I am using the Nz Function it work fine but my Currency is now gone how can i get the currency format to return? SELECT qrySubCost.Division, qrySubCost.Carrier, Nz([Cumulative_Impact],"N/A") AS CumulativeImpact FROM qrySubCost; Joe Are you using this query as the record source for a form or a report? If yes, you can set the Format property of the control that is bound to the CumulativeImpact field -- however, note that you're putting a text string ("N/A") into a format for currency ..... -- Ken Snell <MS ACCESS MVP> "Octet32" <O...

Help : Deleted Items Retension
Someone deleted something out of their deleted items late yesterday and wants it back badly. Where can we check the deleted items retension settings, if any ? What's the procedure for making these deleted items visible so they can be retrieved ? (Exch 2k/outlook 2k environment) Thanks a lot. The Deleted Item Retention setting is in the user Properties in Active Directory Users and Computers, on the Exchange General Tab under Storage Limits. You would need to deselect the default and put a figure in the "keep deleted items for (days" field. Once you have enabled it; then...

instr for select Query
Hi. I've rersearched this forum extensivley and get the idea on how to do it but don't have enough experience to make it all the way. In a MS Access select query, I have a field that returns names in the following format: ;#Lastname1, Firstname1;#Lastname2, Firstname2;# I can't seem to sting the instr, len etc, functions together properly to clean up the string to: Lastname1, Firstname1; Lastname2, Firstname2 I've seen the Microsoft examples as well, I think I have to parse through it to break it up then concatenate back together the cleaned names, but the closest I can g...

Appending worksheets
Hello, I have a huge workbook with some 200 worksheets (Excel 2002) and wouldlike to create one single worksheet by appending the data on all worksheets one after another. Is there an easy way to do this without programming a macro? Thanks so much. Provided the total data rows do not exceed 65535 (assuming a standard top row for col labels), one way would be sequential manual copy > paste into a single new sheet placed to the left of the 200 source sheets. At an est 15 sec per manual op, 200 sheets would only take roughly an hour of work to accomplish. Thereafter, to clean up, jus...

Help Desk Features
Has anyone used the Microsoft CRM for Help Desk in addition to its core functions? Can the tool truly be a helpdesk tool out of the box for a small company? I am in need of simple call tracking features with the ability to develop more as well. Thanks. I'm interested in this as well if anyone can provide any info... Thanks, Andy ...

Macro to change pivot field with latest value 02-19-10
Hi there and thank you advance for any help. I have written a macro that changes (hides / shows) fields on a pivot table. On of these changes the field used for the page fields section of the pivot table. However, when i add a field to this section, it defaults to All. How can have this field default to the latest value in this field. Let me explain further: I want the pivot to be shown either by date or by week. The macro then changes the page field section to show either the date or week field. If the date field is selected, I would like the macro to change this to the late...

Append and Append To priviliges
Anybody know what these two priviliges do for a record?? Jay Append allows a user to be able to add items to an object for example you can append an address to an account. Append to allows a user to append this object to something else. You would need append to priv on the address object. They work together. "Jay Mehta" <jay.mehta@conexio.com> wrote in message news:b6caea5f.0310311043.682d756c@posting.google.com... > Anybody know what these two priviliges do for a record?? > > Jay So, in the example you gave of appending an Address object to an Account object,...

lost e-mails 08-17-10
I set up a folder for each month to save the e-mails that I receive. These are extremely important to my business. Are there any way for me to retrieve these. Also, why would I want to compact my e-mails and if I do can I retrieve them? "Annie" <Annie@discussions.microsoft.com> wrote in message news:55A4A7CD-DAE0-4D77-9906-F2AB8FC31B33@microsoft.com... > I set up a folder for each month to save the e-mails that I receive. These > are extremely important to my business. Are there any way for me to > retrieve > these. Also, why would I want to compact...

I need help with MFC and Crystal Reports 9
I have created a VC++ 6 application using MFC. I have created my reports in Crystal Reports 9. Now I am having a problem on how to have my application call the crystal reports. I know that I need to create a class for printing and for previewing the cr reports. What .tlb do I need to use to create the problem COleDispatch derived classes? I have used CR with VB but using it with C++ seems to be a whole lot different. ...

Creating A Search Database..Need Help
Hi, i need help creating a search database using excel lookup functions vlookup, match and index. The criteria is below: I have a sheet filled with data. Data Sheet: Title, Season, Air Date, Star Date, Synopsis (Respectively. Data i sorted alphabetically by Title) I have to create another worksheet, in which a user types a Seaso number, and the formula will display the related results. For example: User types in Season 1 Sheet displays: Title Air Date Star Date Synopsis Pilot xxx xxxx yyyyy New aaa bbb zzzz etc..etc.. Thank -- ajaffe --...

Why do Outlook folders and emails disappear !!? HELP!
I have Office 2003 on Windows XP: I create personal folders, move emails in, and all are gone later (didn't even shut off computer). Inbox emails also disappear and I believe I've correctly disabled the Auto Archive. I am a business and this is a serious loss of needed emails and information! Please assist. SPECIEZnm wrote: > I have Office 2003 on Windows XP: I create personal folders, move > emails in, and all are gone later (didn't even shut off computer). > Inbox emails also disappear and I believe I've correctly disabled the > Auto Archive. I am a busine...

call function in query
dear friends i want an query function to have serial numbers between two pre said numbers like between 100 and 105 = 101,102,103,104,105.thus i created an function in standard access module . public function _ foils(firstfoil as int ,lastfoil as int) as integer for foils = firstfoil TO lastfoil step 1 next foils end function IN query window it appeared as foils( <firstfoil>,<lastfoil>) while accessing and running on QBE window and while runnig query error accoring as "undefined function "foils" in qbe how to build query f...

Querying
I have been playing with MS Query in Excel2000, and have noticed that when I type data that contains both numbers and letters, query doesn't recognise it and does not display it. I have tried to format the cell so that it is recognised as text but still it does not recognise it. It is also interesting to note that MS Query puts a decimal point and a 0 behind the data that are numbers. Please tell me what I have to do to make MSQuery recognise data that contains both numbers and letters ...

Help with Test
Hi, I am trying to complete a test in MFC (which I am not very familiar with). Any help would be greatly appreciated!!!! Rules � Create a grid of 20 Columns by 50 rows. Marks will be deducted for using the Microsoft Flexgrid control. � Each cell in the grid must display a random value between 1 and 999 inclusive. � Each cell in the grid must allow for a threshold value to be set. � If a threshold value has been set for a given cell, the cell's background colour should adhere to the following conditions: If the random va...

Running low on storage on a Ex 5.5 bridgehead server, need help qu
Our environment is a multi Exchange server , with a mix of E2K3 and 1 Exch 5.5. The storage issue is on the 5.5 box. The 5.5 box is setup as a bridgehead server without any recipients. Any incoming external email comes through the Exchange 5.5 box 1st. I have noticed that the priv.edb is 24GB, and the pub.edb is 68MB, 326+ edb logs. Should the priv.edb be this large, especially since there are no mailboxes stored on this server? Should we be having that many edb logs? Would it be safe if I were to stop the Exchange related services, and rename both priv and pub EDBs, and then restart th...

Re-Post Append to Append Query
Hi there, Awhile back, I posted about the following: Have a main form and sub form. When generate a new form, fill in the main form and tab to sub-form, an append query is executed that populates the questions and possible answers for the questionnaire selected in the main form. The user then selects there response. Now, however, additional questions have been added. This means I need to execute some kind of additional append query that will ignore question ids that have already been populated, and add new ones. As he so often does, Allen Brown very kindly posted responses and I ha...

append query with dtae and username
I am using an append query to add a new line to my table - one of the field in this table is "Update_Date" and one is "Updated_By". How via this query I can populated those two fields with the current date&Time and current username of the user . Many thanks, Dan Please ignore- wrong Group, sorry "Dan" wrote: > I am using an append query to add a new line to my table - one of the field > in this table is "Update_Date" and one is "Updated_By". > How via this query I can populated those two fields with the current ...

Excel help and advice needed please
Hi, I'm not very good with Excel, which must be obvious or I wouldn't be asking for help would I? I have a spreadsheet which has grown to hold 23,562 data entries from 7888 name categories, and I need to trim it by deleting old and excess data. Doing this manually will take too long to consider doing it on a regular basis, and there must be some method of speeding the process up. I can manage to get the data into alphabetical and date order, and run the subtotal tool to show how many entries are under each heading.. The smallest entry is 1, and the largest entry found so far is 17....

Help: Shortcut for editing a calendar series
When I want to edit an entire series of calendar events, I have to go through the annoying 'do you want to edit this occurrence or the entire series' dialog. Does anyone know if there is a keyboard or mouse/key combination that opens the series for editing without the dialog? Thanks for an advice. You'll only have to deal with the dialogue once if you want to "open the series for editing..." So I don't understand your question. What are you trying to do? Edit each separate occurence or "edit an entire series of calendar events" Judy Gleeson MVP...

need a bit of help programming
hi i am trying to build a program for basic accounting based on an ol dos program which i used to own 12 years ago! i cant find it anymore so im trying to build this program in excel an i have most of the work done but i need help sorting the basic data all the framework is there for inputing the data around a cashboo principle using item codes as keys for manipulating the data later on trouble is i dont know how to do the manipulating to get the seperat data out and sorted the file will be available for download if anyone would like to take look at it: email me at alan@squarebanana.com a...

formula needed 02-07-10
I am looking for a formula and this is really stumping me. I have a worksheet that has one column w a name and one w a date that of things given, since the name can be on their numberous times as can the date, I need to find a formula that will look to see if the cell above is the same or not and count only if it is different, (which I have that formula) however, I need to go a step further, because not all the names recieve something on the same day, so I need it to if the cell is blank then to leave it blank. How can I do this in ONE formula....so, in short I am looking at so...

Group by Range in Crosstab Query
I have created a crosstab query which displays the number of days from referral to consult. I want to groups the days into ranges (0-14 days); (15-21 days);(>21 days), etc. and show total unit numbers based on that range. The sql statement currently reads as follows: TRANSFORM Count(qryWaitTimesMOReferralToConsult.Unit) AS CountOfUnit SELECT qryWaitTimesMOReferralToConsult.[Ref To Consult (Days)], Count(qryWaitTimesMOReferralToConsult.Unit) AS [Total Of Unit] FROM qryWaitTimesMOReferralToConsult GROUP BY qryWaitTimesMOReferralToConsult.[Ref To Consult (Days)] PIVOT Format([Re...