Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Location
    County Kildare, Ireland
    Posts
    3

    Unanswered: To loop, query or something else.

    Hi,

    I have just made my first database. It is based on an existing excel spreadsheet.

    I was able to get this far by solving any problems through looking at various forums.

    The database is for recording tests made on 10 different products that are produced in batches of approx 30-35 tonnes.

    What I want to achieve is this, when the test results are being entered on the input form, count the quantity for product already stored plus the quantity being entered and when the total is >= 200, highlight the quantity on the form and in the database. The surplus over 200 will be the beginning of the next total. This must be done for each product.

    Hopeing for some direction.

    Thanks

    Pedro123

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Pedro,

    You never store formats, or any information that can be calculated, directly in the table. If you want to see certain fields highlighted on your form or report, simply use VBA behind the appropriate textbox (on the form or report) to do the calculation and determine the correct color. There are a couple of color functions to do this. Look at the help file and have

    good luck,

    Sam

  3. #3
    Join Date
    Feb 2012
    Location
    County Kildare, Ireland
    Posts
    3

    To loop, query or something else.

    Hi Sam,

    Thanks for replying.

    I came up with a solution of my own but this solution created another problem.

    Solution; I queried the Table for each product and then ran the following code.

    Sub makealoop3()
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim ToTtonnes As Integer

    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryBatch200")
    With rs
    ToTtonnes = 0
    If rs.EOF = False And rs.BOF = False Then
    rs.MoveFirst

    Do Until .EOF
    ToTtonnes = ToTtonnes + !Weighttonnes
    If ToTtonnes >= 200 Then
    .Edit
    !Batch200 = -1
    .Update
    ToTtonnes = ToTtonnes - 200
    End If
    rs.MoveNext
    Loop
    End If
    .Close
    End With
    Set rs = Nothing
    End Sub

    The code loops through the field Weighttonnes, add the quantities until they are greater than or equal to 200, changes the Yes/No field Batch200 to positive and stores the amount greater than 200 to continue the loop.

    The Problem: it is not adding the decimal element of each value in the Weighttonnes field.

    The Weighttonnes field settings are, Field Size: Double, Format: Fixed, Decimal Places: 2.

    Two questions:
    1. Would you have a solution to this.
    2. Can you amend the code so that I do not have to do a query first. Something like if field Products equals product A then loop elseif etc.

    This is a learning curve for me, ultimately I want to show on the input form that when a product is entered on the form a TextBox will show that the running total for a product is X amount.

    TIA

    Peter

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The Problem: it is not adding the decimal element of each value in the Weighttonnes field.
    For very good reason. You DIMmed ToTtonnes as an Integer. Ergo, it can't add decimal values. You have to change it to a Single or Double data type to add decimals. Also, be wary of the Integer data type. It can only deal with values up to 256. Beyond that, you need a Long data type, which handles whole numbers beyond 2 billion.

    I can't comment on the second question; I don't know what's in the query's SQL, or the contents of the data table.

    When you copy and paste VBA code, you can keep the original format - indentations, etc. - by putting the CODE marks (the '#' marks above the text window) around the code. It makes the snippet a lot more readable.

  5. #5
    Join Date
    Feb 2012
    Location
    County Kildare, Ireland
    Posts
    3

    To loop, query or something else.

    Sam,

    Thanks for the reply, by changing to Double I am able to identify the "200 Batches".

    Thank you very much, I will post a different question to try to sort my second question.

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •