End Function From Private Sub...

T

TL

I have been having problems getting a Copy to New Record button to work. I
have finally determined that the autofill function is interfering with the
Copy to New Record button. The Copy to New Record code is below:

Private Sub CopyRecord_Click()
On Error GoTo Err_CopyRecord_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_CopyRecord_Click:
Exit Sub

Err_CopyRecord_Click:
MsgBox Err.Description
Resume Exit_CopyRecord_Click

End Sub

The code for the AutoFillNewRecord Function is below:

Function AutoFillNewRecord(F As Form)
Dim RS As Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

'Exit if not on the new record.
If Not F.NewRecord Then Exit Function

'Goto the last record of the form recordset (to autofill form)
Set RS = F.RecordsetClone
RS.MoveLast
'Exit if you cannot move to the last record(no records).
If Err <> 0 Then Exit Function

'Get the list of fields to autofill.
FillFields = ";" & F![AutoFillNewRecordFields] & ";"

'If there is no criteria field, then set flag indicating ALL
'fields should be autofilled.
FillAllFields = Err <> 0

F.Painting = False

'Visit each field on the form.
For Each C In F
'Fill the field if ALL fields are to be filled OR if the
'...ControlSource field cn be found inthe FillFields list.
If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0 Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function

Is there anyway to end the AutoFillNewRecord Function from the Private Sub
CopyRecord? I am very new to Access and VBA and will appreciate any help
given.

Thanks,

Tiffany
 
R

Rob Oldfield

Set up a global boolean variable (called Duplicate for example) and set it
to false. Set the autofill code to only run if the variable is false. In
your CopyRecord code set it to true, and then back to false at the end.

More importantly, you should figure out which part of the copy command is
triggering the AutoFill sub (why is it a function? it's not returning any
value....which is what functions do...)
 
T

TL

Rob,

Thanks for your help. I have little or no knowledge of why it was
originally setup as a function. I got the code for this from Microsoft's
website. The code was in a sample database called FrmSmp97.exe. Access 97
is the version my company has. I adapted this code for the error tracking
database that I created. My only guess about why it is a function is because
it has to look at values from a previous record, and return those values to a
new record. Unfortunately, I have no way of knowing if that is a correct
assessment.

Also, because my experience with writing VBA code is limited, I have no idea
how find out where the conflict is occurring in the Copy To New Record Code.
I will try out what you suggested and post back here with the results.

Thanks,

Tiffany

Rob Oldfield said:
Set up a global boolean variable (called Duplicate for example) and set it
to false. Set the autofill code to only run if the variable is false. In
your CopyRecord code set it to true, and then back to false at the end.

More importantly, you should figure out which part of the copy command is
triggering the AutoFill sub (why is it a function? it's not returning any
value....which is what functions do...)


TL said:
I have been having problems getting a Copy to New Record button to work. I
have finally determined that the autofill function is interfering with the
Copy to New Record button. The Copy to New Record code is below:

Private Sub CopyRecord_Click()
On Error GoTo Err_CopyRecord_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_CopyRecord_Click:
Exit Sub

Err_CopyRecord_Click:
MsgBox Err.Description
Resume Exit_CopyRecord_Click

End Sub

The code for the AutoFillNewRecord Function is below:

Function AutoFillNewRecord(F As Form)
Dim RS As Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

'Exit if not on the new record.
If Not F.NewRecord Then Exit Function

'Goto the last record of the form recordset (to autofill form)
Set RS = F.RecordsetClone
RS.MoveLast
'Exit if you cannot move to the last record(no records).
If Err <> 0 Then Exit Function

'Get the list of fields to autofill.
FillFields = ";" & F![AutoFillNewRecordFields] & ";"

'If there is no criteria field, then set flag indicating ALL
'fields should be autofilled.
FillAllFields = Err <> 0

F.Painting = False

'Visit each field on the form.
For Each C In F
'Fill the field if ALL fields are to be filled OR if the
'...ControlSource field cn be found inthe FillFields list.
If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0 Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function

Is there anyway to end the AutoFillNewRecord Function from the Private Sub
CopyRecord? I am very new to Access and VBA and will appreciate any help
given.

Thanks,

Tiffany
Click to expand...
Click to expand...
 
T

TL

