Updating a field in a column in a datasheet

[tonygg]

Hi

Before I explain the issue in detail I though I would put the simple version: How do I specify the field in a datasheet I want to update to a value. I am stepping through the records but each time I update the field in the datasheet (Me.ProductSectionName.Value ) it changes the field value for every record to the new value. I need to specify somehow to only update the current row.?
I have a datasheet form that is populated from a query that brings back PRODUCT NAME and PRODUCT REF NUMBER. The datasheet populates fine. I have gone into design view for the datasheet and added some extra columns. Again this seems fine. On load the extra columns are blank (as you expect).

I will be using these extra columns to display calculated data (results of other queries and calculations in vba). The first thing I wanted to do was populate a column in the datasheet with the first word from the PRODUCT NAME when this value changes. To do this I used:



Form.SetFocus
Me.Recordset.MoveFirst

For i = 0 To NumRecords - 1
DoCmd.GoToRecord , , acNext

ProductNameTextCurrent = Me.Short_description.Value

intPos = InStr(ProductNameTextCurrent, " ")
If intPos > 0 Then
' Retrieve Control Name from the first part of the string

ProductNameTextCurrent = Left$(ProductNameTextCurrent, intPos - 1)

' MsgBox ProductNameTextCurrent & " " & ProductNameTextPrevious

If ProductNameTextCurrent <> ProductNameTextPrevious Then

ProductNameTextPrevious = ProductNameTextCurrent
Me.ProductSectionName.Value = ProductNameTextCurrent

End If
End If

Next I

I can see the logic working fine. The issue is that it Me.ProductSectionName.Value = ProductNameTextCurrent changes all rows to the current value in ProductNameTextCurrent. I need to be able to specify the row in Me.ProductSectionName.Value but cant see a way to do this?


Many thanks

Tony

[ranman256]

1. you dont update records by cycling thru them 1 at a time. (For i = 0 To NumRecords - 1) That is what queries are for (they do it instantaneously)

2. If you are using a Form viewed as a datasheet or continuous forms, unbound fields show on every record. Bound fields show 1 record val. Put calcs in the query not the form.

[tonygg]

Sorry I should have been clearer. This was my example to see how to add fields.

I actually want to create a datasheet (or continuous form) that shows for all products (based on the query) a series of calculated values that include very complex calculation that manipulate data across many tables (e.g. the total discount given for all orders for the product calculated on the highest price paid on any one order). I have written the code (600+ lines) and tested it using a listbox as output. The listbox has 2000+ rows so it is unwieldy and not really suitable.

I wanted to use datasheet (or continuous form) as I want to be able to sort and filter the data based on various user entered criteria. I would also like to insert sub totals into the datasheet for each product group.

Given the objective of my sheet my questions are:

1. Is the update of a cell in the datasheet possible (It appears that I can update the whole column using Me.ProductSectionName.Value =)? - If so how
2. Can I insert the sub totals rows? - If so how

3. If I cant do 1 and 2 how would you suggest I create my form or report ?

Many thanks for your help.

Tony

Th

[orange]

Tony,

Your terminology suggests (to me) that you have a lot of familiarity with Excel/spreadsheet.
Access does not have cells. Access (database) has tables, queries, forms, reports..... Database is a different object model/foundation than spreadsheet. You may have to "unlearn" your spreadsheet habits to gain the benefits of Access.
Good luck.

[tonygg]

Thank you both for your observations. I take them both on board.

I would really appreciate knowing the answers to my 3 questions (above) and rephrased in Access terms (I hope):

1. I have added some additional columns to a form created from a query (opened as a datasheet or as a continuous form). I would like to update each row in these columns with a different value. I know I can create the calculation as an expression in the query and fill in the column. However if I choose NOT to do this and instead loop through all the rows please could someone tell me the syntax to update the value in each row. If I use "Me.ColumnName.Value = abc " it updates all rows for that column to abc rather than just the current row. I guess I am missing the row and column referencing as it seems very odd that access allows you to add columns but then provides no flexibility to add values into it?

2. I would like to add subtotal rows to my form as new rows in the datasheet. Again I am at a loss to find the syntax to add a row to the datasheet (or continuous form) in a similar way as you do with a listbox ( Me.listboxName.AddItem Item:= "data").

If there is definitely no way to do either or both of these I would be extremely grateful if someone could tell me so I can find an alternative method.

Many thanks

Tony

[orange]

If I use "Me.ColumnName.Value = abc " it updates all rows for that column to abc rather than just the current row. I guess I am missing the row and column referencing as it seems very odd that access allows you to add columns but then provides no flexibility to add values into it?

Update tableName
Set fieldName = "abc" 
WHERE yourKeyField = keyvalue

