Hello and Help

[Asif]

Hi

I am trying to use some code I found on Allen Browne
website to carry data across from previous record to a new
record.

I have a orders form with a orders detail subform, what I
am trying to do is set a button which will carry the data
from the previous record to the current one.

I have a button with the following code

Private Sub Command81_Click()

Call CarryOver(Me)

End Sub

and the module code is

Option Compare Database

Sub CarryOver(frm As Form)
On Error GoTo Err_CarryOver
' Purpose: Carry the values over from the last record to
a new one.
' Usage: In a form's BeforeInsert event procedure, enter:
' Call CarryOver(Me)
' Notes: This example limited to text boxes and combo
boxes.
' Text/combo boxes must have same Name as the
fields they represent.
Dim rst As DAO.Recordset
Dim ctl As Control
Dim i As Integer

Set rst = frm.RecordsetClone
If rst.RecordCount > 0 Then
rst.MoveLast
For i = 0 To frm.Count - 1
Set ctl = frm(i)
If TypeOf ctl Is TextBox Then
If Not IsNull(rst(ctl.Name)) Then
ctl = rst(ctl.Name)
End If
ElseIf TypeOf ctl Is ComboBox Then
If Not IsNull(rst(ctl.Name)) Then
ctl = rst(ctl.Name)
End If
End If
Next
End If

Exit_CarryOver:
Set rst = Nothing
Exit Sub

Err_CarryOver:
Select Case Err
Case 2448 'Cannot assign a value
Debug.Print "Value cannot be assigned to " &
ctl.Name
Resume Next
Case 3265 'Name not found in this collection.
Debug.Print "No matching field name found for " &
ctl.Name
Resume Next
Case Else
MsgBox "Carry-over values were not assigned,
from " & ctl.Name & _
". Error #" & Err.Number & ": " &
Err.Description, vbExclamation, "CarryOver()"
Resume Exit_CarryOver
End Select
End Sub

The problem is when I click on the button the following
error occurs

Run-Time error '91':
Object variable or With block variable not set

and the
MsgBox "Carry-over values were not assigned, from " &
ctl.Name & _
". Error #" & Err.Number & ": " & Err.Description,
vbExclamation, "CarryOver()"

is highligted. I am using Access 2002 and I am creating
an adp.

I hope I have explained it well and any help will be much
appreciated.

Kindest Regards
Asif

 

[Dirk Goldgar]

Hi

I am trying to use some code I found on Allen Browne
website to carry data across from previous record to a new
record.

I have a orders form with a orders detail subform, what I
am trying to do is set a button which will carry the data
from the previous record to the current one.

I have a button with the following code

Private Sub Command81_Click()

Call CarryOver(Me)

End Sub

and the module code is

Option Compare Database

Sub CarryOver(frm As Form)
On Error GoTo Err_CarryOver
' Purpose: Carry the values over from the last record to
a new one.
' Usage: In a form's BeforeInsert event procedure, enter:
' Call CarryOver(Me)
' Notes: This example limited to text boxes and combo
boxes.
' Text/combo boxes must have same Name as the
fields they represent.
Dim rst As DAO.Recordset
Dim ctl As Control
Dim i As Integer

Set rst = frm.RecordsetClone
If rst.RecordCount > 0 Then
rst.MoveLast
For i = 0 To frm.Count - 1
Set ctl = frm(i)
If TypeOf ctl Is TextBox Then
If Not IsNull(rst(ctl.Name)) Then
ctl = rst(ctl.Name)
End If
ElseIf TypeOf ctl Is ComboBox Then
If Not IsNull(rst(ctl.Name)) Then
ctl = rst(ctl.Name)
End If
End If
Next
End If

Exit_CarryOver:
Set rst = Nothing
Exit Sub

Err_CarryOver:
Select Case Err
Case 2448 'Cannot assign a value
Debug.Print "Value cannot be assigned to " &
ctl.Name
Resume Next
Case 3265 'Name not found in this collection.
Debug.Print "No matching field name found for " &
ctl.Name
Resume Next
Case Else
MsgBox "Carry-over values were not assigned,
from " & ctl.Name & _
". Error #" & Err.Number & ": " &
Err.Description, vbExclamation, "CarryOver()"
Resume Exit_CarryOver
End Select
End Sub

The problem is when I click on the button the following
error occurs

Run-Time error '91':
Object variable or With block variable not set

and the
MsgBox "Carry-over values were not assigned, from " &
ctl.Name & _
". Error #" & Err.Number & ": " & Err.Description,
vbExclamation, "CarryOver()"

is highligted. I am using Access 2002 and I am creating
an adp.

I hope I have explained it well and any help will be much
appreciated.

Kindest Regards
Asif

I'm not certain, but I suspect your problem stems from the fact that
you're using this code in an ADP. Although I haven't worked with ADPs
yet, I believe that in an ADP a form's RecordsetClone property returns
an ADO recordset, not a DAO recordset. If this is the case, the code
would fail with a Type Mismatch at the moment you assign the form's
RecordsetClone to the rst variable, because that variable is declared as
a DAO recordset. Thus, on entry to the error-handling code, the
variable ctl hasn't yet been set to any object, and attempting to
reference it raises the new error, "Object variable or With block
variable not set".

Try changing this:

Dim rst As DAO.Recordset

to this:

Dim rst As ADODB.Recordset

and see if that solves the problem.

 

[Dirk Goldgar]

Hi Dirk

Thanks for your help I think it is working just one
problem I have a Invoice Number field which is read only
how do I get it to ignore that field.

That would depend on why the field is read-only. Is it an autonumber
field, a calculated field, or just one you've set as read-only?

As I read the code, the error-handling routine is supposed to trap the
error (2448) relating to being unable to assign a value to a control.
Is it not doing that? What error are you getting?

 

[Dirk Goldgar]

Hi Dirk

I get the following error and it is an autonumber field,

Carry-over values were not assigned, from
InvoiceNumber.Error #30016: The field 'InvoiceNumber' is
read only.

Although you could insert code to check the field's attrbutes to see if
it can be assigned to before attempting to write to it, probably the
easiest solution is just to ignore the relevant error. That's what the
error-handler code referring to error 2448 is supposed to do. I guess
you get a different error number in an ADP than you do in an MDB. I
suggest you just add error 30016 to that case:

Case 2448, 30016 'Cannot assign a value
Debug.Print "Value cannot be assigned to " & ctl.Name
Resume Next

That should handle it. You may or may not want to take out the line,

Debug.Print "Value cannot be assigned to " & ctl.Name

when you're done debugging.

Is it also possible to use this on the subform which is a
datasheet to carry that data over too?.

I don't think you can use the same code, because the subform's recordset
is already filtered to include only the records relevant to the current
main-form record. Besides, you probably want to copy the subform
records relevant to the record you just copied, which won't necessarily
be the last ones in the sub-table.

If you want to copy the subform records that are related to the
main-form record you're copying, and you're willing to hard-code some
things that are relevant only to thes forms (such as the key field names
and the subform's recordsource table name) you could modify the
CarryOver() function for this specific form relatively easily so that it
copies the related records, too.