Allen Browne's Duplicate Record Code

  • Thread starter Thread starter PHisaw
  • Start date Start date
P

PHisaw

Hi,

Using the following code, can anyone please tell me why "Module" field is
copied with "forms_fsparepartsmainform" in the new record? All others are
duplicating properly. I can't seem to find error with this field.

Private Sub Command36_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!Customer = Me.Customer
!Date = Date
!Model = Me.Model
!SerialNumber = Me.SerialNumber
!Multiplier = Me.Multiplier
!Manufacturer = Me.Manufacturer
!Module = Me.Module
!CustomerItem = Me.CustomerItem
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !QuoteNumber

'Duplicate the related records: append query.
If Me.[fSparePartsSubform3].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tsparepartssubform3] ( QuoteNumber,
Model, Module, PartIDNumber, Qty, Class1, Class2, Class3, DelWks ) " & _
"SELECT " & lngID & " As NewID, Model, Module,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks " & _
"FROM [tsparepartssubform3] WHERE QuoteNumber = " &
Me.QuoteNumber & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"command36_Click"
Resume Exit_Handler
End Sub

Thanks in advance for your help.
Phisaw
 
Module is a reserved word. A pretty important one at that. Change the name
of the field. Use a naming convention to avoid this. Personally, I prefix
all field names with "fld", then you can use anything you want after it.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
....in fact, there's a few reserved words in there. Definately look into a
naming scheme.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



Jack Leach said:
Module is a reserved word. A pretty important one at that. Change the name
of the field. Use a naming convention to avoid this. Personally, I prefix
all field names with "fld", then you can use anything you want after it.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



PHisaw said:
Hi,

Using the following code, can anyone please tell me why "Module" field is
copied with "forms_fsparepartsmainform" in the new record? All others are
duplicating properly. I can't seem to find error with this field.

Private Sub Command36_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!Customer = Me.Customer
!Date = Date
!Model = Me.Model
!SerialNumber = Me.SerialNumber
!Multiplier = Me.Multiplier
!Manufacturer = Me.Manufacturer
!Module = Me.Module
!CustomerItem = Me.CustomerItem
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !QuoteNumber

'Duplicate the related records: append query.
If Me.[fSparePartsSubform3].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tsparepartssubform3] ( QuoteNumber,
Model, Module, PartIDNumber, Qty, Class1, Class2, Class3, DelWks ) " & _
"SELECT " & lngID & " As NewID, Model, Module,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks " & _
"FROM [tsparepartssubform3] WHERE QuoteNumber = " &
Me.QuoteNumber & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"command36_Click"
Resume Exit_Handler
End Sub

Thanks in advance for your help.
Phisaw
Click to expand...
Click to expand...
 
Thanks, Jack. I forgot about reserved words and especially module. Module
is on the form I'm trying to duplicate and I copied from there.

One other thing, can you tell me why I have to press the command button to
append records to a form 3-4 times before it will work. The hour glass pops
up briefly each time, but nothing happens until the 3rd or 4th click. Here's
the code:

Private Sub Command24_Click()

Dim db As DAO.Database
Dim strSql As String

Set db = CurrentDb()
Debug.Print strWhere

strSql = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, SPModule,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.SPModule, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.SPModule = tSparePartsTemplate.SPModule) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model & "' "
& _
" AND tSparePartsTemplate.SPModule= '" & Forms!fsparepartsmainform!SPModule
& "' "

db.Execute strSql, dbFailOnError

db.Close
Set db = Nothing

Me.fSparePartsSubform3.Requery


End Sub

If you can help with this, I would really appreciate it.

Thanks again,
Phisaw

Jack Leach said:
Module is a reserved word. A pretty important one at that. Change the name
of the field. Use a naming convention to avoid this. Personally, I prefix
all field names with "fld", then you can use anything you want after it.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



PHisaw said:
Hi,

Using the following code, can anyone please tell me why "Module" field is
copied with "forms_fsparepartsmainform" in the new record? All others are
duplicating properly. I can't seem to find error with this field.

Private Sub Command36_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!Customer = Me.Customer
!Date = Date
!Model = Me.Model
!SerialNumber = Me.SerialNumber
!Multiplier = Me.Multiplier
!Manufacturer = Me.Manufacturer
!Module = Me.Module
!CustomerItem = Me.CustomerItem
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !QuoteNumber

