Help Printing reports (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 00:36
Joined
Sep 21, 2011
Messages
14,048
Well I am puzzled as to why I could not set it from the immediate window?, that is my normal get out clause if I get in that position.?

I could amend Stocklinestatus no problem from the immediate window.

Regardless the syntax is correct *IF* you have a valid value for Stockline ?
You could use the NZ() function to set it at least to something to prove this, but you need to find the reason why it is not set. Obviously should be set for wherever it is used.

Onky you are au fait with how it is meant to work?
I'd look at those errors I got as well.

Code:
Private Sub IncomingGoodsID_Click()

Form_FrmSubIOHReceiving.Visible = True
Form_FrmPoGoodReceipts.SetFocus
Form_FrmPoGoodReceipts.FrmSubIOHReceiving.SetFocus
DoCmd.GoToRecord , , acNewRec
Form_FrmSubIOHReceiving.Packinglisttxt = ""
Form_FrmSubIOHReceiving.Packinglisttxt.SetFocus
Form_FrmSubIOHReceiving.ReceivePoLineBt.Visible = False
Form_FrmSubIOHReceiving.IncomingGoodsID_FK = Me.IncomingGoodsID.Value
Form_FrmSubIOHReceiving.PurchaseOrderID_FK = Me.PurchaseOrderID_FK.Value
Form_FrmSubIOHReceiving.PoQty = Me.PoQty.Value
Form_FrmSubIOHReceiving.PoCost = Me.PoCost.Value
Form_FrmSubIOHReceiving.StocklineStatus = 1
Form_FrmSubIOHReceiving.MaterialTypeID_FK = 1
[COLOR="MediumTurquoise"]'Me.IncomingGoodsID.Enabled = False[/COLOR]
End Sub

There was another similar looking block of code, but cannot find it now. :(

I'd be putting Option Explicit at the top of all your modules to ensure variables are defined and exist

Tools/Option/Require variable checking in the IDE

You will need to add that line to existing modules.

HTH
 

RickHunter84

Registered User.
Local time
Yesterday, 20:36
Joined
Dec 28, 2019
Messages
85
Well I am puzzled as to why I could not set it from the immediate window?, that is my normal get out clause if I get in that position.?

I could amend Stocklinestatus no problem from the immediate window.

Regardless the syntax is correct *IF* you have a valid value for Stockline ?
You could use the NZ() function to set it at least to something to prove this, but you need to find the reason why it is not set. Obviously should be set for wherever it is used.

Onky you are au fait with how it is meant to work?
I'd look at those errors I got as well.

Code:
Private Sub IncomingGoodsID_Click()

Form_FrmSubIOHReceiving.Visible = True
Form_FrmPoGoodReceipts.SetFocus
Form_FrmPoGoodReceipts.FrmSubIOHReceiving.SetFocus
DoCmd.GoToRecord , , acNewRec
Form_FrmSubIOHReceiving.Packinglisttxt = ""
Form_FrmSubIOHReceiving.Packinglisttxt.SetFocus
Form_FrmSubIOHReceiving.ReceivePoLineBt.Visible = False
Form_FrmSubIOHReceiving.IncomingGoodsID_FK = Me.IncomingGoodsID.Value
Form_FrmSubIOHReceiving.PurchaseOrderID_FK = Me.PurchaseOrderID_FK.Value
Form_FrmSubIOHReceiving.PoQty = Me.PoQty.Value
Form_FrmSubIOHReceiving.PoCost = Me.PoCost.Value
Form_FrmSubIOHReceiving.StocklineStatus = 1
Form_FrmSubIOHReceiving.MaterialTypeID_FK = 1
[COLOR="MediumTurquoise"]'Me.IncomingGoodsID.Enabled = False[/COLOR]
End Sub

There was another similar looking block of code, but cannot find it now. :(

I'd be putting Option Explicit at the top of all your modules to ensure variables are defined and exist

Tools/Option/Require variable checking in the IDE

You will need to add that line to existing modules.

HTH

regarding your question *IF*, that's the reason I created this post. the good receipts report is suppose to print automatically every time you receive a line, along with a label for that item. I get to print the report every time, but every time I get the syntax error, so I presume that VBA does recognize there is a value in there. the only time it doesn't find a value I when I completely remove the &"" from the code.

everything else works, it always boils down to the report no printing automatically after clicking "Receive Line".

regards,

Rick
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:36
Joined
Sep 21, 2011
Messages
14,048
No, the post was about a syntax error.
MajP sugegsted that there was no value for Stockline as everything else looked OK?

You cannot use that line of code unless there is a numeric value for Stockline.

You need to find out the circumstances where it is not set, as clearly it should be set anytime you use that variable.?

The code 'looks' OK, but when I have this problem, I just go to the debug window and execute either line my line, or suitably positioned breakpoints.
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:36
Joined
Sep 21, 2011
Messages
14,048
Right

When this code starts Stockline is 148. By the time it gets to the errant line it is Null.

You take it from here.

Code:
                 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 ' i[COLOR="Red"]s 148 here[/COLOR]
                    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 [COLOR="red"]' is Null now[/COLOR]

HTH
 

RickHunter84

Registered User.
Local time
Yesterday, 20:36
Joined
Dec 28, 2019
Messages
85
thank you for the insight! I will let you know if I can make it work.

best regards,

Rick
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:36
Joined
Sep 21, 2011
Messages
14,048
I would suspect the Requery is causing the issue?

Best of luck with it.
 

RickHunter84

Registered User.
Local time
Yesterday, 20:36
Joined
Dec 28, 2019
Messages
85
I would suspect the Requery is causing the issue?

Best of luck with it.

Hello there,

I checked on your recommendation and something strange is happening, even though I assigned the value of my Stockline to a variable... VBA after a few lines picks up the autonumber from the GoodReceiptstable ....what???:banghead::banghead:

I tried assigning the value of the stockline to a variable as well and print the variable but the value changes along the way somehow...

wow LOL, not sure what to think at this point.

Rick
 

Micron

AWF VIP
Local time
Yesterday, 20:36
Joined
Oct 20, 2018
Messages
3,476
If I step through the code, with or without having chosen discrepancy, the reports open. If I don't set discrepancy to True I get the error about related record as was mentioned earlier. At the very least, create the parent record first if you must use code. After the requery, the stockline value is still there, albeit I didn't notice if it was the same.

*After a cursory glance, I'd say you have made this more difficult than it needs to be and that will frequently come back to bite you IMHO. Why use code to update records when you seem to be using bound forms everywhere?

I see that while composing this you have discovered there are times when values are not as you expect. See * above!

Time to take a step back and decide if you're going to soldier on with this design or start anew. I know, it's not easy to do that, but if you figure that at least you learned something along the way... I'd kindly suggest some basics need to be covered before diving in to db creation right away. Did you perchance review this aspect of NorthWind (or any other sample db)? Did you research and/or review templates for stock/inventory/receipts before implementing design? Those would probably help.
 

RickHunter84

Registered User.
Local time
Yesterday, 20:36
Joined
Dec 28, 2019
Messages
85
Hello Micron,

I have been doing research here and there, I've come a long way :). nevertheless, of course there is still a lot to learn ahead.

Ever since the last post I did here, I managed to have the form almost working.

Regarding your comment about updating the database, I picked that UPDATE statement because I needed specific records to be updated, not sure how to accomplish that in a different way. Please share with me any material I should check to continue building the DB.

thank you for your comments.

regards,

Rick
 

Micron

AWF VIP
Local time
Yesterday, 20:36
Joined
Oct 20, 2018
Messages
3,476
Please share with me any material I should check to continue building the DB.
If I knew more about your knowledge level I might be able to do that but that's a broad topic. Assuming that it's beginner level, then get your hands on a book, or you can still find Access 2003(?) bible for download. Outdated for sure, but design principles never go out of style. I have a set of links that I post for novices which I will do anyway, but they don't really apply to your current situation. Aside from those (which you should read before any old material) there are TONS of YouTube type tutorials (Steve Bishop is one that I often see recommended). Don't skimp over the basics if you want a good foundation supporting your db, and don't overlook sample databases, and believe it or not, pencil and paper is where many accomplished designers start. This forum can also be a good source of vetting a design you come up with but I think it's not really for teaching on a grand scale.

Rather than me ramble on I'll leave you with the following, but make sure you find other sources as well - especially Normalization if you don't fully grasp it.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.html
and/or
http://holowczak.com/database-normalization/
Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diagramming-part-i.html
How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com/2009/05/how-do-i-create-application-in.html
Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk/forums/showthread.php?t=225837
What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm
The evils of lookup fields
- http://access.mvps.org/access/lookupfields.htm
Table and PK design tips
- http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields
- http://allenbrowne.com/casu-14.html
About Multi Value Fields
-http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:36
Joined
Sep 21, 2011
Messages
14,048
Also if you do a requery of a form, unless you save the ID of the record your are on and relocate to it after the requery, you will get the first record in the recordset.
 

Micron

AWF VIP
Local time
Yesterday, 20:36
Joined
Oct 20, 2018
Messages
3,476
Good point. That's a good reason to use a variable instead of a control reference.
 

RickHunter84

Registered User.
Local time
Yesterday, 20:36
Joined
Dec 28, 2019
Messages
85
hello gasman,

ive decided to move on and keep it simple for now. i did try the variable idea in one of my attempts to figure the issue out, but then the subreport (good receipts) of the parent report was not showing up. ill revisit at some other time. i listened to normalization videos today and i think i did a pretty good job, might not be perfect but the thought process i put to it wasn't that far off from a good DB setup.
i bought a book a while back "access bible" and it has helped me a lot. im looking into what Micron shared with me yesterday as well.

long stort short, ill address the issue in the future with an update enhancement.

Thank you for your help!

best regards

Rick
 

Users who are viewing this thread

Top Bottom