COPYING OF RECORDS

[Kevsim]

I have a database where some of the records are very near the same as other records and it can be quite tedious when inserting the data. To save time when typing in a new record, I choose a record close to the one I want to type in, then press a command button for the data to be copied in the controls of my new record. This works very well, except 2 of my controls are Text Boxes bound to a Memo field. When the data is copied in and the memo field being copied is long, not all the data appears in my new memo field record. I have eliminated the” >” in the memo fields format. I am using a combo box to obtain all the required fields with the following code to move the data,-
F!Text11 = F!Combo15.Column(2)
F!Text13 = F!Combo15.Column(3) etc;
How can I obtain all the data from the memo field being copied. I would appreciate your advise.
Kevsim

 

[VBslammer]

You'll have to use a different technique to copy the memo fields. A combobox can only hold 255 characters per field:

Combo Box Displays Only 255 Characters of Memo Field
Last reviewed: November 18, 1998
Article ID: Q96987



-----------------------------------------------------------

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97


SYMPTOMS
Novice: Requires knowledge of the user interface on single-user computers.

When you are looking at the contents of a Memo field in a combo box, the entire contents of the Memo field are not visible. Instead, only the first 255 characters of the Memo field are visible.



RESOLUTION
If you need to view the entire contents of a Memo field, use a text box instead of a combo box.



STATUS
This behavior is by design.


VBSlammer

 

[Kevsim]

VBslammer, Thank you for the info, would you have some idea how I could go about writting the code to obtain the memeo field information? It would be appreciated.
kevsim

 

[VBslammer]

Normally you would set the first column of the combo to the primary key for the record (often hidden), and use that column to identify and process the field information using a recordset.

Private Sub cmdCopy_Click()
On Error GoTo ErrHandler

    Dim rst As Recordset
    Dim varPK As Variant
    Dim strSQL As String

    varPK = F!Combo15.Columns(0)   ' zero-based
    
    ' Remove single quotes for numeric PK.
    strSQL = "SELECT * FROM MyTable WHERE [PKField]='" & varPK & "'"   
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    If rst.EOF Then Goto ExitHere
    
    With rst
       F!Text11 = .Fields("Field1")
       F!Text12 = .Fields("Field2")
       F!Text13 = .Fields("Field3")
       F!Text14 = .Fields("Field4")
       F!Text15 = .Fields("Field5")
       F!Text16 = .Fields("Field6")
       .Close
    End With

ExitHere:
    On Error Resume Next
    Set rst = Nothing
    Exit Sub
ErrHandler:
    MsgBox "Error in cmdCopy_Click( ): " & Err & "-" & Err.Description
    Resume ExitHere
End Sub

VBSlammer

 

[Kevsim]

VBslammer, Once more thanks for the info, I will try it. I was trying to use the following code in an attempt to put the memo field data in a text box.
When I reached line “Set FaultsInfo” I receive the following error message “Runtime error 3061, Too few parameters, Expected 1”. The FaultsID is the correct number chosen from the combo box. When I put the cursor over “Set FaultFixInfo”, it reads Nothing. What would be wrong with the coding?
Private Sub Combo135_AfterUpdate()
Dim ID
Dim FaultsInfo As Recordset, SQLText
Dim SEQ As String
ID = Combo135.Column(0)
SQLText = "SELECT * FROM [Faults] WHERE" & "[ID] = " & Me!FaultsID
Set FaultsInfo = CurrentDb.OpenRecordset(SQLText)
Me![Text13] = FaultsInfo![Fix]
kevsim

 

[VBslammer]

You're missing a space between WHERE and [ID], should look like this:

SQLText = "SELECT * FROM [Faults] WHERE [ID] = " & Me!FaultsID

VBSlammer

 

[VBslammer]

Also, make sure you put the actual field name from your table where you have '[ID]' in the SELECT statement:

SQLText = "SELECT * FROM [Faults] WHERE [ActualFieldName] = " & Me!FaultsID

Plus, you put the PK into a variable named ID but used 'Me!FaultsID' as the where criteria. Should be:

SQLText = "SELECT * FROM [Faults] WHERE [ActualFieldName] = " & ID

I would use explicit variable declarations to make the code more readable:

Private Sub Combo135_AfterUpdate()
    Dim ID            As Long
    Dim FaultsInfo    As Recordset
    Dim SQLText       As String
    Dim SEQ           As String

    ID = Combo135.Column(0)

    SQLText = "SELECT * FROM [Faults] WHERE [ID]=" & ID

    Set FaultsInfo = CurrentDb.OpenRecordset(SQLText)
    
    Me![Text13] = FaultsInfo![Fix]

End Sub

VBSlammer

 

[Kevsim]

VbSlammer, sorry to be a pain, I run the code as written and receive an error “Runtime error 13 Type mismatch", I feel embarrassed at not picking the problem, is it because FaultsID is a numeric value? I tried setting Dim varPK to Integer, that did not help. If I add '" & varPK & "'" after the equals sign I receive error message “Data type mismatch in criteria expression”. When I put my cursor over (SQLText), it displays SQLText = "SELECT * FROM [T_FaultFix] WHERE [FaultID] = 199”, the statement includes the quotes, this is the record number I had chosen. Can you see where the problem is?

Private Sub Combo135_AfterUpdate()
Dim F As Form: Set F = Me
Dim FaultsInfo As Recordset
DIM SQLText as string
Dim varPK As Variant

varPK = Combo135.Column(0)
SQLText = "SELECT * FROM [T_Faults] WHERE [FaultsID] = " & varPK
' [T_Faults] is actual table name and [FaultsID] is actual field name and numeric value.
Set FaultsInfo = CurrentDb.OpenRecordset(SQLText)
' Above statement reads Runtime error 13 "Type mismatch"
Me![Text13] = FaultsInfo![Fix]
Stop

kevsim

 

[VBslammer]

You need to set a reference to the 'Microsoft DAO 3.6 Object Library' using:

Tools-->References

from within the VB editor. Since Access 2000, DAO must be referenced manually.

That should clear up the type mismatch.

VBSlammer

 

[Kevsim]

VBslammer, I went to Tools-->References 'Microsoft DAO 3.6 Object Library' within the VB editor, as it was not checked, I selected and checked. Tried the code again, same problem. Tried closing Access and reopening, same problem. Rebooted the computer, same problem. Are there any other things I could do?
kevsim

 

[VBslammer]

Try to declare the recordset using:

Dim FaultsInfo As DAO.Recordset

VBSlammer