'Duplicate the related records: append query.
If Me.[fSparePartsSubform3].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tsparepartssubform3] ( QuoteNumber,
Model, Module, PartIDNumber, Qty, Class1, Class2, Class3, DelWks ) " & _
"SELECT " & lngID & " As NewID, Model, Module,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks " & _
"FROM [tsparepartssubform3] WHERE QuoteNumber = " &
Me.QuoteNumber & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"command36_Click"
Resume Exit_Handler
End Sub

Thanks in advance for your help.
Phisaw
Click to expand...
Click to expand...
 
I don't know. I can't really see anything in there that would keep the code
from running until you click 3-4 times. That's quite unusual behavior.
What I would do first is verify whether or not the code actually is running.
Put a MsgBox "Code Running" first thing in the sub before you do any of your
normal coding. This should tell you quite easily if the sub is actually
running or not.

One thing may be the load time on the query... it's possible that the
performance of the query is not up to par, and you are only noticing the
update after a couple clicks. But certainly, if you click a button and
there's an event procedure tied to it, it should be running. A msgbox at the
start of the sub is an easy way to verfiy this.

Also, I notice that you have Debug.Print strWhere in your procedure, but
strWhere does not seem to be dimensioned. Are you using Option Explicit at
the top of all of your modules? If not, you most certainly should be. This
will throw a compile error if you try to use a variable that has not been
defined. Problems in debugging that come with the failure to use this option
are considerable.

So, check your Option Explicit status on all modules (add it and compile,
one module at a time... you will not be able to compile until all issues are
resolved). One that's done, add the msgbox to the start of the procedure and
see if you get the message on the first click.

Report back and we'll take a look from there.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



PHisaw said:
Thanks, Jack. I forgot about reserved words and especially module. Module
is on the form I'm trying to duplicate and I copied from there.

One other thing, can you tell me why I have to press the command button to
append records to a form 3-4 times before it will work. The hour glass pops
up briefly each time, but nothing happens until the 3rd or 4th click. Here's
the code:

Private Sub Command24_Click()

Dim db As DAO.Database
Dim strSql As String

Set db = CurrentDb()
Debug.Print strWhere

strSql = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, SPModule,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.SPModule, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.SPModule = tSparePartsTemplate.SPModule) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model & "' "
& _
" AND tSparePartsTemplate.SPModule= '" & Forms!fsparepartsmainform!SPModule
& "' "

db.Execute strSql, dbFailOnError

db.Close
Set db = Nothing

Me.fSparePartsSubform3.Requery


End Sub

If you can help with this, I would really appreciate it.

Thanks again,
Phisaw

Jack Leach said:
Module is a reserved word. A pretty important one at that. Change the name
of the field. Use a naming convention to avoid this. Personally, I prefix
all field names with "fld", then you can use anything you want after it.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



PHisaw said:
Hi,

Using the following code, can anyone please tell me why "Module" field is
copied with "forms_fsparepartsmainform" in the new record? All others are
duplicating properly. I can't seem to find error with this field.

Private Sub Command36_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!Customer = Me.Customer
!Date = Date
!Model = Me.Model
!SerialNumber = Me.SerialNumber
!Multiplier = Me.Multiplier
!Manufacturer = Me.Manufacturer
!Module = Me.Module
!CustomerItem = Me.CustomerItem
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !QuoteNumber

'Duplicate the related records: append query.
If Me.[fSparePartsSubform3].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tsparepartssubform3] ( QuoteNumber,
Model, Module, PartIDNumber, Qty, Class1, Class2, Class3, DelWks ) " & _
"SELECT " & lngID & " As NewID, Model, Module,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks " & _
"FROM [tsparepartssubform3] WHERE QuoteNumber = " &
Me.QuoteNumber & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"command36_Click"
Resume Exit_Handler
End Sub

Thanks in advance for your help.
Phisaw
Click to expand...
Click to expand...
Click to expand...
 
Jack,

I included "refresh" and that aspect seems to be working now. I want to
include a msgbox for instances when a template has not yet been created for
model and module. I added an "if" statement and now error 424 'object
required' and failing on this line of code:

Set rst = dbs.OpenRecordset("tsparepartstemplate", dbOpenDynaset)

Here's complete code:

Private Sub Command24_DblClick(Cancel As Integer)
Dim db As DAO.Database
Dim strSql As String
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rst = dbs.OpenRecordset("tsparepartstemplate", dbOpenDynaset)

