Need some help with coding

[Marinus]

Dear All,

Have this problem as is seen on the attached JPG. On the subform weights are displayed from the a table and a calculation. This is transferred into the table by a Material list, which pops up a form with choices. When one of the buttons is pressed the following code will run.

Private Sub SelectMe(Ctl As String)

'/Before we simply displayed the selection on the previous screen
'/What we are doing now is adding the selection to the TblDocketMaterials Table
Dim rs As DAO.Recordset
If Forms!frmMultiTouchscreen!Sale = False Then
Set rs = CurrentDb.OpenRecordset("Select * From TblDocketBuy Where DocId = " & LngDocket & " And CatID = " & Me(Ctl).Tag & " And MatID = " & LngMaterial & " And F_Weight = " & LngWeight & " And B_Price = " & LngBuy & " And  Dirt = " & LngDirt)
Else
Set rs = CurrentDb.OpenRecordset("Select * From TblDocketSell Where DocId = " & LngDocket & " And CatID = " & Me(Ctl).Tag & " And MatID = " & LngMaterial & " And F_Weight = " & LngWeight & " And B_Price = " & LngBuy)
End If

If Not rs.EOF And Not rs.BOF Then
    '/selection already exists for this docket - don't want duplicates
Else
    rs.AddNew
    rs("DocID") = LngDocket
    rs("MatID") = LngMaterial
    rs("CatID") = Me(Ctl).Tag
    rs("F_Weight") = LngWeight
    'Rs("E_Weight") = LngEmpty
    rs("B_Price") = LngBuy
    rs("Dirt") = LngDirt
    rs.Update
    rs.Close
End If
Set rs = Nothing

DoCmd.Close


End Sub

My problem is that I want to deduct 1% from the weight of the first transaction, this is to compensate for any dirt on the material being bought. Currently the form works fine but as you can see the Empty weight of the first transaction doesn't match the weight of the Full weight of the second transaction and I have no clue where to switch.

The way I do this now doesn't work because try to handle the 1% in Text boxes. I will attach the program of which I only use the forms frmTouchscreen, frmMultiTouchscreen and the popup frmMultiMaterial which contains the above code. The command button Read Scale reads a file that comes from a wedge to grab the weight from a scale. Weigh Out registers the weight when a lorry comes back after (partially) unloading.

In the above code there is also data transferred to a sales table in which case no % should be deducted as this is clean material

Any help much appriciated..

 

[stopher]

I took a quick look at your database. I don't really understand your question. But I think there's a couple of problems to sort out in your table design before you resolve this problem:

You have two tables called TblDocketBuy and TbleDocketSell. Surely these are the same thing? i.e. in/out transactions. They should be in the same table. This will make your form designs easier e.g. you don't need two subforms in frmMultiTouchScreen.

You have put CatID in the DocketBuy/Sell tables. But really this should go in the material table. So a docketBuy/Sell is related to a material. A material is related to a category. This is why you have some odd relationships in some of your queries.

I don't really understand the workflow of your project e.g. what is full weight what is empty weight etc, so I can't really help there.

Any reason you've chosen to do lots of updates through code rather than through the form and bound fields e.g. E_Weight? Maybe due to my lack of undertstanding.

Chris

 

[Marinus]

Thanks Chris for your that Chris, This application was started without any input from my friend, It evolved from a form based application to a code based application to minimise the use of keyboard and mouse. As a beginner I have made many mistakes and others have helped me solving them. Also with code. As the application grew I was asked to explore other possibilities.. Hence the changes in the application and now the 1% rule on buying.

The object is to create a docket in the first screen frmTouchscreen and add materials on the second frmMultitouchscreen. This second screen collects the data of Waste being bought and sold. So the work flow is A truck comes on the scale for the first time. A docket is created, on the second screen the weight of the full truck is registered, 1% deducted for dirt and a buyprice agreed, the category is chosen and a material, this is stored in the table and when the truck returns empty or partly empty a weight out is recorded. If the truck has more then one material the empty weight should become the full weight in the next record, buyprice entered and material chosen again. Then upon return the empty weight is recorded again. Until the truck is empty. In the process there could be seven or 8 truck on the compound so dockets / trucks are chosen from the listbox as they come up on the weighbridge.. The two tables came to make up for a lack of knowledge..
Hope this clarifies somewhat..

 

[gemma-the-husky]

I would say

store the gross weight, and the tare weight

have another field to store the waste factor (1%) - get this from a "system" table, so you can change it.

when you calculate the weight for charging either do

(gross minus tare ) less waste factor OR
(gross less waste factor) minus tare

whichever you decide to use.

 

[Marinus]

Thanks Dave, Somehow that makes sense to me, can I ask you the question about storing in the buy and sell in one table or two.. From my beginners point of view buy and sell separate seemed like a good idea... However I could be so wrong here.. Would you like to share any views on that..
By the way love the picture of Gemma, she must have been a wonderful dog...

 

[gemma-the-husky]

not sure exactly what industry you are in ... but buy and sell are really different things

so, you need a way of storing each customer's prices for the various materials they buy.
and you need a way of storing the cost prices you are paying.

as I say, the exact structures you need for storing these prices does depend on the exact industry - so storing them both in the same place may or may not be appropriate - depends whether you are doing back-to-back sales, to some extent I would think.

now when you sell something you need to be able to get to the sell rate and the buy rate - normalisation rules would say you don't need to store these values - as you can determine them from the system - but personally, I would still store the calculated values in the delivery record

hope this helps

 

[Marinus]

Thanks Dave, I have solved my one percent issue with a entry in the system table and Iif statements in the text boxes. As you are right about the back to back sales, I do not store price information with materials as this changes in every record. I do store the buy and sales price in a transaction table together with the nett weight (Gross - Factor) - Tare This works perfectly.
Thanks everybody for thinking with me to find this solution..
Great people... reminds of someone on the board that says "There are more ways to skin a Cat"

 

[vbaInet]

Great people... reminds of someone on the board that says "There are more ways to skin a Cat"

That would be JohnBigBooty or missinglinq

 

[Marinus]

That would be JohnBigBooty or missinglinq

And you are great too.. !!

 

[vbaInet]

Thanks. I was actually talking about the user that has the quote, "There's more than one way to skin a cat".

Just checked and it's missinglinq

 

[Marinus]

Thanks. I was actually talking about the user that has the quote, "There's more than one way to skin a cat".

Just checked and it's missinglinq

I know but you have helped too, so worth mentioning together with David and Bob, John, Missinglinq and Gemma.. Without whom I would still be staring at the wallpaper of my computer screen... And of course the others I have missed.