Need help with this code

[Praveenevg]

I am getting a syntax error with this insert statement.



can someone help me resolve this?



Sub extra()

Dim rs As DAO.Recordset
Dim ext_times As Integer
Dim Idx As Integer


Set rs = CurrentDb.OpenRecordset("SELECT * FROM MASTER_JOB_LIST WHERE frequency = 1")

If Not (rs.EOF And rs.BOF) Then

rs.MoveFirst

Do Until rs.EOF = True
CurrentDb.Execute "INSERT INTO final_table (ID,JobID) VALUES ('10', rs!ID);" ' (rs!ID,rs!JobID);"

Loop




End If



End Sub

[June7]

Concatenate variables. Reference to recordset field is a variable. Why do you save two IDs? Is JobID a text field?

CurrentDb.Execute "INSERT INTO final_table (ID, JobID) VALUES (" & rs!ID & ", '" & rs!JobID & "');"
rs.MoveNext

[Ruegen]

Do Until rs.EOF = True

lose the red as you don't need it

I always tend to count the recordset using rs.recordcount

that way if it is above 0 you can continue and if it isn't you can have it do something else

so

if rs.recordcount <= 0 then
'you have to either exit the code or do some alternative as there are no records to do anything
end if

update: I usually exit sub that way I can close the IF statement early and not have it at the bottom of my code - keeping things neater.

[Praveenevg]

Hello,

that helped. I am getting a syntax error again with following INSERT statement..

CurrentDb.Execute "INSERT INTO final_table (ID,JobID,JobName) VALUES ( " & rs!ID & ", " & rs!JobID & ", " & rs!JobName & ");"

Do you see anything wrong? If i remove the third field (JobName) from the insert, it is working fine. SYNTAX error shows up when I am trying to add this third field to INSERT. Please help

[June7]

Text fields require apostrophe delimiters around the input value. Dates use #.

CurrentDb.Execute "INSERT INTO final_table (ID,JobID,JobName) VALUES ( " & rs!ID & ", " & rs!JobID & ", '" & rs!JobName & "');"
rs.MoveNext

Again, why are you saving JobID and JobName and not just ID or just JobID?

[amrut]

Probably JobName is Text type field. If it is so, you need ' ( single quote mark) around it ", '" & rs!JobName & "');"

[Praveenevg]

Thanks.. It worked.

My end goal is as follows:

I have two tables: Table1 and Table2.

In table 1, i have 3 fields ( Job ID, Job Name and Job Date) fields.

I need to insert this record from table1 into table2 30 times. For each record in Table2, the value for date field should be incremented by 1 day.

Therefore, I am looping through TABLE1 and inserting records into second table 30 times for each record in TABLE1.

[June7]

I still don't understand why you are duplicating data. If you save the ID, the JobName and JobID are not needed to be saved. Is ID the primary key field?

[Ruegen]

so same job, but done each day (total 30 times) over 30 days...?

Why not have a date range that updates each day then you have a range?

e.g
[JobStartDate], [JobLastDate]

[Praveenevg]

Yes you are right.

My next problem is a syntax error as follows when trying to use IS NULL condition for a date field:

Dim JOB_DATE As Date
If JOB_DATE Is Null Then
JOB_DATE = rs!JOB_DATE
Else
JOB_DATE = JOB_DATE + 1
End If


Can we use IS NULL condition this way on a date field?

[Ruegen]

Yes you are right.

My next problem is a syntax error as follows when trying to use IS NULL condition for a date field:

Dim JOB_DATE As Date
If isnull(JOB_DATE) Then 'note I think you meant to have isnull(rs!JOB_DATE)
JOB_DATE = rs!JOB_DATE 'here put date() ?
Else
JOB_DATE = dateadd('d',1, JOB_DATE) 'dateadd(d',1, rs!JOB_DATE)
End If



Can we use IS NULL condition this way on a date field?

function to determine if null field is true/false is - null()

and if you just created a variable with no value (blue) its always going to be null


....?

if JOB_DATE is the field of the current edited record - no need to make variable (delete blue)