If "(rst![Model])= '" & Forms!fsparepartsmainform!Model & "' " & _
" AND (rst![SPModule])= '" & Forms!fsparepartsmainform!SPModule & "' " Then
strSql = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, SPModule,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.SPModule, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.SPModule = tSparePartsTemplate.SPModule) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model & "' "
& _
" AND tSparePartsTemplate.SPModule= '" & Forms!fsparepartsmainform!SPModule
& "' "
Else
MsgBox "Template does not exist for this model and module. Please use
button ""Create Template"" to go to form and enter template information."
End If
db.Execute strSql, dbFailOnError
Me.Refresh

db.Close
Set db = Nothing

End Sub

I would really appreciate it if you would review for error(s).
Thanks for your time and help.
Phisaw




Jack Leach said:
I don't know. I can't really see anything in there that would keep the code
from running until you click 3-4 times. That's quite unusual behavior.
What I would do first is verify whether or not the code actually is running.
Put a MsgBox "Code Running" first thing in the sub before you do any of your
normal coding. This should tell you quite easily if the sub is actually
running or not.

One thing may be the load time on the query... it's possible that the
performance of the query is not up to par, and you are only noticing the
update after a couple clicks. But certainly, if you click a button and
there's an event procedure tied to it, it should be running. A msgbox at the
start of the sub is an easy way to verfiy this.

Also, I notice that you have Debug.Print strWhere in your procedure, but
strWhere does not seem to be dimensioned. Are you using Option Explicit at
the top of all of your modules? If not, you most certainly should be. This
will throw a compile error if you try to use a variable that has not been
defined. Problems in debugging that come with the failure to use this option
are considerable.

So, check your Option Explicit status on all modules (add it and compile,
one module at a time... you will not be able to compile until all issues are
resolved). One that's done, add the msgbox to the start of the procedure and
see if you get the message on the first click.

Report back and we'll take a look from there.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



PHisaw said:
Thanks, Jack. I forgot about reserved words and especially module. Module
is on the form I'm trying to duplicate and I copied from there.

One other thing, can you tell me why I have to press the command button to
append records to a form 3-4 times before it will work. The hour glass pops
up briefly each time, but nothing happens until the 3rd or 4th click. Here's
the code:

Private Sub Command24_Click()

Dim db As DAO.Database
Dim strSql As String

Set db = CurrentDb()
Debug.Print strWhere

strSql = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, SPModule,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.SPModule, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.SPModule = tSparePartsTemplate.SPModule) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model & "' "
& _
" AND tSparePartsTemplate.SPModule= '" & Forms!fsparepartsmainform!SPModule
& "' "

db.Execute strSql, dbFailOnError

db.Close
Set db = Nothing

Me.fSparePartsSubform3.Requery


End Sub

If you can help with this, I would really appreciate it.

Thanks again,
Phisaw

Jack Leach said:
Module is a reserved word. A pretty important one at that. Change the name
of the field. Use a naming convention to avoid this. Personally, I prefix
all field names with "fld", then you can use anything you want after it.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:

Hi,

Using the following code, can anyone please tell me why "Module" field is
copied with "forms_fsparepartsmainform" in the new record? All others are
duplicating properly. I can't seem to find error with this field.

Private Sub Command36_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!Customer = Me.Customer
!Date = Date
!Model = Me.Model
!SerialNumber = Me.SerialNumber
!Multiplier = Me.Multiplier
!Manufacturer = Me.Manufacturer
!Module = Me.Module
!CustomerItem = Me.CustomerItem
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !QuoteNumber

'Duplicate the related records: append query.
If Me.[fSparePartsSubform3].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tsparepartssubform3] ( QuoteNumber,
Model, Module, PartIDNumber, Qty, Class1, Class2, Class3, DelWks ) " & _
"SELECT " & lngID & " As NewID, Model, Module,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks " & _
"FROM [tsparepartssubform3] WHERE QuoteNumber = " &
Me.QuoteNumber & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"command36_Click"
Resume Exit_Handler
End Sub

Thanks in advance for your help.
Phisaw
Click to expand...
Click to expand...
Click to expand...
 
Dim db As DAO.Database
Dim strSql As String
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rst = dbs.OpenRecordset("tsparepartstemplate", dbOpenDynaset)
Click to expand...


