Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help stop table updating

Status
Not open for further replies.

NeilT123

Technical User
Jan 6, 2005
302
GB
I have a subform which I use for data entry and editing. There are two tables which are potentially updated and the query behind the form is:
Code:
SELECT tblSoilAnalysisResults.AnalysisNumber, tblSoilAnalysisResults.FieldCode, tblSoilAnalysisResults.DateOfAnalysis, tblSoilAnalysisResults.LabRef, tblSoilAnalysisResults.pH, tblSoilAnalysisResults.[Pmg/l], tblSoilAnalysisResults.PIndex, tblSoilAnalysisResults.[Kmg/l], tblSoilAnalysisResults.KIndex, tblSoilAnalysisResults.[Mgmg/l], tblSoilAnalysisResults.MgIndex, tblSoilAnalysisResults.Analysiscomments, tblSoilAnalysisResults.[%Sand], tblSoilAnalysisResults.[%Silt], tblSoilAnalysisResults.[%Clay], tblSoilAnalysisResults.LabTestTexturalClassification, tblSoilAnalysisResults.LabTestSoilType, tblSoilAnalysisResults.UseResinNutBal, tblSoilAnalysisResults.TraceElementAnalysis, tblSoilAnalysisResults.SoylMapped, tblLimeRequirements.AnalysisNumber, tblLimeRequirements.FieldCode AS FieldCodeLR, tblLimeRequirements.DateTested, tblLimeRequirements.pH, tblLimeRequirements.[LimeRequiredT/Ha], tblLimeRequirements.SourceOfRecommendation, tblLimeRequirements.RequirementNotes, tblLimeRequirements.LabRef
FROM (tblTEMPFieldList INNER JOIN tblSoilAnalysisResults ON tblTEMPFieldList.FieldCode = tblSoilAnalysisResults.FieldCode) LEFT JOIN tblLimeRequirements ON tblSoilAnalysisResults.AnalysisNumber = tblLimeRequirements.AnalysisNumber
ORDER BY tblSoilAnalysisResults.DateOfAnalysis DESC;

When I am entering data via this form there will always be a Soil Analysis Result and that will update Table tblSoilAnalysisResults. Sometimes there will be a Lime Requirement and that will update tblLimeRequirements using this code behind one of the buttons:
Code:
Private Sub LimeRequiredT_Ha_AfterUpdate()
' sub 2 and Me.DateTested are in the lime required section
Me.DateTested = Me.DateOfAnalysis
Me.pH2 = Me.pH
Me.FieldCode2 = Parent!FieldCode
Me.AnalysisNumber2 = Me.AnalysisNumber
Me.LabRef2 = Me.LabRef
SourceOfRecommendation.SetFocus
End Sub

The form works as I want it, except for one particular scenario. If I have entered a Soil Analysis Result but there is no Lime Requirement and I copy the complete line of data from the form and paste it as a new record then I get a new blank record in the table tblLimeRequirements which I don’t want. Does anyone have any suggestions as to how I can stop this happening please.
Thank you in advance for any suggestions.
 
What does the code for inserting the record look like? That is possibly the bit that is inserting the blank record.
 
You mention that you have ONE sub-form, but TWO sub-tables.

Are both on the same sub-form?
If so, what is the sub-form container parent / child relationship?

If you expect individual behaviour for each sub-table, I'd expect 2 sub-forms - one for each table.
I'm also intrigued about it behaving differently - simply due to pasting data as opposed to typing it.

(I have a sneaking suspicion that you have some code in a <On_Click>, <On_Change> event of a text box, (which doesn't fire if you paste and then immediately click a 'save' button)).

Def
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top