In plain English you update a table by identifying the change you want to make
and the record/records to apply that change to(criteria/where clause [usually]).

Update MyTable Set AddressLine1 = "10 Smithers Crescent"
where employeeNumber = 12345

vs (no criteria, so apply to all records in the table)

Update MyTable Set AddressLine1 = "10 Smithers Crescent"

I don't understand #2.

[tonygg]

Hi orange

Thank you for coming back to me with the example. I understand the logic you are suggesting but I am being really thick and struggling to apply it. I think in my case I do not know what the "tableName" is for the datasheet in the form (I know this is very basic). I have tried to set things out in detail with the query, code and screen shots. I hope it helps make it clearer. To head the obvious point off at the pass - Yes I am using a simple example that easy done in the query itself (I have actually done it in the query too to create column 5). However my final sheet will have many additional columns and code creating them.

My Query is:

SELECT DISTINCT Product.[Short description], Product.[Product Reference], product.[nParentSectionID], Product.nstockonhand, Product.Price, Left$([Short description],InStr([Short description],' ')-1) AS Expr1 FROM Product LEFT JOIN [Orderdetail] ON Product.[Product Reference] = Orderdetail.[ProductReference]
WHERE (((Orderdetail.[QuantityOrdered] > 0 And Orderdetail.[Price] > 0 And Orderdetail.[QuantityShipped] > 0) Or (Orderdetail.ProductReference Is Null)) and Product.[Product Reference] not like '*!*' and Product.nstockonhand > 0)
UNION SELECT DISTINCT Orderdetail.[sProductDescription], Orderdetail.ProductReference, Orderdetail.Status, Orderdetail.TaxTreatment, Orderdetail.TaxTreatment, Left$([sProductDescription],InStr([sProductDescription],' ')-1) AS Expr1
FROM Orderdetail LEFT JOIN Product ON Orderdetail.[ProductReference] = Product.[Product Reference]
WHERE ( ((Product.[Product Reference]) Is Null) AND ((Orderdetail.QuantityOrdered)>0) AND ((Orderdetail.Price)>0) AND ((Orderdetail.QuantityShipped)>0));

and using the create form in access I get my datasheet based on the query. I had then added a new column in the form in design view. On running the form I get the following datasheet. The final column (ProductSectionName) is the one I added in form design (see attached screenshot)


Now if I step through each row of the datasheet on load using the following code:


Private Sub Form_Load()

Dim ProductNameTextPreviousWord As String
Dim ProductNameTextCurrentWord As String
Dim ProductNameTextCurrent As String
Dim NumRecords As Double
ProductNameTextPreviousWord = ""
ProductNameTextCurrentWord = ""

NumRecords = Me.Recordset.RecordCount


Form.SetFocus
Me.Recordset.MoveFirst


For i = 0 To NumRecords - 2

ProductNameTextCurrent = Me.Short_description.Value

intPos = InStr(ProductNameTextCurrent, " ")
If intPos > 0 Then
' Retrieve Control Name from the first part of the string

ProductNameTextCurrentWord = Left$(ProductNameTextCurrent, intPos - 1)

If ProductNameTextCurrentWord <> ProductNameTextPreviousWord Then

ProductNameTextPreviousWord = ProductNameTextCurrentWord

Me.ProductSectionName.Value = ProductNameTextCurrentWord

End If
End If

Me.Recordset.MoveNext

Next i
End Sub

It should set the final column to be the same as the first word in the first column.

However what actual happens is that for each row it finds the first word in column 0 (correctly) and then sets the last column of every row to that value. Hence as the last row brought back in the query has "Yuletide" as its first word so all the rows are set to Yuletide.



So what I need is the line of code to replace :

Me.ProductSectionName.Value = ProductNameTextCurrentWord

in the load form code. I guess it will look something like:

Update "DATASHEET NAME" Set ProductSectionName.Value = ProductNameTextCurrentWord where [Short description] = ProductNameTextCurrent.

I hope this makes it a bit clearer?

On point 2: I wanted to be able insert into the table additional rows that subtotal the column nstockonhand where the Expr1 is the same value. So for example if you see on the datasheet the first 2 rows have the same value in expr1 (200). I would like to insert a new row 3 that sums up the nstockonhand values. So this new 3rd row would have nstockonhand equal to a value of 97 (62 + 35). and so on for each group of rows.

Thank you once again for your patience and understanding,

Tony

[June7]

Why are you using VBA code?

The query shows this value being extracted by calculation.

It is not necessary to save this calculated value to table. It can be calculated whenever needed.

Adding records that sums other records is also bad design.

Data aggregation is what queries and reports are for.

[orange]

