Append Query help

[dan.cawthorne]

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?

 

[dan.cawthorne]

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?

 

[gllincoln]

Hi Dan,

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

Gordon