Simple Stock Control (1 Viewer)

Ah - yes we do. Any suggestions to what I have done to change that easily?

I have been working on the form FrmUseage and it is pretty much there. It seems to work with the functionality I wanted.

Its getting late here so I am stopping for now before my brain fries.

I have looked at the database, made a few changes. I am returning the adjusted database
in the same zip file. The database has been modified. See the startup form in the revised database for info.

Many thanks for the help, I would not have known where to start with the module!!

I have modified the module to work with my tables I as I want to carry on down that road as it seems to work perfectly for what we want.

I do however have a couple of issues.

1. When I add a new Cloth using my form FrmUseage an error message appears Error 94(invalid use of Null) in the module. I do not know where to start debugging this in the module.

2. Again in my FrmUseage form when I update the amount used or the amount lost in the subform SFrmUseage the record displayed in the subform goes back to the first record. This threw me for a while as I did not know what was going on. I have an After Update event

Private Sub Form_AfterUpdate()
Forms!FrmUseages.Form.Requery
End Sub

I removed this and the focus stayed on the record I was working with, but obviously I lost the functionality of the Requery to update my form. Is there a way of executing this command and retaining the focus on the record that I am working on.

I hope this makes sense!!

I have attached the latest, don't worry about adding or deleting any records, I'm just working on the functionality at the moment.

If it makes any difference I am using Access 2003.

The issue with the 94 error is that if you attempt to call the function with an invalid/non existent ClothID, it results in invalid use of Null.

We'll go with your tables for the moment, but, as I said earlier, your database design (tables and relationships) must support your business requirements.


You know your business better than I do, so we'll continue on with your tables.

Do you ever need reports/data by LotNo, JobNo, BatchNo?

I recommend you remove the TotalUsed from thblUseage --NEVER store calculated values.

Did you look at the immediate window to see how the starting amount is adjusted for each useage of cloth?

When you add new Cloth, you should be working with tblCloth. It is NOT a Useage at that point in time. All you are really doing is identifying this new cloth and its attributes - including InitialRollAmount. You work with the tblUseage when you are setting up/doing a Job. As I mentioned in previous post, the query does not properly deal with the InitialRollAmount, so should be removed (unless you know how to adjust it). You should use the function.

On frmCloth, you are also using the query to calculate cloth remaining. (and as mentioned the query is an issue) When you add a new Cloth to the database, using frmCloth, the Cloth Remaining is equal to InitialRollAmount since it is a brand new roll.
To me, it does not make sense to record the amount remaining.

Separate the functionality -- you have a useage form and a frmCloth and both seem to have mechanism to do useages???? I recommend separating these -- use FrmCloth to Add a new cloth; and frmUseages to deal with Jobs and Useages.

I added a new Cloth via your frmCloth ---you can see where the errors are if you look at the design view on record 3 of 3.

As before I am returning the zip (same name) but database has changed a little.
Have to go out for a while, but will be back online later.

Thank you again.

Did you look at the immediate window to see how the starting amount is adjusted for each useage of cloth?

Not quite sure what was meant by this?

I take on board your recommendation for removing the TotalUsed Calculated value. Also not creating entries for a new Cloth in the Useage form, I will just Use the Cloth form for that.

I have removed the query and just use the module now on the FrmUseage, however the Module does not update the Cloth Remaining when I enter a job or edit an existing job without me hitting the refresh button or when the form is reopened. Is there a way of making this live? Also if it is a new roll and has not had any usage then the Cloth Remaining shows 0 as it has not calculated any usage yet.

I have also removed the Query from FrmCloth as you suggested - It makes sense. I would like to use the Module on my FrmCloth to show how much cloth is on a roll, it would be a handy "quick look"

As far as any reports go, all we should ever need to do is create a report of what rolls of cloth we have in stock and how much is on that roll.

I will try and create a nice front end so that users have the choice of interrogating stock levels, entering job details, booking in a new roll. It would also be nice to have some sort of search functionality so you can go straight to a particular roll to see its history.

Once again, many thanks.

Who are the intended users? Where are they located?
You can have a Requery within your vba code (event etc) to requery the control or form without clicking a button.

As for the immediate window, hit Ctrl-G and it opens the vbe. The immediate window is an area in which you can interact with code.

If you have an example useage where many Jobs use the same ClothId, and you have showDebug as TRUE, you will see print outs of each useage of that Cloth. It will show the calculated starting amount of Cloth for each useage.

Now for the real part of all this:

Do you have some test scenarios?
Adding x number of Rolls of Cloth? Using various Cloths to satisfy Jobs?
Asking questions of the database?
How many yards/feet of clothname XYZ were used? etc.
Do we have enough of ZZ on hand to do Job 444?

Don't skimp on this and don't assume it will all work. This can be tested from your data model with pencil and paper. Too many people create a table or 2 and then go onto to the details -- only to find out there was a structure problem. Get your tables and relationships matching your requirements --- then go on to develop/prototype the application.

Post with questions, I'm here.

All seems to be working for now.
Just got the issue of the Cloth remaining module reporting 0 when a new roll has not had anything cut from it. It would be handy to have that info on the FrmCloth form.

Also Where would it be best to put the Requery command so that the records on the subform SfrmUseage don't jump back to the first one?

You don't need cloth remaining on the cloth table. When it's a new roll, it is InitialRollAmount.

You can run a query, call the function to find amount remaining on a roll. It subtracts all usages/transactions (used and lost) from all useages for this cloth. (Always correct value)

Did you look/test the immediate window?
Do you have some test scenarios?

Add some records or some manipulations, then repost the test database and identify where exactly you would like the "requery" to happen.

What exactly does this mean and what do you want to happen.

the records on the subform SfrmUseage don't jump back to the first one?

Click to expand...

I understand that I don't need cloth remaining on the cloth table etc. I've been trying to create a query that shows current cloth stock QryCLothStock It shows Remaining after cut which uses the module and the InitialRollAmount but it would be good to show the InitialRollAmount of cloth from an unused roll as cloth remaining or somehow have a single field that is the actual quantity on a roll whether it has been cut yet or not. If you see what I mean??

I have also created a search form FrmSearchCloths for searching job numbers or cloth names and this only shows cloths that have been cut. Cloths that do not have an amountremaining figure do not appear. Again a single field that is the current cloth on a roll field.

I hope this makes sense.

I have added a routine to get the Current remaining Cloth on each roll.
I added a small button on frmUseage (binoculars) to invoke this function. That's for demo, you could call it, use it any way you want wherever you want.

I looked at frmCloth--how do you move between clothids? It only shows 1 cloth.
Shows Cloth remaining and New Cloth and Enter useage buttons.
Clicking on Enter Useage has me confused.

Are there instructions for using frmSearchClothJobs? Normally you would start with a specification for the form, then prototype/code it and test it.

You might want to redefine JobNo to be numeric. It's currently text, and it sorts as text not a number. It could be confusing.

I modified the module to deal with New Cloth with no usage.

Again, the zip has same name but the database has been changed as stated above.

Thank you very much for all your help, I'm getting there.
Will post db when I think I have got it working satisfactorily.

Thanks

Glad to hear you are making good progress. You did see that the module was changed, a new procedure was added, a button was added to your form for demo.

Did you look at the immediate window info?

As I mentioned previously

Too many people create a table or 2 and then go onto to the details -- only to find out there was a structure problem. Get your tables and relationships matching your requirements --- then go on to develop/prototype the application.

Click to expand...

Good luck and Merry Christmas.