Page 3 of 3 FirstFirst 123
Results 31 to 41 of 41
  1. #31
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

    Thanks. There is a lot of jargon in the system which makes it a little difficult to understand. I get the impression your system has something to do with electricity and hospitals and adding power or repairing power to places.

  2. #32
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I've probably edited too much for any information to be useful. I just wanted to keep any information secure. I trust you but, this is public.

    We are an electrical company that works on locations across the UK. Its not common for there to be a new "site"/location. But obviously new sites are added every now and then. The hospital information shows the neared hospital to a site for health and safety purposes. On the other database, I send jobs out via access/outlook. All the relevant information is in the calendar event on outlook for the engineer. Its saved a lot of time for the company.

    So with this I'm trying to reduce errors in accounts and save time/money there too.

  3. #33
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Are we trying to complete the Order_Tbl and/or Order_Lines tables with our current focus?

  4. #34
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Again this may lead to other issues or questions but;

    [temp quantity] on the order lines form is where I want to update via this form.

    I actually want to make a new record on the invoice_entries table but I've attempted to simplify it by storing that information temporarily on the line table. I can then run an append query that makes the invoice entry record. (keeping temp_quantity empty with an update query on the open event of this form).

    That is where I was heading, probably not the cleanest solution but a working one. (just trying to focus on the validation over anything else)

  5. #35
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm sorry if I ask stupid questions but learning is sometimes slow for me these days. I look at the validation of the "temp_quantity" as a separate process that should be possible to isolate without a problem (he says as if he knows...ha ha).

  6. #36
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Don't apologise. I appreciate your time. On another post about email attachments I was just working on. the solution was to use recordset clone to loop through the recordset.

    I'm trying to apply that here but it seems the variables are named differently to how I expect.

    Code:
    Private Sub Command51_Click()
        Dim rst As DAO.Recordset
     
        Set rst = Me.RecordsetClone
        rst.MoveLast
        rst.MoveFirst
        Do Until rst.EOF
       
    If rst.Temp_Quantity > rst.remaining_quantity Then
       MsgBox "Value exceeds available quantity"
       Else
        MsgBox "Created Invoice"
     End If
     
     
        Loop
      
        rst.Close
        Set rst = Nothing
     
        MsgBox "Done"
    End Sub
    Method or data member not found:
    Code:
      
    If rst.Temp_Quantity > rst.remaining_quantity Then
    but Temp_Quantity is written like this in the record source for the form. Its named like this on the table also. now I don't believe record set even considers textbox names so I don't think its that.

    What do you think about this approach?
    and how are these variables defined? (clearly not how I expect)

    I felt smart for about five mins earlier when I was making this. haha.

  7. #37
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    using:

    Code:
    Private Sub Command52_Click()
        Dim rst As Recordset, intI As Integer
        Dim fld As Field
     
        Set rst = Me.RecordsetClone
        For Each fld In rst.Fields
            ' Print field names.
            Debug.Print fld.Name
        Next
    End Sub
    we get:

    Code:
    Contact_Name
    Line Number
    Order_Lines.description
    Unit
    Quantity
    Order_Lines.Price Per Unit
    Order_Lines.Total Line Value
    Temp_Quantity
    Framework_Num
    Order_Number
    Notes
    Orders_Framework.description
    Category
    Company_Name
    CountOfLine_ID
    SumOfQuantity
    Invoice_Line_Group test.Price Per Unit
    Invoice_Line_Group test.Total Line Value
    remaining_quantity
    remaining value
    Line ID
    Order_ID

  8. #38
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    added rst.Movenext before the loop code. (not that it gets this far)

  9. #39
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Just figured out I'm an idiot and had "." instead of "!". So I am able to validate each row now. Ill have to play with it a bit to stop where there is an error or see what I'm doing next. Perhaps have two separate loops, first one stops the VBA if something doesn't meet the rules.

  10. #40
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I see you found the solution.


    AFAIK, there are 4 ways to address a field in a DAO recordset:
    rst!FieldName
    rst("FieldName")
    rst.Fields("FieldName")
    rst.Fields(indx) 'position in the record set - zero based

  11. #41
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Hold up. I might actually know what I'm doing if you carry on.

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 01-07-2014, 04:20 PM
  2. Data Validation
    By Traci in forum Programming
    Replies: 1
    Last Post: 08-21-2012, 10:59 AM
  3. Data Validation using VBA
    By Cheshire101 in forum Programming
    Replies: 3
    Last Post: 05-10-2011, 08:43 AM
  4. Data Validation of another field
    By dssrun in forum Access
    Replies: 4
    Last Post: 03-22-2011, 01:09 PM
  5. Data Validation - Please help
    By larry S in forum Access
    Replies: 0
    Last Post: 11-16-2008, 10:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums