Insert Query within VBA Form not working (1 Viewer)

[Mackbear]

Hi everyone! Good day! reaching out for help as I can't seem to make the insert query work. ='c I have checked everything, this query is within a VBA code, I checked this on the query builder and it works it just does not work within the vba. It is supposed to be saving data in a certain table tbl_pbcntctmissebill. I have attached a copy of the DB Thanks in advance!

 

[theDBguy]

Hi. Were you getting any errors? If so, was it 3061? If so, you could try this generic function. I'll see if I can download your file later, if this doesn't help.

 

[isladogs]

Can you please explain more clearly what the issue is.
I think I've followed the instructions but have no idea what works as a query or which code doesn't.
I did generate an email though no idea why/how....

Hi,
Good day!
Root Cause: No trigger; missing historical invoice

PMC: ABC Company
Site: EFG Site
Vendor: CDE Vendor
Details: D
Inv Date | Account No. | Amount
25/04/2019 | '55024764502 | $35.8

I haven't looked at the code as its not clear what to look at.

 

[Mackbear]

Hi. Were you getting any errors? If so, was it 3061? If so, you could try this generic function. I'll see if I can download your file later, if this doesn't help.

Hello, I am not getting any errors

 

[Mackbear]

Can you please explain more clearly what the issue is.
I think I've followed the instructions but have no idea what works as a query or which code doesn't.
I did generate an email though no idea why/how....



I haven't looked at the code as its not clear what to look at.

Hello, yes this should generate an email, and at the same time I would like it to save certain information on a table with this query:

Dim sqlupdpbtreas As String
sqlupdpbtreas = "INSERT INTO tbl_pbcntctmissebill ( entryid_cntctmissebill, BillAmount, SubmittedbyAP ) SELECT qry_pbworkflow.entryid_pbreso, qry_pbworkflow.CurrentCharges, qry_pbworkflow.AP FROM qry_pbworkflow WHERE qry_pbworkflow.[entryid_pbreso] IN (" & Replace(SelectedList, "|", ",") & ")"
Debug.Print sqlupdpbtreas
CurrentDb.Execute sqlupdpbtreas

It does not give me errors so I don't know what to look out for ='c

 

[HiTechCoach]

If you have not resolved your issue, it could be related to the 11/12/2019 Office update.


I had several users where Insert and Update queries stopped working

See the link below to see if your version of Acces is on the list of versions imp0acted and how to install the fix.


See Access error: "Query is corrupt"

NOTE: If you use error trapping, the error message may not be displayed.

This issue impacts all supported builds of Access.

The issue was introduced on November 12, 2019 via the following patch updates for MSI builds:

Office 2010: Description of the security update for Office 2010: November 12, 2019 (KB4484127)
Office 2013: Description of the security update for Office 2013: November 12, 2019 (KB4484119)
Office 2016: Description of the security update for Office 2016: November 12, 2019 (KB4484113)
Office 2016: November 12, 2019, update for Office 2016 (KB3085368)

 

[Mackbear]

If you have not resolved your issue, it could be related to the 11/12/2019 Office update.


I had several users where Insert and Update queries stopped working

See the link below to see if your version of Acces is on the list of versions imp0acted and how to install the fix.


See Access error: "Query is corrupt"

NOTE: If you use error trapping, the error message may not be displayed.

Hi, for this issue, all queries using table names to update will not work, I can say this is not the problem because my other insert and update queries are working, only this one isnt ='c I have tried to recreate the table and build the query on the query builder tried to run the append query and it works but when I put it within the vba it doesn't, i dont know what to do anymore =' c please look into the db and see if there is anything i may be missing??

 

[Mackbear]

I hope someone could see what I cannot see, :banghead::banghead:

 

[vba_php]

Mack,

See the attached images for the debugging process I used. Also I made extensive use of "find and replace" in the vba editor to quickly nail down the issue using your description. You need to use stuff like this so you don't pull your hair out. also, use the immediate window in the vba editor to print out variable values at different points in a routine's execution. set a breakpoint, press "control+G" and type "?" followed by the variable name to see the value at the time your code is halted by the breakpoint. It's just like HR people scanning a million resumes for keywords. the only difference is, HR people are missing the great people because the software is horrid whereas doing the "scan" thing here works quickly to pinpoint the problem. when I worked through your stuff, I did not even get as far as the offending line you're talking about. I got an "invalid use of null" error on a statement that came way before your line. I'm pretty sure I did everything correctly as far as the order or operations a user of yours would go thru, because every field on the main form and popup form were filled out with data. does this give you any more insight?


<edit>
I forgot to select the record in the subform as part of the process. I'm re-researching now. I'll post again if I find the issue. sorry.

 

[vba_php]

Mack,

You already have a debug.print statement set after the offending line you're lookin at. When I ran the test, this is what the sql statement is set at when the query is attempting to run:

INSERT INTO tbl_pbcntctmissebill ( entryid_cntctmissebill, BillAmount, SubmittedbyAP ) SELECT qry_pbworkflow.entryid_pbreso, qry_pbworkflow.CurrentCharges, qry_pbworkflow.AP FROM qry_pbworkflow WHERE qry_pbworkflow.[entryid_pbreso] IN (5,)

obviously that won't work. ''IN (5,)"??? no. try to do a little research on how you're using that field at the bottom of your popup form where the number "5" appears (or whatever number you see when your users use the db) when the form is loaded. does this help? Post back if this helps out and you will get another boost to get to the end....

 

[Mackbear]

Mack,

You already have a debug.print statement set after the offending line you're lookin at. When I ran the test, this is what the sql statement is set at when the query is attempting to run:

