Tricky calculation to perform

[leeli67]

Hello again JZW , made a bit of an error somewhere and some how lost the ability to produce that form that appears when clicking the cmdCreateSorders button on the frmCreateNewOrderFromStandingOrders form , not sure but think the code for the filter in that button may have changed , from reading your notes in the code , it would seem when i click the button it should filter to the record i just created but it just brings up the next blank order after the one i just created,

one other thing for some reason when i used the afore mentioned form it wouldnt give me the right customer days for the customer I selected , so I changed the bound column in the select days combobox to orderID number NOT SOdays and now it works fine , anyway dont think that anything to do with this blank form thingy , just wondered if you could take a look at where im up to. about that code problem, by the way does all that code get typed in manually , thats alot of stuff to remember , impressive

[jzwp11]

I did make an error in creating the append query; I referenced the bound column rather than the orderID column. In changing the bound column of the combo box, you fixed that. I went ahead and changed the code in the query anyway. I had me.cboSOrder. I changed it to me.cboSOrder.Column(2). Note: Access counts columns from zero when referencing a column in code or an expression but count from 1 within the combo box (a little confusing). So a bound column of 3 in the combo box is actually column 2 when referenced in code.


mySQL = "INSERT INTO tblOrderDetail ( ProductsID, Quantity, Price, Disct, OrderID )"
mySQL = mySQL & " SELECT tblOrderDetail.ProductsID, tblOrderDetail.Quantity, tblOrderDetail.Price, tblOrderDetail.Disct, " & hdpkOrderID
mySQL = mySQL & " FROM tblOrderDetail"
mySQL = mySQL & " WHERE tblOrderDetail.OrderID=" & Me.cboSOrder.Column(2)

I could not duplicate the form issue you mentioned. I am guessing that you went and selected a new customer (after having already selected a customer & order day) from the dropdown but did not reselect an item from the second drop down. That would cause an issue. Also, if the order form was already open that may cause an issue as well.

Could you try to duplicate the problem again? If so, let me know exactly what forms were open at the time and what you were actually doing (which selections you made in the combo boxes etc.)

[leeli67]

Just tried again , opened DB went straight to the Form we talk of , and selected olivers butchers and their monday order , clicked create order button , which in turn opens a blank (no OrderID) form (my normal order entry form) , Now the order that was supposed to be made is there but its 1 order number back , ie the previous order?..

one thing i did notice is when i open the DB sometimes i get the yellow bar offering me the option to enable macros i think, but sometimes its not there , is there a way to permanently enable this?.

lee

[jzwp11]

I still could not duplicate the problem. If I do the same as you did, the form opens to the order just created and the lower left corner shows filtered record 1 of 1.

As to the yellow caution bar, you have to set up a trusted location (folder) and put your database file there. To set up trusted locations: Open Access (not a database file) then go to File-->Options-->Trust Center then click the button Trust Center Settings... Once you have that set up, move your DB files to that trusted location.

[leeli67]

Hi JZW still trying to get to the bottom of the form Production thing, when I open the Db from scratch, and the first thing i do is a standing order order it does produce the filtered form all filled in , but then I try to do another and that one fails, would that be a code thing ?



EDIT : Just tried to do a standing order and it worked fine , tried the next worked fine and so on , would seem that if I start to open other forms ,Queries or tables , it starts the problem.? , but getting better

[jzwp11]

When you say that you did another what exactly do you mean? You need to tell me the exact steps that you did so that I can do the same in order to do the troubleshooting.


So you selected a customer and then a standing order and clicked the button. It worked fine then...
Did you close the order form (not the standing order creation form) that opened after the first one you created or did you leave it open?
What did you do next? Did you select a new customer? Then a new day?

[leeli67]

My apologies JZ for a slightly abridged version of events, I just updated my last post , but I will try and explain with out going on too long. its actually strarting too work now.

1. open Access 2010
2. open my Bakery DB
3. open The FormCreateOrderFormFromStandingOrder
4. choose customer
5. choose day
6. press the create order button
7. the new order DOES now open


so I now have open 2 Forms OrderForm (filtered) & CreateOrderFromStandingOrder.

now if I go back to the CreateStandingOrderFromStandingOrder form , I can use it again, and it does give me the OrderForm With the Next new order , SO FAR SO GOOD. , its then after when maybe a query is run or another table or form selected. It seems to mess up the Filter.

hope that explains better.
lee

[jzwp11]

Did you leave each OrderForm open after each new order was created? In other words, did you have the CreateOrderFromStandingOrder and 2 or more instances of OrderForm open? I would close the forms before you do something else especially if the form/query you open uses the order or order detail tables. I can modify the code to check to see if the order form is open and if so close it before the new order is created. Now if you find that you are creating multiple orders over and over each day, that could be automated.

[jzwp11]

Ignore my previous post, each time you click the button the orderform just updates. Now if you create an order for a customer for a specific day, it determines the next order date. Now if you create another order for the same customer/day combination, a new order will be created with the same customerID and order date, so now your filtered form will show 2 records instead of one.

I did notice that I forgot the code to close the recordset. I'm not sure if that responsible for the issue you mention, but it is worth a try. I've attached the modified DB.

[leeli67]

Hi Jz , couldnt enter a new record on the last DB you Uploaded , gave me an error in the code, Then I got thinking , would it be easier to just go to the last record in the order form rather than filter the last order entered, I,ve just uploaded my last DB , would this one need the recordset closed.

this is the error from RavensDBGood.zip

Attachment 7053

[jzwp11]

It looks like a reference is at fault. I went ahead and added the code to close the recordset in the DB you provided; it is attached.

[leeli67]

Got the same error , does that mean its something on my machine , I'm guessing you give it a try before you send it back ?.

[jzwp11]

Yes, I did give it a try and it seemed to work fine. I am guessing that a VBA reference is missing. I have attached a screenshot of the references I have checked. To access the references, force the error to occur again which should take you to the VBA window. Click the square reset button icon (or click on Run from the toolbar and then reset). Then select Tools from the tool bar and then References. See if any say MISSING. If so uncheck that (if it is checked) and then look for the reference as shown in the screenshot below.

[leeli67]

mine are the same as yours

[jzwp11]

You said that you recently switched to Access 2010. Did you also install Service Pack 1 for Access 2010?