Replace Field Name with Variable (SQL)

[tasawer]

in my current procedure, I have string variable 'ConfirmRate',
in my sql section shown below, I need to replace the VATrate (select section) with ConfirmRate, I don't know how to do that.
your help is appreciated.

If Me.[ExtraDetailssub].Form.RecordsetClone.RecordCount > 0 Then
  strSql = "INSERT INTO [Extras] ( PH_DetailsID, ExtraAmt, ItemDetails, VATyesno, VATrate ) " & _
  "SELECT " & lngID & " As NewID, ExtraAmt, ItemDetails, VATyesno, VATrate " & _
 "FROM [Extras] WHERE PH_DetailsID = " & Me.PH_DetailsId & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError

[ajalwaysus]

I think you need to be more specific, I'm not sure what your problem is, my first reaction would be to try this:

If Me.[ExtraDetailssub].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Extras] ( PH_DetailsID, ExtraAmt, ItemDetails, VATyesno, VATrate ) " & _
"SELECT " & lngID & " As NewID, ExtraAmt, ItemDetails, VATyesno, " & ConfirmRate & " FROM [Extras] WHERE PH_DetailsID = " & Me.PH_DetailsId & ";"
 
DBEngine(0)(0).Execute strSql, dbFailOnError 
 

But from looking at your code, you seem like you would know that, so if I am wrong please post more information.

-AJ

[tasawer]

Thank you AJ for your prompt response. You are working along the lines I am expecting. I tired the modification but it did not work.

In my fully working application, when an order is created, VATrate is applied to it according to the date period it was created.
if at a later date, the same customer repeats the order, I simpy duplicate it, including the VAT rate.
However, recently in the UK, there have been some VAT changes.
so now I wish to duplicate the order but apply the VAT rate applicable on the date period of duplication. current vat rate is 'ConfirmRate'

I hope I have explained myself better this time.

[NeoPa]

AJ's response seems fine to me, assuming a numeric value is required.

Otherwise, please state which type of variable is required.

If this is not the problem perhaps you can post the relevant extract of the code that you've tried but have found to fail. I can only assume it's something else causing the problem.

Please include a clear explanation of any error.

[ADezii]

Unless I am reading this all wrong, which is very probable (LOL), you are trying to substitute an actual Value contained in the Variable ConfirmRate to a Field Descriptor in the Select Clause. If I am correct, then I imagine that this has to be a 2-Stage process, as in:

I have string variable 'ConfirmRate', in my sql section shown below

Dim strSQL As String
Dim strSQL2 As String
 
strSQL = "INSERT INTO [Extras] ( PH_DetailsID, ExtraAmt, ItemDetails, VATyesno, VATrate ) " & _
         "SELECT " & lngID & " As NewID, ExtraAmt, ItemDetails, VATyesno, VATrate " & _
         "FROM [Extras] WHERE PH_DetailsID = " & Me.PH_DetailsId & ";"
            DBEngine(0)(0).Execute strSQL, dbFailOnError
 
          'If ConfirmRate is a String, as you indicated in Initial Post
strSQL2 = "Update [Extras] Set [VATrate] = '" & ConfirmRate & "' Where [lngID] = " & _
           DLast("[lngID]", "[Extras]") & ";"
 
          'If ConfirmRate is Numeric
strSQL2 = "Update [Extras] Set [VATrate] = " & ConfirmRate & " Where [lngID] = " & _
           DLast("[lngID]", "[Extras]") & ";"
             DBEngine(0)(0).Execute strSQL2, dbFailOnError

[tasawer]

Hello Adezii, you have understood correctly.

I have tried both options above, Record gets duplicated but I get the error '2001 - You cancelled previous operation' (VATRate never gets updated though).

here is the full Function. (It basically copies the main record and the sub-records) - Main record gets the new vat rate but the sub records don't.

Thanks in advance to all willing to help.

Private Sub ReserveDup()
 
'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim strSql2 As String   'SQL statment for VAT
    Dim lngID As Long       'Primary key value of the new record.
    Dim ThisWeek As Integer 'current week of hire
    Dim LastDate As Date    'Last Date of Hire
    Dim DaysDiff As Long    'Period of Each Hire
    Dim ConfirmRate As Double 'Confirmed VATRate
 
    'Save and edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
 
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
 
ThisWeek = DMax("Week", "QryWeek") + 1
LastDate = DMax("ToDate", "QryWeek") + 1
DaysDiff = DateDiff("d", Me.FromDate, Me.ToDate)
'MsgBox (DaysDiff)
 
                !HireId = Me.HireId
                !Week = ThisWeek
                !FromDate = LastDate
                !ToDate = LastDate + DaysDiff
                !HireCharge = Me.HireCharge
                !ContactID = Me.ContactID
                !OtherCharges = Me.OtherCharges
                Call VATRateCheck(LastDate, LastDate + DaysDiff, ConfirmRate)
                !VATrate = ConfirmRate
                '!VATrate = Me.VATrate
                '!VATamt = Me.VATamt
 
'MsgBox "updatetime"
                'etc for other fields.
            .Update
 
    'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !PH_DetailsId
 
            'Duplicate the related records: append query.
            If Me.[ExtraDetailssub].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [Extras] ( PH_DetailsID, ExtraAmt, ItemDetails, VATyesno, VATrate ) " & _
                    "SELECT " & lngID & " As NewID, ExtraAmt, ItemDetails, VATyesno, VATrate " & _
                    "FROM [Extras] WHERE PH_DetailsID = " & Me.PH_DetailsId & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
 
            Else
                'MsgBox "Main record duplicated, but there were no related records."
            End If
 
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If
 
Forms!Private_Hire_Charges.Refresh
Forms!Private_Hire.Refresh
 
End Sub

[ADezii]

I have added Code Tags and did a little reformatting. Will look at this closer tomorrow.

Private Sub ReserveDup()
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String        'SQL statement.
Dim strSql2 As String       'SQL statment for VAT
Dim lngID As Long           'Primary key value of the new record.
Dim ThisWeek As Integer     'current week of hire
Dim LastDate As Date        'Last Date of Hire
Dim DaysDiff As Long        'Period of Each Hire
Dim ConfirmRate As Double   'Confirmed VATRate
 
'Save and edits first
If Me.Dirty Then Me.Dirty = False
 
'Make sure there is a record to duplicate.
If Me.NewRecord Then
  MsgBox "Select the record to duplicate."
Else
  'Duplicate the main record: add to form's clone.
  With Me.RecordsetClone
   .AddNew
     ThisWeek = DMax("Week", "QryWeek") + 1
     LastDate = DMax("ToDate", "QryWeek") + 1
     DaysDiff = DateDiff("d", Me.FromDate, Me.ToDate)
 
     !HireId = Me.HireId
     !Week = ThisWeek
     !FromDate = LastDate
     !ToDate = LastDate + DaysDiff
     !HireCharge = Me.HireCharge
     !ContactID = Me.ContactID
     !OtherCharges = Me.OtherCharges
       Call VATRateCheck(LastDate, LastDate + DaysDiff, ConfirmRate)
     !VATrate = ConfirmRate
   .Update
 
     'Save the primary key value, to use as the foreign key for the related records.
   .Bookmark = .LastModified
     lngID = !PH_DetailsId
 
    'Duplicate the related records: append query.
    If Me.[ExtraDetailssub].Form.RecordsetClone.RecordCount > 0 Then
      strSql = "INSERT INTO [Extras] ( PH_DetailsID, ExtraAmt, ItemDetails, VATyesno, VATrate ) " & _
               "SELECT " & lngID & " As NewID, ExtraAmt, ItemDetails, VATyesno, VATrate " & _
               "FROM [Extras] WHERE PH_DetailsID = " & Me.PH_DetailsId & ";"
        DBEngine(0)(0).Execute strSql, dbFailOnError
 
    Else
    End If
 
      'Display the new duplicate.
      Me.Bookmark = .LastModified
  End With
End If
 
Forms!Private_Hire_Charges.Refresh
Forms!Private_Hire.Refresh
End Sub

[ADezii]

A couple of items stick right out:

1. An Exit Sub should be placed between Lines 16 and 17, since there is no need to have the code fall through and process the Refresh Statements after Line #54 in Post #7.
2. Is the [Week] Field declared as an Integer, since the ThisWeek Variable is defined as such in Line #6 of Post #7.
3. Is the [ToDate] Field declared as a Date, since the LastDate Variable is defined as such in Line #7 of Post #7.
4. What exactly does the VATRateCheck() Sub-Routine do? You are passing this Routine 2 Dates and a Double. Are the Sub-Routine Parameters an exact match as far as Data Types?
5. ConfirmRate is declared in Line #9 but never initialized, yet it is passed (having no Value) to VATRateCheck() in Line #32 of Post #7, and then assigned to !VATRate in Line #33 where it again has no value.

[tasawer]

1. I could move the refresh statements above the end if Lines #53 post #7
2. Week is defined as Long Integer
3. TODate is Date/Time Data type
4. VATRATEcheck checks the VAT rate applicable between two dates -

Public Function VATRateCheck(StartDate, EndDate, ConfirmRate)
 
Dim Rate1 As String
Dim Rate2 As String
Dim dt As String
Dim df As String
 
    df = Month(StartDate) & "/" & Day(StartDate) & "/" & Year(StartDate)
    dt = Month(EndDate) & "/" & Day(EndDate) & "/" & Year(EndDate)
 
    Rate1 = 0
    Rate2 = 0
 
'MsgBox df & " " & dt
 
    Rate1 = DLookup("vatrate", "tvatrate", _
    "RateDateStart <= #" & df & "#" & " And RateDateEnd >= #" & df & "#")
 
    Rate2 = DLookup("vatrate", "tvatrate", _
    "RateDateStart <= #" & dt & "#" & " And RateDateEnd >= #" & dt & "#")
 
'MsgBox Rate1 & " " & Rate2
 
    If Rate1 <> Rate2 Then
        MsgBox "VAT RATE CHANGES DURING THIS WEEK"
        ConfirmRate = 0
        Else
        ConfirmRate = Rate1
        End If
 
End Function

5. I reading the CONFIRMRATE value back from the function VATRATECHECK

As you can see, I am duplicating a record with subrecords.
when a customers hires equipment between two dates, I manually enter all the items.
when the same customers returns at a later date and hires exactly the same equipement again, I simply duplicate.
Since the VAT rate changes (01/12/08) (and another one this year), when I duplicate I am carryiing over the old vat rate.
with my coding, above, main record take the new vat, subrecords do not.
I believe a change to line #43 post #7 is required.

I am wondering if I should add an SQL statment 'INSERT INTO' to the newly created record and change the vat value.
what is your suggestion?

[ADezii]

In the initial Post you indicate ConfirmRate is a String, then it is Declared Locally as a Double. It is then passed to a Public Function where this Parameter (placeholder) is a Variant. You 'Call' this Function which negates the entire purpose of a Return Value from a Function. Instead of the Function itself returning the Value of ConfirmRate by assignment (=), its value is sent back as an Argument. I imagine that this logic is only part of the problem, if not now then in the future. The only suggestion that I can make is that if you are willing to send me the Database to my Private E-Mail Address, I'll have a look at it when I get a chance. Other than that, I am basically dead in the water. Let me know either way.

P.S. - What Version of Access are you using?

[NeoPa]

Tasawer,

It seems you missed my edit comment requesting you use the [ CODE ] tags when you post code.

Please make sure you read this, and use the tags in all such posts in future.

[tasawer]

to Adezzi, thanks for offering to look at my database. (post #10)
I have been to private messages but donot see a link for attachments. How do I send the file to yourself only please.

[ADezii]

@tasawer
I'll send you a Private Message (PM) with my E-Mail Address in it. Send it to that Address as an Attachment.

[ADezii]

The way the current Logic is structured, it appears as though you may need 2 independent events to accomplish what you are requesting, An Append to the Sub-Form (Extras) then an Update to same. See if this does the trick. Add Lines 7, 8, and 9 to this code segment in ReserveDup().

If Me.[ExtraDetailssub].Form.RecordsetClone.RecordCount > 0 Then
  strSql = "INSERT INTO [Extras] ( PH_DetailsID, ExtraAmt, ItemDetails, VATyesno, VATrate ) " & _
           "SELECT " & lngID & " As NewID, ExtraAmt, ItemDetails, VATyesno, VATrate " & _
           "FROM [Extras] WHERE PH_DetailsID = " & Me.PH_DetailsId & ";"
              DBEngine(0)(0).Execute strSql, dbFailOnError
 
              DBEngine(0)(0).Execute "UPDATE [Extras] SET [VATrate] = " & ConfirmRate & _
                                     " WHERE [PH_DetailsID] = " & DLast("[PH_DetailsID]", _
                                     "[Private_Hire_Detail]") & " AND [VATyesno] = True;", dbFailOnError
Else
  'MsgBox "Main record duplicated, but there were no related records."
End If

[tasawer]

Fantastic.. that works wonderfully. Thanks a million.
Now What about the double clicking to create new record.

[ADezii]

@tasawer

Fantastic.. that works wonderfully. Thanks a million.

You are quite welcome. If you look back at Post #5, you'll see that we were on the right track - just lacked the specifics.

What about the double clicking to create new record.

Did not happen for me.