Need help updating a table from a recordset.

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

Guest

I receive a report in Excel that I link to my Access database. The report is
a mainframe report so it lists the customer name/id on the first record and
not on the following records. These are very large reports and I need to
automate updating each line with the correct customer name/id but I'm new to
VB. The data looks like this.
0Client Name Client ID Co Policy Cov Transaction
DOE, JOHN X-A01001876J-11 BBL U1002966L 1 New Business
BBL U1002966L 2 New Business
BBL U11100713L 1 Surrender

DOE, JANE X-LL5010025R-11 BBL AA0029822L 1 New Business
BBL DJ1002505X 1 Surrender

CLIENT, ONE G-LL5051008R-11 BBL U00M22299L 1 New Business
BBL KL1005805Y 1 Surrender

CLIENT, TWO K-LL2004460R-11 BBL GG00302265 1 New Business
BBL DM4452000S 1 Surrender
BBL LL0482000S 1 Surrender
BBL LL0482000S 2 Surrender

CLIENT, THREE D-UU629002S -11 UUL YY8055040 1 New Business
UUL KK029026P 1 Lapse

I've been unsuccessful find code on the net to do this and I can't even find
a complete sample of code similar. I would appreciate any assistance.
 
sapphire said:
I receive a report in Excel that I link to my Access database. The report is
a mainframe report so it lists the customer name/id on the first record and
not on the following records. These are very large reports and I need to
automate updating each line with the correct customer name/id but I'm new to
VB. The data looks like this.
0Client Name Client ID Co Policy Cov Transaction
DOE, JOHN X-A01001876J-11 BBL U1002966L 1 New Business
BBL U1002966L 2 New Business
BBL U11100713L 1 Surrender

DOE, JANE X-LL5010025R-11 BBL AA0029822L 1 New Business
BBL DJ1002505X 1 Surrender

CLIENT, ONE G-LL5051008R-11 BBL U00M22299L 1 New Business
BBL KL1005805Y 1 Surrender

CLIENT, TWO K-LL2004460R-11 BBL GG00302265 1 New Business
BBL DM4452000S 1 Surrender
BBL LL0482000S 1 Surrender
BBL LL0482000S 2 Surrender

CLIENT, THREE D-UU629002S -11 UUL YY8055040 1 New Business
UUL KK029026P 1 Lapse

I've been unsuccessful find code on the net to do this and I can't even find
a complete sample of code similar. I would appreciate any assistance.
Click to expand...

If the fields are in the order above, this *should* work.... I did some
testing. At least it should get you started.

Create a form and put a listbox and a button on it.

The unbound listbox RowSource should be:

SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=6));

This allows you to pick a linked table without typing.

Next, create a standard module and paste in this code. I named the sub
"test"... use whatever name you want. (Watch for line wrap....)

'*****Code Start ***********
Public Sub test()
On Error GoTo OhNo

Dim rs As Recordset
Dim i As Integer, RecCount As Long
Dim CName As String, CID As String, tblName As String
Dim ErrOK As Integer

'get name of linked tables from list box
tblName = Trim(Forms![form1].[List6] & "")

' didn't select a linked table
If Len(tblName) < 1 Then
ErrOK = MsgBox("Please select a table from the list box", vbOKOnly +
vbExclamation)
Forms![form1].List6.SetFocus
Exit Sub
End If

Set rs = CurrentDb.OpenRecordset(tblName)

' check for empty table
If Not (rs.BOF And rs.EOF) Then
With rs
.MoveLast
RecCount = .RecordCount

.MoveFirst

For i = 1 To RecCount
' remember - fields in a table are zero based
' if cell (field) is not empty, use to fill in cells (fields)
If Len(Trim(.Fields(0) & "")) > 0 Then
CName = .Fields(0)
CID = .Fields(1)
End If
' if 4th col not empty, fill first 2 fields
If Len(Trim(.Fields(3))) > 0 Then
.Edit
.Fields(0) = CName
.Fields(1) = CID
.Update
End If
.MoveNext
Next
End With
End If

WeBGone:
rs.Close
Set rs = Nothing
Exit Sub

' simple error checking/ might want to add more
OhNo:
MsgBox Err.Number & " - " & Err.Description
Resume WeBGone

End Sub
"**********End Code ***********

The last thing to do is add code to the button. This is how to call the code
above:

Private Sub Command0_Click()
test ' or whatever you named the above code
End Sub
"**

Remember to try this on a COPY (backup) first!!!!

HTH
 
Back
Top