Well, plainly you have some issues declaring objects with a variable name
and using that name to refer to them. You have dimmed "db", set the
CurrentDb to "db", and then you try to open a recordset on "dbs" which, so
far as I can tell, isn't a variable (vba won't guess that you mean "db" when
you tell it "dbs")

You have dimmed "rs", but later refer to it as "rst", which, so far as I can
tell, isn't a variable.

You're going to have to pay a lot more attention to these types of details
if you aspire to be an adequate programmer.


I'm going to go out on a limb here and assume that you haven't tried using
Option Explicit per my suggestion in the earlier post. I would appreciate it
if you would do so before you come back with any other issues, because all of
the above are things that would not be allowed to happen if you implemented
the Option Explicit statement at the top of your modules.

You may notice that, if you try using Option Explicit, you are getting all
sorts of errors when trying to compile. NOT using it won't make those go
away... it will just make them go away until you try to run the code. Far
better to use Option Explicit and get them all out of the way right up front.
Either way, you're going to have to get them straightened out.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



PHisaw said:
Jack,

I included "refresh" and that aspect seems to be working now. I want to
include a msgbox for instances when a template has not yet been created for
model and module. I added an "if" statement and now error 424 'object
required' and failing on this line of code:

Set rst = dbs.OpenRecordset("tsparepartstemplate", dbOpenDynaset)

Here's complete code:

Private Sub Command24_DblClick(Cancel As Integer)
Dim db As DAO.Database
Dim strSql As String
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rst = dbs.OpenRecordset("tsparepartstemplate", dbOpenDynaset)

If "(rst![Model])= '" & Forms!fsparepartsmainform!Model & "' " & _
" AND (rst![SPModule])= '" & Forms!fsparepartsmainform!SPModule & "' " Then
strSql = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, SPModule,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.SPModule, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.SPModule = tSparePartsTemplate.SPModule) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model & "' "
& _
" AND tSparePartsTemplate.SPModule= '" & Forms!fsparepartsmainform!SPModule
& "' "
Else
MsgBox "Template does not exist for this model and module. Please use
button ""Create Template"" to go to form and enter template information."
End If
db.Execute strSql, dbFailOnError
Me.Refresh

db.Close
Set db = Nothing

End Sub

I would really appreciate it if you would review for error(s).
Thanks for your time and help.
Phisaw




Jack Leach said:
I don't know. I can't really see anything in there that would keep the code
from running until you click 3-4 times. That's quite unusual behavior.
What I would do first is verify whether or not the code actually is running.
Put a MsgBox "Code Running" first thing in the sub before you do any of your
normal coding. This should tell you quite easily if the sub is actually
running or not.

One thing may be the load time on the query... it's possible that the
performance of the query is not up to par, and you are only noticing the
update after a couple clicks. But certainly, if you click a button and
there's an event procedure tied to it, it should be running. A msgbox at the
start of the sub is an easy way to verfiy this.

Also, I notice that you have Debug.Print strWhere in your procedure, but
strWhere does not seem to be dimensioned. Are you using Option Explicit at
the top of all of your modules? If not, you most certainly should be. This
will throw a compile error if you try to use a variable that has not been
defined. Problems in debugging that come with the failure to use this option
are considerable.

So, check your Option Explicit status on all modules (add it and compile,
one module at a time... you will not be able to compile until all issues are
resolved). One that's done, add the msgbox to the start of the procedure and
see if you get the message on the first click.

Report back and we'll take a look from there.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



PHisaw said:
Thanks, Jack. I forgot about reserved words and especially module. Module
is on the form I'm trying to duplicate and I copied from there.

One other thing, can you tell me why I have to press the command button to
append records to a form 3-4 times before it will work. The hour glass pops
up briefly each time, but nothing happens until the 3rd or 4th click. Here's
the code:

Private Sub Command24_Click()

Dim db As DAO.Database
Dim strSql As String

Set db = CurrentDb()
Debug.Print strWhere

strSql = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, SPModule,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.SPModule, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.SPModule = tSparePartsTemplate.SPModule) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model & "' "
& _
" AND tSparePartsTemplate.SPModule= '" & Forms!fsparepartsmainform!SPModule
& "' "

db.Execute strSql, dbFailOnError

db.Close
Set db = Nothing

Me.fSparePartsSubform3.Requery


End Sub

If you can help with this, I would really appreciate it.

Thanks again,
Phisaw

:

