[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

adding new record with details of previous record

Posted on 2011-10-08
13
Medium Priority
?
306 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:msmerry
13 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36937342
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36937347
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
 
LVL 9

Assisted Solution

by:sshah254
sshah254 earned 400 total points
ID: 36937348
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:msmerry
ID: 36937464

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
 
LVL 61

Expert Comment

by:mbizup
ID: 36937485
That error almost always means you have a field name wrong.  Check for typos.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36937498
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
 

Author Comment

by:msmerry
ID: 36937521
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36937546
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
 

Author Comment

by:msmerry
ID: 36937657
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
 
LVL 61

Accepted Solution

by:
mbizup earned 1400 total points
ID: 36937672
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36937676
In the screenshot you posted, there is no interval specified, so the dates for that scenario will all be the same.
0
 
LVL 40

Assisted Solution

by:als315
als315 earned 200 total points
ID: 36938045
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
 

Author Closing Comment

by:msmerry
ID: 37004984
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question