Rob,

I placed the following at the beginning of the code to Copy To New Record.

Set booCopy = True

' And placed this at the end:

Set booCopy = False

'See below for the placement of the code for the Autofill Function:

On Error Resume Next

' If booCopy is False, then run AutoFill, If booCopy is True, End Function

Set booCopy = False

If booCopy = True Then Exit Function

But now I get a compile error - "Object required" I declared my global
variable as follows:

Public booCopy As Boolean

After I click OK, the "booCopy" from Set booCopy = True is highlighted.

I know I must be missing something really simple, but I can't figure it out.
Please help! Thanks!

Tiffany

Rob Oldfield said:
Set up a global boolean variable (called Duplicate for example) and set it
to false. Set the autofill code to only run if the variable is false. In
your CopyRecord code set it to true, and then back to false at the end.

More importantly, you should figure out which part of the copy command is
triggering the AutoFill sub (why is it a function? it's not returning any
value....which is what functions do...)


TL said:
I have been having problems getting a Copy to New Record button to work. I
have finally determined that the autofill function is interfering with the
Copy to New Record button. The Copy to New Record code is below:

Private Sub CopyRecord_Click()
On Error GoTo Err_CopyRecord_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_CopyRecord_Click:
Exit Sub

Err_CopyRecord_Click:
MsgBox Err.Description
Resume Exit_CopyRecord_Click

End Sub

The code for the AutoFillNewRecord Function is below:

Function AutoFillNewRecord(F As Form)
Dim RS As Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

'Exit if not on the new record.
If Not F.NewRecord Then Exit Function

'Goto the last record of the form recordset (to autofill form)
Set RS = F.RecordsetClone
RS.MoveLast
'Exit if you cannot move to the last record(no records).
If Err <> 0 Then Exit Function

'Get the list of fields to autofill.
FillFields = ";" & F![AutoFillNewRecordFields] & ";"

'If there is no criteria field, then set flag indicating ALL
'fields should be autofilled.
FillAllFields = Err <> 0

F.Painting = False

'Visit each field on the form.
For Each C In F
'Fill the field if ALL fields are to be filled OR if the
'...ControlSource field cn be found inthe FillFields list.
If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0 Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function

Is there anyway to end the AutoFillNewRecord Function from the Private Sub
CopyRecord? I am very new to Access and VBA and will appreciate any help
given.

Thanks,

Tiffany
Click to expand...
Click to expand...
 
R

Rob Oldfield

Try just removing the 'set' whenever you're assigning a value to booCopy.
It should only be used with object references.

You also don't need to set it to false in your AutoFill code... if you
have...

booCopy = False
If booCopy = True Then Exit Function

.... then it's never going to exit the function.


TL said:
Rob,

I placed the following at the beginning of the code to Copy To New Record.

Set booCopy = True

' And placed this at the end:

Set booCopy = False

'See below for the placement of the code for the Autofill Function:

On Error Resume Next

' If booCopy is False, then run AutoFill, If booCopy is True, End Function

Set booCopy = False

If booCopy = True Then Exit Function

But now I get a compile error - "Object required" I declared my global
variable as follows:

Public booCopy As Boolean

After I click OK, the "booCopy" from Set booCopy = True is highlighted.

I know I must be missing something really simple, but I can't figure it out.
Please help! Thanks!

Tiffany

Rob Oldfield said:
Set up a global boolean variable (called Duplicate for example) and set it
to false. Set the autofill code to only run if the variable is false. In
your CopyRecord code set it to true, and then back to false at the end.

More importantly, you should figure out which part of the copy command is
triggering the AutoFill sub (why is it a function? it's not returning any
value....which is what functions do...)


TL said:
I have been having problems getting a Copy to New Record button to
Click to expand...
work.
I
have finally determined that the autofill function is interfering with the
Copy to New Record button. The Copy to New Record code is below:

Private Sub CopyRecord_Click()
On Error GoTo Err_CopyRecord_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_CopyRecord_Click:
Exit Sub

Err_CopyRecord_Click:
MsgBox Err.Description
Resume Exit_CopyRecord_Click

End Sub

The code for the AutoFillNewRecord Function is below:

Function AutoFillNewRecord(F As Form)
Dim RS As Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

