Db form programming?

[swat]

Good afternoon all:



If this should be in the forms category I apologize.

I am looking for some help with a db from Access '10 that I have been working on. For some reason it shows as an access 07 file in the title bar, but thats not the issue. In the db, there are two tables in particular that are more important than the others. I am tracking the usage of one material by using a form to create a new record on the usage log table and also to update the appropriate record in the inventory table to show the current inventory after the record is created. To do this I have an update query that runs when the add record button is pushed on the form.

Here is the issue. Some people are not as careful as others when weighing or documenting the weight of the material that is used. I would like for the form to recognize that the starting weight field on the form does not match the current weight field of the inventory table. The two are related by another field on the form that is the primary key for the inventory table. Once it recognizes this, I would like it to inform the user that the numbers are not right. It also needs to prevent the adding/updating of records on both tables if possible.

I can upload the db to look at if you would prefer. Also, on a side note, for some reason, a calculated field that I have set to double and 1 decimal place comes up with a 10digit decimal on occasion. ie, 61.1(field 1) - 60.9(field 2) = .20000000006 .

Thank you in advance for your time.

V/R
Shawn

[SteveF]

Good afternoon all:

If this should be in the forms category I apologize.

I am looking for some help with a db from Access '10 that I have been working on. For some reason it shows as an access 07 file in the title bar, but thats not the issue. In the db, there are two tables in particular that are more important than the others. I am tracking the usage of one material by using a form to create a new record on the usage log table and also to update the appropriate record in the inventory table to show the current inventory after the record is created. To do this I have an update query that runs when the add record button is pushed on the form.

Here is the issue. Some people are not as careful as others when weighing or documenting the weight of the material that is used. I would like for the form to recognize that the starting weight field on the form does not match the current weight field of the inventory table. The two are related by another field on the form that is the primary key for the inventory table. Once it recognizes this, I would like it to inform the user that the numbers are not right. It also needs to prevent the adding/updating of records on both tables if possible.

I can upload the db to look at if you would prefer. Also, on a side note, for some reason, a calculated field that I have set to double and 1 decimal place comes up with a 10digit decimal on occasion. ie, 61.1(field 1) - 60.9(field 2) = .20000000006 .

Thank you in advance for your time.

V/R
Shawn

Without seeing the details of your project it'll be hard to be real specific, but in general you'll want to modify your add-record button to check that the weights entered are valid:

Read current weight from inventory table
Compare to weight entered on form
If OK, then
Perform Updates
Else
Throw up a message
Dump the user back to the weight-entry control (SetFocus)
End if

With this type of logic wrapped around the update, it will only happen if the entered value passes the test.

As far as your calculated-field anomaly goes, that's unfortunately "normal behavior" for any floating-point calculations in any programming language. To always have exact calculations out to 4 decimal places, change your data types for the numbers involved to "Currency". Despite its name, Currency is actually a special scaled-integer data type that is meant to be used when exact decimal calculations are needed.

Steve

[rpeare]

Here is the issue. Some people are not as careful as others when weighing or documenting the weight of the material that is used. I would like for the form to recognize that the starting weight field on the form does not match the current weight field of the inventory table. The two are related by another field on the form that is the primary key for the inventory table. Once it recognizes this, I would like it to inform the user that the numbers are not right. It also needs to prevent the adding/updating of records on both tables if possible.

There are a couple of things to consider here.
1. Is the person weighing the material before taking new material right
2. Did the last person to use the material correctly weigh the amount they took?
3. Was there some loss that occurred from a spilled container between the last use and the current use?

The way you are currently operating there is no way to track who is using your material because you're using a non-normalized table structure. If you are going to provide for the possibility that the container does not weigh the same between uses or that someone has made an error in weight you've also got to provide for some way to make an adjustment to the container weight if the person taking new material out measures the weight differently than the last time it was stored, particularly if you intend to block updates/additions of records if the weights do not match.

As SteveF pointed out you would have to do the update blocking via VB code.

I can upload the db to look at if you would prefer. Also, on a side note, for some reason, a calculated field that I have set to double and 1 decimal place comes up with a 10digit decimal on occasion. ie, 61.1(field 1) - 60.9(field 2) = .20000000006 .

Depending on where/how you are displaying your information this is not really an issue, if it's on a table or query it doesn't matter much unless you're distributing table/query contents directly to your users. If you are showing data through reports etc you can limit your numbers in your report setup. It's not clear whether you're talking about queries or tables though since you are storing a calculated value