Module is a reserved word. A pretty important one at that. Change the name
of the field. Use a naming convention to avoid this. Personally, I prefix
all field names with "fld", then you can use anything you want after it.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:

Hi,

Using the following code, can anyone please tell me why "Module" field is
copied with "forms_fsparepartsmainform" in the new record? All others are
duplicating properly. I can't seem to find error with this field.

Private Sub Command36_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!Customer = Me.Customer
!Date = Date
!Model = Me.Model
!SerialNumber = Me.SerialNumber
!Multiplier = Me.Multiplier
!Manufacturer = Me.Manufacturer
!Module = Me.Module
!CustomerItem = Me.CustomerItem
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !QuoteNumber

'Duplicate the related records: append query.
If Me.[fSparePartsSubform3].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tsparepartssubform3] ( QuoteNumber,
Model, Module, PartIDNumber, Qty, Class1, Class2, Class3, DelWks ) " & _
"SELECT " & lngID & " As NewID, Model, Module,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks " & _
"FROM [tsparepartssubform3] WHERE QuoteNumber = " &
Me.QuoteNumber & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"command36_Click"
Resume Exit_Handler
End Sub

Thanks in advance for your help.
Phisaw
Click to expand...
Click to expand...
Click to expand...
 
...plainly you have some issues...
You're going to have to pay a lot more attention to these types of details
if you aspire to be an adequate programmer
Click to expand...
...I would appreciate it
if you would do so before you come back with any other issues...
Click to expand...


It occured to me after I posted that this could come off as rather
offensive. My apologies, it wasn't meant that way. I have a tendancy to not
sugar coat things at all, and often it's taken the wrong when when spoken,
and the effect is even worse in written text. So my apologies if that came
across as rude, it surely wasn't meant to. But you should still be careful
of what you're doing in vba. The smallest error can screw your day right up,
and left unchecked they'll have you pulling out your hair, as well as those
who are trying to help.



--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



Jack Leach said:
Dim db As DAO.Database
Dim strSql As String
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rst = dbs.OpenRecordset("tsparepartstemplate", dbOpenDynaset)
Click to expand...


Well, plainly you have some issues declaring objects with a variable name
and using that name to refer to them. You have dimmed "db", set the
CurrentDb to "db", and then you try to open a recordset on "dbs" which, so
far as I can tell, isn't a variable (vba won't guess that you mean "db" when
you tell it "dbs")

You have dimmed "rs", but later refer to it as "rst", which, so far as I can
tell, isn't a variable.

You're going to have to pay a lot more attention to these types of details
if you aspire to be an adequate programmer.


I'm going to go out on a limb here and assume that you haven't tried using
Option Explicit per my suggestion in the earlier post. I would appreciate it
if you would do so before you come back with any other issues, because all of
the above are things that would not be allowed to happen if you implemented
the Option Explicit statement at the top of your modules.

You may notice that, if you try using Option Explicit, you are getting all
sorts of errors when trying to compile. NOT using it won't make those go
away... it will just make them go away until you try to run the code. Far
better to use Option Explicit and get them all out of the way right up front.
Either way, you're going to have to get them straightened out.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



PHisaw said:
Jack,

I included "refresh" and that aspect seems to be working now. I want to
include a msgbox for instances when a template has not yet been created for
model and module. I added an "if" statement and now error 424 'object
required' and failing on this line of code:

Set rst = dbs.OpenRecordset("tsparepartstemplate", dbOpenDynaset)

Here's complete code:

Private Sub Command24_DblClick(Cancel As Integer)
Dim db As DAO.Database
Dim strSql As String
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rst = dbs.OpenRecordset("tsparepartstemplate", dbOpenDynaset)

If "(rst![Model])= '" & Forms!fsparepartsmainform!Model & "' " & _
" AND (rst![SPModule])= '" & Forms!fsparepartsmainform!SPModule & "' " Then
strSql = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, SPModule,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.SPModule, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.SPModule = tSparePartsTemplate.SPModule) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model & "' "
& _
" AND tSparePartsTemplate.SPModule= '" & Forms!fsparepartsmainform!SPModule
& "' "
Else
MsgBox "Template does not exist for this model and module. Please use
button ""Create Template"" to go to form and enter template information."
End If
db.Execute strSql, dbFailOnError
Me.Refresh

db.Close
Set db = Nothing

End Sub

