Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-21-2014, 03:48 AM   #1
pheidlauf
Newly Registered User
 
Join Date: Jun 2013
Location: Chicago
Posts: 42
Thanks: 20
Thanked 0 Times in 0 Posts
pheidlauf is on a distinguished road
Question Help creating duplicate record with specific fields blank.

I'm working on a form with almost a hundred various fields (it's what my employer needs). He wants to be able to create new records with much of the same information as the old ones by clicking on a button that will copy the information to a new record, then clear certain specific fields (or some process that will safely create the same result). I don't need specifics on how to write the same line of code for each and every field I wish to clear, but I would like some advice on how to write either a VBA procedure or SQL update query.

Say I have a form with 5 fields: part_number, part_owner, procedure, file_name, and date.
How could I copy the record to keep the fields part_owner and procedure the same, but clear part_number, file_name, and date?

Note: All of the fields are from the same table (no, it's not normalized... I know this, it makes me sad, but it's not something I have time to do for them)

Thanks for any help!

pheidlauf is offline   Reply With Quote
Old 07-21-2014, 03:58 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,148
Thanks: 40
Thanked 3,617 Times in 3,491 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Help creating duplicate record with specific fields blank.

your vba code for your example would be

Code:
 
currentdb.execute "insert into mytable (part_number, part_owner) VALUES (" & me.part_number & ", " & me.part_owner & ")")
Basically inserting only the fields you require and leaving the rest to be populated by their default values per the table design (typically 0 or null).

Note the above is based on the fields buing numeric. It they are text modify this (if part_owner is text)

& ", " & me.part_owner & ")"

to
& ", '" & me.part_owner & "')"

- Inserting a single quote before and after the text value
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
pheidlauf (07-22-2014)
Old 07-21-2014, 04:11 AM   #3
pheidlauf
Newly Registered User
 
Join Date: Jun 2013
Location: Chicago
Posts: 42
Thanks: 20
Thanked 0 Times in 0 Posts
pheidlauf is on a distinguished road
Re: Help creating duplicate record with specific fields blank.

Quote:
Originally Posted by CJ_London View Post
your vba code for your example would be

Code:
 
currentdb.execute "insert into mytable (part_number, part_owner) VALUES (" & me.part_number & ", " & me.part_owner & ")")
Basically inserting only the fields you require and leaving the rest to be populated by their default values per the table design (typically 0 or null).

Note the above is based on the fields buing numeric. It they are text modify this (if part_owner is text)

& ", " & me.part_owner & ")"

to
& ", '" & me.part_owner & "')"

- Inserting a single quote before and after the text value
This looks like just the code I need. I do have two other questions for you on this. First, you have an extra parenthesis in your sample code, which one is the typo?
More importantly, is there an easy way that I could break this code into several parts? Here's what I mean in pseudo code:
Code:
field_names = (part_owner,process)
field_vals = (me.part_owner,me.process)
currentdb.execute "insert into mytable field_names VALUES field_vals"
The reason I would like to be able to write my code like this is the ~40 fields I need to copy--I want it to be as legible and reproducible as possible. Thanks again!

pheidlauf is offline   Reply With Quote
Old 07-21-2014, 05:13 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,148
Thanks: 40
Thanked 3,617 Times in 3,491 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Help creating duplicate record with specific fields blank.

Quote:
you have an extra parenthesis in your sample code, which one is the typo?
Neither - my mistake there should be an opening bracket after 'execute'

With regards breaking down your code you could do

Code:
 
field_names = "(part_owner, process)"
field_vals = "(" & me.part_owner & ", " & me.process & ")"
currentdb.execute ("insert into mytable " & fieldnames & " VALUES " & field_vals)
or to break it down even further
Code:
 
field_names = "("
field_names = field_names & "part_owner"
field_names = field_names & ", process"
field_names =field_names & ")"
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
pheidlauf (07-21-2014)
Old 07-21-2014, 07:41 AM   #5
pheidlauf
Newly Registered User
 