[swat]

Thank you for your reply Steve. The "Currency" fix worked. For the coding on the button, I am using the event builder. I added a new IF statement around the rest of the events beginning with
IF
[Weight_Before] = [SF6 Cylinder Info Running Iventory]![Current_Weight] Then "rest of code"

Else
message...
GoToControl..

I use GoToControl because SetFocus is not available in the dropdrown list.

When I run the macro from the macro tools tab, it gives me an error saying that "Weight_Before" is not available. When I run the macro by using the form, an error stating the Cylinder Inventory sheet property is not made available by the component comes up.

I guess I am not familiar enough with access to know all the ins and outs as of yet, so if there is a better way to do the coding than the event builder, I am willing to try it.

The Form itself is over the 500kb limit for uploading or I would upload the file for you.

Thanks again
Shawn

[swat]

Thank you for your reply as well Reapre.

There are a couple of things to consider here.
1. Is the person weighing the material before taking new material right
2. Did the last person to use the material correctly weigh the amount they took?
3. Was there some loss that occurred from a spilled container between the last use and the current use?

This is exactly the issue we are facing. We are having training annually on the proper weighing of the items to hopefully avoid the issue of incorrect weights. The product is a gas in a cylinder. Hopefully there are no spills, or we will have some problems with the California Air Resources Board. =)

By blocking the form from updating and informing the user of the issue, I am hoping that we correct the issue by talking with the technician listed on the form.

The way you are currently operating there is no way to track who is using your material because you're using a non-normalized table structure. If you are going to provide for the possibility that the container does not weigh the same between uses or that someone has made an error in weight you've also got to provide for some way to make an adjustment to the container weight if the person taking new material out measures the weight differently than the last time it was stored, particularly if you intend to block updates/additions of records if the weights do not match.

For this issue, only one person will be entering the data into the computer. The technicians are using a paper version of the form. We can update the cylinder inventory data manually if we need to, but we have to account for any gas that is released into the atmosphere, which is why I am trying to have the user catch the problem right away so we can discuss it with the technicians.

As SteveF pointed out you would have to do the update blocking via VB code.

I am possibly in over my head on this project. I'm not certain of how to get into the forms vb code other than the macro page. The convert macros to vb button is greyed out on the macro page.

Thanks again

Shawn

[rpeare]

How about zipping up your database (just make a copy of it and delete a bunch of information you don't need, just leave enough data in it to go through the process you're struggling with) then compact/repair to make it as small as possible then zip it up and attach it to his forum.

I don't want to build an example from scratch and it'd be easier to modify yours I think.

[swat]

Attachment 4279Rpeare,

Thank you for reminding me, I totally forgot about zipping files. Here is the file in question, I removed extraneous tables, but the size didn't shrink for some reason before I zipped it.

Thanks again.

Shawn

Removed wrong file, added correct file. Ok to daownload.

[rpeare]

Oof I can not read access 2010 format files, if you can convert it to 2007 or lower I'll have a look.

[swat]

It has default type as Access 2007, 2010 is not an option, as if there were no 2010. I can change it to 2003 if you like.

[rpeare]

That would work too

[swat]

Hmm. I have a calculated field in a table, so I can't down convert the save to 2007 or earlier...

[rpeare]

Hah, it appears we're at an impasse then, and yet another reason not to use calculated values in a table.

Try clicking your windows button and hovering over SAVE AS, does one of those options give you the ability to save as Access 2007?

[swat]

2010 has a file tab in the ribbon now for doing those types of things, instead of the windows button, but yes that is where I tried to save it and the only save as option just says access database, the access 2007 file type option is not there.

[swat]

From the Microsoft website:

The following list describes factors that prevent conversion to an earlier file format:

• Use of new data types, field property settings, and objects The following new data types, field property settings, and objects are not supported by earlier versions of Access:
  
  • Calculated fields in tables
  • Data macros
  • Multivalued lookup fields (lookup fields that have the Allow Multiple Values property set to Yes)
  • The Attachment data type
  • History-tracking Memo fields (Memo fields that have the Append Only property set to Yes)

You cannot convert the database unless you revise the database so that it no longer uses these features.


I tried removing the calculated fields, but to no avail. The data macro is probably holding me up now, which is the part I need help on. Sorry for wasting your time. And thank you for attempting to help.

[swat]

I am getting SP1 for 2010 now, with it I can convert my embedded macro to vba, and then maybe I can downconvert for you rpeare.