UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Variable Not Defined, Access 2010    
 
   
rizmin
post Oct 26 2017, 04:36 AM
Post#1



Posts: 120
Joined: 10-August 17



Hi UA Nation!

I'm doing a simple data updating on my ongoing project, once I try to execute the error has occurred as shown in the picture. I have used the below code FYR.
CODE
Private Sub Command2_Click()

Set rst = CurrentDb.OpenRecordset("tblComment", dbOpenSnapshot)
rst.FindFirst "Rec_Dept=""" & [Forms]![frmReportPage]![txtDept] & """ and Rec_Location=""" & [Forms]![frmReportPage]![txtLocation] & """ and Rec_Year=""" & [Forms]![frmReportPage]![txtYearEntry] & """"

If Not rst.NoMatch Then
Cancel = True
    
    Me.txtRec1.SetFocus
    
Else

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblComment]")
rs.AddNew


rs![Rec_Dept] = [Forms]![frmReportPage]![txtDept].Value
rs![Rec_Location] = [Forms]![frmReportPage]![txtLocation].Value
rs![Rec_Year] = [Forms]![frmReportPage]![txtYearEntry].Value
rs![Rec_Comment] = Me.txtRec1.Value
rs.Update

DoCmd.OpenReport "rptInternalAuditReport", acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdFitToWindow

rs.Close
Set rs = Nothing
End If
End Sub


Also I have defined the globle module as find function as below:

CODE
Function FindRecordCount(strSQL As String) As Long

Dim db As Database
Dim rstRecords As Recordset

Set db = CurrentDb
Set rstRecords = db.OpenRecordset(strSQL)

    If rstRecords.EOF Then
    FindRecordCount = 0
    Else
    rstRecords.MoveFirst
    FindRecordCount = rstRecords.RecordCount
    End If
    
rstRecords.Close
db.Close
Set rstRecords = Nothing
Set db = Nothing
End Function


Anyone can suggest me the solution.

Thank you all
Attached File(s)
Attached File  Capture.JPG ( 48.16K )Number of downloads: 2
 
Go to the top of the page
 
ngins
post Oct 26 2017, 04:55 AM
Post#2



Posts: 300
Joined: 18-August 05
From: DFW, TX, USA


The button click event does not have a Cancel variable. That's why you're getting the error.

Why are you setting Cancel = True in the first place. It makes no sense in a button click event.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
rizmin
post Oct 26 2017, 05:01 AM
Post#3



Posts: 120
Joined: 10-August 17



Hi ngins

Here I want to find the duplicate value,

If found the duplicate value for Dept, Year & Location I want to update the Rec_Comment with the new text otherwise I wanted to add as a new record.

Thank you
Go to the top of the page
 
rizmin
post Oct 26 2017, 05:38 AM
Post#4



Posts: 120
Joined: 10-August 17



Hi ngins,

I have removed and add the below code,

the database is not updating the duplicate value and its still adding as new, whats the wrong with my code please thank you.

CODE
Private Sub Command2_Click()

Set rst = CurrentDb.OpenRecordset("tblComment", dbOpenSnapshot)
rst.FindFirst "Rec_Dept=""" & [Forms]![frmReportPage]![txtDept] & """ & Rec_Location=""" & [Forms]![frmReportPage]![txtLocation] & """ & Rec_Year=""" & [Forms]![frmReportPage]![txtYearEntry] & """"

If rst.NoMatch = False Then

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblComment]")
rs.Edit
rs![Rec_Dept] = [Forms]![frmReportPage]![txtDept].Value
rs![Rec_Location] = [Forms]![frmReportPage]![txtLocation].Value
rs![Rec_Year] = [Forms]![frmReportPage]![txtYearEntry].Value
rs![Rec_Comment] = Me.txtRec1.Value
rs.Update
DoCmd.OpenReport "rptInternalAuditReport", acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdFitToWindow
rs.Close
Set rs = Nothing
Else

Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblComment]")
rs.AddNew


rs![Rec_Dept] = [Forms]![frmReportPage]![txtDept].Value
rs![Rec_Location] = [Forms]![frmReportPage]![txtLocation].Value
rs![Rec_Year] = [Forms]![frmReportPage]![txtYearEntry].Value
rs![Rec_Comment] = Me.txtRec1.Value
rs.Update

DoCmd.OpenReport "rptInternalAuditReport", acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdFitToWindow
'DoCmd.Close acForm, "frmRecommendation", acSaveYes
rs.Close
Set rs = Nothing
End If
End Sub


I need if the Year, Location & Dept is existing already the Rec_Comment to updated the records without duplicating.

THank you again
Attached File(s)
Attached File  Capture.JPG ( 20.1K )Number of downloads: 0
 
Go to the top of the page
 
orange999
post Oct 26 2017, 06:53 AM
Post#5



Posts: 1,714
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I recommend making a unique composite index of the fields you do not want to be duplicated in tblComments.

--------------------
Good luck with your project!
Go to the top of the page
 
BruceM
post Oct 26 2017, 08:24 AM
Post#6


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I agree about the unique composite index, but have a few other points too.

First, declare all variables. Add Option Explicit to the top of the code module, below Option Compare Database to enforce this. More in Method 1, here.

There is no difference of which I am aware between OpenRecordset("tblComment") and OpenRecordset("SELECT * FROM tblComment). Why not just open the recordset once?

If Rec_Year is a number, don't use quotes. If a number it will never be "2017", but it may be 2017. I expect NoMatch is always True, which means to add a new record. I have assigned the search string to a variable in the code below, and added Debug.Print. See the link above for more information about Debug.Print. My preference is to use If rst.NoMatch and Else, rather than adding = False to the NoMatch. You are going to test for both anyhow. But it's up to you.

Since you are not specifying a record, your Edit will take place on the first record in the table, by whatever default sort the table is using. However, as mentioned above, most likely there is never a match for the search string, so this never happens.

Is your form (where the code resides) based on tblComment, or a query based on tblComment? If so, the procedure could be simplified.

Since you are using [Forms]![frmReportPage]![txtDept] in several places it may be convenient to assign it to a variable. Maybe something like the following untested code.
CODE
Dim db      As DAO.Database
Dim rs      As DAO.Recordset
Dim strDept As String
Dim strLoc  As String
Dim lngYear As Long
Dim strFind As String

Set db = CurrentDb
Set rst = db.OpenRecordset("tblComment")

strDept = [Forms]![frmReportPage]![txtDept]
strLoc = [Forms]![frmReportPage]![txtLocation]
lngYear = [Forms]![frmReportPage]![txtYearEntry]

strFind =  "Rec_Dept=""" & strDept & """ & Rec_Location=""" & strLoc & """ & Rec_Year=" & lngYear
Debug.Print strFind

rst.FindFirst strFind

If rst.NoMatch = False Then

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [tblComment] WHERE " & strFind)

With rst
   .Edit
   ![Rec_Dept] = txtDept
   ![Rec_Location] = txtLoc
   ![Rec_Year] = lngYear
   ![Rec_Comment] = Me.txtRec1
   .Update
End With
' etc.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:26 PM