Help creating duplicate record with specific fields blank. (1 Viewer)

pheidlauf

Registered User.
Local time
Today, 12:59
Joined
Jun 5, 2013
Messages
42
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!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Feb 19, 2013
Messages
16,553
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
 

pheidlauf

Registered User.
Local time
Today, 12:59
Joined
Jun 5, 2013
Messages
42
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!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Feb 19, 2013
Messages
16,553
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 & ")"
 

pheidlauf

Registered User.
Local time
Today, 12:59
Joined
Jun 5, 2013
Messages
42
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?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Feb 19, 2013
Messages
16,553
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") & "#"
 

pheidlauf

Registered User.
Local time
Today, 12:59
Joined
Jun 5, 2013
Messages
42
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!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Feb 19, 2013
Messages
16,553

pheidlauf

Registered User.
Local time
Today, 12:59
Joined
Jun 5, 2013
Messages
42
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/a...7-reserved-words-and-symbols-HA010030643.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)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Feb 19, 2013
Messages
16,553
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

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Feb 19, 2013
Messages
16,553
or add dbfailonerror as a parameter to the execute line

currendb.execute(qryStr,dbfailonerror)
 

pheidlauf

Registered User.
Local time
Today, 12:59
Joined
Jun 5, 2013
Messages
42
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

Registered User.
Local time
Today, 12:59
Joined
Jun 5, 2013
Messages
42
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

Registered User.
Local time
Today, 12:59
Joined
Jun 5, 2013
Messages
42
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/forums/showthread.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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Feb 19, 2013
Messages
16,553
Also, the date formatting I made based off of your original post is not working well:

Public Function getDateText(ByVal field_name As Date) As String

go about formatting my query for a Yes/No field
Yes/No,True/False and -1/0 are all the same thing which is numeric so I would just use the getnumeric function
 

pheidlauf

Registered User.
Local time
Today, 12:59
Joined
Jun 5, 2013
Messages
42
Yes/No,True/False and -1/0 are all the same thing which is numeric so I would just use the getnumeric function

Fixed it for good this time (I think :O).

Code:
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 = "'" & Replace(Me(field_name), "'", "''") & "'"
End Function

Public Function getDateText(ByVal field_name As String) As String
    getDateText = "#" & Format(Me(field_name), "mm/dd/yyyy") & "#"
End Function

I'm running all True/False fields through the getNumericText function.
 

Users who are viewing this thread

Top Bottom