Help Printing reports (1 Viewer)

RickHunter84

RickHunter84

Registered User.
Local time
Today, 13:40
Joined
Dec 28, 2019
Messages
85
Hello all,

I'm reaching out to you all in the hopes someone can give me hand to figure out what's wrong with my code.

Access Expertise: Bit more than beginner.

Context: I'm putting together an inventory management program using MS Access, which includes: Purchase order Form, Receiving PO form, etc.

I'm in the phase of doing the receiving of the "items purchased" into the inventory. Functionality all works, but when I try to print the Goods receiving report, I keep on getting syntax 3075 error. --- UG added error description - Run time error '3075': Syntax error (missing operator) in query expression


The part that is driving me nuts, is that I tweak the line where the "problem" is, and it works, then I try another line, or another purchase order to test it again, and it gives me the same error, so I add what I tweaked before, and it works again...so as you can see, it driving me nuts :D.

I cant figure the solution, since any tweak I do works for the current print out, but when I try to test another PO#, VBA give me the syntax error again.

The reports I'm printing are based out of queries. I'm not using the PO number to pull the report, since a report can have many lines, to avoid this I'm using the "Stockline" which is the unique identifier of the line being receive. In this way when I print, the report doesn't print all the items inside that specific purchase order.

Please let me know if you need additional details.

Thank you in advance.

Rick
 

Attachments

  • Error example.png
    Error example.png
    33.1 KB · Views: 92
Last edited by a moderator:
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 18:40
Joined
Sep 21, 2011
Messages
14,217
You do not need the &"" at the end of the lines, as the value is numeric?
 
RickHunter84

RickHunter84

Registered User.
Local time
Today, 13:40
Joined
Dec 28, 2019
Messages
85
Let me try that, I'll let you know shortly.

Yes, the Stockline is auto-number.

Rick
 
RickHunter84

RickHunter84

Registered User.
Local time
Today, 13:40
Joined
Dec 28, 2019
Messages
85
Hello Gasman,

I removed &"" and still get the syntax error. see attached pics.

Let me know if you see something that could cause this syntax error.

thank you in advance.

Ricardo
 

Attachments

  • Error example 2.png
    Error example 2.png
    5.6 KB · Views: 94
  • Error example 2a.png
    Error example 2a.png
    10.4 KB · Views: 90
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:40
Joined
May 21, 2018
Messages
8,519
The error looks pretty obvious to me. It is saying your sql string looks like
.... [StockLine] =

In other words you are returning a null for stockline

Code: 
dim strWhere as string
strWhere = "stockLine = " & me.stockline
debug.print strWhere
'check this before printing
 
RickHunter84

RickHunter84

Registered User.
Local time
Today, 13:40
Joined
Dec 28, 2019
Messages
85
MajP said:
The error looks pretty obvious to me. It is saying your sql string looks like
.... [StockLine] =

In other words you are returning a null for stockline

Code: 
dim strWhere as string
strWhere = "stockLine = " & me.stockline
debug.print strWhere
'check this before printing
Click to expand...

Hello there,

The pics you just saw were after the changed I was asked to do. when I had hovered over the debugged, the variable was holding the value I needed to print. I've tried many syntaxes as I explained in my original post. It works, but always screams the syntax, no matter if I add, remove, or change the order of the syntax. I'm scratching my head on this since last night.

Thank you for your help!

Ricardo
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:40
Joined
May 21, 2018
Messages
8,519
If you want help we need to see the code you are using, not an error message. I can only guess from your error message. Post the code and not an image.
 
RickHunter84

RickHunter84

Registered User.
Local time
Today, 13:40
Joined
Dec 28, 2019
Messages
85
Sure. You need all the code for this form? do you need something from the report as well?
 
RickHunter84

RickHunter84

Registered User.
Local time
Today, 13:40
Joined
Dec 28, 2019
Messages
85
here is the code for this action button:

Code: 
Private Sub ReceivePoLineBt_Click()
msg = "Warning, Ready to receive Line?"
Style = vbYesNo + vbCritical
Title = "Confirm information!"
response = MsgBox(msg, Style, Title)
    If response = vbYes Then
        'Create variable to obtain number of open records open in the PO.
        Dim recordcounttxt As Integer
        Dim ReportPrint As String
        Dim LabelPrint As String
        recordcounttxt = 0
        recordcounttxt = Form_FrmSubPoGoodsReceipts.recordcounttxt.Value
        If (recordcounttxt = 1) Then
            'if record count equals one, execute the lines below
            If (Me.discrepancybox = True) Then
                'if record equals one, and it has a discrepany execute lines below
                CurrentDb.Execute "UPDATE PurchaseOrderDetailTable SET StocklineStatusID_FK = 3 " & " WHERE [IncomingGoodsID]=" & Me!IncomingGoodsID_FK & ""
                Me.StocklineStatus.Value = 3
                CurrentDb.Execute "UPDATE PurchaseOrderTable SET PurchaseOrderStatusID_FK = 2" & " WHERE [PurchaseOrderID]=" & Me!PurchaseOrderID_FK & ""
                Form_FrmSubGoodReceipts.Stockline_FK = Me.Stockline
                Form_FrmSubGoodReceipts.SupplierID_FK = Form_FrmPoGoodReceipts.SupplierID_FK.Value
                Form_FrmSubGoodReceipts.Buyer = Form_FrmPoGoodReceipts.Fname.Value
                Form_FrmSubGoodReceipts.SupplierPackingList = Me.Packinglisttxt.Value
                Form_FrmSubGoodReceipts.PurchaseOrderID_FK = Me.PurchaseOrderID_FK.Value
                Form_FrmSubGoodReceipts.AmountToPaid = Me.PoCost.Value
                Form_FrmSubGoodReceipts.QtyReceived = Me.PoQty.Value
                Form_FrmSubGoodReceipts.GrStatus = 3
                Me.POReceivedDate = Now()
                MsgBox "Order received complete!"
                Form_FrmSubGoodReceipts.DateReceived = Now()
                
                Form_FrmSubIOHReceiving.Requery
                'ReportPrint = "RpSubFrmGrDet"
                'LabelPrint = "RpPrintLabel"
                DoCmd.OpenReport "RpSubFrmGrDet", , , "[Stockline]=" & Me.Stockline
                DoCmd.OpenReport "RpPrintLabel", , , "[Stockline]=" & Me.Stockline
                
                
                DoCmd.Close
                msg = "Order received complete, Would you like to receive another Purchase order?"
                Style = vbYesNo + vbCritical
                Title = "Receive another PO"
                response = MsgBox(msg, Style, Title)
                If response = vbYes Then
                    DoCmd.Close
                    DoCmd.OpenForm "FrmSearchPoReceive"
                    Exit Sub
                 End If
                 If response = vbNo Then
                    DoCmd.Close
                    DoCmd.OpenForm "Xappmenu"
                    Exit Sub
                End If
                 
            Else
                'if records equals one, no dsicrepancy execute line below
                CurrentDb.Execute "UPDATE PurchaseOrderDetailTable SET StocklineStatusID_FK = 2 " & " WHERE [IncomingGoodsID]=" & Me!IncomingGoodsID_FK & ""
                Me.StocklineStatus.Value = 2
                CurrentDb.Execute "UPDATE PurchaseOrderTable SET PurchaseOrderStatusID_FK = 2" & " WHERE [PurchaseOrderID]=" & Me!PurchaseOrderID_FK & ""
                Form_FrmSubGoodReceipts.Stockline_FK = Me.Stockline
                Form_FrmSubGoodReceipts.SupplierID_FK = Form_FrmPoGoodReceipts.SupplierID_FK.Value
                Form_FrmSubGoodReceipts.Buyer = Form_FrmPoGoodReceipts.Fname.Value
                Form_FrmSubGoodReceipts.SupplierPackingList = Me.Packinglisttxt.Value
                Form_FrmSubGoodReceipts.PurchaseOrderID_FK = Me.PurchaseOrderID_FK.Value
                Form_FrmSubGoodReceipts.AmountToPaid = Me.PoCost.Value
                Form_FrmSubGoodReceipts.QtyReceived = Me.PoQty.Value
                Form_FrmSubGoodReceipts.GrStatus = 1
                Me.POReceivedDate = Now()
                MsgBox "Order received complete!"
                Form_FrmSubGoodReceipts.DateReceived = Now()
                
                Form_FrmSubIOHReceiving.Requery
                'ReportPrint = "RpSubFrmGrDet"
                'LabelPrint = "RpPrintLabel"
                DoCmd.OpenReport "RpSubFrmGrDet", , , "[Stockline]=" & Me.Stockline
                DoCmd.OpenReport "RpPrintLabel", , , "[Stockline]=" & Me.Stockline
                
                DoCmd.Close
                msg = "Order receive complete, Would you like to receive another Purchase order?"
                Style = vbYesNo + vbCritical
                Title = "Receive another PO"
                response = MsgBox(msg, Style, Title)
                If response = vbYes Then
                    DoCmd.Close
                    DoCmd.OpenForm "FrmSearchPoReceive"
                    Exit Sub
                 End If
                 If response = vbNo Then
                    DoCmd.Close
                    DoCmd.OpenForm "Xappmenu"
                    Exit Sub
                End If
            End If
        Else
        'If purchase order has more than one line, execute below instructions and no discrepancies and check discrepancy
                If (Me.discrepancybox = True) Then
                    'if discrepancy, execute lines below
                    CurrentDb.Execute "UPDATE PurchaseOrderDetailTable SET StocklineStatusID_FK = 3 " & " WHERE [IncomingGoodsID]=" & Me!IncomingGoodsID_FK & ""
                    Me.StocklineStatus.Value = 3
                    Form_FrmSubGoodReceipts.Stockline_FK = Me.Stockline
                    Form_FrmSubGoodReceipts.SupplierID_FK = Form_FrmPoGoodReceipts.SupplierID_FK.Value
                    Form_FrmSubGoodReceipts.Buyer = Form_FrmPoGoodReceipts.Fname.Value
                    Form_FrmSubGoodReceipts.SupplierPackingList = Me.Packinglisttxt.Value
                    Form_FrmSubGoodReceipts.PurchaseOrderID_FK = Me.PurchaseOrderID_FK.Value
                    Form_FrmSubGoodReceipts.AmountToPaid = Me.PoCost.Value
                    Form_FrmSubGoodReceipts.QtyReceived = Me.PoQty.Value
                    Form_FrmSubGoodReceipts.GrStatus = 3
                    Form_FrmSubGoodReceipts.DateReceived = Now()
                    
                    Form_FrmSubPoGoodsReceipts.Requery
                    Form_FrmSubIOHReceiving.Requery
                    DoCmd.OpenReport "RpSubFrmGrDet", , , "[Stockline]=" & Me.Stockline
                    DoCmd.OpenReport "RpPrintLabel", , , "[Stockline]=" & Me.Stockline
                    
                    
                    Me.ContinueGrBt.Enabled = True
                    Form_FrmSubPoGoodsReceipts.IncomingGoodsID.Enabled = True
                    Me.ContinueGrBt.Enabled = True
                    Form_FrmPoGoodReceipts.SetFocus
                    Form_FrmPoGoodReceipts.FrmSubPoGoodsReceipts.SetFocus
                    Form_FrmSubPoGoodsReceipts.IncomingGoodsID.SetFocus
                    Form_FrmSubIOHReceiving.Refresh
                    Call invisible
                    Form_FrmPoGoodReceipts.FrmSubIOHReceiving.Visible = False
                    Exit Sub
                 Else
                    'if no discrepancy execute lines below
                    CurrentDb.Execute "UPDATE PurchaseOrderDetailTable SET StocklineStatusID_FK = 2 " & " WHERE [IncomingGoodsID]=" & Me!IncomingGoodsID_FK & ""
                    Me.StocklineStatus.Value = 2
                    Form_FrmSubGoodReceipts.Stockline_FK = Me.Stockline
                    Form_FrmSubGoodReceipts.SupplierID_FK = Form_FrmPoGoodReceipts.SupplierID_FK.Value
                    Form_FrmSubGoodReceipts.Buyer = Form_FrmPoGoodReceipts.Fname.Value
                    Form_FrmSubGoodReceipts.SupplierPackingList = Me.Packinglisttxt.Value
                    Form_FrmSubGoodReceipts.PurchaseOrderID_FK = Me.PurchaseOrderID_FK.Value
                    Form_FrmSubGoodReceipts.AmountToPaid = Me.PoCost.Value
                    Form_FrmSubGoodReceipts.QtyReceived = Me.PoQty.Value
                    Form_FrmSubGoodReceipts.GrStatus = 1
                    Form_FrmSubGoodReceipts.DateReceived = Now()
                    
                    Form_FrmSubPoGoodsReceipts.Requery
                    Form_FrmSubIOHReceiving.Requery
                    DoCmd.OpenReport "RpSubFrmGrDet", , , "[Stockline]=" & Me.Stockline
                    DoCmd.OpenReport "RpPrintLabel", , , "[Stockline]=" & Me.Stockline
                    
                    
                    Form_FrmSubPoGoodsReceipts.IncomingGoodsID.Enabled = True
                    Me.ContinueGrBt.Enabled = True
                    Form_FrmPoGoodReceipts.SetFocus
                    Form_FrmPoGoodReceipts.FrmSubPoGoodsReceipts.SetFocus
                    Form_FrmSubPoGoodsReceipts.IncomingGoodsID.SetFocus
                    Form_FrmSubIOHReceiving.Refresh
                    Call invisible
                    Form_FrmPoGoodReceipts.FrmSubIOHReceiving.Visible = False
                    Exit Sub
                End If
         End If
         Exit Sub
    End If
    If response = vbNo Then
        Me.Undo
        Exit Sub
    End If
