Command button not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a command button that I'm using for my form and subform. Mr. Allen
Brown was very nice to have one posted in his tips section on his webpage. I
have a question does the command button go on the form or the subform. I
cannot get it to work?

Thanks,
Keri
 
Here is the code that I'm using for the command button. I motified from Mr.
Allen Browne web page on tips for advance access users. I'm trying to fix
the error in it but Access freezes up everytime I tried to fix the problem.
Private Sub cmdDupe_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
" !Project Name = Me.Project Name
!FHA Number = Me.FHA Number
!Project Manager = Me. Project Manager"
'etc for other fields.
.Update

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

'Duplicate the related records: append query.
If
Me.[F_sub_FinancialDataSubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [F_sub_FinancialData]
(Mnth/YrFormSent, DateReceived, Excess Income Earned in Excess of Basic
Rents, Excess Income Retained (Project), Excess Income owed to HUD, Paid to
HUD, Number of Check Received,FHA_Num ) " & _
"SELECT " & lngID & " As NewID, Mnth/YrFormSent,
DateReceived, Excess Income Earned in Excess of Basic Rents, Excess Income
Retained (Project), Excess Income owed to HUD, Paid to HUD, Number of Check
Received,FHA_Num " & _
"FROM [F_sub_FinancialData] WHERE FHA_Num = " &
Me.FHA_Num & ";"
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, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
The quotation marks around the part of the code after Add new is marked in
red in the VBA screen. I went fix it but it keeps frezzing and closing on
me. Please let me know what I'm doing wrong. I haven't been able to figure
it out.

thanks, Keri
 
1. That command button needs to be on the main form
2. The quotation marks before !Project Name and after Me.Project Manager
should not be there, remove them.
3. You have broken one of the ten commandments of Access by putting spaces
in your field names. As a result you will now need to enclose the field
names in square brackets e.g.

![Project Name] = Me.[Project Name]

http://www.mvps.org/access/tencommandments.htm

Keri said:
Here is the code that I'm using for the command button. I motified from Mr.
Allen Browne web page on tips for advance access users. I'm trying to fix
the error in it but Access freezes up everytime I tried to fix the problem.
Private Sub cmdDupe_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
" !Project Name = Me.Project Name
!FHA Number = Me.FHA Number
!Project Manager = Me. Project Manager"
'etc for other fields.
.Update

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

'Duplicate the related records: append query.
If
Me.[F_sub_FinancialDataSubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [F_sub_FinancialData]
(Mnth/YrFormSent, DateReceived, Excess Income Earned in Excess of Basic
Rents, Excess Income Retained (Project), Excess Income owed to HUD, Paid to
HUD, Number of Check Received,FHA_Num ) " & _
"SELECT " & lngID & " As NewID, Mnth/YrFormSent,
DateReceived, Excess Income Earned in Excess of Basic Rents, Excess Income
Retained (Project), Excess Income owed to HUD, Paid to HUD, Number of Check
Received,FHA_Num " & _
"FROM [F_sub_FinancialData] WHERE FHA_Num = " &
Me.FHA_Num & ";"
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, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
The quotation marks around the part of the code after Add new is marked in
red in the VBA screen. I went fix it but it keeps frezzing and closing on
me. Please let me know what I'm doing wrong. I haven't been able to figure
it out.

thanks, Keri


Baz said:
It depends entirely on what you want it to do.

webpage.
I
Click to expand...
Click to expand...
 
Hi I retype out the code provided on mr. Allen browne tip page. I also
pulled out a book to follow through. Here is the code.
Private Sub Command13_Click()
'On Error GoTo Err_Handler
'Purpose: Dulicate 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 "Seclect the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!ProjectName = Me.Project_Name
!FHANumber = Me.FHA_Number
!ProjectManager = Me.Project_Manager
'etc for other fields
.Update

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

'Duplicate the related records: append query
If
Me.[F_sub_FinancialDataSubform].Form.Recordset.RecordCount > 0 Then
strSqul = "INSERT INTO [F_sub_FinancialData] (FHA_Num,
Mnth/YrFormSent, DateRecevied, ExcessIncomeEarned, ExcessIncomeRetained,
ExcessIncomeowedtoHUD, PaidtoHUD, CheckNumber) " & _
"SELECT " & lngID & "As NewID, FHA_Num, Mnth/YrFormSent,
DateRecevied,ExcessIncomeEarned, ExcessIncomeRetained, ExcessIncomeowedtoHUD,
PaidtoHUD, CheckNumber " & _
"From [F_sub_FinancialData] WHERE FHA_Num = " &
Me.FHA_Num & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no
related reocrds."
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub

I still can not get it to work. I've tried debugging it but I'm not sure
what I'm doing wrong.

Baz said:
1. That command button needs to be on the main form
2. The quotation marks before !Project Name and after Me.Project Manager
should not be there, remove them.
3. You have broken one of the ten commandments of Access by putting spaces
in your field names. As a result you will now need to enclose the field
names in square brackets e.g.

![Project Name] = Me.[Project Name]

http://www.mvps.org/access/tencommandments.htm

Keri said:
Here is the code that I'm using for the command button. I motified from Mr.
Allen Browne web page on tips for advance access users. I'm trying to fix
the error in it but Access freezes up everytime I tried to fix the problem.
Private Sub cmdDupe_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
" !Project Name = Me.Project Name
!FHA Number = Me.FHA Number
!Project Manager = Me. Project Manager"
'etc for other fields.
.Update

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

'Duplicate the related records: append query.
If
Me.[F_sub_FinancialDataSubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [F_sub_FinancialData]
(Mnth/YrFormSent, DateReceived, Excess Income Earned in Excess of Basic
Rents, Excess Income Retained (Project), Excess Income owed to HUD, Paid to
HUD, Number of Check Received,FHA_Num ) " & _
"SELECT " & lngID & " As NewID, Mnth/YrFormSent,
DateReceived, Excess Income Earned in Excess of Basic Rents, Excess Income
Retained (Project), Excess Income owed to HUD, Paid to HUD, Number of Check
Received,FHA_Num " & _
"FROM [F_sub_FinancialData] WHERE FHA_Num = " &
Me.FHA_Num & ";"
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, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
The quotation marks around the part of the code after Add new is marked in
red in the VBA screen. I went fix it but it keeps frezzing and closing on
me. Please let me know what I'm doing wrong. I haven't been able to figure
it out.

thanks, Keri


Baz said:
It depends entirely on what you want it to do.

I have a command button that I'm using for my form and subform. Mr. Allen
Brown was very nice to have one posted in his tips section on his webpage.
I
have a question does the command button go on the form or the subform. I
cannot get it to work?

Thanks,
Keri
Click to expand...
Click to expand...
Click to expand...
 
Sorry, I'm not going to examine all that code in detail looking for possible
problems. Please describe exactly what happens. Is there an error message,
and if so what is it? Which line gets highlighted, if any?

Keri said:
Hi I retype out the code provided on mr. Allen browne tip page. I also
pulled out a book to follow through. Here is the code.
Private Sub Command13_Click()
'On Error GoTo Err_Handler
'Purpose: Dulicate 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 "Seclect the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!ProjectName = Me.Project_Name
!FHANumber = Me.FHA_Number
!ProjectManager = Me.Project_Manager
'etc for other fields
.Update

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

'Duplicate the related records: append query
If
Me.[F_sub_FinancialDataSubform].Form.Recordset.RecordCount > 0 Then
strSqul = "INSERT INTO [F_sub_FinancialData] (FHA_Num,
Mnth/YrFormSent, DateRecevied, ExcessIncomeEarned, ExcessIncomeRetained,
ExcessIncomeowedtoHUD, PaidtoHUD, CheckNumber) " & _
"SELECT " & lngID & "As NewID, FHA_Num, Mnth/YrFormSent,
DateRecevied,ExcessIncomeEarned, ExcessIncomeRetained, ExcessIncomeowedtoHUD,
PaidtoHUD, CheckNumber " & _
"From [F_sub_FinancialData] WHERE FHA_Num = " &
Me.FHA_Num & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no
related reocrds."
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub

I still can not get it to work. I've tried debugging it but I'm not sure
what I'm doing wrong.

Baz said:
1. That command button needs to be on the main form
2. The quotation marks before !Project Name and after Me.Project Manager
should not be there, remove them.
3. You have broken one of the ten commandments of Access by putting spaces
in your field names. As a result you will now need to enclose the field
names in square brackets e.g.

![Project Name] = Me.[Project Name]

http://www.mvps.org/access/tencommandments.htm

Keri said:
Here is the code that I'm using for the command button. I motified
Click to expand...
from
Mr.
Allen Browne web page on tips for advance access users. I'm trying to fix
the error in it but Access freezes up everytime I tried to fix the problem.
Private Sub cmdDupe_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
" !Project Name = Me.Project Name
!FHA Number = Me.FHA Number
!Project Manager = Me. Project Manager"
'etc for other fields.
.Update

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

'Duplicate the related records: append query.
If
Me.[F_sub_FinancialDataSubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [F_sub_FinancialData]
(Mnth/YrFormSent, DateReceived, Excess Income Earned in Excess of Basic
Rents, Excess Income Retained (Project), Excess Income owed to HUD,
Click to expand...
Paid
to
HUD, Number of Check Received,FHA_Num ) " & _
"SELECT " & lngID & " As NewID, Mnth/YrFormSent,
DateReceived, Excess Income Earned in Excess of Basic Rents, Excess Income
Retained (Project), Excess Income owed to HUD, Paid to HUD, Number of Check
Received,FHA_Num " & _
"FROM [F_sub_FinancialData] WHERE FHA_Num = " &
Me.FHA_Num & ";"
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, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
The quotation marks around the part of the code after Add new is marked in
red in the VBA screen. I went fix it but it keeps frezzing and closing on
me. Please let me know what I'm doing wrong. I haven't been able to figure
it out.

thanks, Keri


:

It depends entirely on what you want it to do.

I have a command button that I'm using for my form and subform.
Click to expand...
Mr.
Allen
Brown was very nice to have one posted in his tips section on his webpage.
I
have a question does the command button go on the form or the
Click to expand...
subform.
I
cannot get it to work?

Thanks,
Keri
Click to expand...
Click to expand...
Click to expand...
 
Private Sub Command13_Click() is highlight in yellow.


Baz said:
Sorry, I'm not going to examine all that code in detail looking for possible
problems. Please describe exactly what happens. Is there an error message,
and if so what is it? Which line gets highlighted, if any?

Keri said:
Hi I retype out the code provided on mr. Allen browne tip page. I also
pulled out a book to follow through. Here is the code.
Private Sub Command13_Click()
'On Error GoTo Err_Handler
'Purpose: Dulicate 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 "Seclect the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!ProjectName = Me.Project_Name
!FHANumber = Me.FHA_Number
!ProjectManager = Me.Project_Manager
'etc for other fields
.Update

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

'Duplicate the related records: append query
If
Me.[F_sub_FinancialDataSubform].Form.Recordset.RecordCount > 0 Then
strSqul = "INSERT INTO [F_sub_FinancialData] (FHA_Num,
Mnth/YrFormSent, DateRecevied, ExcessIncomeEarned, ExcessIncomeRetained,
ExcessIncomeowedtoHUD, PaidtoHUD, CheckNumber) " & _
"SELECT " & lngID & "As NewID, FHA_Num, Mnth/YrFormSent,
DateRecevied,ExcessIncomeEarned, ExcessIncomeRetained, ExcessIncomeowedtoHUD,
PaidtoHUD, CheckNumber " & _
"From [F_sub_FinancialData] WHERE FHA_Num = " &
Me.FHA_Num & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no
related reocrds."
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub

I still can not get it to work. I've tried debugging it but I'm not sure
what I'm doing wrong.

Baz said:
1. That command button needs to be on the main form
2. The quotation marks before !Project Name and after Me.Project Manager
should not be there, remove them.
3. You have broken one of the ten commandments of Access by putting spaces
in your field names. As a result you will now need to enclose the field
names in square brackets e.g.

![Project Name] = Me.[Project Name]

http://www.mvps.org/access/tencommandments.htm

Here is the code that I'm using for the command button. I motified from
Mr.
Allen Browne web page on tips for advance access users. I'm trying to fix
the error in it but Access freezes up everytime I tried to fix the
problem.
Private Sub cmdDupe_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
" !Project Name = Me.Project Name
!FHA Number = Me.FHA Number
!Project Manager = Me. Project Manager"
'etc for other fields.
.Update

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

'Duplicate the related records: append query.
If
Me.[F_sub_FinancialDataSubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [F_sub_FinancialData]
(Mnth/YrFormSent, DateReceived, Excess Income Earned in Excess of Basic
Rents, Excess Income Retained (Project), Excess Income owed to HUD, Paid
to
HUD, Number of Check Received,FHA_Num ) " & _
"SELECT " & lngID & " As NewID, Mnth/YrFormSent,
DateReceived, Excess Income Earned in Excess of Basic Rents, Excess Income
Retained (Project), Excess Income owed to HUD, Paid to HUD, Number of
Check
Received,FHA_Num " & _
"FROM [F_sub_FinancialData] WHERE FHA_Num = " &
Me.FHA_Num & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
The quotation marks around the part of the code after Add new is marked in
red in the VBA screen. I went fix it but it keeps frezzing and closing on
me. Please let me know what I'm doing wrong. I haven't been able to
figure
it out.

thanks, Keri


:

It depends entirely on what you want it to do.

I have a command button that I'm using for my form and subform. Mr.
Allen
Brown was very nice to have one posted in his tips section on his
webpage.
I
have a question does the command button go on the form or the subform.
I
cannot get it to work?

Thanks,
Keri
Click to expand...
Click to expand...
Click to expand...
 
OK so that tells me it doesn't compile, but what does the *error message*
say?

Keri said:
Private Sub Command13_Click() is highlight in yellow.


Baz said:
Sorry, I'm not going to examine all that code in detail looking for possible
problems. Please describe exactly what happens. Is there an error message,
and if so what is it? Which line gets highlighted, if any?

Keri said:
Hi I retype out the code provided on mr. Allen browne tip page. I also
pulled out a book to follow through. Here is the code.
Private Sub Command13_Click()
'On Error GoTo Err_Handler
'Purpose: Dulicate 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 "Seclect the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!ProjectName = Me.Project_Name
!FHANumber = Me.FHA_Number
!ProjectManager = Me.Project_Manager
'etc for other fields
.Update

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

'Duplicate the related records: append query
If
Me.[F_sub_FinancialDataSubform].Form.Recordset.RecordCount > 0 Then
strSqul = "INSERT INTO [F_sub_FinancialData] (FHA_Num,
Mnth/YrFormSent, DateRecevied, ExcessIncomeEarned, ExcessIncomeRetained,
ExcessIncomeowedtoHUD, PaidtoHUD, CheckNumber) " & _
"SELECT " & lngID & "As NewID, FHA_Num, Mnth/YrFormSent,
DateRecevied,ExcessIncomeEarned, ExcessIncomeRetained, ExcessIncomeowedtoHUD,
PaidtoHUD, CheckNumber " & _
"From [F_sub_FinancialData] WHERE FHA_Num = " &
Me.FHA_Num & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no
related reocrds."
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub

I still can not get it to work. I've tried debugging it but I'm not sure
what I'm doing wrong.

:

1. That command button needs to be on the main form
2. The quotation marks before !Project Name and after Me.Project Manager
should not be there, remove them.
3. You have broken one of the ten commandments of Access by putting spaces
in your field names. As a result you will now need to enclose the field
names in square brackets e.g.

![Project Name] = Me.[Project Name]

http://www.mvps.org/access/tencommandments.htm

Here is the code that I'm using for the command button. I motified from
Mr.
Allen Browne web page on tips for advance access users. I'm
Click to expand...
trying to
fix
the error in it but Access freezes up everytime I tried to fix the
problem.
Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records
Click to expand...
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
" !Project Name = Me.Project Name
!FHA Number = Me.FHA Number
!Project Manager = Me. Project Manager"
'etc for other fields.
.Update

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

'Duplicate the related records: append query.
If
Me.[F_sub_FinancialDataSubform].Form.RecordsetClone.RecordCount >
Click to expand...
0
Then
strSql = "INSERT INTO [F_sub_FinancialData]
(Mnth/YrFormSent, DateReceived, Excess Income Earned in Excess of Basic
Rents, Excess Income Retained (Project), Excess Income owed to
Click to expand...
HUD,
Paid
to
HUD, Number of Check Received,FHA_Num ) " & _
"SELECT " & lngID & " As NewID, Mnth/YrFormSent,
DateReceived, Excess Income Earned in Excess of Basic Rents,
Click to expand...
Excess
Income
Retained (Project), Excess Income owed to HUD, Paid to HUD, Number of
Check
Received,FHA_Num " & _
"FROM [F_sub_FinancialData] WHERE FHA_Num = " &
Me.FHA_Num & ";"
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, ,
"cmdDupe_Click"
Resume Exit_Handler
End Sub
The quotation marks around the part of the code after Add new is marked in
red in the VBA screen. I went fix it but it keeps frezzing and closing on
me. Please let me know what I'm doing wrong. I haven't been able to
figure
it out.

thanks, Keri


:

It depends entirely on what you want it to do.

I have a command button that I'm using for my form and
Click to expand...
subform.
Mr.
Allen
Brown was very nice to have one posted in his tips section on his
webpage.
I
have a question does the command button go on the form or the subform.
I
cannot get it to work?

Thanks,
Keri
Click to expand...
Click to expand...
Click to expand...
 
Back
Top