VBA Coding Help - Need to edit field

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

Hi,

I am updating our invoice form which is generated from Access via VBA to
Word. I need to replace 1 field from the main table with another field in
VBA. When I just change all instances of the old field name to the new field
name I get an error code every time. Can someone advise me the est way to
update this?

I need to move "SalesDate" and "SaleDate2" to "REPPUN" please see the code
below and I apologize if this is too much code but I am very basic with VBA
and don't know what you might need to help me.

'Build SQL clauses for recordsets
sSQL = "SELECT [qrySalesInvoice2].* FROM [qrySalesInvoice2] "
sSQL = sSQL & "WHERE [qrySalesInvoice2].SO=" & iSO & ";"
sTotals = "SELECT Sum(qrySalesInvoice2.PriceTrsp) AS Subtotal, "
sTotals = sTotals & "Sum(qrySalesInvoice2.SalesTax) AS SalesTax,
Sum(qrySalesInvoice2.Total) AS Total, Count(qrySalesInvoice2.SO) AS [Count],
qrySalesInvoice2.TaxRate AS tax, "
sTotals = sTotals & "[TaxRate] AS Tax1 FROM qrySalesInvoice2 "
sTotals = sTotals & "GROUP BY qrySalesInvoice2.TaxRate, [TaxRate],
qrySalesInvoice2.SO "
sTotals = sTotals & "HAVING (qrySalesInvoice2.SO)=" & iSO & ";"
sSQLOwner = "SELECT qrySystemOwner.* FROM qrySystemOwner;"

'Open necessary recordsets
rstOwner.Open sSQLOwner, cnn
rstInvoice.Open sSQL, cnn
rstInvoice.MoveFirst
rstTotals.Open sTotals, cnn

DoCmd.Close acForm, "frmSalesInvoice"

' Launch Word and load the invoice template
Set objWord = New Word.Application
strPath = Application.CurrentProject.Path & "\SalesI~1.dot"
objWord.Documents.Add strPath
objWord.Visible = True

' Add invoice information using predefined bookmarks
With objWord.ActiveDocument.Bookmarks
.Item("CompanyName").Range.Text =
IIf(IsNull(rstInvoice!CompanyName), "", rstInvoice!CompanyName)
.Item("SALEDEP").Range.Text = IIf(IsNull(rstInvoice!SALEDEP), "",
rstInvoice!SALEDEP)
.Item("DepotComboPh").Range.Text =
IIf(IsNull(rstInvoice!DepotComboPh), "", rstInvoice!DepotComboPh)
.Item("DepotComboFX").Range.Text =
IIf(IsNull(rstInvoice!DepotComboFX), "", rstInvoice!DepotComboFX)
.Item("Credit").Range.Text = strCredit
.Item("DepotTrucking").Range.Text = strDepot
.Item("Message").Range.Text = strMessage
End With
With objWord.ActiveDocument.Bookmarks
.Item("Subtotal").Range.Text = IIf(IsNull(rstTotals!Subtotal), 0,
rstTotals!Subtotal)
.Item("SalesTax").Range.Text = IIf(IsNull(rstTotals!SalesTax), 0,
rstTotals!SalesTax / 100)
'.Item("Total").Range.Text = IIf(IsNull(rstTotals!Total), 0,
(rstTotals!Total))
.Item("Count").Range.Text = IIf(IsNull(rstTotals!Count), 0,
rstTotals!Count)
.Item("tax").Range.Text = IIf(IsNull(rstTotals!Tax1), 0,
rstTotals!Tax1)
End With
objWord.ActiveDocument.Fields.Update

'Populate Header and Footer data fields on invoice.
With
objWord.ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.Bookmarks
.Item("OwnerCompanyName").Range.Text =
IIf(IsNull(rstOwner!OwnerCompanyName), "", rstOwner!OwnerCompanyName)
.Item("OwnerCompanyStreet").Range.Text =
IIf(IsNull(rstOwner!OwnerCompanyStreet), "", rstOwner!OwnerCompanyStreet)
.Item("OwnerCityStateZip").Range.Text =
IIf(IsNull(rstOwner!OwnerCityStateZip), "", rstOwner!OwnerCityStateZip)
.Item("OCC").Range.Text = IIf(IsNull(rstOwner!OwnerCompanyCountry),
"", rstOwner!OwnerCompanyCountry)
.Item("OwnerPhone").Range.Text = IIf(IsNull(rstOwner!OwnerPhone),
"", rstOwner!OwnerPhone)
.Item("OwnerFax").Range.Text = IIf(IsNull(rstOwner!OwnerFax), "",
rstOwner!OwnerFax)
.Item("OwnerCompanyEmail").Range.Text =
IIf(IsNull(rstOwner![OwnerCompanyE-mail]), "", rstOwner![OwnerCompanyE-mail])
.Item("SO").Range.Text = IIf(IsNull(rstInvoice!SO), "", rstInvoice!SO)
.Item("SaleDate").Range.Text = IIf(IsNull(rstInvoice!SaleDate), "",
rstInvoice!SaleDate)
.Item("NewBuyer").Range.Text = IIf(IsNull(rstInvoice!NewBuyer), "",
rstInvoice!NewBuyer)
.Item("NewAddress").Range.Text = IIf(IsNull(rstInvoice!NewAddress),
"", rstInvoice!NewAddress)
.Item("ComboZip").Range.Text = IIf(IsNull(rstInvoice!ComboZip), "",
rstInvoice!ComboZip)
.Item("LAND").Range.Text = IIf(IsNull(rstInvoice!LAND), "",
rstInvoice!LAND)
.Item("ComboName").Range.Text = IIf(IsNull(rstInvoice!ComboName),
"", rstInvoice!ComboName)
.Item("BuyerComboPh").Range.Text =
IIf(IsNull(rstInvoice!BuyerComboPh), "", rstInvoice!BuyerComboPh)
.Item("BuyerComboFX").Range.Text =
IIf(IsNull(rstInvoice!BuyerComboFX), "", rstInvoice!BuyerComboFX)
.Item("ResaleII").Range.Text = IIf(IsNull(rstInvoice!ResaleII), "",
rstInvoice!ResaleII)
End With
'Sales_Release is sometimes null and needs to be skipped.
If IsNull(rstInvoice!Sales_Release) Then
GoTo Skip
End If
With
objWord.ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.Bookmarks
.Item("Sales_Release").Range.Text = rstInvoice!Sales_Release
End With
Skip:
With
objWord.ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary).Range.Bookmarks
.Item("BankName").Range.Text = IIf(IsNull(rstOwner!BankName), "",
rstOwner!BankName)
.Item("BankAddress").Range.Text = IIf(IsNull(rstOwner!BankAddress),
"", rstOwner!BankAddress)
.Item("BankCity").Range.Text = IIf(IsNull(rstOwner!BankCity), "",
rstOwner!BankCity)
.Item("BankAccount").Range.Text = IIf(IsNull(rstOwner!BankAccount),
"", rstOwner!BankAccount)
.Item("BankCode").Range.Text = IIf(IsNull(rstOwner!BankCode), "",
rstOwner!BankCode)
.Item("BankSwift").Range.Text = IIf(IsNull(rstOwner!BankSwift), "",
rstOwner!BankSwift)
End With