'Exit if not on the new record.
If Not F.NewRecord Then Exit Function

'Goto the last record of the form recordset (to autofill form)
Set RS = F.RecordsetClone
RS.MoveLast
'Exit if you cannot move to the last record(no records).
If Err <> 0 Then Exit Function

'Get the list of fields to autofill.
FillFields = ";" & F![AutoFillNewRecordFields] & ";"

'If there is no criteria field, then set flag indicating ALL
'fields should be autofilled.
FillAllFields = Err <> 0

F.Painting = False

'Visit each field on the form.
For Each C In F
'Fill the field if ALL fields are to be filled OR if the
'...ControlSource field cn be found inthe FillFields list.
If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") >
Click to expand...
0
Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function

Is there anyway to end the AutoFillNewRecord Function from the Private Sub
CopyRecord? I am very new to Access and VBA and will appreciate any help
given.

Thanks,

Tiffany
Click to expand...
Click to expand...
Click to expand...
 
T

TL

Rob,

You are AWESOME! It is working perfectly. I appreciate your help very
much!

Thanks!

Tiffany

Rob Oldfield said:
Try just removing the 'set' whenever you're assigning a value to booCopy.
It should only be used with object references.

You also don't need to set it to false in your AutoFill code... if you
have...

booCopy = False
If booCopy = True Then Exit Function

.... then it's never going to exit the function.


TL said:
Rob,

I placed the following at the beginning of the code to Copy To New Record.

Set booCopy = True

' And placed this at the end:

Set booCopy = False

'See below for the placement of the code for the Autofill Function:

On Error Resume Next

' If booCopy is False, then run AutoFill, If booCopy is True, End Function

Set booCopy = False

If booCopy = True Then Exit Function

But now I get a compile error - "Object required" I declared my global
variable as follows:

Public booCopy As Boolean

After I click OK, the "booCopy" from Set booCopy = True is highlighted.

I know I must be missing something really simple, but I can't figure it out.
Please help! Thanks!

Tiffany

Rob Oldfield said:
Set up a global boolean variable (called Duplicate for example) and set it
to false. Set the autofill code to only run if the variable is false. In
your CopyRecord code set it to true, and then back to false at the end.

More importantly, you should figure out which part of the copy command is
triggering the AutoFill sub (why is it a function? it's not returning any
value....which is what functions do...)


I have been having problems getting a Copy to New Record button to work.
I
have finally determined that the autofill function is interfering with the
Copy to New Record button. The Copy to New Record code is below:

Private Sub CopyRecord_Click()
On Error GoTo Err_CopyRecord_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_CopyRecord_Click:
Exit Sub

Err_CopyRecord_Click:
MsgBox Err.Description
Resume Exit_CopyRecord_Click

End Sub

The code for the AutoFillNewRecord Function is below:

Function AutoFillNewRecord(F As Form)
Dim RS As Recordset, C As Control
Dim FillFields As String, FillAllFields As Integer

On Error Resume Next

'Exit if not on the new record.
If Not F.NewRecord Then Exit Function

'Goto the last record of the form recordset (to autofill form)
Set RS = F.RecordsetClone
RS.MoveLast
'Exit if you cannot move to the last record(no records).
If Err <> 0 Then Exit Function

'Get the list of fields to autofill.
FillFields = ";" & F![AutoFillNewRecordFields] & ";"

'If there is no criteria field, then set flag indicating ALL
'fields should be autofilled.
FillAllFields = Err <> 0

F.Painting = False

'Visit each field on the form.
For Each C In F
'Fill the field if ALL fields are to be filled OR if the
'...ControlSource field cn be found inthe FillFields list.
If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0
Then
C = RS(C.ControlSource)
End If
Next

F.Painting = True

End Function

Is there anyway to end the AutoFillNewRecord Function from the Private Sub
CopyRecord? I am very new to Access and VBA and will appreciate any help
given.

Thanks,

Tiffany
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AutoFillNewRecord for multiselect field 2
Error 2237 8
Duplicate subForm Info 0
Auto-filling new record with (some) values from previous record... 5
add one to a duplicated record that checks to see if one exists 0
Copy Record Date Won't Copy 0
Code not working for text duplication to new record 2
Autofill subform when move to new record on Main form 30

Top