I think June is correct. I answered your post based on my interpretation that you were having difficulty with the scope of an UPDATE query. In reality I was guessing at WHAT you were trying to do, and guessed wrong partly because I didn't ask and partly because you seemed quite clear.
Usually I get the poster to tell readers WHAT they are trying to accomplish in plain English. Once we understand WHAT, there may be options/alternatives for HOW.
Good luck with your project.

[tonygg]

Hi. I had said " to head the obvious point off at the pass " it was a simple example in order to hopefully not need you to make the point in your reply. I will put at the end of the post the code for another field I need to add which is the order level discounts given for a product. It is a good 100+ lines of code to demonstrate that it would be impractical to put it in the query.

Please, please, please could someone just let me have the correction to my code to be able to update the calculated value in the last column of my datasheet from within the form vba. It seems it is possible and it takes only takes a simple statement but I am just not able to see what the correct syntax is.

An example of the type of data I would be adding to each row is working out and displaying the order level discount % attributable to each product (the value will be different for each row). The VBA to work out the code is set out below. If I can put this into a query I apologise. Please could you indicate how:

Function OrderLevelDiscountsPercentCalc(OrderNum As Integer) As String
' Find if there is order level discounts
Dim SQLOrderString As String
Dim rsOrders As DAO.Recordset
Dim rsComponents As DAO.Recordset
Dim NumofOrderLevelDiscounts As Integer
Dim ValueofOrderLevelDiscounts As Integer
Dim NumofOrderedItems As Integer
Dim ValueofOrderedItems As Integer
Dim ValueofOrderLevelAdjustments As Integer
Dim ProductOrderDiscMsgandCancelTxt As String
SQLOrderString = "SELECT OrderDetail.sProductDescription, OrderDetail.OrderSequenceNumber, OrderDetail.QuantityOrdered, OrderDetail.sCancelMessage, OrderDetail.nLineType, OrderDetail.sCoupon, OrderDetail.ProductReference, OrderDetail.Price, OrderDetail.TotalCost, OrderDetail.OrderDetailID FROM OrderDetail" & _
" WHERE ((OrderDetail.ProductReference)= ':::::' AND (OrderDetail.OrderSequenceNumber)=" & OrderNum & ")" & _
" ORDER BY OrderDetail.OrderDetailID ASC;"

Set rsOrders = Nothing
Set rsOrders = CurrentDb.OpenRecordset(SQLOrderString)
' Set DiscOrderList.Recordset = CurrentDb.OpenRecordset(SQLOrderString)
NumofOrderLevelDiscounts = 0
ValueofOrderLevelDiscounts = 0
If Not (rsOrders.EOF) Then

' Calculate number and value of order level discounts

NumofOrderLevelDiscounts = 0
ValueofOrderLevelDiscounts = 0
ValueofOrderLevelAdjustments = 0
ProductOrderDiscMsgandCancelTxt = ""

With rsOrders
Do Until .EOF

NumofOrderLevelDiscounts = NumofOrderLevelDiscounts + 1

' If the discount lines is to be applied to order it will be nline type 3 but not GIFT VOUCHER

If rsOrders![nLineType] = 3 And InStr(rsOrders![sProductDescription], "GIFT VOUCHER") = 0 Then

ValueofOrderLevelDiscounts = ValueofOrderLevelDiscounts + Val(rsOrders![TotalCost])

End If

' If there are order level adjustments nline type 4. Need to add up and take off total order to work out discount

If rsOrders![nLineType] = 4 Then

ValueofOrderLevelAdjustments = ValueofOrderLevelAdjustments + Val(rsOrders![TotalCost])

End If


' format line for order level discount listbox

ProductOrderDiscMsgandCancelTxt = "ORDER DISC: " & rsOrders![sProductDescription]

If Not IsNull(rsOrders![sCancelMessage]) Then

ProductOrderDiscMsgandCancelTxt = ProductOrderDiscMsgandCancelTxt & " : " & rsOrders![sCancelMessage]

End If

.MoveNext
Loop
End With

Else
' No order level discounts
OrderLevelDiscountsPercentCalc = "0"
rsOrders.Close
Set rsOrders = Nothing
Exit Function

End If

rsOrders.Close
Set rsOrders = Nothing

' Find Order lines related to order to work out % discount

SQLOrderString = "SELECT OrderDetail.OrderSequenceNumber, OrderDetail.QuantityOrdered, OrderDetail.TotalCost, OrderDetail.sProductDescription, OrderDetail.ProductReference, OrderDetail.Price, OrderDetail.OrderDetailID FROM OrderDetail" & _
" WHERE (((Val(OrderDetail.Price))<>0) AND (OrderDetail.OrderSequenceNumber)=" & OrderNum & ")"

