Re: Require Method VBA SQL
Quote:
Originally Posted by
cycleinmars
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.
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
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