How to Initialize a new continuous form? 1

[jetspin]

Hi

I have been lookin for how to initialize values on a new continuous form? I have a continuous form which list all recs where the rec source is base on a query for all recs
of a "many' side of a one to many relation ship, but if a user adds a new form of that continuous form,,, it loses the key to the "one" side of the relationship. Is a subform the only answer? Thanks.

 

[TheAceMan1]

How are ya jetspin . . . . .

Add the table for the one side to the query. You don't need to add any fields from the one side. You just need the table in the query. (Your current query has no reference to the one side!)

TheAceMan

 

[jetspin]

Hi AceMan,

Thanks for the reply. I tried that. I ensured the "one" table was in the query view with none of its fields in the actual query fields but get "You cannot add or change a record because a related record is required in tblItems. (this is my one table). Perhaps I was not clear in
my explanation,, Heres a very brief description of tables, forms and my query. Thanks very much...if anyone can point me in the right direction...

TABLES...
tblItems
itmItmID PK
itmName

tblTransactions
trnTrnID PK
trnItmID FK from tblItmID
trnDate

Many tblTransactions can belong to One tblItem.

FORMS...
I Have a continuous forms for each table.
frmItems frmTransactions

On frmItems it 'lists' all Items. Each individual
'form' has a "TX" button which opens the
frmTransactions form to list all transactions for that item.

The onclick event for the TX button is
stDocName = "frmTransactions"
stLinkCriteria = "[trnItmID]=" & Me![itmItmID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

This works great, but when I click on the navigator to add
a new tblTransaction on the frmTransaction.... I get errors
as described.

My query record source for frmTransactions is...

SELECT tblTransactions.trnTrnID,
tblTransactions.trnItmID,
tblTransactions.trnDate,
FROM tbItems INNER JOIN tblTransactions ON tbItems.itmItmID = tblTransactions.trnItmID;

Thanks again.

 

[TheAceMan1]

OK jetspin . . . . . . . sorry to get back so late . . . . .

No, a subform is not the only answer.

For clarity, in one place you say "if a user adds a new form of that continuous form", and in another "Each individual 'form' has a "TX". I believe your refering to records here?

Your problem is simply that your not telling tblTransactions which Item you want to relate the new record with! Manually, all you have to do is type in the trnItmID for the related itmItmID that you want. But this is a pain, besides the fact, who can remember! So lets fix it! . . . . All of it! (Don't forget to perform a backUp before you make any changes!)

First, as a preliminary, do the following:

In any module, click Tools-References. Your looking to make sure Microsoft DAO 3.6 Object Library is checked and pushed as high in priority as it will go. Next add the following code to a module in the modules window.

Function IsOpenFrm(frmName As String) As Boolean
   Dim cp As CurrentProject, Frms As Object
   
   Set cp = CurrentProject()
   Set Frms = cp.AllForms
   
   If Frms.Item(frmName).IsLoaded Then IsOpenFrm = True
   
   Set Frms = Nothing
   Set cp = Nothing

End Function

Public Function uMsg(Msg as String,Style as Integer,Title as String) As Integer
   Dim DQ as String
   DQ=Chr(34)
   Beep
   uMsg = Eval("MsgBox(" & DQ & Msg & DQ & "," & Style & "," & DQ & Title & DQ & ")")

End Function

Next, in the AfterUpdate Event for trnDate add the following code (this takes care of assigning the related itmItmID and resolves adding a new record):

Dim frm As Form
   Set frm = Forms!frmItems
   Me!trnItmID = frm!itmItmID
   Set frm = Nothing

Next, were going to move the criteria from the TX code to the query. The MS Jet is much more efficient when used with query, espcially where a large number of records are involved. So in query design view of your frmTransactions query, add the following line in the criteria row for trnItmID.

[Forms]![frmItems]![itmItmID]

The SQL should now be:

SELECT tbltransactions.trnTrnID,
        tbltransactions.trnItmID, 
        tbltransactions.trnDate
FROM tblItems
INNER JOIN tbltransactions
ON tblItems.itmItmID = tbltransactions.trnItmID
WHERE (((tbltransactions.trnItmID)=[Forms]![frmItems]![itmItmID]));

Next, were going to modify the code in your TX button. Two reasons are necessary here. One, an error will occur when you click the TX button for a new record. This is because no itmItmID exist. Two, we've moved the criteria to the SQL and the Transactions form can now be 'Required' which is much faster than the DoCmd. As such, wether or not the Transactions form is open needs to be detected. With that, replace your TX code with the following:

Dim stDocName As String, DL As String
   Dim Msg As String, Style As Integer, Title As String
   
   DL = vbNewLine & vbNewLine
   stDocName = "frmTransactions"
   
   If Me.NewRecord Then
      Msg = "Cannot Perform Operation!" & DL & _
            "No itmItmID Available!" & _
            "@Not Allowed With New Record!" & _
            "@Select a Record With Data!"
      Style = vbInformation + vbOKOnly
      Title = "Improper Operation Error! . . . . ."
      Call uMsg(Msg, Style, Title)
   ElseIf IsOpenFrm(stDocName) Then
      Forms(stDocName).Requery
   Else
      DoCmd.OpenForm stDocName
   End If

Note the criteria and its associated variable are gone in the code . . . . . .

Next, as an option, I recommend you move the code for your TX button to the DoubleClick Event for itmName, and get rid of the button. If you have many records (by your explaination the button appears to be in the Detail Section), the button will seriously degrade performance. I would also add a tooltip to itmName to prompt operation.

And thats it! . . . . . . . Give it a whirl and let me know how ya make out! ;-)











TheAceMan

 

[jetspin]

AceMan,

Wow. Thanks. That is a lot of information and more! I am in the middle of going the subform route as I could not resolve this issue for several days, BUT there is a lot of very good info here can still use if I go back to this method and for the future. Even the note about double clicking on test instead of a button is helpful! Thanks again.

 

[TheAceMan1]

OK jetspin . . . . .

I detect your having problems with the subform. This is mainly because the Form Wizard does not allow the linking of two continuous forms. Besides, it should have taken only 'minutes' to get it up & runing. Bear in mind you do not have to settle with one form being single!

Let me know if ya need a hand here!

TheAceMan

 

[jetspin]

Hi.

Yes I made 3 forms...but have a problem with setting up
totals on the 2nd form... This is traditional item, qty, value tx's from which I want to set up extended values
on each tx as well as for the item.

The 3 forms are...

1) frmItems which is one continuous form of the 'items' from which to select and click on an 'Item' to bring up its transactions, the next form...

2) frmItemTransactions form which is a single form that contains a subform of transactions for that item,, the next form...

3) frmTransactions which is the subform in datasheet view of the transactions link to the above form by item.

You must have ESP, because I've 'hit a wall' about showing totals on the frmTransactions subform from which to reference on the frmItemTranasactions form to show total
qty for all the tx's on frmTransactions.

From a book I have, one sets up a text field on the subform with a sum() function. Then one sets up a text field on the from that holds the subform which simply has =nameofthesubformtextfieldthathasthesumfunction for example.

I was able to to do this and prove it while viewing the subform in form view as well as get the value to the frmItemTransactions that contains the subform.

BUT as soon as I add a second text field with another =sum function to total another tx value on the subform,,, I get #Error in both text fields.... I don't understand why adding a second field would cause this error on the first already working text field. It must be something obvious. I've tried re-creating all from scratch with same problem. Can one have more than one text field on a subform with a sum() function?

Anyway... thanks greatly for you excellent feedback.

Jetspin2

 

[TheAceMan1]

jetspin . . . . . . .

Forgive my ignorance :-(, but you said:

This is traditional item, qty, value tx's from which I want to set up extended values on each tx as well as for the item.

What are tx's?
Be specific about extended values?

You said:

I've 'hit a wall' about showing totals on the frmTransactions subform from which to reference on the frmItemTranasactions form to show total qty for all the tx's on frmTransactions.

I'm not sure at all what you want to do here. Could you be a little more specific and to the point. Taking into account the present depth of this thread, we don't wanna get into any communications problem, so keep it short and simple if you can.

Also, since you've changed you form schema, you need to supply the form names, PK's, FK's, relationships and fields of interest like you did before.

TheAceMan

 

[jetspin]

Hi AceMan,

Hi. Thanks for your patience. Sorry. I started using new terms. Its the same schema and I am using a subform of tblTransactions and I've added additional quantity and dollar value fields. First I'll try to explain exactly my issue and then follow with a more detailed overview if needed....

By "Extended Value', I mean 'Quantity' * 'Dollar' = 'Extended Value'. In my subform I have bound fields to quantity and dollar and a computed text field that computes the extended value. On the subform I have these fields...

trnQty bound to tblTransactions
trnDollar bound to tblTransactions
=[trnQty]*[trnDollar] Name of this field is txtExtValue

I have a 2 text fields at the bottom of my subform. I use the 'single form' view to design these 2 text fields to produce totals. They have these functions...

=sum(trnqty) =sum(txtExtValue)

If I just use the '=sum(trnqty)' total text field it works!
As soon as I add the second total text field with '=sum(txtExtValue)' I get #Error in both of these total text fields?

So I think the question is can one use the sum() funtion on a computed text field? That seems to cause the issue. Do I have to use an additonal function to convert the computed value to numeric?

Here is an overview if needed..................

Thanks again for any info.
I've tried several approaches inlcluding re-creating eveything with no luck.

TABLES...

tblItems (Items)
itmItmID PK
itmName

tblTransactions (Transactions)
trnTrnID PK
trnItmID FK from tblItmID
trnDate
trnQty
trnDollar

Many tblTransactions can belong to tblItems.

HERE ARE THE FORMS...

frmItems.
This is a continuous form of tblItems.
Bound fields on frmItems are itmItmID and itmName.
A User can select an itmName by clicking on button (I know I can also use a double click event) which brings up frmItemTransactins
to list all (Transactions) for that (Item).

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmItemTransactions"
stLinkCriteria = "[itmItmID]=" & Me![itmItmID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

======================================

frmItemTransactions.
This is a single form with a subform on it.
A text field at top of frmItemTransactions is bound to itmName to show the item name selected.
The subform is frmsubTransactions and list all the (Transactions) for the (Item)

Source Object frmsubTransactions
Link Child Fields trnItmID
Link Master Fields itmItmID

A text field at bottom of frmItemTransactins is txtItemTotalQty with source = [Item Transactions].Form!txtTotalQty

===========================================

frmsubTransactions. This is the subform which displays as a datasheeet.
It has all the tblTransaction these fields bound on it and computed text field.
itnTrnID
trnDate
trnQty
trnDollar
=[trnDollar]*[trnQty] < This is 'Extended Value' and is named 'txtExtValue'

A text field at the bottom of field when viewed as single form is named txtTotalqty and has source =Sum([trnQty])
THe caption of this form is &quot;Item Transactions&quot;.

=============================================

So the text field on the subform uses the sum() funtion to total the quantities of the (Transactions) on the subform.
A text field on the form that hold the subform is simply set to = the value of the text field on the subform. This works for quantity and the Extended Value also is computed on the subform. This is all fine.
=============================================

My issue is when I try to total the computed 'Extended Value' field in the same manner that I did the trnQty on the subform, I set the text name to txtTotalValue and its source =Sum([txtExtValue]). When I do this I get #Error in both text fields that have the sum() function txtTotalqty and txtTotalValue. I don't know why this affects the first
text field that totals the trnqty that was working? Maybe this is a clue? I tested by using =sum(trnqty) in both total text fields and that works.

I think it is something to do with using the sum() function on a text field that has a computed value...

Thanks. I hope this is specific enough.

 

[TheAceMan1]

jetspin . . . . Almost There . . . .

I see some abbiguities already, but for clarity,what is the RecordSource for frmItemTransactions? If its a query, post it.

TheAceMan

 

[jetspin]

Hi TheAceMan.

Record Soruce for frmItemTransactions is tbItems

 

[TheAceMan1]

gotcha jetspin . . . . .

I won't be able to eork on it till later this evening.

Look for a post tomorrow about 10ish . . . . .

TheAceMan

 

[TheAceMan1]

OK jetspin . . . . here ya go! . . . .

Your query as to the use of the Sum() function is correct.

The problem is that the Sum Function was not designed to work on a calculated Textbox control. It in fact was designed as part of a group of aggregate functions to provide statistical results from data sets. Those sets specifically involve work on fields in a table or query, or the results of a Record Source based on such. During calculations your =Sum(txtExtValue) expression is calculated before =Sum(trnQty) and raises an error which causes calculations to halt. When this happens the MS Jet fills all other calculated fields not in the Detail Section with #Error.

To fix this, your gonna have to move =Sum(txtExtValue) to a field in a Table or Query. So lets do that . . . . .

If the subform Record Source is not based on a query, make it so.

Next, get the query in Design View. In the Field Row of an empty field (top line in design view), copy & paste the following:

txtExtValue:trnQty*trnDollar

Save the query.

Go back to your txtExtValue textbox, and in the Control Source dropdown list, select txtExtValue. Check that the Name Property is retained in the Other Tab, if not, make it so.

This should fix the problem . . . . . . . ;-)

TheAceMan

 

[TheAceMan1]

jetspin . . . .

I made an error (don't believe it) in my previous post.

I said:

To fix this, your gonna have to move =Sum(txtExtValue) to a field in a Table or Query.

Should be:

To fix this, your gonna have to move =trnQty*trnDollar to a field in a Table or Query.

TheAceMan

 

[jetspin]

TheAceMan...

That worked. I put the computed value in the query. Thanks for your super patience and tips along the way on this one.!

jetspin