adding new record with details of previous record

I asked previously how to "add new records with dates", and received an excellent response.
However, I had pared my question to what seemed to me the important fields; now I discover that I should have included more fields.
This was the response that I used:-

Private Sub AddVisits_Click()

Dim iFreq As Integer
Dim iInterval As Integer
Dim ddate As Date

iFreq = Me.txFreq
iInterval = Me.Txinterval
ddate = Me.TxStart

Dim i As Integer

For i = 1 To iFreq
  CurrentDb.Execute "INSERT INTO TblActivity([ActivityDate]) VALUES(#" & Format(ddate, "mm/dd/yyyy") & "#)"
  ddate = DateAdd("d", ddate, iInterval)
Next i

End Sub

What I need in addition is to have it add other fields - e.g., worksid, type, jobid.  These fields do not need to be updated, simply repreated..  How do I add them to the code?
msmerryAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
It should work, just replacing your current execute statement with the new one, assuming you have a value entered in txtInterval.  If nothing is entered there, the date will not increment.

Private Sub AddVisits_Click()

Dim iFreq As Integer
Dim iInterval As Integer
Dim ddate As Date

iFreq = Me.txFreq
iInterval = Me.Txinterval
ddate = Me.TxStart

Dim i As Integer

For i = 1 To iFreq

 CurrentDb.Execute "INSERT INTO TblActivity([ActivityDate], WorksID, ActivityType,JobID) VALUES(#" & Format(ddate, "mm/dd/yyyy") & "#,'" & Me.WorksID & "', '" & me.Activitytype & "'," & Me.JobID & ")", dbfailonerror


  ddate = DateAdd("d", ddate, iInterval)
Next i

End Sub

0
 
mbizupCommented:
You just need to add on:

 CurrentDb.Execute "INSERT INTO TblActivity([ActivityDate], WorksID, Type,JobID) VALUES(#" & Format(ddate, "mm/dd/yyyy") & "#," & WorksID & ", '" & me.type & "'," & Me.JobID & ")"

Assumptions:

Type is text
Worksid and JobID are numeric
You have controls or fields on your form for these items

The syntax may vary if those assumptions don't hold true.
0
 
mbizupCommented:
I would also recommend adding "dbFailOnError" to that, to alert you to any troubles in your SQL statement:

CurrentDb.Execute "INSERT INTO TblActivity([ActivityDate], WorksID, Type,JobID) VALUES(#" & Format(ddate, "mm/dd/yyyy") & "#," & WorksID & ", '" & me.type & "'," & Me.JobID & ")", dbfailonerror

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sshah254Connect With a Mentor Commented:
Just change the following statement.

CurentDb.Execute "insert into TblActivity([ActivityDate], [worksid], [type], [jobid]) values (#"&Format(ddate, "mm/dd/yyyy") & "#, 12345, 23456, 34567)"

where 12345 = worksid
23456 = type
34567 = jobid

etc.

replace the 12345 with a variable, if needed.

SS
0
 
msmerryAuthor Commented:

ActivityType is text ( I made the necessary changes to the code)
Jobid is numeric
Worksid is text

My AddVisits form (attached) has those fields, as does my tblActivity

The error message I get is
Runtime Error 3061
Too few parameters Expected 1

It then highlights the whole line I inserted (and adjusted) from mbizup
CurrentDb.Execute "INSERT INTO TblActivity([ActivityDate], WorksID, ActivityType,JobID) VALUES(#" & Format(ddate, "mm/dd/yyyy") & "#," & WorksID & ", '" & me.Activitytype & "'," & Me.JobID & ")", dbfailonerror


I am about to try sshah54's response
addvisits.jpg
0
 
mbizupCommented:
That error almost always means you have a field name wrong.  Check for typos.
0
 
mbizupCommented:
Also,  do you actually have text boxes or something else on your form for worksid and jobsid?

Can they be null or do they always have values?
0
 
msmerryAuthor Commented:
First, no typos, checked very carefully.
Second, the first five items on the form are sourced from TblActivity, and are automaticall filled in from the table; the other three are unbound to generate the dates
addvisitsdesign.jpg
0
 
mbizupCommented:
This modifies the syntax to account for a Text worksID (I've added single quotes around Me.WorksID), but double check the control NAME properties for both of these added fields... You can either check them from the property sheet under the "Other" tab, or from code, where they will autocomplete if you start typing "Me."

CurrentDb.Execute "INSERT INTO TblActivity([ActivityDate], WorksID, ActivityType,JobID) VALUES(#" & Format(ddate, "mm/dd/yyyy") & "#,'" & Me.WorksID & "', '" & me.Activitytype & "'," & Me.JobID & ")", dbfailonerror


0
 
msmerryAuthor Commented:
Ok, when I use this code, I get my ten new entries with worksid, activitytype, jobid- but all with the same date.  It would seem that the piece of code in my question that creates new dates has been overlooked.  I have tried adding it where I thought most logical, but to no avail.
This is what I tried - what am I doing wrong?
CurrentDb.Execute "INSERT INTO TblActivity([ActivityDate], WorksID, ActivityType,JobID) VALUES(#" & Format(ddate, "mm/dd/yyyy") & "#,'" ddate = DateAdd("d", ddate, iInterval) & Me.WorksID & "', '" & Me.ActivityType & "'," & Me.jobid & ")", dbFailOnError
0
 
mbizupCommented:
In the screenshot you posted, there is no interval specified, so the dates for that scenario will all be the same.
0
 
als315Connect With a Mentor Commented:
You can return to other suggested way (me and fyed) - do it with query. It is very simple to add any additional field to query, especially if you have no enough experience in VBA. If you upload DB with your form  and TblActivity, I can prepare exact query.
0
 
msmerryAuthor Commented:
mbizup provided the answer I needed .  I appreciate the answer from sshah 254which provided me with the date solution.
als315, thanks for replying, but by then I had my answer, so didn't get around to you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.