I would really appreciate it if you would review for error(s).
Thanks for your time and help.
Phisaw




Jack Leach said:
I don't know. I can't really see anything in there that would keep the code
from running until you click 3-4 times. That's quite unusual behavior.
What I would do first is verify whether or not the code actually is running.
Put a MsgBox "Code Running" first thing in the sub before you do any of your
normal coding. This should tell you quite easily if the sub is actually
running or not.

One thing may be the load time on the query... it's possible that the
performance of the query is not up to par, and you are only noticing the
update after a couple clicks. But certainly, if you click a button and
there's an event procedure tied to it, it should be running. A msgbox at the
start of the sub is an easy way to verfiy this.

Also, I notice that you have Debug.Print strWhere in your procedure, but
strWhere does not seem to be dimensioned. Are you using Option Explicit at
the top of all of your modules? If not, you most certainly should be. This
will throw a compile error if you try to use a variable that has not been
defined. Problems in debugging that come with the failure to use this option
are considerable.

So, check your Option Explicit status on all modules (add it and compile,
one module at a time... you will not be able to compile until all issues are
resolved). One that's done, add the msgbox to the start of the procedure and
see if you get the message on the first click.

Report back and we'll take a look from there.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:

Thanks, Jack. I forgot about reserved words and especially module. Module
is on the form I'm trying to duplicate and I copied from there.

One other thing, can you tell me why I have to press the command button to
append records to a form 3-4 times before it will work. The hour glass pops
up briefly each time, but nothing happens until the 3rd or 4th click. Here's
the code:

Private Sub Command24_Click()

Dim db As DAO.Database
Dim strSql As String

Set db = CurrentDb()
Debug.Print strWhere

strSql = "INSERT INTO tsparepartssubform3 ( QuoteNumber, Model, SPModule,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks )" & _
"SELECT tSparePartsMainForm.QuoteNumber, tSparePartsTemplate.Model,
tSparePartsTemplate.SPModule, tSparePartsTemplate.PartIDNumber,
tSparePartsTemplate.Qty, tSparePartsTemplate.Class1,
tSparePartsTemplate.Class2, tSparePartsTemplate.Class3,
tSparePartsTemplate.DelWks " & _
"FROM tSparePartsMainForm INNER JOIN tSparePartsTemplate ON
(tSparePartsMainForm.SPModule = tSparePartsTemplate.SPModule) AND
(tSparePartsMainForm.Model = tSparePartsTemplate.Model)" & _
"WHERE tSparePartsMainForm.QuoteNumber= " &
Forms!fsparepartsmainform!QuoteNumber & _
" AND tSparePartsTemplate.Model= '" & Forms!fsparepartsmainform!Model & "' "
& _
" AND tSparePartsTemplate.SPModule= '" & Forms!fsparepartsmainform!SPModule
& "' "

db.Execute strSql, dbFailOnError

db.Close
Set db = Nothing

Me.fSparePartsSubform3.Requery


End Sub

If you can help with this, I would really appreciate it.

Thanks again,
Phisaw

:

Module is a reserved word. A pretty important one at that. Change the name
of the field. Use a naming convention to avoid this. Personally, I prefix
all field names with "fld", then you can use anything you want after it.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



:

Hi,

Using the following code, can anyone please tell me why "Module" field is
copied with "forms_fsparepartsmainform" in the new record? All others are
duplicating properly. I can't seem to find error with this field.

Private Sub Command36_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!Customer = Me.Customer
!Date = Date
!Model = Me.Model
!SerialNumber = Me.SerialNumber
!Multiplier = Me.Multiplier
!Manufacturer = Me.Manufacturer
!Module = Me.Module
!CustomerItem = Me.CustomerItem
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngID = !QuoteNumber

'Duplicate the related records: append query.
If Me.[fSparePartsSubform3].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tsparepartssubform3] ( QuoteNumber,
Model, Module, PartIDNumber, Qty, Class1, Class2, Class3, DelWks ) " & _
"SELECT " & lngID & " As NewID, Model, Module,
PartIDNumber, Qty, Class1, Class2, Class3, DelWks " & _
"FROM [tsparepartssubform3] WHERE QuoteNumber = " &
Me.QuoteNumber & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"command36_Click"
Resume Exit_Handler
End Sub

Thanks in advance for your help.
Phisaw
Click to expand...
Click to expand...
Click to expand...
 
Back
Top