How to sum populated txt boxes based on Yes/No with prices that can be changed.

[LazarusLong88]

I'm having a couple different issues. A little background on the db: I am IT/Dispatch Manager a a small AC company and I have made a db to streamline their dispatch system, particularly the part where I need to debrief with the tech and gather information. I wanted to have a button on my form that pulls up a sub-form where I could select any of the 26 parts available a technician could recommend. So the sub-form has two rows of 13 Yes/No boxes, each Yes/No box has 2 text fields next to it representing the regular price(field is called RPrice) for the part and the price with a service agreement(field called SAPrice).

When you click yes on the box I want it to auto populate with the default values for RPrice and SAprice, but if necessary the prices should be able to be changed if the tech quoted somthing other than the default values. I have gotten this far just fine.

My problem is that I want to get the total (both RPrice and SAPrice of all of the parts selected and I want that to transfer over to a field on the main form(FrmDispatch.RecPartsTotal). I also want to be able to recall what parts were selected and what price the tech left. I am having difficulty just getting these prices to add up on the FrmParts sub-form. It seems it just wants to list the numbers one after another like I am adding text to a sentence. How am I supposed to be abled to get the SUM of these fields?

I also wonder how my tables should be set up for this. Should I be keeping a second table that links with the primary key(DispatchID) that has a field for all 26 parts and a field for the Rprice and SAPrice of each part selected? Ultimately (and I seem to have this down pretty well already), I want the program to auto generate the notes with the parts and both prices for however many parts the tech discussed with the customer. So when the notes are generating it would look something like, “Coil cleaning Reg. “ & RPRice1 & “ with SA “ & SAPrice1
Let me know if I have been too vague or if more info is needed. Thank You.
-Paul

[pbaldy]

It sounds like you have a normalization problem. Different parts should be records, not fields. This type of thing is normally done with two relevant tables; one for the main order info and a related table for the parts, where each part is a record. Visualize an invoice, where the name, address, date, etc is at the top and there's a line for each item purchased on the invoice. I'd have those 2 plus a table of the parts, prices, etc where each part is a record in that table. In your design when you get a 27th part you're going to have to change the design of everything. I'm going to add a record to my parts table...actually, the users will do that with a form I'd create to manage that, so I won't do a thing.

[AccessMSSQL]

In order to autogenerate the notes you need to do this with VBA function and DAO to loop through the selected parts in the table and concatenate the values you get out of the recordset into a string, then return the string. Do you use VBA at all? You can get the sum on your main form by also using VBA or creating a grouped query that gets the sum of the selected parts. Here is an example of VBA to concatenate your string:

Function GetParts() as string
Dim rs as dao.recordset
set rs = currentdb.openrecordset("select * from PartsSelected")
if not rs.eof then

[AccessMSSQL]

..sorry, accidentally hit send before finished..here it is again:

In order to autogenerate the notes you need to do this with VBA function and DAO to loop through the selected parts in the table and concatenate the values you get out of the recordset into a string, then return the string. Do you use VBA at all? You can get the sum on your main form by also using VBA or creating a grouped query that gets the sum of the selected parts. Here is an example of VBA to concatenate your string:

Function GetParts() as string
Dim rs as dao.recordset
set rs = currentdb.openrecordset("select * from PartsSelected")
if not rs.eof then

[AccessMSSQL]

let's try this one more time...
In order to autogenerate the notes you need to do this with VBA function and DAO to loop through the selected parts in the table and concatenate the values you get out of the recordset into a string, then return the string. Do you use VBA at all? You can get the sum on your main form by also using VBA or creating a grouped query that gets the sum of the selected parts. Here is an example of VBA to concatenate your string:

Function GetParts() as string
Dim rs as dao.recordset
Dim strText as string
strText = ""
set rs = currentdb.openrecordset("select * from PartsSelected WHERE PartSelected= True")
if not rs.eof then
do until rs.eof
strText = strText & rs("PartName") & " " & rs("RPPrice1") & " SAPrice " & rs("SAPrice1") & vbcrlf
rs.movnext
loop
end if
rs.close
set rs = nothing
getParts = strText
end Function