'Add detailed line item information and insert additional rows into table if
needed.
With objWord.ActiveDocument.Bookmarks
.Item("Type").Range.Text = IIf(IsNull(rstInvoice!Type), "",
rstInvoice!Type)
.Item("Size").Range.Text = IIf(IsNull(rstInvoice!Size), "",
rstInvoice!Size)
.Item("Prefix").Range.Text = IIf(IsNull(rstInvoice!Prefix), "",
rstInvoice!Prefix)
.Item("UnitN").Range.Text = IIf(IsNull(rstInvoice!UnitN), "",
rstInvoice!UnitN)
.Item("ChkN").Range.Text = IIf(IsNull(rstInvoice!CheckN), "",
rstInvoice!CheckN)
.Item("SaleDate2").Range.Text = IIf(IsNull(rstInvoice!SaleDate), "",
rstInvoice!SaleDate)
.Item("Price").Range.Text = IIf(IsNull(rstInvoice!Price), "",
rstInvoice!Price)
.Item("Trsp").Range.Text = IIf(IsNull(rstInvoice!Trsp), "",
rstInvoice!Trsp)
.Item("PriceTrsp").Range.Text = IIf(IsNull(rstInvoice!PriceTrsp),
"", rstInvoice!PriceTrsp)
End With
Dim x As Integer
x = 1
rstInvoice.MoveNext
Do Until rstInvoice.EOF
Do While rstInvoice.EOF = False
objWord.ActiveDocument.Tables(2).Rows.Add
With objWord.ActiveDocument.Tables(2)
.Cell(x + 1, 1).Range.Text = IIf(IsNull(rstInvoice!Type), "",
rstInvoice!Type)
.Cell(x + 1, 2).Range.Text = IIf(IsNull(rstInvoice!Size), "",
rstInvoice!Size)
.Cell(x + 1, 3).Range.Text = IIf(IsNull(rstInvoice!Prefix), "",
rstInvoice!Prefix)
.Cell(x + 1, 4).Range.Text = IIf(IsNull(rstInvoice!UnitN), "",
rstInvoice!UnitN)
.Cell(x + 1, 5).Range.Text = IIf(IsNull(rstInvoice!CheckN), "",
rstInvoice!CheckN)
.Cell(x + 1, 6).Range.Text = IIf(IsNull(rstInvoice!SaleDate),
"", rstInvoice!SaleDate)
.Cell(x + 1, 7).Range.Text = IIf(IsNull(rstInvoice!Price), "",
rstInvoice!Price)
.Cell(x + 1, 8).Range.Text = IIf(IsNull(rstInvoice!Trsp), "",
rstInvoice!Trsp)
.Cell(x + 1, 9).Range.Text = IIf(IsNull(rstInvoice!PriceTrsp),
"", rstInvoice!PriceTrsp)
End With
x = x + 1
rstInvoice.MoveNext
Loop
Loop

'Close recordsets
rstInvoice.Close
rstOwner.Close
rstTotals.Close

Exit_cmdPreview_Click:
Exit Sub

Err_cmdPreview_Click:
MsgBox Err.Description
Resume Exit_cmdPreview_Click

End Sub
Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

strMessage = ""
bDepotYN = False
bTruckingYN = False
bCreditYN = False
iSO = 0

DoCmd.Close

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub


Thank you!
 
I am updating our invoice form which is generated from Access via VBA to
Word. I need to replace 1 field from the main table with another field in
VBA. When I just change all instances of the old field name to the new field
name I get an error code every time. Can someone advise me the est way to
update this?
Click to expand...

Hi Sarah,

It would help to know the actual error you get.

Regardless, you may have references to your old field names in other
places, like in your forms.

You may want to try a search & replace utility like Rick Fisher's Find
and Replace. It will find your field names *everywhere* in your
application, not just in your VBA code.

We use and like his utility, but have no affiliation with him.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back
Top