Avatar of msmerry
msmerry
Flag for Australia asked on

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?
Microsoft AccessMicrosoft Applications

Avatar of undefined
Last Comment
msmerry

8/22/2022 - Mon
mbizup

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

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

SOLUTION
sshah254

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
msmerry

ASKER

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
mbizup

That error almost always means you have a field name wrong.  Check for typos.
mbizup

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?
msmerry

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
mbizup

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


msmerry

ASKER
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
ASKER CERTIFIED SOLUTION
mbizup

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
mbizup

In the screenshot you posted, there is no interval specified, so the dates for that scenario will all be the same.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
als315

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
msmerry

ASKER
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