Append query skipping records

[Groundrush]

I have an "Append" qry that gets new records from another database (not Access) and appends it to a table in Access for people to view and use.

The problem that I am having is that sometimes certain fields from these records are sometimes left blank and because they are required in my main table, they are skipped.

The only way I can do fix this is by running the qry and then copying & pasting it into the table manually.

Does anyone have any ideas on how I can append all records, regardless of whether or not there are any blank fields?

see below:

Private Sub cmdGetNewOrders_Click()
DoCmd.SetWarnings False
DoCmd.OpenForm "frmPleaseWait", acNormal, "", "", acReadOnly, acNormal
DoCmd.RepaintObject acForm, "frmPleaseWait"
DoCmd.OpenQuery "qryGetConceptOrders", acNormal, acReadOnly
DoCmd.Close acForm, "frmPleaseWait"
DoCmd.Close acForm, "frmSubConOrders"
DoCmd.OpenForm "frmSubConOrders", acNormal, "", "", , acNormal
DoCmd.SetWarnings True

cmdGetNewOrders_Click_Exit:
Exit Sub
cmdGetNewOrders_Click_Err:
MsgBox Error$
Resume cmdGetNewOrders_Click_Exit
End Sub



Thanks

 

[Groundrush]

Oh and here is the sql I am using, I have tried changing the join types a couple of times..........cheers

INSERT INTO tblConceptOrders ( TA_TASK_ID, BG_SITE, BG_ADDRESS, TA_DATE, TA_SHORT_DESC, TA_LOC, TA_STATUS, TA_LONG_DESC, BDET_KEY_PERS, BDET_PHONE )
SELECT dbo_F_TASKS.TA_TASK_ID, dbo_FLOCATE.BG_SITE, dbo_FLOCATE.BG_ADDRESS, dbo_F_TASKS.TA_DATE, dbo_F_TASKS.TA_SHORT_DESC, dbo_F_TASKS.TA_LOC, dbo_F_TASKS.TA_STATUS, dbo_F_TASKS.TA_LONG_DESC, dbo_F_BD_DETAILS.BDET_KEY_PERS, dbo_F_BD_DETAILS.BDET_PHONE
FROM dbo_FLOCATE RIGHT JOIN ((dbo_F_TASKS LEFT JOIN dbo_F_CONTRACT ON dbo_F_TASKS.TA_FKEY_CTR_SEQ = dbo_F_CONTRACT.CTR_SEQ) LEFT JOIN dbo_F_BD_DETAILS ON dbo_F_TASKS.TA_SEQ = dbo_F_BD_DETAILS.BDET_FKEY_TA_SEQ) ON dbo_FLOCATE.BG_SEQ = dbo_F_TASKS.TA_FKEY_BG_SEQ
GROUP BY dbo_F_TASKS.TA_TASK_ID, dbo_FLOCATE.BG_SITE, dbo_FLOCATE.BG_ADDRESS, dbo_F_TASKS.TA_DATE, dbo_F_TASKS.TA_SHORT_DESC, dbo_F_TASKS.TA_LOC, dbo_F_TASKS.TA_STATUS, dbo_F_TASKS.TA_LONG_DESC, dbo_F_BD_DETAILS.BDET_KEY_PERS, dbo_F_BD_DETAILS.BDET_PHONE
HAVING (((dbo_F_TASKS.TA_TASK_ID) Like "0293*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0294*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0295*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0296*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0297*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0298*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0299*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0300*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0301*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0302*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0303*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0305*"))
ORDER BY dbo_F_TASKS.TA_TASK_ID, dbo_F_TASKS.TA_DATE;

 

[namliam]

use a NZ to fill the empty values...

NZ([YourRequiredEmptyField], "Unknown")
or
NZ([YourRequiredEmptyField], 0)

you can change to 0 or the Unknown to what ever you like....

BR

 

[Groundrush]

Oh yes, didn't think of that one
will give that a go.


cheers

 

[Groundrush]

Where do I put the NZ code?


I tried using it my Append query but it still skipping records
this is how I tried to use it:
Expr1: Nz([TA_LOC],"Unknown")

 

[namliam]

That looks ok but you need to do it for all required fields...

also "Allow zero length strings" may be a problem...

an Index may be the problem (set to unique)

- just to make sure - do the NZ thing with the data your getting not where your storing it....

Regards

 

[Groundrush]

That looks ok but you need to do it for all required fields...

Ok done that

also "Allow zero length strings" may be a problem...

will look into that one

an Index may be the problem (set to unique)

mmmm...that one too

- just to make sure - do the NZ thing with the data your getting not where your storing it....

that was my next point.
I am able to retrieve all records when the qry has completed when manually run. Its when it is appended to the main table using the command button and all the warnings set to false that they are skipped.

When I run it manually you get the message that certain records cant be appended because of duplication or other reasons and you then get the choice to continue or to cancel, when you continue it then appends the all normal records and the ones with missing fields.......great.

How do I get it to do that via a command button??

maybe the Nz Function is not required after all.


Regards

 

[namliam]

It would seem strange for a query to do this, are you sure you are executing the same query?

If you choose continue it should still skipp the records... Is the box you get manually "Skip records" or "Blank values"?

BR

 

[Groundrush]

It would seem strange for a query to do this, are you sure you are executing the same query?

I made a copy of the append qry and turned it into a select qry.
so I have a two qry's
one to automatically append and the other to show the results ---I did this to try and find out where the problem was.

If you choose continue it should still skipp the records... Is the box you get manually "Skip records" or "Blank values"?

I have attached a copy of the messages I get when running both.

cheers

 

[namliam]

The fact that it does add the manual way might be a bug of somekind, readint the error message it should skip them also...

can you compile a small DB with some example data and post it ? I will then have a look at what i can do about adding the data....

Regards

 

[Groundrush]

The fact that it does add the manual way might be a bug of somekind, readint the error message it should skip them also...

can you compile a small DB with some example data and post it ? I will then have a look at what i can do about adding the data....

Regards

Thanks namliam, very kind of you.

the problem is that the source data is on our ODBC server and I won't be able to send a sample.

I will have to think of another way of doing a sample db for you.

cheers