Need suggestion or help please

[KLynch0803]

I have a database that I'm calculating maerials on for projects to do quotes..



My issue is I have a primary form "frmQuoteProject" that has several fields on it but we will us the Linear Foot field for the example "txtLinFt" and a query that builds a raw materials list. I need this query to calculate the qty needed of each item within the query but each items calculation is different therefore I cant put a simple equation into one query..

Is there a simpler way to do this rather than creating a query for each item and then bringing them all into one query to display? I'm concerned about run time.. Or can I make a subform with the items in it in text boxes and use a text box to pull a particular price out of a table "tblItemList" like row6 (PrimKey "6") column 4 automatically for that particular text box?

Or posibly place the matmatical equation in the "tblItemList" and draw that into the query for calculations?

Any suggestions or help with code would be greatly appreciatted.. I know there is a simple solution but I have been racking my brain for two days to decide what is simpler and faster..

SOLUTION:!!!!

DLOOKUP Function

=DLookUp("[rawcost]","[tblitemlist]","[id]=" & "3")

[RuralGuy]

I don't think you have given us specific enough data to make a responsible suggestion yet. Maybe if you give us some more of the details of the quotes and the other fields?

[KLynch0803]

I don't think you have given us specific enough data to make a responsible suggestion yet. Maybe if you give us some more of the details of the quotes and the other fields?

I have attached my db to possibly help with the issue... I hae two concepts you can maybe help me with the easier way of the two??

Here are some more details rather long...

Concept 1:

If you look at the table "tblItemList" I have several fields for a quote setup..

I have a query based on the table named "qryItemList6ftprivacy" that gathers all items from the table that are related to 6ft Privacy fence quotes and calculates the cost *2. The issue is I need it to gather the actual qty needed as well.. What I was thinking was to use the form "frmQuoteProject" and the text field "feet" to get the number to do the calculations from..

Of course after I get the query to work I will embed it as a subform on the form "frmquoteproject" but for testing purposes I just open that form enter the Linear Feet (200) and then open the query "qryItemList6ftprivacy"...

