Append Qry and Dcount
Experts,
I am having a real tough time trying to figure out why this APPEND qry is not firing in the attached.
I dont receive a msg box stating that it is APPENDING.
but the UPDATE qry does run and I get msg box stating it will UPDATE records but no APPEND.
I have worked all night on this and I give up.
I really need this to work correctly.
I have added TEXT to each qry to clearly see which one is firing after clicking the button and looking at tblMilestones.MS (there is text stating which qry ran).
YOu can see that after running, only the UPDATE qry runs as the text shows UPDATE in the field [MS] . If the APPEND qry ran then it would say APPEND in tblMilestones.MS.
BAsically, the logic is
If match on [tbliMPORTMSTEST].[Invoice
then UPDATE
or if there match on tbliMPORTMSTEST.[Milestone
then UPDATE
Else
APPEND
Please take a look at the attached db and look at the Click Event code under the button on the autoexec form.
I think it might be an easy solution.
Please ask if you have questions.
Database1MS-EE.accdb
I am having a real tough time trying to figure out why this APPEND qry is not firing in the attached.
I dont receive a msg box stating that it is APPENDING.
but the UPDATE qry does run and I get msg box stating it will UPDATE records but no APPEND.
I have worked all night on this and I give up.
I really need this to work correctly.
I have added TEXT to each qry to clearly see which one is firing after clicking the button and looking at tblMilestones.MS (there is text stating which qry ran).
YOu can see that after running, only the UPDATE qry runs as the text shows UPDATE in the field [MS] . If the APPEND qry ran then it would say APPEND in tblMilestones.MS.
BAsically, the logic is
If match on [tbliMPORTMSTEST].[Invoice
then UPDATE
or if there match on tbliMPORTMSTEST.[Milestone
then UPDATE
Else
APPEND
Please take a look at the attached db and look at the Click Event code under the button on the autoexec form.
I think it might be an easy solution.
Please ask if you have questions.
Database1MS-EE.accdb
I'm not sure what you're expecting us to look for.
The Update query runs because the Dcounts are not zero.
The Update query runs because the Dcounts are not zero.
ASKER
Peter, i realize that but there are some records that =0 so the APPEND qry should run om each record where the InvoiceNo matches.
You can see there are records between the two tables that have the same invoiceno.
Do you see that? Thanks for the response.
You can see there are records between the two tables that have the same invoiceno.
Do you see that? Thanks for the response.
I see.
I tried using the second record (ID54) and the append query ran OK. It appended 37 records.
I tried using the second record (ID54) and the append query ran OK. It appended 37 records.
ASKER
Peter, did you run it from the form frmMilestones? This is where I have to run it from. I ran it several times from this form and it does not work at least for me. Please let me know...
ASKER
Peter, if it successfully APPENDS from frmMIlestones, there should be text in the field [MS] that states "APPEND QRY" somethere in the filed tblMilestones.[Milestone].
here is the formula for it: MilestoneConcac: [iMPORTMSTEST].[MS] & "// APPEND QRY - " & [iMPORTMSTEST].[Title] & " // " & [iMPORTMSTEST].[Event]
here is the formula for it: MilestoneConcac: [iMPORTMSTEST].[MS] & "// APPEND QRY - " & [iMPORTMSTEST].[Title] & " // " & [iMPORTMSTEST].[Event]
ASKER
<here should be text in the field [MS]
should have been tblMilestones.[Milestone]
should have been tblMilestones.[Milestone]
I'm attaching an xl file from tblMilestones after clicking the button once while on ID 54.
tblMilestones.xls
tblMilestones.xls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Peter:
Maybe I see the issue here.
in frmMilestones, the record you are on affects how the qry runs.
It defaults to record ID 53 (and you get different output...APPEND does not run as it does while on ID 54)
YOu said you are on record 54 on frmMilestones but it should not matter what record you are on
It should cycle through the entire list of records from first to last (basically for the entire # records for that ProjectID (or ProjID on iMPORTMSTEST), which is 71 in this case).
Maybe this line should be adjusted?
If Nz(DCount("*", "iMPORTMSTEST", "[Invoice No] =" & Nz(Me.txtInvoiceNo.Value, 0)), 0) = 0 Or Nz(DCount("*", "iMPORTMSTEST", "[Milestone Amount in USD] = " & Nz(Me.txtMSAmount.Value, 0)), 0) = 0 Then
I see it has "ME" and I think this means whatever record you are presently on so if you dont manually move to the next record then the code does not look at the ENTIRE recordset for that ProjectID.
It should cycle through each record/ID and look at the [InvoiceNo] or [MSAmount] and either run the APPEND or UPDATE.
Am I making sense? (I dont know that much about code so I cant explain it perfectly....please excuse me.)
Maybe I see the issue here.
in frmMilestones, the record you are on affects how the qry runs.
It defaults to record ID 53 (and you get different output...APPEND does not run as it does while on ID 54)
YOu said you are on record 54 on frmMilestones but it should not matter what record you are on
It should cycle through the entire list of records from first to last (basically for the entire # records for that ProjectID (or ProjID on iMPORTMSTEST), which is 71 in this case).
Maybe this line should be adjusted?
If Nz(DCount("*", "iMPORTMSTEST", "[Invoice No] =" & Nz(Me.txtInvoiceNo.Value, 0)), 0) = 0 Or Nz(DCount("*", "iMPORTMSTEST", "[Milestone Amount in USD] = " & Nz(Me.txtMSAmount.Value, 0)), 0) = 0 Then
I see it has "ME" and I think this means whatever record you are presently on so if you dont manually move to the next record then the code does not look at the ENTIRE recordset for that ProjectID.
It should cycle through each record/ID and look at the [InvoiceNo] or [MSAmount] and either run the APPEND or UPDATE.
Am I making sense? (I dont know that much about code so I cant explain it perfectly....please excuse me.)
ASKER
you can see that in this attached db (which is the original one) thre are 31 records on frmMilestones. BAsically, the code should look 31 times...each record and either APPEND or UPDATE.
Database1MS-EE.accdb
Database1MS-EE.accdb
ASKER
harfang: I did not see your response. I think my response after might address that though.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Peter:
<That is the first time you have said that. And it is quite a different but crucial issue.
I had thought that the Dcount does that but apparently it does not.
Sorry for not making that clear.
At least we now know what is the issue.
Can you help me?
<That is the first time you have said that. And it is quite a different but crucial issue.
I had thought that the Dcount does that but apparently it does not.
Sorry for not making that clear.
At least we now know what is the issue.
Can you help me?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I see. maybe I can remove the button from the form and run it elsewhere. That way I am not on the recordset tblMilestones. I might prompt user for the [ProjectID], which will always be 71.
what do you think about the prompt method?
The code will still run from first to last record?
what do you think about the prompt method?
The code will still run from first to last record?
ASKER
OK I have a solution.
I made a delete qry
then append the contents of the table.
I am first deleting the data from tblMIlestones where ProjectID = 71
Then appending the data from tblImportMS.
Knowing you guys are experts, I would like to know how you would have solved this issue.
I am not going to implement it so dont worry about that.
I think I am good now.
thank you
attached: updated db Database1MS-EE.accdb
I made a delete qry
then append the contents of the table.
I am first deleting the data from tblMIlestones where ProjectID = 71
Then appending the data from tblImportMS.
Knowing you guys are experts, I would like to know how you would have solved this issue.
I am not going to implement it so dont worry about that.
I think I am good now.
thank you
attached: updated db Database1MS-EE.accdb
ASKER