Db form programming?

[swat]

Ok,

I think this is it now. I have the macro converted to VBA, and removed the calculated fields in the two tables that had them. I hope this one works for you.

[rpeare]

Ok, just a few notes on your database

1. I don't change any field names or anything in your database but you should really take a look at your naming conventions. For instance you have a field called cylinder_#_used. # is a special use character (used to denote a date field) the only special character you should use is the _ (underscore). This includes spaces in object names (tables, field names, etc)

2. The two fields in your running inventory (Current Weight, Date Updated are unnecessary. I did not change your add record procedure but you can get this information from your log. You really only have to look at the most recent (maximum ID) for the selected cylinder to obtain the most recent date and weight. I've provided you with a query to show you how it's done. The query that shows the most recent weight/date is called qryMRData.

3. In your Usage Log table, you have a field Cylinder_#_Used, other than the naming convention I can not tell whether you are storing the ID from the Running Inventory table or whether you are storing the field that is NOT the primary key. If this field is NOT the primary key you really, really, really need to switch your data to just reflect the ID of the tank not the internally designated number. For the purposes of this example I assumed that the Cylinder_#_Used field was indeed the foreign key from your Running inventory table.

Anyway, here's the database

[swat]

Thank you Rpeare,

Sorry for the slow response, I had some family issues to deal with.

First, I really like the new setup of the form, great idea. This setup seems to work, just a few questions though. Does the usage log not update until you close the form? It didn't seem to until then for me. Also, once you hit add record, there are two message boxes that come up, with no information other than numbers displaying. I believe it is this code:

MsgBox Me.Weight_Before
MsgBox Me.CylinderNum

I am sorry for being such a pain. As far as the qryMRData goes, should I just uncheck the columns that don't need to be seen in the output table?

For the question about the primary key in the usage log, I'm not sure what exactly you are asking, but I can't make the cylinder number the primary key because it will repeat.

Thanks again.

Shawn

[rpeare]

First, I really like the new setup of the form, great idea. This setup seems to work, just a few questions though. Does the usage log not update until you close the form? It didn't seem to until then for me. Also, once you hit add record, there are two message boxes that come up, with no information other than numbers displaying. I believe it is this code:

MsgBox Me.Weight_Before
MsgBox Me.CylinderNum

Because you are using a bound form whatever data you enter is put on the table. You have to control whether it stays there with the UNDO command, for instance, you have left the buttons in the upper right hand corner that allow someone to close the form, thus bypassing the security of not having the 'add record' button highlight.

In the ON CLOSE event of the form, you should likely have another check that sees if the calculated field and the field the person enter are the same thing, and if they are keep the record otherwise UNDO it.

You can avoid a lot of this if you went to an unbound form but that's more of a hassle to newer people.

The msgbox lines can be removed I was just using them during testing. I use msgboxes when I want to stop the code and debug.print when I want to let the whole thing run regardless of consequences, those are remnants of my testing.

I am sorry for being such a pain. As far as the qryMRData goes, should I just uncheck the columns that don't need to be seen in the output table?

You can do whatever you want with that query, my purpose was to show you that you didn't need to update your 'running inventory' table because really, you're just updating it to reflect the most recent record's return weight. Likewise you can figure out how much product was used by subtracting the return weight from the outgoing weight for any reports you need.

For the question about the primary key in the usage log, I'm not sure what exactly you are asking, but I can't make the cylinder number the primary key because it will repeat.

You have to have a table that has a primary key and whatever your internal numbering system is along with whatever other pieces of information about each cylinder are important (in your case tare weight). In your table SF6 Cylinder Info Running Inventory you have an autonumber field that is identical to the cylinder field in content so I could not tell which field you were referring to in the table SF6 Usage Log 2011 to Present. I just assumed it was the AUTONUMBER field because that is a stable (unchangable) primary key. You do not want to use the CYLINDER field because you may get rid of a cylinder 2 (cylinder is damaged or past it's lifespan or something like that) and replace it with another cylinder which you number 2. To that end I would likely suggest you have a field (yes/no) that indicated whether the cylinder had been removed from service.

[swat]

Thank you again Rpeare. I appreciate all of the help you have given me.