Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Forms (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=9)
-   -   [SOLVED] Help creating duplicate record with specific fields blank. (https://www.access-programmers.co.uk/forums/showthread.php?t=267071)

pheidlauf 07-21-2014 03:48 AM

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!

CJ_London 07-21-2014 03:58 AM

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

pheidlauf 07-21-2014 04:11 AM

Re: Help creating duplicate record with specific fields blank.
 
Quote:

Originally Posted by CJ_London (Post 1366846)
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 07-21-2014 05:13 AM

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 & ")"


pheidlauf 07-21-2014 07:41 AM

Re: Help creating duplicate record with specific fields blank.
 
Quote:

Originally Posted by CJ_London (Post 1366861)
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 07-21-2014 08:01 AM

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") & "#"

pheidlauf 07-21-2014 12:00 PM

Re: Help creating duplicate record with specific fields blank.
 
Quote:

Originally Posted by CJ_London (Post 1366944)
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 07-21-2014 02:45 PM

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

pheidlauf 07-22-2014 03:47 AM

Re: Help creating duplicate record with specific fields blank.
 
Quote:

Originally Posted by CJ_London (Post 1367087)
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)

CJ_London 07-22-2014 04:00 AM

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 07-22-2014 04:04 AM

Re: Help creating duplicate record with specific fields blank.
 
or add dbfailonerror as a parameter to the execute line

currendb.execute(qryStr,dbfailonerror)

pheidlauf 07-22-2014 04:08 AM

Re: Help creating duplicate record with specific fields blank.
 
Quote:

Originally Posted by CJ_London (Post 1367212)
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 07-22-2014 05:16 AM

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


CJ_London 07-22-2014 05:17 AM

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?

pheidlauf 07-22-2014 06:13 AM

Re: Help creating duplicate record with specific fields blank.
 
Quote:

Originally Posted by CJ_London (Post 1367240)
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



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

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