Simple problem for a Guru

[plcman]

I am putting together an inventory database for steel bars. The bars are booked in and withdrawn in feet and inches.

With help from people on this forum I have decided to store all the stock in inches only, so when the operator books in say 100feet of a particular bar this gets stored as 100 * 12 (inches).

When using the database however the operator has to see and enter the stock in feet and inches so the inches as stored should not be displayed but a calculated value of feet and inches should be shown in two seperate boxes.

This I have done and works OK. My problem is I need the operator to be able to click on the field to modify or add or withdraw stock but when the operator clicks on the field, because it is bound to a calculation, modification is not allowed.

I would like to be able to modify the value in the calculated field and then invoke a calculation to modify the stored inches value.

The transactions are shown on a subform in datasheet view which may be complicating things.

Any suggestions, please

 

[Remou]

I think I would use the After Update event.

 

[plcman]

Sorry, could you explain futher?

Many Thanks

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[Remou]

Perform the calculations in the After Update event:

Private Sub txtText_AfterUpdate()
Me.UnitsReceived = Int(Nz([txtText]) * 12)
End Sub

Private Sub UnitsReceived_AfterUpdate()
Me.txtText = Int(Nz([UnitsReceived]) / 12)
End Sub

 

[TSIsolutions]

Hi plcman,

From what I understand, you want ot change the amounts directly on the record of that item. So that if there was 50 and the user changes it to 40 then he has taken 10, right?

In my opinion Inventory Management application should always have records of transactions so that if the user removes 10 form the qty then a new line in the inventory is created with a -10 so the the Sum of all transactions of a certain metal would give you the amount in stock. Is that the way you are doing it?

Also, to answer your question, you cannot change values in a calculation bound field. You will have to circumvent the problem using code (usually on the Form_Current event)

If you have any more questions let me know.

"Every solution breeds a new problem...

 

[plcman]

Hi

Thanks for your reply, unfortunately this isn't working because it is in a subform. All of the vaules in the txtText colum fill with the same value.

Any more sugestions please?

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[Remou]

I do not quite understand the point of unbound controls in a subform.

 

[plcman]

Hi

TSIsolutions Thanks for that, I do have a inventory transactions table(based on the microsoft template). The field I want to edit is on the subform that points to the transactions table, I would appreciate your thoughts on the best way to get round this in code.

Many Thanks

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[plcman]

Remou, Thanks, the point is that I want to show the data in another way, I want to show the data that is stored in Inches as feet and inches. I also want to be able to enter the data in feet and inches before converting to just inches to be stored.

Thanks


Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[stix4t2]

plcman,

How do you compensage for fall off? But I guess this would be a different story.

You can add buttons to increment the inch and foot fields up and down, by updating the total length data field, the calculated fields would change. You could also consider adding an edit entry subform at the top which would allow for foot and inch entry, and then modify the total length value. Or use a popup form to edit the values and again modify the total length value.

 

[Remou]

This is quite unsatisfactory in several ways, but it may be a start. Field1 is from the table, txtText1 is the calculated control.

Private Sub txtText1_AfterUpdate()
'Fill in bound control
Me.Field1 = Int(Nz(Me.txtText1) / 12)
'Put the control source back
Me.txtText1.ControlSource = "=Int(Nz([Field1]) * 12)" 
End Sub

Private Sub txtText1_GotFocus()
'Get rid of the control source for data entry
Me.txtText1.ControlSource = ""
Me.txtText1 = ""
End Sub

Private Sub txtText1_LostFocus()
'Put the control source back
Me.txtText1.ControlSource = "=Int(Nz([Field1]) * 12)" 
End Sub

 

[jsteph]

plcman,
In my opinion, I think the way to approach this is to simply show both fields.

Show the live field, in inches, with a greyed out calculated field next to it. You don't *need* to grey it out or disable it, since being calc'd it isn't updateable, but you grey it to make a visual cue that it's not the one the user should be trying to update. The calc'd field is automatically updated on update of the form, so no extra code is needed.
--Jim

 

[plcman]

jsteph (TechnicalUser)Thanks for your reply, but when booking in 5000ft of bar I don't want the operator to have to work out the number of inches to book in, I Think I will do it though a sepertae transactions form instigated from a transaction button on the main product record screen....maybe?

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[psemianonymous]

You need to learn the Access event model, specifically the control's AfterUpdate, Changed, BeforeUpdate events, and the form's BeforeUpdate, AfterUpdate, and Current events. Once you figure out how those events work it will be pretty easy to adapt the above code to your needs.

Otherwise we're just programming your solution for you, which hasn't worked for you thus far.

 

[plcman]

Sorry... Thought this was a forum where one could ask for advice.

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[jsteph]

plcman,
Ok, you don't want them to enter it in inches. How then will they enter? For example:
Will they enter 5' 10"
< with the single quote and double quote as feet & inches respectively>

Will they enter "5 feet 10 inches"?

Will they enter 5.5 < where 5.5 means 5 feet 6 inches>

Will they have a Feet box and an Inches box where they enter each seperately?

The problem to me seems to be more focused on the format in which you want them to enter the data--the code and events to do whatever conversion are simple and I can help with that but please give an example of what you see as how they'll enter the example above of Five feet and ten inches.
--Jim

 

[plcman]

jsteph

I have seperate boxes for feet and inches. I am now working down the line of having a button on the stock card form. This button opens a transaction form where the operator has feet box and inch box and 2 button: Add Stock and Draw stock.

Do you think that this is a sensible way to proceed?

Many Thanks

Alan

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[stix4t2]

Alan,

I thought you had a good idea of opening a form that they could set or change the value. You could use a button or they could double click on the values. If you make the pop up a dialog, then you can populate it before showing and pull the values after. Then you would do all the conversion in the back ground.

 

[jsteph]

Alan,
The transaction form would be a good way to go, and as stix said, use a dialog form. To do this, you'd put the code behind the button:
docmd.openform "frmInventoryTrans",,,,,acDialog

This causes code in the button click to stop at this line, and only continue after the form is closed.

You could set a public variable prior to the open of the form, and check it on return, since the Feet and Inches fields would likely be unbound. Alternatively, the dialog form could have a single-record dummy table as it's source, with a feet and inches field which could be read after closing the dialog.

Then of course you'd just do the calcs upon return and load the live data field, which is in inches. You'd still have a calculated field for display.
--Jim