Only show records that are not added in subform

[luzz]

Hi guys, I would like to ask if it is possible to show only recordset that are not added into another table in the subform?

For example;

My subform is link to table 1 with all the details from table 1 but i want to add the data from table 1 to table 2. Besides that, once i save the data from table into table 2, that particular data that i have saved should not show in the subform. The subform should only show data that i have not added into table 2. Possible?

Thank you in advance!!

 

[JHB]

Use an unmatched query, use the wizard to create the query.

 

[luzz]

Use an unmatched query, use the wizard to create the query.

Hi, I have tried the unmatched query but when i want to add the record into another table, the record does not appear in my another table. How do i Fix that?

 

[isladogs]

The unmatched query wizard creates a SELECT query.
Change the query created to an APPEND query

 

[luzz]

The unmatched query wizard creates a SELECT query.
Change the query created to an APPEND query

Why do i need to append the query?

 

[isladogs]

I suggested the append query because that's what this indicated to me

when i want to add the record into another table, the record does not appear in my another table. How do i Fix that

To me that reads you want to add (APPEND) the record to another table!

 

[luzz]

I suggested the append query because that's what this indicated to me



To me that reads you want to add (APPEND) the record to another table!

oh i see, but when i append the query, it shows me error " there is an an duplication" when i open my form and the details on subform on my form is gone

 

[isladogs]

oh i see, but when i append the query, it shows me error " there is an an duplication" when i open my form and the details on subform on my form is gone

Sorry that doesn't make sense.
Show us the query SQL and the exact error mesage

 

[Mark_]

Luzz,

Is there a reason you do not have a field in your 1st table (most likely a date) that shows it is now in the 2nd?

My guess is you've a need for the data to be in two places, so I'm surprised you do not note when it was taken care of.

 

[luzz]

Sorry that doesn't make sense.
Show us the query SQL and the exact error mesage

After creating the append query, i should use the append query as my subform?

 

[isladogs]

No!
We seem to be going round in circles
It seems that you are unclear about what an append query does...

Back in post #1 you wrote:

Hi guys, I would like to ask if it is possible to show only recordset that are not added into another table in the subform?

For example;

My subform is link to table 1 with all the details from table 1 but i want to add the data from table 1 to table 2. Besides that, once i save the data from table into table 2, that particular data that i have saved should not show in the subform. The subform should only show data that i have not added into table 2. Possible?

You cannot use any type of action query (append/update/delete) for a form.

You can show the results of an unmatched SELECT query for a subform
BUT to add those records to another table you need an APPEND query

As I previously asked back in post #8, please show us the query SQL and the exact error message

 

[luzz]

No!
It seems that you are unclear about what an append query does...

Back in post #1 you wrote:


You cannot use any type of action query (append/update/delete) for a form.

You can show the results of an unmatched SELECT query for a subform
BUT to add those records to another table you need an APPEND query

oh! i have the unmatch query into my form, how do i add the record from my unmatch query into the append table? Currently, my save button is tied to another subform which is link to my main table

 

[isladogs]

To repeat...
You take the unmatched select query & make an APPEND copy of that query.
Run the APPEND query you have made

Please read this link as it may help you understand the idea:
https://support.office.com/en-gb/article/Add-records-to-a-table-by-using-an-append-query-98a5bd66-2190-4243-9638-8ef649cf3596

 

[luzz]

To repeat...
You take the unmatched select query & make an APPEND copy of that query.
Run the APPEND query you have made

Please read this link as it may help you understand the idea:
https://support.office.com/en-gb/article/Add-records-to-a-table-by-using-an-append-query-98a5bd66-2190-4243-9638-8ef649cf3596

This is the error i get when i run the append query

 

[isladogs]

You have the ID field twice in your query.
Once in the table whose fields you want to copy & again in the destination table
The second one has the criteria 'Is Null'.

Clear the ID from the Append To row
Similar example in screenshot.
The highlighted item needs to be removed.

Hopefully you've got the idea now.
Please read the link!

 

[luzz]

You have the ID field twice in your query.
Once in the table whose fields you want to copy & again in the destination table
The second one has the criteria 'Is Null'.

Clear the ID from the Append To row
Similar example in screenshot.
The highlighted item needs to be removed.

Hopefully you've got the idea now.
Please read the link!

I tried and run, the results is not what i want.

What i want is that by using the unmatched query as the subform on my form, it will be able to show me what are the records that are not added in my main table (Which its working). Next, when i add the record from the unmatched query subform into my main table, the record that i have added should not appear on the unmatched query subform so that i will be able to know what are the records i have not added into my main table.

For example; I have created a student details table and a main student details table and i also used the unmatched query to see which are the details that i have not added into from the student details table into the main student details table.
With the help of the unmatched query, i am able to see the details that i have not added into my main student details table, now i want to add ID 3 students details in my main table after keying in the student parent name. So after i click the save button, ID3 should not appear in the unmatched query subform on my form, now the subform should only display ID 4 ,5 and 6.
I have attached a image for your reference.
I hope my explanation is clear. Thank you!

 

[isladogs]

What you asked in post 1 was very simple & should have been achieved with one or at the most 2 replies.
This is post #17 and I don't feel we're getting anywhere

I've asked you several times to provide the query you are using but you haven't done so.
I've also supplied a link to explain the idea but it appears you either haven't read it or didn't understand it

If you have transferred your unmatched records, then just requery the subform with the unmatched records and these should disappear

Hopefully that's it because I'm logging off and dropping out!