Calculated fields in forms reading back to a table.

[Nosdron]

I have a calculated field "Length" on a form I need the calculated field to read back to a field "Booth Length" on a table tblquotation and another table "Booth Dimensions". Please help.

Angela

 

[mincefish]

I have a calculated field "Length" on a form I need the calculated field to read back to a field "Booth Length" on a table tblquotation and another table "Booth Dimensions". Please help.

So Let me get this straight:
1) You have a field called Length, on a form, and this is calculated. What is it calculated from? Other fields on the form?
2) When you say you want to read back to a field called Booth Length, do you mean you want to enter the information in field "Length", into the field "Booth Length" on tblQuotation? And also written back into "Booth Dimensions"? - what field in booth dimensions?

Please give me more information, and I'll try to help you!

Mincefish

 

[Nosdron]

1) Yes it is calculated from other calculated fields on the form.

2)In a table Booth dimensions I will have an empty field called "Booth Length" an for each record on the form there will be a Booth Length calculated and for each one of these records I want the booth length calculated field to read back to the booth dimensions table with the value of the calculated field Booth Length from the form.

Hope I'm making sense.

Angela

 

[mincefish]

So if I try to describe what I'm thinking of doing, you let me know if its the right thing, and then we can work on the code.

Firstly - how many records are being shown on the form? Is it one record at a time?

Do you show a ID field from the Booth Dimensions on this form? If you do then we can open up just that record in a recordset, and then write the value of the booth length (from the form) into the field Booth Length field.

Whadda ya think?!

Mincefish

 

[Nosdron]

I am trying to convert my Excel quotation package to access. The form is where all the sales people will enter there needs and all the calculations are calculated on this form.

So the form in question is individual to each quote number so therefore on one record at a time on the form.

I have not yet set up the booth dimension table because of the underlying problem. So no I donnot have an ID field from the table on the form but is there anything stopping me setting this up at this stage.

That is create the table and have a ID field hmm but how would I link this then to the form. With a Dlookup function ?

I dont know.

Angela

 

[mincefish]

Presumably your booth dimension table will have a quote id in it, as each booth dimension will be linked to a quote on tblQuotation right?

 

[Nosdron]

Yes, I could set up that relationship. You are correct.

 

[mincefish]

Well in that case, you need to either have a query, or I'd suggest some code to update that value.

This assumes you are using DAO, in Access97, and that you have a field in Booth Dimensions table called QuoteID, which holds the Quote Number. Also a field called Booth Length in the Booth Dimensions, which we will write the booth length into. Make sense, or have I not understood your problem?

Function fncUpdateBoothDimension() as boolean
On Error Resume Next
Dim rstBooth as Recordset
Dim strSQL as String

strSQL = "SELECT * FROM [Booth Dimensions] WHERE QuoteID = " & me.txtQuoteID.Value 

Set rstBooth = Currentdb.OpenRecordset(strSQL)

If rstBooth.BOF = True Then
    MsgBox "Cannot Find This Quote ID to update Booth Dimensions"
    fncUpdateBoothDimension = False
    Exit Function
Endif

rstBooth.Edit
rstBooth![Booth Length] = Me.txtBoothLength.Value
rstBooth.Update

rstBooth.close

fncUpdateBoothDimension = True

Err_Exit:
    Exit Function

Err_Func:
    fncUpdateBoothDimension = True    
    Goto Err_Exit

End Function

 

[Nosdron]

I have now set up a seperate table Booth Dimensions with a field Quote Number which I have set up a relationship in the relationship window with quote number in the QuoteGeneral form where the booth length is calculated.

Also in the table Booth dimensions I have set up a field Booth length.

I have entered your code but firstly have encountered the following problems and wonder if you can help:

1)In you Select statement you refer to the Booth dimensions table not the form with the info in is this correct ? (Where and how do we refer to the form ?)

2)Where you have rstBooth.Edit it will ony let me have rstBooth.EditMode and I dont know why ?

3)When I enter info on the form and go to next record the Booth dimensions table is still empty.

I do not know if I have a problem with the code and if I have set the relationship between the two tables correctly!!!

tblBoothDimensions QuoteID feld is linked to QuoteID field in the quotegeneral table. The quote general table is linked to the form frmQuotegeneral which has the calculated field BoothLength but this field is not linked to the table quote general.

Please help

Angela

 

[mincefish]

1)In you Select statement you refer to the Booth dimensions table not the form with the info in is this correct ? (Where and how do we refer to the form ?)

I'm refering to the form using

me.txtQuoteID.Value

- This means that the select statement only pulls out the record from Booth Dimensions which matches the record you are currently on. This select statement is merely opening the table up on the correct record so we can write the values back in

2)Where you have rstBooth.Edit it will ony let me have rstBooth.EditMode and I dont know why ?

Erm....are you using Access 2000? If you are then you have ADO, and this was written with DAO. Try commenting that line out.

When I enter info on the form and go to next record the Booth dimensions table is still empty

This function needs to be called from somewhere. You could put a button on the form, that when clicked, will do:

Call fncUpdateBoothDimension

It depends what works for you

Let me know if this helps, and if you have any more questions.

 

[Nosdron]

I have put a command button on the form to excecute the function but when I click I get an error message

Compile Error:

Invalid qualifier and it is highlighting fncUpdateBoothDimension in the code.

 

[CosmoKramer]

Two cents:

It's not good practice to store calculated values in your tables. If the underlying values change, the calculated one will not automatically be updated. Let the value be recalculated each time the record is accessed by the form/report etc.

The only time a calculated value should be stored is for historical purposes, and then you have to design properly to accomodate it......