Join Date: Jun 2013
Location: Chicago
Posts: 42
Thanks: 20
Thanked 0 Times in 0 Posts
pheidlauf is on a distinguished road
Re: Help creating duplicate record with specific fields blank.

Quote:
Originally Posted by CJ_London View Post
Code:
 
field_names = "("
field_names = field_names & "part_owner"
field_names = field_names & ", process"
field_names =field_names & ")"
Ok, so I've written some code that I think is on the right track, but I am still getting an error. Here is what I have so far:
Code:
Private Sub CreateDuplicateRecord_Click()
    field_names = "("
    field_vals = "("
    
    field_names = field_names & "ENGINEER" & ", "
    field_vals = field_vals & getStringText("ENGINEER")
    
    field_names = field_names & "CUSTOMER" & ", "
    field_vals = field_vals & ", " & getStringText("CUSTOMER")
    
    field_names = field_names & "ROUTING CARD REVISION NUMBER"
    field_vals = field_vals & ", " & getNumericText("ROUTING CARD REVISION NUMBER")
    
    field_names = field_names & ")"
    field_vals = field_vals & ")"
    Dim query As String
    query = """" & "INSERT INTO [ProJet Routing Card Table] " & field_names & " VALUES " & field_vals & """"
    Debug.Print query
    
    CurrentDb.Execute (query)
End Sub

Public Function getNumericText(ByVal field_name As String) As String
    getNumericText = "me." & field_name
End Function

Public Function getStringText(ByVal field_name As String) As String
    getStringText = "'me." & field_name & "'"