End Sub
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 18:40
Joined
Sep 21, 2011
Messages
14,217
I cannot see the syntax error unless as MajP states, there is no value for Me.Stockline.

Syntax looks OK to me. :confused:
Whic particular line is it comlaining about as you have that statement in a few places.?
 
RickHunter84

RickHunter84

Registered User.
Local time
Today, 13:40
Joined
Dec 28, 2019
Messages
85
VBA is complaining for every case I tried. If the Purchase order has one line, it tells me syntax is wrong, I do the change and hit the play button, then it prints. I tried another purchase order with more lines, same syntax error, I changed it to how it was, it works and it prints....driving me nuts! :D

Rick
 
M

Micron

AWF VIP
Local time
Today, 13:40
Joined
Oct 20, 2018
Messages
3,478
PMFJI but maybe you'd get a solution much faster if you could post a copy of your db. I think that would eliminate a lot of the back and forth questions and answers.
 
M

Micron

AWF VIP
Local time
Today, 13:40
Joined
Oct 20, 2018
Messages
3,478
Options
- compact/repair/zip then see what size it is (I think limit here is 5mb)
- remove unnecessary tables/queries/forms/reports and test if whatever is needed will still load and can be examined. Then repeat above, assuming that will get the size down.
- post to a drop box (some will not download from such sites, so use that as a last resort)
Doing the c/r will often make a big difference, and zipping is important as well.
 
