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.
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.
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.
Are we trying to complete the Order_Tbl and/or Order_Lines tables with our current focus?
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)
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).
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.
Method or data member not found: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 Subbut 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.Code:If rst.Temp_Quantity > rst.remaining_quantity Then
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.
using:
we get: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
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
added rst.Movenext before the loop code. (not that it gets this far)
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.
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
Hold up. I might actually know what I'm doing if you carry on.