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.
Code:
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):
Code:
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:
Code:
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:
Code:
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
![[wiggle]](/data/assets/smilies/wiggle.gif "[wiggle] [wiggle]")