RickHunter84

RickHunter84

Registered User.
Local time
Today, 13:40
Joined
Dec 28, 2019
Messages
85
Hello,

see attached, Let me know if it works.

The form is FrmPoGoodReceipts

Thanks in advance.

Ricardo
 

Attachments

  • InvMgmtDB.zip
    445 KB · Views: 86
M

Micron

AWF VIP
Local time
Today, 13:40
Joined
Oct 20, 2018
Messages
3,478
I have to go out very soon but I expect someone else will take advantage of your info in the mean time. If not, I'll look when I get back. If it matters to anyone, I wasn't trying to take over this; just trying to facilitate.

Hopefully it is obvious as to what anyone has to do to replicate your problem as I don't think you've posted any instruction.
 
RickHunter84

RickHunter84

Registered User.
Local time
Today, 13:40
Joined
Dec 28, 2019
Messages
85
Hello there,

You are right. here is a description on how I usually start the process by going through the following steps:

Click on : Search "FrmSearchPoReceive"

Here is a list of open PO's
11 (Lines inside #2)
12 (Lines inside #1)
13 (Lines inside #3)
14 (Lines inside #1)
16 (Lines inside #3)
17 (Lines inside #2)
18 (Lines inside #1)
21 (Lines inside #1)
23 (Lines inside #4)
24 (Lines inside #1)
26 (Lines inside #1)
27 (Lines inside #1)
29 (Lines inside #3)
30 (Lines inside #1)

By entering any of the PO's above, the FrmPoGoodReceipts will open. If you click in the Start receiving button, another subform will open showing the lines open inside the PO. By clicking in the Line ID, another subform will open, this is where you enter the data of the item being received (This subform needs additional validation work, the plan is to come back later to fine tune it).
Inside this subform "FrmSubIOHReceving", there is another subform called "FrmSubGoodReceipts", it completes itself as you enter information on "FrmSubIOHReceving". After all info is entered, you click on Receive Line! and that's where the code giving me the problem is.

Please let me know if you need additional info on how to operate the forms.

thank you in advance!!!

Rick
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 18:40
Joined
Sep 21, 2011
Messages
14,217
Sorry, I cannot make head or tail of this.?

When I click on the line ID I get an error saying I cannot disbale a control that has the focus.
I've got a subform which has Inventort Receiving on it, I filled in packing list No and the other enabled controls on that form, but have no clue where to go next?

If I press the start button I get an error as in the attached pic.?
 

Attachments

  • Capture.PNG
    Capture.PNG
    8.4 KB · Views: 85
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 18:40
Joined
Sep 21, 2011
Messages
14,217
Right, manged to bludgeon my way through it and commented out the lines causing the error (2 lines) and I get the error. The error is generated as Stockline is Null ?

I tried to set it in the immediate window and I get an error 'I cannot set a value for this object'

Set a breakpoint at 'If response = vbYes ' and step through the code with F8

HTH
 
RickHunter84

RickHunter84

Registered User.
Local time
Today, 13:40
Joined
Dec 28, 2019
Messages
85
Stockline value is null depending on how you syntax the docmd.openreport.
as I said earlier, I used different ways, and some of them work but other gave me the Null value you received. Let me know if I can provide you with further details.

thank you so much for your help.

Rick
 
You must log in or register to reply here.

Similar threads

H
Solved Problems in Printing Report
Replies
2
Views
421
HillTJ
H
R
Order of table rows switch when added to a sub report
Replies
7
Views
473
LarryE
LarryE
T
Debug error???
Replies
10
Views
171
The_Doc_Man
The_Doc_Man
D
Dynamic Report based on Current Form Filter
Replies
9
Views
379
arnelgp
arnelgp
S
datetime field conversion on access report
Replies
18
Views
466
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom