dcsimg
Results 1 to 4 of 4

Thread: Require Method VBA SQL

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2010
    Posts
    32

    Require Method VBA SQL

    Hello,
    I have two tables, Dealerdata and dailiytab
    dealerdata has dealerid, dealername, marketingguy feilds
    dailydtatab has dealerid, dealername, date feilds

    i want to enter data in daily tab using a form
    i have a text object which takes input for dealerid
    it matches with the dealerdata table and inserts dealerid , dealername and date(current) in the dailydtatab table
    Code:
    Private Sub adddelid_Click()
    Dim r1 As Object
    Dim r2 As Object
    Dim sqla As String
    
     
     
    Set r2 = CurrentDb.OpenRecordset("SELECT dealerid FROM dealerdata WHERE dealerid = ' " & Me.delident.Value & "'")
    sqla = r2.Fields![dealerid]
    Set r1 = CurrentDb.OpenRecordset("SELECT * FROM dailydtatab WHERE FALSE")
    
    r1.AddNew
    r1.Fields![dealerid] = r2.Fields![dealerid]
    r1.Fields![dealername] = r2.Fields![dealername]
    
    
    r1.Update
    r1.Close
    Set r1 = Nothing
       
    End Sub
    kindly help me with a code

    i am getting a 3021 error
    Attached Images Attached Images  
    Last edited by cycleinmars; Feb 9th, 2012 at 12:53 PM.

  2. #2
    Frenzied Member ice_531's Avatar
    Join Date
    Aug 2002
    Location
    Sitting w/ Bob Status: -Next -To- Null- Friend: Philip
    Posts
    1,152

    Re: Require Method VBA SQL

    Quote Originally Posted by cycleinmars View Post
    Hello,
    I have two tables, Dealerdata and dailiytab
    dealerdata has dealerid, dealername, marketingguy feilds
    dailydtatab has dealerid, dealername, date feilds

    i want to enter data in daily tab using a form
    i have a text object which takes input for dealerid
    it matches with the dealerdata table and inserts dealerid , dealername and date(current) in the dailydtatab table
    Code:
    Private Sub adddelid_Click()
    Dim r1 As Object
    Dim r2 As Object
    Dim sqla As String
    
     
     
    Set r2 = CurrentDb.OpenRecordset("SELECT dealerid FROM dealerdata WHERE dealerid = ' " & Me.delident.Value & "'")
    sqla = r2.Fields![dealerid]
    Set r1 = CurrentDb.OpenRecordset("SELECT * FROM dailydtatab WHERE FALSE")
    
    r1.AddNew
    r1.Fields![dealerid] = r2.Fields![dealerid]
    r1.Fields![dealername] = r2.Fields![dealername]
    
    
    r1.Update
    r1.Close
    Set r1 = Nothing
       
    End Sub
    kindly help me with a code

    i am getting a 3021 error

    Code:
    Set r2 = CurrentDb.OpenRecordset("SELECT dealerid FROM dealerdata WHERE dealerid = ' " & Me.delident.Value & "'")
    Above line of code only opens a recordset that contains dealerid, in r2.

    But when you use the following lines of code...

    Code:
    r1.Fields![dealerid] = r2.Fields![dealerid]
    r1.Fields![dealername] = r2.Fields![dealername]
    You are trying to get data from dealername, in r2 recordset. But you dont actually have the dealername in the r2 recordset. So you need to modify that top select query to include dealername as well.
    :::`DISCLAIMER`:::
    Do NOT take anything i have posted to be truthful in any way, shape or form.
    Thank You!

    --------------------------------
    "Never heard about "hiking" poles. I usualy just grab a stick from the nature, and use that as a pole." - NoteMe
    "Finaly I can look as gay as I want..." - NoteMe
    Languages: VB6, BASIC, Java, C#. C++

  3. #3

    Thread Starter
    Member
    Join Date
    Feb 2010
    Posts
    32

    Re: Require Method VBA SQL

    Hai,

    i am not getting the error ther, i am getting the error

    Code:
    sqla = r2.Fields![dealerid]
    3021 error

    thanks for helping

    regards

    Praveen

  4. #4

    Thread Starter
    Member
    Join Date
    Feb 2010
    Posts
    32

    Resolved Re: Require Method VBA SQL

    i used the following cod , it is working
    Code:
    Dim rec, rec2 As Recordset
    Dim db As Database
    
    Set db = CurrentDb
    Set rec = db.OpenRecordset("SELECT dealerid,dealername,marketingperson FROM dealerdata WHERE dealerid = '" & Me.delident.Value & "'")
    Set rec2 = db.OpenRecordset("dailydtatab")
    
    
    rec2.AddNew
    rec2("dealerid") = rec("dealerid")
    rec2("dealername") = rec("dealername")
    rec2("MARKETINGPERSON") = rec("marketingperson")
    rec2("DATE") = Me.delident.Value
    rec2("ORDERNUMBER") = Me.delident.Value
    rec2("CATEGORY") = Me.delident.Value
    rec2.Update
    thanks guys
    Last edited by cycleinmars; Feb 12th, 2012 at 11:32 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width