INSERT INTO tbl_pbcntctmissebill ( entryid_cntctmissebill, BillAmount, SubmittedbyAP ) SELECT qry_pbworkflow.entryid_pbreso, qry_pbworkflow.CurrentCharges, qry_pbworkflow.AP FROM qry_pbworkflow WHERE qry_pbworkflow.[entryid_pbreso] IN (5,)

obviously that won't work. ''IN (5,)"??? no. try to do a little research on how you're using that field at the bottom of your popup form where the number "5" appears (or whatever number you see when your users use the db) when the form is loaded. does this help? Post back if this helps out and you will get another boost to get to the end....

Hi, thank you very much for looking into it. Yes, that number is the entry id for the record/s selected. That should be the criteria that the query should be looking for. It should select the data with entry ID that's in the textbox on the popup. This works on my other forms. Not sure why on this one it doesn't. It is exactly the same syntax as the others just different data selected. ='c

 

[vba_php]

okay Mack, you've got a little more work to do my friend. Here's what's up:

1. your sql string sqlupdpbtreas produces this:

   INSERT INTO tbl_pbcntctmissebill ( entryid_cntctmissebill,  BillAmount, SubmittedbyAP ) SELECT qry_pbworkflow.entryid_pbreso,  qry_pbworkflow.CurrentCharges, qry_pbworkflow.AP FROM qry_pbworkflow  WHERE qry_pbworkflow.[entryid_pbreso] IN (6,)

   however, your query qry_pbworkflow, when run by itself returns nothing. Hence, nothing is being put in table tbl_pbcntctmissebill
2. the WHERE statement in query qyr_pbworkflow:

   WHERE (((tbl_PBreso.ResolutionStatus) Not In ("Resolved","Closed","Open","Auto Closed")));

   is using a subquery that does not have a SELECT statement in it. I'm not sure that's even right. You have a value list in your subquery, not a SELECT statement. maybe you can do that, but I've always used a SELECT statement.
3. also, the WHERE statement is looking for values that are NOT "Resolved","Closed","Open" or "Auto Closed". there are *no* records in the table that comply with that request, thus a return of 0.

that's all the research I did. give it a go at fixing that stuff first, then come back and hopefully you will have made more progress. I should've pointed out enough here to get you going again.

 

[Gasman]

Hi, thank you very much for looking into it. Yes, that number is the entry id for the record/s selected. That should be the criteria that the query should be looking for. It should select the data with entry ID that's in the textbox on the popup. This works on my other forms. Not sure why on this one it doesn't. It is exactly the same syntax as the others just different data selected. ='c

I believe you would need to get rid of the trailing comma in the IN() syntax?

 

[Micron]

Isn't the data in the IN clause text data type? If you're using replace function to replace character separated delimiters (redirect pipe characters), then the string must be text, no? If so, the IN part doesn't put any quotes around the value(s) as far as I can see.

Hope I'm not way off in left field. I haven't downloaded the db sample because it seems so many are way ahead with it and I wouldn't want to do that if the problem turned out to be that simple.

 

[Mackbear]

okay Mack, you've got a little more work to do my friend. Here's what's up:

1. your sql string sqlupdpbtreas produces this:

   INSERT INTO tbl_pbcntctmissebill ( entryid_cntctmissebill,  BillAmount, SubmittedbyAP ) SELECT qry_pbworkflow.entryid_pbreso,  qry_pbworkflow.CurrentCharges, qry_pbworkflow.AP FROM qry_pbworkflow  WHERE qry_pbworkflow.[entryid_pbreso] IN (6,)

   however, your query qry_pbworkflow, when run by itself returns nothing. Hence, nothing is being put in table tbl_pbcntctmissebill
2. the WHERE statement in query qyr_pbworkflow:

   WHERE (((tbl_PBreso.ResolutionStatus) Not In ("Resolved","Closed","Open","Auto Closed")));

   is using a subquery that does not have a SELECT statement in it. I'm not sure that's even right. You have a value list in your subquery, not a SELECT statement. maybe you can do that, but I've always used a SELECT statement.
3. also, the WHERE statement is looking for values that are NOT "Resolved","Closed","Open" or "Auto Closed". there are *no* records in the table that comply with that request, thus a return of 0.

that's all the research I did. give it a go at fixing that stuff first, then come back and hopefully you will have made more progress. I should've pointed out enough here to get you going again.

Thank you soo much for looking into this! I figured it out, thank you for pointing out that it is not returning anything and how the query was filtered. What I found out is that I have a first query that tags the record "Resolved", then my next query is filtered to records tagged as "Open" only. That is why it is not getting anything! Thank you everyone, this forum has really helped me a lot, and helped me help everyone here at work to simplify their tasks.

 

[Mackbear]

Isn't the data in the IN clause text data type? If you're using replace function to replace character separated delimiters (redirect pipe characters), then the string must be text, no? If so, the IN part doesn't put any quotes around the value(s) as far as I can see.

Hope I'm not way off in left field. I haven't downloaded the db sample because it seems so many are way ahead with it and I wouldn't want to do that if the problem turned out to be that simple.

It should be number type... I had it figured out, on the other post... thank you everyone for taking the time to look into it

 

[vba_php]

Thank you soo much for looking into this! I figured it out, thank you for pointing out that it is not returning anything and how the query was filtered. What I found out is that I have a first query that tags the record "Resolved", then my next query is filtered to records tagged as "Open" only. That is why it is not getting anything! Thank you everyone, this forum has really helped me a lot, and helped me help everyone here at work to simplify their tasks.

.......................