Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 19th, 2008, 06:48 PM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default Drop Changes or Copy to clipboard

I have a database that is Access FE and SQL Server BE.

Once the user has input all the relevant data into the Combo & TextBox's I
have a submit cmdButton which will then update the relevant recordset in the
table. My problem is that once this has been done and the User clicks Add New
record, after inputting a PatientID there is a pop up box stating
"Write Conflict"
"This recod has been changed by another user since you started editing it. If
you save the record, you will overwrite the changes the other user has made.
Copying the changes to the clipboard will let you look at the values the
other user has entered, and then paste your changes back in if you decide to
make changes"
Copy to Clipboard or Drop Changes

How can I make sure that when a new record is created that it will be not
faced with this message?

below are my AddNew Records & Submit Code

Private Sub cmdAddNew_Click()
Dim sQRY As String
Dim varInput As String
Dim varNewID As Integer
'**************************************
   varInput = InputBox("Enter NHS Number", "Add new visit")
   If varInput = "" Then Exit Sub
'**************************************
   DoCmd.RunSQL "INSERT INTO jez_SWM_Visits (NHSNo) " & _
       "VALUES ('" & varInput & "')"
   varNewID = DLookup("max(VisitID)", "jez_SWM_Visits")
'**************************************
   Me.RecordSource = "SELECT jez_SWM_Visits.* FROM jez_SWM_Visits WHERE " &
_
       "jez_SWM_Visits.VisitID = " & varNewID & " "
'**************************************
   Call UnLockAll
   Me.txtNHSNo.Value = varInput
   Me.txtForename.SetFocus
End Sub

Private Sub cmdSubmit_Click()
Dim varResponse As Variant
Dim sQRY As String
Dim rs As DAO.Recordset
Dim intNHSNo As String
'**************************************
' On Error GoTo Err
   varResponse = MsgBox("Save Changes?", vbYesNo, cApplicationName)
       If varResponse = vbNo Then
           Me.Undo
               Exit Sub
       End If
'**************************************
               sQRY = "UPDATE jez_SWM_Visits " & _
                           "SET [NHSNo] = '" & Me.txtNHSNo & "', [Surname] =
'" & Me.txtSurname & "', [Forename] = '" & Me.txtForename & "', [Gender] = '"
& Me.cboGender & "', [Address1] = '" & _
                           Me.txtAddress1 & "', [Address2] = '" & Me.
txtAddress2 & "', [Address3] = '" & Me.txtAddress3 & "', [Postcode] = '" & Me.
txtPostcode & "', [Telephone] = '" & _
                           Me.txtTelephone & "', [DateOfBirth] = '" & Me.
txtDOB & "', [ReferralReasonDescription] = '" & Me.cboReferralRsn & "',
[SourceDescription] = '" & _
                           Me.cboReferralSource & "', [DateOfReferral] = '"
& Me.txtReferralDate & "', [VisitDate] = '" & Me.txtVisitDate & "',
[OpenorClosed] = '" & Me.chkFinalVist & "'," & _
                           "[Weight] = '" & Me.txtWeight & "', [Height] = '"
& Me.txtHeight & "', [BMI] = '" & Me.txtBMI & "', [BloodPressure] = '" & Me.
txtBlood & "', [ExerciseLevel] = '" & _
                           Me.txtExercise & "', [DietLevel] = '" & Me.
txtDiet & "', [SelfEsteem] = '" & Me.txtSelf & "', [WaistSize] = '" & Me.
txtWaist & "', [Comments] = '" & _
                           Me.txtComments & "', [SessionType] = '" & Me.
cboSessionType & "', [NHSStaffName] = '" & Me.txtStaffName & "', [Arrived] =
'" & Me.cboAttendance & "', " & _
                           "[ActiveRecord] = -1, [InputBy] = '" &
fOSUserName & "', [InputDate] = '" & VBA.Now & "', [InputFlag] = -1 " & _
                           "WHERE jez_SWM_Visits.VisitID = Forms!frmVisits!
txtVisitID "
               DoCmd.RunSQL sQRY
'**************************************
              sQRY = "INSERT INTO jez_SWM_UsersLog ([UserName], [RequestDate]
, [RequestType], [NHSNo], [VisitID])" & _
                            "VALUES ('" & fOSUserName & "', '" & VBA.Now &
"', 'InsertRecord', '" & Me.txtNHSNo & "', '" & Me.txtVisitID & "')"
              DoCmd.RunSQL sQRY
'**************************************
   Me.lblBMIInfo.Visible = False
   Me.txtDummy.SetFocus
   Me.txtNHSNo = ""
   Me.txtForename = ""
   Me.txtSurname = ""
   Me.txtAddress1 = ""
   Me.txtAddress2 = ""
   Me.txtAddress3 = ""
   Me.txtPostcode = ""
   Me.txtTelephone = ""
   Me.cboGender = ""
   Me.txtDOB = ""
   Me.cboReferralRsn = ""
   Me.cboReferralSource = ""
   Me.txtReferralDate = ""
   Me.txtVisitDate = ""
   Me.chkFinalVist = 0
   Me.txtHeight = ""
   Me.txtWeight = ""
   Me.txtWaist = ""
   Me.txtBlood = ""
   Me.txtExercise = ""
   Me.txtDiet = ""
   Me.txtSelf = ""
   Me.cboSessionType = ""
   Me.txtStaffName = ""
   Me.cboAttendance = ""
   Me.txtComments = ""
   Me.txtInputUser = ""
   Me.txtInputDate = ""
   Me.chkActive = 0
   Me.chkInputFlag = 0
   Call LockAll
   'DoCmd.OpenForm "frmSplash"
   'Form_frmVisits.Visible = False
'Err:
' basError.LogError VBA.Err, VBA.Error$, "Form_frmMain - cmdSubmit_Click()
"
End Sub



  #2 (permalink)  
Old November 20th, 2008, 08:58 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Where in this code is the error appearing? Are the tables connected to the database?

One problem you may be having here is that you are using DAO with SQL Server, which does not support DAO, only ADO. Normally with a SQL Server back end, I would do something like this:

Dim rs As New ADODB.Recordset
Dim sSQL As String

sSQL = "INSERT INTO jez_SWM_Visits (NHSNo) " & _
       "VALUES ('" & varInput & "')"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

This assumes the tables are connected to Acces, which I am assuming since you are still able to use DAO somewhat, so there is no ADODB remote provider statement.

The act of opening the recordset does the insert, so it acts the same way as DoCmd.RunSQL

Anyway, without knowing where in the code you are getting this error, its ahrd to say. There are 3 transactions involving saves.


mmcdonal

Look it up at: http://wrox.books24x7.com
  #3 (permalink)  
Old November 20th, 2008, 09:13 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks,

Currently the code falls over on this line

Me.RecordSource = "SELECT jez_SWM_Visits.* FROM jez_SWM_Visits WHERE " &
_
     "jez_SWM_Visits.VisitID = " & varNewID & " "

when inputing another record.

My tables are connected to Access through using Link Tables

trying the ADO code you wrote that has worked great :-)

How would be best to write all the other unbound objects to the DB now as the current code I have doesnt send the data to the table now?
  #4 (permalink)  
Old November 20th, 2008, 09:57 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

First, I would try this:

sSQL = "SELECT jez_SWM_Visits.* FROM jez_SWM_Visits WHERE " & _
       "jez_SWM_Visits.VisitID = " & varNewID

Me.RecordSource = sSQL

Note that you don't need the closing space as in your original string.
Also, try not to build strings at runtime. Access doesn't like that. Build the string in a variable, and then run the variable.

Did that help?





mmcdonal

Look it up at: http://wrox.books24x7.com
  #5 (permalink)  
Old November 20th, 2008, 10:24 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Excellent, thats worked far better. I understand what you have said.
So this is now my AddNew_Click

Private Sub cmdAddNew_Click()
Dim rs As New ADODB.Recordset
Dim varInput As String
Dim varNewID As Integer
Dim sSQL As String

    varInput = InputBox("Enter NHS Number", "Add new visit")
    If varInput = "" Then Exit Sub

            sSQL = "INSERT INTO jez_SWM_Visits (NHSNo) " & _
                    "VALUES ('" & varInput & "')"
    varNewID = DLookup("max(VisitID)", "jez_SWM_Visits")
    Set rs = New ADODB.Recordset
    rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

            sSQL = "SELECT jez_SWM_Visits.* FROM jez_SWM_Visits WHERE " & _
                    "jez_SWM_Visits.VisitID = " & varNewID
    Me.RecordSource = sSQL

    Call UnLockAll
    Me.txtNHSNo.Value = varInput
    Me.txtForename.SetFocus
End Sub

It seems to run faster aswell.

How would it be better to commit the other fields within the form to the record as in my Submit_Click?


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Metafile to the Clipboard TMA_Master BOOK: Visual Basic 2008 Programmer's Reference ISBN: 978-0-470-18262-8 1 August 26th, 2008 10:59 PM
Copy to clipboard in EMF format. watashi C# 1 April 17th, 2008 09:02 AM
how to copy text in a word file on the clipboard donrafeal Word VBA 3 February 1st, 2007 12:32 AM
Copy text to win Clipboard irresistible007 Beginning VB 6 6 October 5th, 2006 06:04 AM
Copy to Clipboard Clive Astley Access 5 July 16th, 2004 07:16 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.