Here is the calculations I need the query to do (some are from results of another item within the query and they all always round up to the highest number..

item 4x4x8 = [Linear Feet]/8+1
item 2x4x8 = result of [4x4x8]*3+1
item concrete = [4x4x8]/2
item 6'dogearpickets = [linearfeet]*12/5.5

There are other items in the query but they are based on results only if the boxes have a value...

Concept 2:

It may be simpler to use my subform "subfrm6ftprivacy" and add a text field on it beside 4x4x8 and just link it to the table value for the cost for example Line with [ID] 2 [item] "4x4x8" and the [Rawcost] $5.97... This way when the table is edited for the cost it will reflect in the form when its opened. Therefore when you open "frmquoteproject" and enter linear foot select wood and 6 for the height it would display the subform "subfrm6ftprivacy" and the quote price and qty's...

[KLynch0803]

I am building a db that has a form with a subform that has data on it as well that Im trying to save the records to seperate tables that have relationships.. I have the code writing from the main form to the proper table correctly, but I'm having trouble getting the data from the subform...

I know I'm just having brain loss temporarily can someone give me a kickstart again... I have highlighted the area I need the correct code in red...

Thanks for any help..

 
Private Sub savequote_Click()
On Error GoTo Err_savequote_Click
Dim strsql As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblcontactlist", dbOpenDynaset)
    With rst
        .AddNew
        !LastName = LastName.Value
        !FirstName = FirstName.Value
        !Address = Address.Value
        !City = City.Value
        !State = State.Value
        !ZIP = ZIP.Value
        !HomePhone = HomePhone.Value
        !MobilePhone = MobilePhone.Value
        !Notes = Notes.Value
        !Salesman = Salesman.Value
        !customer = "1"
        .Update
    End With
    
    Set rst = Nothing
    
Set rst = db.OpenRecordset("tblquotes", dbOpenDynaset)
    With rst
        .AddNew
        !qty = I want to access subform "subfrm6ftprivacy" text box "posts"
        !cost = postcost.Value
        !saleprice = postsale.Value
        !Item = "4x4x8"
        .Update
    End With
    Set db = Nothing: Set rst = Nothing
    
    Me.LastName = ""
    Me.FirstName = ""
    Me.Address = ""
    Me.City = ""
    Me.State = ""
    Me.ZIP = ""
    Me.HomePhone = ""
    Me.MobilePhone = ""
    Me.Notes = ""
    Me.Salesman = ""
    
    Salesman.SetFocus
Exit_savequote_Click:
    Exit Sub
Err_savequote_Click:
    MsgBox Err.Description
    Resume Exit_savequote_Click

End Sub

[RuralGuy]

Are your forms bound. If so then none of this code is necessary. Access will do all of the work for you. If the forms are not bound, then why not?

[KLynch0803]

Are your forms bound. If so then none of this code is necessary. Access will do all of the work for you. If the forms are not bound, then why not?

I have the primary form bound to the contact table, and sub form bound to the items table.. but the tables I'm trying to write to are two different tables completely.. I assume thats why I need this code to write to the proper tables.. This is also how I was taught to do this at Ross Stores LOL.. Go Figure..

[RuralGuy]

Data should *not* be duplicated in an RDBMS. What you are doing is breaking that primary rule. It is OK to have Keys scattered throughout your tables but the actual data is *only* in one table. As I said earlier, Access will do all of the heavy lifting for you if you simply work with properly bound forms.

[KLynch0803]

Data should *not* be duplicated in an RDBMS. What you are doing is breaking that primary rule. It is OK to have Keys scattered throughout your tables but the actual data is *only* in one table. As I said earlier, Access will do all of the heavy lifting for you if you simply work with properly bound forms.

Would u take a look at this and how I have it setup and give me a short lesson please lol...

[RuralGuy]

In order to describe and form a relationship in tables you need to use PrimaryKeys (PK) and ForeignKeys (FK). If you put a PK (no duplicates allowed) of one table in another table as a FK then you will have a 1:m relationship assuming you leave the FK as Duplicates Allowed. It is best to use the same name for these two keys as it is easier to understand and maintain later on.

[KLynch0803]

In order to describe and form a relationship in tables you need to use PrimaryKeys (PK) and ForeignKeys (FK). If you put a PK (no duplicates allowed) of one table in another table as a FK then you will have a 1:m relationship assuming you leave the FK as Duplicates Allowed. It is best to use the same name for these two keys as it is easier to understand and maintain later on.

I have modified my db to create the 1:m relationship as I understood your expanation and I have also modified my form "frm6ftfencequote" to be a single form and related code...

Im still not getting the result of records entered into the correct tables and the relationship between them... What is wrong I'm just not getting it or not doing the right thing....? Please modify and explain what was wrong if you would please sir...

[RuralGuy]

I rearranged and renamed some of your fields and the relationships. You may wish to rethink your design here. I would think you would bind a Quote form to a Quote table/query but you have it bound to a Contact table/query. Strange.

[KLynch0803]

I rearranged and renamed some of your fields and the relationships. You may wish to rethink your design here. I would think you would bind a Quote form to a Quote table/query but you have it bound to a Contact table/query. Strange.

RuralGuy,

I'm really rusty at this aparently its been 3 years since I did this and I'm novice user for sure lol... I have built some awesome stuff for my old employer in the past that was very very advanced but I just cant get this kick started aparently I'm rustier than I thought..

I think you have a very good idea of what I'm trying to accomplish by creating an items table & a contact table, quote table that are bound.. The contact table will contain one contact record for each customer and the quote table will contain all items that are required to do the job and specific pricing etc as you see my calculations on the form you edited.. I can get this (even with your last edit) to enter customer data and one of the items to the quote table but not several and they be tied to the same customer.. Example being: one contact and five items linked to it in the quote table.

If you or someone gets time to maybe help me out a little and chop up my database some and restructure it to help me out I can buld upon it.. I know it sounds like I'm asking someone to do this for me but as detailed as this is going to be its really just a solid foundation (structure) and refresher for me to get it kicked off instead of fighting a losing battle I seem to be in..

Thank You RuralGuy for your past and future help I know it gets fustrating with us noobs/novices but ur help is appreciatted...

[RuralGuy]

We usually display and edit a 1:m relationship in a MainForm/Subform arrangement with the SubForm in Datasheet or Continuous form mode for the many side of the relationship.

[KLynch0803]

We usually display and edit a 1:m relationship in a MainForm/Subform arrangement with the SubForm in Datasheet or Continuous form mode for the many side of the relationship.

But it is difficult in my eyes to build a subform in datasheet mode if im using unbound textboxes and not a query... If I build my subform from a query it wont do my calculations on an item by item basis as each item has different equations to calculate the results.. when i did subform originally and used rsm to insert into many table i couldnt figure out or get answer on how to insert the records into the table.. Therefore I went to a single form and tried insertion into tables.. Geez now I'm really confused on what to do...

[RuralGuy]

I am not suggesting unbound forms. Calculations can be done in queries if you want.