Set rsComponents = Nothing
Set rsComponents = CurrentDb.OpenRecordset(SQLOrderString)

If Not (rsComponents.EOF) Then

' Calculate total value of ordered items so can calculate discount %

NumofOrderedItems = 0
ValueofOrderedItems = 0

With rsComponents
Do Until .EOF

If rsComponents![ProductReference] <> ":::::" Then

NumofOrderedItems = NumofOrderedItems + 1
ValueofOrderedItems = ValueofOrderedItems + Val(rsComponents![TotalCost])

End If

.MoveNext
Loop
End With

' Calculate the discount % value

OrderLevelDiscountsPercentCalc = (ValueofOrderLevelDiscounts / (ValueofOrderedItems) * 100)

Else

MsgBox "DB ERROR: Order Level Discount without any ordered items to apply it to!"

End If

rsComponents.Close
Set rsComponents = Nothing

End Function

[June7]

CurrentDb.Execute "UPDATE tablename SET fieldname = Left([Short Description],InStr([Short Description], ' ')-1)"

[tonygg]

Thank you June. I am really sorry but I think I am missing something. This seems to a db update command (?) but by data sheet was populated from a union query across multiple tables (shown in an earlier reply). So there is no row in the underlying tables to update.

Is it that one uses the same command to update the values in the forms datasheet? If so that is great. I just need a pointer to what to use as the tablename ? If not then is there a different command to update the datasheet values.

Sorry once again if this a dumb question.

Many thanks

tony

[June7]

I must be missing something because I don't understand what you are doing. UNION query is not editable by any method. So if there is no table field to be updated, why are you using code? What is it you want to 'update'?

[tonygg]

Sorry. Let me back up to explain the business problem. My wife has a quilting business and we sell fabrics online with a frequent turnover of lines to sell (lots of new ones added and more importantly old ones removed).

We use a package (SellerDeck) to manage the online shop. Behind it is an access DB. We need to analyse the sales we are making and how stock is being sold (how much, what discounts, what highest and lowest price, what units sold etc). There will be 19 calculated fields we need to display per product. In addition to just displaying the data we would like to be able to run some simple filters and sorts on the data.

The universe of products (but not all the data needed for the analysis) can be found in 2 tables. The first is PRODUCT which has all the current products we sell. The second is ORDERDETAIL which has the actual detail of each time a product is ordered (quantity, price, etc - multiple records per product). Additionally the Products that are not current (i.e. now sold out) can only be found in the ORDERDETAIL table - Hence there is not a key relationship between the two tables. So to get the full list of products we need to create a union query across the 2 tables. The First screen shot above show the datasheet form I created with the query and show I added a new column to the datasheet form called ProductSectionName

For each product I find using the Union Query there are a series of calculations required to collect the data to display about that product. There are 19 columns of data to display and 2750 products. Some of it is simple like the number of units left in stock or the total number of units ordered. However there are a number of pieces of information that we need to see that is much more complex. For example the discount that has been given across all the orders for the product to work out an average selling price. The discount can be given at the total order level (i.e. needs to be divided across all products in 1 order or at the product level). I already have ALL the code working to calculate each calculation required and as a test I have populated a listbox with the results for all the products. However as you can image the listbox is very slow and doesn't allow sorting etc - Not fit for this job.

I hope this is clear so far?

So what I was trying to do is create a form containing a datasheet that has all the products (populated from the union query) and then go through each row in this resulting datasheet and "add" the additional columns of calculated data so I got a final datasheet that shows all products and the 19 columns of calculated data. I saw that in the form design view you can add columns (which do NOT exist in the DB or in the query result) so I thought I would add new columns for each of my calculated fields then just loop through all the records in the forms datasheet to enter the calculated data in each of the columns for that row. The end result would be a datasheet that has all the products as rows and all the calculated data as columns. It could then be filtered and sorted easily using the simple Me.filter commands etc. I am stuck on being able to populate the new columns I added in the form design.

So my question was how do I populate my calculated columns that I added in the design view of the form? I would really like to know the way to achieve this single point.

Bigger picture - If there is a simpler or better way to achieve my business goal using forms or reports I am happy to adopt it?

I hope this makes it a bit clearer?

thanks once again for your help and patience.

Tony

[June7]

None of that alters the advice given. Even if you can populate these 'new columns' manually or with code, each 'column' will show the same value for all records. That is because there is only one set of textboxes (as can be seen in form design) which are replicated for all records. The records dynamically show different data only if the textboxes are bound to a field or have an expression that references fields.

The simplest approach is to do calcs in query and bind textboxes to the constructed fields. Otherwise, what you want will require writing records with the calculated values directly into a table.