Require Method VBA SQL

[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

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

[ice_531]

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

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

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...

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.

[cycleinmars]

Hai,

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

sqla = r2.Fields![dealerid]

3021 error

thanks for helping

regards

Praveen

[cycleinmars]

i used the following cod , it is working

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