End Function
The output in the debug box (the query that "Current.Db.Execute() is actually trying to run) is below (the quotation marks are actually part of the string:
Code:
"INSERT INTO [ProJet Routing Card Table] (ENGINEER, CUSTOMER, ROUTING CARD REVISION NUMBER) VALUES ('me.ENGINEER', 'me.CUSTOMER', me.ROUTING CARD REVISION NUMBER)"
However, I'm getting a Run-time error '3078':
The Microsoft Office Access database engine cannot find the input table or query '"INSERT INTO [ProJet Routing Card Table] (ENGINEER, CUSTOMER, ROUTING CARD REVISION NUMBER) VALUES ('me.ENGINEER', 'me.CUSTOMER', me.ROUTING CARD REVISION NUMBER)"'. Make sure it exists and that its name is spelled correctly.

Any ideas on what is wrong with my code?
pheidlauf is offline   Reply With Quote
Old 07-21-2014, 08:01 AM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,148
Thanks: 40
Thanked 3,617 Times in 3,491 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Help creating duplicate record with specific fields blank.

The way you have it set up is fine for field_names, but won't work for field_values for a number of reasons.

1. If you have spaces in field names, you must also use square brackets
2. your get... functions are returning the reference to the control, not the value of the control

It isn't really necessary to use these get.. functions since you are having to decide whether it is text or numeric anyway - and if you have dates you will need a function for that as well

field_vals = field_vals & "'" & me.ENGINEER & "'"

or if you want to use your function then

getStringText = "'" & me(field_name) & "'"

For dates, you need to surround them with the # character and format as US style (mm/dd/yyyy) if not already in that format. e.g.

getStringDate = "#" & format(me(field_name),"mm/dd/yyyy") & "#"
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-21-2014, 12:00 PM   #7
pheidlauf
Newly Registered User
 
Join Date: Jun 2013
Location: Chicago
Posts: 42
Thanks: 20
Thanked 0 Times in 0 Posts
pheidlauf is on a distinguished road
Re: Help creating duplicate record with specific fields blank.

Quote:
Originally Posted by CJ_London View Post
The way you have it set up is fine for field_names, but won't work for field_values for a number of reasons.

1. If you have spaces in field names, you must also use square brackets
2. your get... functions are returning the reference to the control, not the value of the control

It isn't really necessary to use these get.. functions since you are having to decide whether it is text or numeric anyway - and if you have dates you will need a function for that as well

field_vals = field_vals & "'" & me.ENGINEER & "'"

or if you want to use your function then

getStringText = "'" & me(field_name) & "'"

For dates, you need to surround them with the # character and format as US style (mm/dd/yyyy) if not already in that format. e.g.

getStringDate = "#" & format(me(field_name),"mm/dd/yyyy") & "#"
Well, I've made the changes you recommended, and am sadly still getting the same error. Here is the query I'm executing:
Code:
"INSERT INTO [Dummy Routing Card Table] (ENGINEER, CUSTOMER, [ROUTING CARD REVISION NUMBER]) VALUES ('Dummy Name', 'Peter', 123)"
It's still the same error 3078. I've double and triple checked the table name that I'm using in the code.
The error is saying that the MS Access database engine cannot find the input table or query "'INSERT INTO ... 123)'".

Your help has been great so far!

pheidlauf is offline   Reply With Quote
Old 07-21-2014, 02:45 PM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,148
Thanks: 40
Thanked 3,617 Times in 3,491 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Help creating duplicate record with specific fields blank.

Still a couple of issues:

1. You have 'query' which is a reserved word so change it to something like 'qryStr'. Here is a link to reserved words http://office.microsoft.com/en-gb/ac...010030643.aspx

2. You have
Quote:
query = """" & "INSERT....
you don't need the """" & or the bit at the other end just
Code:
qryStr = "INSERT....
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
pheidlauf (07-22-2014)
Old 07-22-2014, 03:47 AM   #9
pheidlauf
Newly Registered User
 
Join Date: Jun 2013
Location: Chicago
Posts: 42
Thanks: 20
Thanked 0 Times in 0 Posts
pheidlauf is on a distinguished road
Re: Help creating duplicate record with specific fields blank.

Quote:
Originally Posted by CJ_London View Post
Still a couple of issues:

1. You have 'query' which is a reserved word so change it to something like 'qryStr'. Here is a link to reserved words http://office.microsoft.com/en-gb/ac...010030643.aspx

2. You have you don't need the """" & or the bit at the other end just
Code:
qryStr = "INSERT....
It's almost there... I don't have any errors popping up any more! But, the query isn't actually generating a new record in the table. Here's the query that's running:
Code:
INSERT INTO [Dummy Routing Card Table] (ENGINEER, CUSTOMER, [ROUTING CARD REVISION NUMBER]) VALUES ('Dummy Engineer', 'Peter', 123)
What do I need to change about this query to make it create a record with these values? I'm currently calling the query using:
Code:
CurrentDb.Execute (qryStr)
pheidlauf is offline   Reply With Quote
Old 07-22-2014, 04:00 AM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,148
Thanks: 40
Thanked 3,617 Times in 3,491 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Help creating duplicate record with specific fields blank.

On the face of it there is no reason why this shouldn't work.

Suggest copy the code into a new query and run it to see what errors you get
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-22-2014, 04:04 AM   #11
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,148
Thanks: 40
Thanked 3,617 Times in 3,491 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Help creating duplicate record with specific fields blank.

or add dbfailonerror as a parameter to the execute line

currendb.execute(qryStr,dbfailonerror)
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-22-2014, 04:08 AM   #12
pheidlauf
Newly Registered User
 
Join Date: Jun 2013
Location: Chicago
Posts: 42
Thanks: 20
Thanked 0 Times in 0 Posts
pheidlauf is on a distinguished road
Re: Help creating duplicate record with specific fields blank.

Quote:
Originally Posted by CJ_London View Post
or add dbfailonerror as a parameter to the execute line

currendb.execute(qryStr,dbfailonerror)
As soon as I add that option to the currentdb.execute() call, I get a compile error and can't even test the code. I'm running Access 2007, could that be the issue?
pheidlauf is offline   Reply With Quote
Old 07-22-2014, 05:16 AM   #13
pheidlauf
Newly Registered User
 
Join Date: Jun 2013
Location: Chicago
Posts: 42
Thanks: 20
Thanked 0 Times in 0 Posts
pheidlauf is on a distinguished road
Thumbs up Re: Help creating duplicate record with specific fields blank.

The code works now. I switched from using CurrentDb.execute() to DoCmd.RunSQL(), and the query successfully runs. Thanks for all of the help. For anyone else who might have had a similar problem, here's a sample of the code that shows everything you need.

Code:
Private Sub CreateDuplicateRecord_Click()
    'Initialize Query Helpers
    field_names = "("
    field_vals = "("
    
    'Add Fields to Query Helpers
    field_names = field_names & "ENGINEER" & ", "
    field_vals = field_vals & getStringText("ENGINEER")
    
    field_names = field_names & "CUSTOMER" & ", "
    field_vals = field_vals & ", " & getStringText("CUSTOMER")
    
    field_names = field_names & "[ROUTING CARD REVISION NUMBER]"
    field_vals = field_vals & ", " & getNumericText("ROUTING CARD REVISION NUMBER")
    
    
    'Finalize Query Helpers
    field_names = field_names & ")"
    field_vals = field_vals & ")"
    
    'Fully Define Query
    Dim qryStr As String
    qryStr = "INSERT INTO [Routing Card Table] " & field_names & " VALUES " & field_vals

    'Run Query and update form
    DoCmd.RunSQL (qryStr)
    Forms![Routing Card Form].Requery
    
End Sub

Public Function getNumericText(ByVal field_name As String) As String
    getNumericText = Me(field_name)
End Function

Public Function getStringText(ByVal field_name As String) As String
    getStringText = "'" & Me(field_name) & "'"
End Function
pheidlauf is offline   Reply With Quote
Old 07-22-2014, 05:17 AM   #14
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,148
Thanks: 40
Thanked 3,617 Times in 3,491 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Help creating duplicate record with specific fields blank.

I wouldn't have thought so - depends on the compile error as to what the problem is.

try removing the brackets - see this thread which has a very similar requirement

http://www.access-programmers.co.uk/...d.php?t=221632

What happens if you copy the generated sql and run it from the query window?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-22-2014, 06:13 AM   #15
pheidlauf
Newly Registered User
 
Join Date: Jun 2013
Location: Chicago
Posts: 42
Thanks: 20
Thanked 0 Times in 0 Posts
pheidlauf is on a distinguished road
Re: Help creating duplicate record with specific fields blank.

Quote:
Originally Posted by CJ_London View Post
I wouldn't have thought so - depends on the compile error as to what the problem is.

try removing the brackets - see this thread which has a very similar requirement

http://www.access-programmers.co.uk/...d.php?t=221632

What happens if you copy the generated sql and run it from the query window?
The code I posted immediately before your response does actually run. I did realize, I have one more request for your expertise. How would I go about formatting my query for a Yes/No field. Also, the date formatting I made based off of your original post is not working well:
Code:
Public Function getDateText(ByVal field_name As String) As Date
    getDateText = "#" & Format(Me(field_name), "mm/dd/yyyy") & "#"
End Function

pheidlauf is offline   Reply With Quote
Reply

Tags
button , copy , duplicate , form , reset

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Specific number of blank new record nofar Forms 9 06-23-2011 04:47 AM
Duplicate Specific Fields Only with Subform too Abdulkawi Forms 1 04-14-2011 07:25 AM
Help: Save, Duplicate Record cum auto del specific field for user to input record Crystallew Forms 1 06-26-2007 08:05 AM
Populate duplicate blank fields Ciaran_Enright Modules & VBA 2 11-25-2005 07:33 AM
How to duplicate a specific record skelley Forms 1 09-09-2000 07:11 PM




All times are GMT -8. The time now is 12:52 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World