Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Method or data member not found error in regards to .AddNew code 1

Status
Not open for further replies.
malaize2

malaize2

Technical User
Joined
Dec 22, 2004
Messages
69
Location
US
Hello,
I have a VB6 project that I am trying to edit. There is a subroutine in this project called Add that I have copied and pasted into the same project as a new subroutine with the name of Add2.
Here is the code for both subs.

****code for first sub****

Private Sub Add()

Set rsTableForUPSImport = dbTableForUPSImport.OpenRecordset("SELECT * FROM TableForUPSImport")

With rsTableForUPSImport
'Set it to Add mode
.AddNew
'Enter the field values
.Fields("void").Value = rsUPSWorld!void
.Fields("serv_type").Value = rsUPSWorld!serv_type
.Fields("billing_op").Value = rsUPSWorld!billing_op
.Fields("return_op").Value = rsUPSWorld!return_op
.Fields("sn1memo").Value = rsUPSWorld!sn1memo
.Fields("sn2fax").Value = rsUPSWorld!sn2fax
.Fields("sn2intlf").Value = rsUPSWorld!sn2intlf
.Fields("sn2memo").Value = rsUPSWorld!sn2memo
.Fields("verbconfop").Value = rsUPSWorld!verbconfop
.Fields("verbcont").Value = rsUPSWorld!verbcont
.Fields("verbphone").Value = rsUPSWorld!verbphone
.Fields("length").Value = rsUPSWorld!length
.Fields("width").Value = rsUPSWorld!Width
.Fields("height").Value = rsUPSWorld!Height
.Fields("merchdesc").Value = rsUPSWorld!merchdesc
.Fields("date").Value = Format$(Date, "mm/dd/yyyy")
'Update it
.Update
'Close it
.Close
End With
End Sub

****end of code for first sub****

****code for second sub****

Private Sub Add2()

Set rsUSPS = dbUSPS.OpenRecordset("Select * FROM USPS")

With rsUSPS
'Set it to Add mode
.AddNew
'Enter the field values
.Fields("void").Value = rsUSPS!void
.Fields("serv_type").Value = rsUSPS!serv_type
.Fields("billing_op").Value = rsUSPS!billing_op
.Fields("return_op").Value = rsUSPS!return_op
.Fields("sn1memo").Value = rsUSPS!sn1memo
.Fields("sn2fax").Value = rsUSPS!sn2fax
.Fields("sn2intlf").Value = rsUSPS!sn2intlf
.Fields("sn2memo").Value = rsUSPSd!sn2memo
.Fields("verbconfop").Value = rsUSPS!verbconfop
.Fields("verbcont").Value = rsUSPS!verbcont
.Fields("verbphone").Value = rsUSPS!verbphone
.Fields("length").Value = rsUSPS!length
.Fields("width").Value = rsUSPS!Width
.Fields("height").Value = rsUSPS!Height
.Fields("merchdesc").Value = rsUSPS!merchdesc
.Fields("date").Value = Format$(Date, "mm/dd/yyyy")
'Update it
.Update
'Close it
.Close
End With
End Sub

****end of code for second sub****

Both subs add records from an Access 97 table to a recordset that is declared in the VB program. When I try to compile the project I receive an error and the .AddNew code in the Add2 subroutine highlighted.
Here is the exact error.

"Compile error:
Method or data member not found."

Is there something obvious I am missing? I could really use some help.

Thank you very much,

malaize2

 
Sort by date Sort by votes
The database is a different DB object, so check that. Make sure it is actually open when this code is running.

Also make sure that rsUSPS is actually an ADODB.Recordset object.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
You appear to be setting the fields in rsUSPS to fields in the same recordset but, since you are in Add mode, those fields have no values. Your real problem however is probably here
Code: 
 .Fields("sn2memo").Value = rsUSPS[b][COLOR=red]d[/color][/b]!sn2memo
 
Upvote 0 Downvote
Hi gmmastros and Golom. Thank you for the help. I had the declaration of the rsUSPS recordset incorrectly stated. After fixing that and removing the 'd' from the code that Golom found I was able to compile the code. For some reason the program copied data to the Access 97 table 'TableForUPSImport' correctly (in the Add subroutine) but only the date was copied to the USPS table. Does anyone see something in my code that needs changed? The code is below. Thanks, malaize2

Code: 
Dim dbUPSWorld As DAO.Database
Dim dbTableForUPSImport As Database
Dim rsUPSWorld As DAO.Recordset
Dim rsTableForUPSImport As DAO.Recordset
Dim dbUSPS As DAO.Database
Dim rsUSPS As DAO.Recordset



Private Sub Add()

Set rsTableForUPSImport = dbTableForUPSImport.OpenRecordset("SELECT * FROM TableForUPSImport")

    With rsTableForUPSImport
        'Set it to Add mode
        .AddNew
        'Enter the field values
        .Fields("void").Value = rsUPSWorld!void
        .Fields("serv_type").Value = rsUPSWorld!serv_type
        .Fields("billing_op").Value = rsUPSWorld!billing_op
        .Fields("return_op").Value = rsUPSWorld!return_op
        .Fields("return_typ").Value = rsUPSWorld!return_typ
        .Fields("satdelop").Value = rsUPSWorld!satdelop
        .Fields("satpickop").Value = rsUPSWorld!satpickop
        .Fields("shipnot1op").Value = rsUPSWorld!shipnot1op
        .Fields("shn1_type").Value = rsUPSWorld!shn1_type
        .Fields("shn1_comp").Value = rsUPSWorld!shn1_comp
        .Fields("shn1_cont").Value = rsUPSWorld!shn1_cont
        .Fields("shn1_phone").Value = rsUPSWorld!shn1_phone
        .Fields("shn1_fax").Value = rsUPSWorld!shn1_fax
        .Fields("shn1_intlf").Value = rsUPSWorld!shn1_intlf
        .Fields("shn1_memo").Value = rsUPSWorld!shn1_memo
        .Fields("shipnot2op").Value = rsUPSWorld!shipnot2op
        .Fields("shn2_type").Value = rsUPSWorld!shn2_type
        .Fields("shn2_comp").Value = rsUPSWorld!shn2_comp
        .Fields("shn2_cont").Value = rsUPSWorld!shn2_cont
        .Fields("shn2_phone").Value = rsUPSWorld!shn2_phone
        .Fields("shn2_fax").Value = rsUPSWorld!shn2_fax
        .Fields("shn2_intlf").Value = rsUPSWorld!shn2_intlf
        .Fields("shn2_memo").Value = rsUPSWorld!shn2_memo
        .Fields("descofgood").Value = rsUPSWorld!descofgood
        .Fields("doconlyind").Value = rsUPSWorld!doconlyind
        .Fields("spec_inst").Value = rsUPSWorld!spec_inst
        .Fields("to_id").Value = rsUPSWorld!to_id
        .Fields("to_comp").Value = rsUPSWorld!to_comp
        .Fields("to_attn").Value = rsUPSWorld!to_attn
        .Fields("to_addr").Value = rsUPSWorld!to_addr
        .Fields("to_addr2").Value = rsUPSWorld!to_addr2
        .Fields("to_addr3").Value = rsUPSWorld!to_addr3
        .Fields("to_country").Value = rsUPSWorld!to_country
        .Fields("to_zip").Value = rsUPSWorld!to_zip
        .Fields("to_city").Value = rsUPSWorld!to_city
        .Fields("to_state").Value = rsUPSWorld!to_state
        .Fields("to_phone").Value = rsUPSWorld!to_phone
        .Fields("to_fax").Value = rsUPSWorld!to_fax
        .Fields("to_taxid").Value = rsUPSWorld!to_taxid
        .Fields("rec_upsnum").Value = rsUPSWorld!rec_upsnum
        .Fields("loc_id").Value = rsUPSWorld!loc_id
        .Fields("resind").Value = rsUPSWorld!resind
        .Fields("pkg_type").Value = rsUPSWorld!pkg_type
        .Fields("weight").Value = rsUPSWorld!Weight
        .Fields("trackno").Value = rsUPSWorld!trackno
        .Fields("oversize").Value = rsUPSWorld!oversize
        .Fields("pkg_ref1").Value = rsUPSWorld!pkg_ref1
        .Fields("pkg_ref2").Value = rsUPSWorld!pkg_ref2
        .Fields("pkg_ref3").Value = rsUPSWorld!pkg_ref3
        .Fields("pkg_ref4").Value = rsUPSWorld!pkg_ref4
        .Fields("pkg_ref5").Value = rsUPSWorld!pkg_ref5
        .Fields("addlhand").Value = rsUPSWorld!addlhand
        .Fields("codopt").Value = rsUPSWorld!codopt
        .Fields("codamount").Value = rsUPSWorld!codamount
        .Fields("cashonly").Value = rsUPSWorld!cashonly
        .Fields("declvalop").Value = rsUPSWorld!declvalop
        .Fields("declvalam").Value = rsUPSWorld!declvalam
        .Fields("delconfop").Value = rsUPSWorld!delconfop
        .Fields("delconfsig").Value = rsUPSWorld!delconfsig
        .Fields("hazmatop").Value = rsUPSWorld!hazmatop
        .Fields("sn1op").Value = rsUPSWorld!sn1op
        .Fields("sn1type").Value = rsUPSWorld!sn1type
        .Fields("sn1comp").Value = rsUPSWorld!sn1comp
        .Fields("sn1cont").Value = rsUPSWorld!sn1cont
        .Fields("sn1phone").Value = rsUPSWorld!sn1phone
        .Fields("sn1fax").Value = rsUPSWorld!sn1fax
        .Fields("sn1intlf").Value = rsUPSWorld!sn1intlf
        .Fields("sn1memo").Value = rsUPSWorld!sn1memo
        .Fields("sn2op").Value = rsUPSWorld!sn2op
        .Fields("sn2type").Value = rsUPSWorld!sn2type
        .Fields("sn2comp").Value = rsUPSWorld!sn2comp
        .Fields("sn2cont").Value = rsUPSWorld!sn2cont
        .Fields("sn2phone").Value = rsUPSWorld!sn2phone
        .Fields("sn2fax").Value = rsUPSWorld!sn2fax
        .Fields("sn2intlf").Value = rsUPSWorld!sn2intlf
        .Fields("sn2memo").Value = rsUPSWorld!sn2memo
        .Fields("verbconfop").Value = rsUPSWorld!verbconfop
        .Fields("verbcont").Value = rsUPSWorld!verbcont
        .Fields("verbphone").Value = rsUPSWorld!verbphone
        .Fields("length").Value = rsUPSWorld!length
        .Fields("width").Value = rsUPSWorld!Width
        .Fields("height").Value = rsUPSWorld!Height
        .Fields("merchdesc").Value = rsUPSWorld!merchdesc
        .Fields("date").Value = Format$(Date, "mm/dd/yyyy")
        'Update it
        .Update
        'Close it
        .Close
    End With

    

End Sub

Private Sub Add2()

Set rsUSPS = dbUSPS.OpenRecordset("Select * FROM USPS")

With rsUSPS
        'Set it to Add mode
        .AddNew
        'Enter the field values
        .Fields("void").Value = rsUSPS!void
        .Fields("serv_type").Value = rsUSPS!serv_type
        .Fields("billing_op").Value = rsUSPS!billing_op
        .Fields("return_op").Value = rsUSPS!return_op
        .Fields("return_typ").Value = rsUSPS!return_typ
        .Fields("satdelop").Value = rsUSPS!satdelop
        .Fields("satpickop").Value = rsUSPS!satpickop
        .Fields("shipnot1op").Value = rsUSPS!shipnot1op
        .Fields("shn1_type").Value = rsUSPS!shn1_type
        .Fields("shn1_comp").Value = rsUSPS!shn1_comp
        .Fields("shn1_cont").Value = rsUSPS!shn1_cont
        .Fields("shn1_phone").Value = rsUSPS!shn1_phone
        .Fields("shn1_fax").Value = rsUSPS!shn1_fax
        .Fields("shn1_intlf").Value = rsUSPS!shn1_intlf
        .Fields("shn1_memo").Value = rsUSPS!shn1_memo
        .Fields("shipnot2op").Value = rsUSPS!shipnot2op
        .Fields("shn2_type").Value = rsUSPS!shn2_type
        .Fields("shn2_comp").Value = rsUSPS!shn2_comp
        .Fields("shn2_cont").Value = rsUSPS!shn2_cont
        .Fields("shn2_phone").Value = rsUSPS!shn2_phone
        .Fields("shn2_fax").Value = rsUSPS!shn2_fax
        .Fields("shn2_intlf").Value = rsUSPS!shn2_intlf
        .Fields("shn2_memo").Value = rsUSPS!shn2_memo
        .Fields("descofgood").Value = rsUSPS!descofgood
        .Fields("doconlyind").Value = rsUSPS!doconlyind
        .Fields("spec_inst").Value = rsUSPS!spec_inst
        .Fields("to_id").Value = rsUSPS!to_id
        .Fields("to_comp").Value = rsUSPS!to_comp
        .Fields("to_attn").Value = rsUSPS!to_attn
        .Fields("to_addr").Value = rsUSPS!to_addr
        .Fields("to_addr2").Value = rsUSPS!to_addr2
        .Fields("to_addr3").Value = rsUSPS!to_addr3
        .Fields("to_country").Value = rsUSPS!to_country
        .Fields("to_zip").Value = rsUSPS!to_zip
        .Fields("to_city").Value = rsUSPS!to_city
        .Fields("to_state").Value = rsUSPS!to_state
        .Fields("to_phone").Value = rsUSPS!to_phone
        .Fields("to_fax").Value = rsUSPS!to_fax
        .Fields("to_taxid").Value = rsUSPS!to_taxid
        .Fields("rec_upsnum").Value = rsUSPS!rec_upsnum
        .Fields("loc_id").Value = rsUSPS!loc_id
        .Fields("resind").Value = rsUSPS!resind
        .Fields("pkg_type").Value = rsUSPS!pkg_type
        .Fields("weight").Value = rsUSPS!Weight
        .Fields("trackno").Value = rsUSPS!trackno
        .Fields("oversize").Value = rsUSPS!oversize
        .Fields("pkg_ref1").Value = rsUSPS!pkg_ref1
        .Fields("pkg_ref2").Value = rsUSPS!pkg_ref2
        .Fields("pkg_ref3").Value = rsUSPS!pkg_ref3
        .Fields("pkg_ref4").Value = rsUSPS!pkg_ref4
        .Fields("pkg_ref5").Value = rsUSPS!pkg_ref5
        .Fields("addlhand").Value = rsUSPS!addlhand
        .Fields("codopt").Value = rsUSPS!codopt
        .Fields("codamount").Value = rsUSPS!codamount
        .Fields("cashonly").Value = rsUSPS!cashonly
        .Fields("declvalop").Value = rsUSPS!declvalop
        .Fields("declvalam").Value = rsUSPS!declvalam
        .Fields("delconfop").Value = rsUSPS!delconfop
        .Fields("delconfsig").Value = rsUSPS!delconfsig
        .Fields("hazmatop").Value = rsUSPS!hazmatop
        .Fields("sn1op").Value = rsUSPS!sn1op
        .Fields("sn1type").Value = rsUSPS!sn1type
        .Fields("sn1comp").Value = rsUSPS!sn1comp
        .Fields("sn1cont").Value = rsUSPS!sn1cont
        .Fields("sn1phone").Value = rsUSPS!sn1phone
        .Fields("sn1fax").Value = rsUSPS!sn1fax
        .Fields("sn1intlf").Value = rsUSPS!sn1intlf
        .Fields("sn1memo").Value = rsUSPS!sn1memo
        .Fields("sn2op").Value = rsUSPS!sn2op
        .Fields("sn2type").Value = rsUSPS!sn2type
        .Fields("sn2comp").Value = rsUSPS!sn2comp
        .Fields("sn2cont").Value = rsUSPS!sn2cont
        .Fields("sn2phone").Value = rsUSPS!sn2phone
        .Fields("sn2fax").Value = rsUSPS!sn2fax
        .Fields("sn2intlf").Value = rsUSPS!sn2intlf
        .Fields("sn2memo").Value = rsUSPS!sn2memo
        .Fields("verbconfop").Value = rsUSPS!verbconfop
        .Fields("verbcont").Value = rsUSPS!verbcont
        .Fields("verbphone").Value = rsUSPS!verbphone
        .Fields("length").Value = rsUSPS!length
        .Fields("width").Value = rsUSPS!Width
        .Fields("height").Value = rsUSPS!Height
        .Fields("merchdesc").Value = rsUSPS!merchdesc
        .Fields("date").Value = Format$(Date, "mm/dd/yyyy")
        'Update it
        .Update
        'Close it
        .Close
    End With
End Sub


Private Sub UpdateRecords()
Form1.Visible = True
Label1.Caption = "Updating records...."

Do While Not rsUPSWorld.EOF
    Call Add
    Call Add2
    rsUPSWorld.MoveNext
Loop

'Close upsworld.dbf
rsUPSWorld.Close
Set rsUPSWorld = Nothing
dbUPSWorld.Close
Set dbUPSWorld = Nothing

'rsTableForUPSImport.Close
Set rsTableForUPSImport = Nothing
'dbTableForUPSImport.Close
Set dbTableForUPSImport = Nothing

'rsUSPS.Close
Set rsUSPS = Nothing
'dbUSPS.Close
Set dbUSPS = Nothing





Label1.Caption = "Updating records....Done!"
Call Finish
End Sub

Private Sub Finish()
Call SetArchive
Timer1.Enabled = True
Timer2.Enabled = True
End Sub



Private Sub Timer1_Timer()
On Error GoTo errorhandler
Dim archive As Integer

archive = GetAttr("upsworld.dbf") And vbArchive

If archive = 32 Then
Timer1.Enabled = False
Set dbUPSWorld = OpenDatabase("MOMImport.mdb")
Set rsUPSWorld = dbUPSWorld.OpenRecordset("SELECT * FROM UPSWorld")
Set dbTableForUPSImport = OpenDatabase("MOMImport.mdb")
'Delete records that are 21 days old
dbTableForUPSImport.Execute ("DeleteQuery")
Set rsTableForUPSImport = dbTableForUPSImport.OpenRecordset("SELECT * FROM TableForUPSImport")
Call UpdateRecords
End If

Exit Sub
errorhandler:
If Err.Number = 53 Then
MsgBox "file not found"
Else
MsgBox Err.Description, vbOKOnly, "Error"
End
End If
End Sub

Private Sub SetArchive()
On Error GoTo error_handler
Set fso = CreateObject("Scripting.FileSystemObject")
Set file = fso.GetFile("upsworld.dbf")
file.Attributes = 0
Exit Sub
error_handler:
MsgBox Err.Description, vbOKOnly, "Error"
End Sub

Private Sub Timer2_Timer()
Timer2.Enabled = False
Form1.Visible = False
Shell "temp.exe", vbNormalNoFocus
End
End Sub
 
Upvote 0 Downvote
As I said before, you are setting the fields in the rsUSPS recordset equal to themselves. For example this
Code: 
With rsUSPS
        .Fields("void").Value = rsUSPS!void
is equivalent to
Code: 
With rsUSPS
        .Fields("void").Value = .Fields("void").Value

The Date field is copied correctly because that's the only one that isn't being sourced from the recordset.

You probably want some other source for the data that you are loading into rsUSPS
 
Upvote 0 Downvote
Hi Golom,

Now I understand what your saying. Thanks for sticking with me. I made the change so hopefully the data will be written to the USPS table when orders are processed later today.

I also need to have the data that is added to the USPS table appended to another Access database after the Add2 subroutine is completed. Would this be as simple as adding the following code after the Add2 subroutine?

Code: 
Open "G:\UPS\MOMall.mdb" For Append ?

I am not sure what else to add to the code to copy the data from the USPS table in the Add2 sub to a table called ThreeCompanies in the MOMall.mdb.


Thanks for any help,

malaize2



 
Upvote 0 Downvote
Ya don't wanna do that!

Open "G:\UPS\MOMall.mdb" For Append

will open your database as if it was a text file and probably hopelessly corrupt it.

What you probably do want to do is open a table in that database and append the data to it. Taking your Add2 routine as a model
Code: 
Private Sub UpdateRecords()
Form1.Visible = True
Label1.Caption = "Updating records...."

Do While Not rsUPSWorld.EOF
    Call Add
    Call Add2 dbUSPS.Name, "USPS"
    Call Add2 "G:\UPS\MOMall.mdb", "TableNameInMOMall"
    rsUPSWorld.MoveNext
Loop

Private Sub Add2(DatabaseName as String, TableName As String)
Dim db As DAO.Database
Set db = DAO.DBEngine(0).OpenDatabase(DatabaseName)
Set rsUSPS = db.OpenRecordset("Select * FROM " & TableName)

With rsUSPS
    .AddNew
    .Fields("void").Value = rsUPSWorld!void
    [COLOR=green]' etc.[/color]
    .Fields("date").Value = Format$(Date, "mm/dd/yyyy")
    .Update
    .Close
End With
Set db = Nothing
End Sub

BTW: It may be useful to look into the INSERT INTO SQL statement. It tends to be much faster for inserting multiple records than looping through a recordset.
 
Upvote 0 Downvote
Golom,

I changed the code that you suggested starting with the Add2 subroutine. I keep receiving a syntax error with this code highlighted.

Code: 
Call Add2 dbUSPS.Name, "USPS"
    Call Add2 "G:\MOMAll.mdb" "TableMain"

I am not sure what dbUSPS.Name is for. Here is all of the edited code starting with the Add2 sub.

Code: 
Private Sub Add2(MOMAll, TableMain)
Dim db As DAO.Database
Set db = DAO.DBEngine(0).OpenDatabase(MOMAll)
Set rsUSPS = dbUSPS.OpenRecordset("Select * FROM USPS")

With rsUSPS
        'Set it to Add mode
        .AddNew
        'Enter the field values
        .Fields("void").Value = rsUPSWorld!void
        .Fields("serv_type").Value = rsUPSWorld!serv_type
        .Fields("billing_op").Value = rsUPSWorld!billing_op
        .Fields("return_op").Value = rsUPSWorld!return_op
        .Fields("return_typ").Value = rsUPSWorld!return_typ
        .Fields("satdelop").Value = rsUPSWorld!satdelop
        .Fields("satpickop").Value = rsUPSWorld!satpickop
        .Fields("shipnot1op").Value = rsUPSWorld!shipnot1op
        .Fields("shn1_type").Value = rsUPSWorld!shn1_type
        .Fields("shn1_comp").Value = rsUPSWorld!shn1_comp
        .Fields("shn1_cont").Value = rsUPSWorld!shn1_cont
        .Fields("shn1_phone").Value = rsUPSWorld!shn1_phone
        .Fields("shn1_fax").Value = rsUPSWorld!shn1_fax
        .Fields("shn1_intlf").Value = rsUPSWorld!shn1_intlf
        .Fields("shn1_memo").Value = rsUPSWorld!shn1_memo
        .Fields("shipnot2op").Value = rsUPSWorld!shipnot2op
        .Fields("shn2_type").Value = rsUPSWorld!shn2_type
        .Fields("shn2_comp").Value = rsUPSWorld!shn2_comp
        .Fields("shn2_cont").Value = rsUPSWorld!shn2_cont
        .Fields("shn2_phone").Value = rsUPSWorld!shn2_phone
        .Fields("shn2_fax").Value = rsUPSWorld!shn2_fax
        .Fields("shn2_intlf").Value = rsUPSWorld!shn2_intlf
        .Fields("shn2_memo").Value = rsUPSWorld!shn2_memo
        .Fields("descofgood").Value = rsUPSWorld!descofgood
        .Fields("doconlyind").Value = rsUPSWorld!doconlyind
        .Fields("spec_inst").Value = rsUPSWorld!spec_inst
        .Fields("to_id").Value = rsUPSWorld!to_id
        .Fields("to_comp").Value = rsUPSWorld!to_comp
        .Fields("to_attn").Value = rsUPSWorld!to_attn
        .Fields("to_addr").Value = rsUPSWorld!to_addr
        .Fields("to_addr2").Value = rsUPSWorld!to_addr2
        .Fields("to_addr3").Value = rsUPSWorld!to_addr3
        .Fields("to_country").Value = rsUPSWorld!to_country
        .Fields("to_zip").Value = rsUPSWorld!to_zip
        .Fields("to_city").Value = rsUPSWorld!to_city
        .Fields("to_state").Value = rsUPSWorld!to_state
        .Fields("to_phone").Value = rsUPSWorld!to_phone
        .Fields("to_fax").Value = rsUPSWorld!to_fax
        .Fields("to_taxid").Value = rsUPSWorld!to_taxid
        .Fields("rec_upsnum").Value = rsUPSWorld!rec_upsnum
        .Fields("loc_id").Value = rsUPSWorld!loc_id
        .Fields("resind").Value = rsUPSWorld!resind
        .Fields("pkg_type").Value = rsUPSWorld!pkg_type
        .Fields("weight").Value = rsUPSWorld!Weight
        .Fields("trackno").Value = rsUPSWorld!trackno
        .Fields("oversize").Value = rsUPSWorld!oversize
        .Fields("pkg_ref1").Value = rsUPSWorld!pkg_ref1
        .Fields("pkg_ref2").Value = rsUPSWorld!pkg_ref2
        .Fields("pkg_ref3").Value = rsUPSWorld!pkg_ref3
        .Fields("pkg_ref4").Value = rsUPSWorld!pkg_ref4
        .Fields("pkg_ref5").Value = rsUPSWorld!pkg_ref5
        .Fields("addlhand").Value = rsUPSWorld!addlhand
        .Fields("codopt").Value = rsUPSWorld!codopt
        .Fields("codamount").Value = rsUPSWorld!codamount
        .Fields("cashonly").Value = rsUPSWorld!cashonly
        .Fields("declvalop").Value = rsUPSWorld!declvalop
        .Fields("declvalam").Value = rsUPSWorld!declvalam
        .Fields("delconfop").Value = rsUPSWorld!delconfop
        .Fields("delconfsig").Value = rsUPSWorld!delconfsig
        .Fields("hazmatop").Value = rsUPSWorld!hazmatop
        .Fields("sn1op").Value = rsUPSWorld!sn1op
        .Fields("sn1type").Value = rsUPSWorld!sn1type
        .Fields("sn1comp").Value = rsUPSWorld!sn1comp
        .Fields("sn1cont").Value = rsUPSWorld!sn1cont
        .Fields("sn1phone").Value = rsUPSWorld!sn1phone
        .Fields("sn1fax").Value = rsUPSWorld!sn1fax
        .Fields("sn1intlf").Value = rsUPSWorld!sn1intlf
        .Fields("sn1memo").Value = rsUPSWorld!sn1memo
        .Fields("sn2op").Value = rsUPSWorld!sn2op
        .Fields("sn2type").Value = rsUPSWorld!sn2type
        .Fields("sn2comp").Value = rsUPSWorld!sn2comp
        .Fields("sn2cont").Value = rsUPSWorld!sn2cont
        .Fields("sn2phone").Value = rsUPSWorld!sn2phone
        .Fields("sn2fax").Value = rsUPSWorld!sn2fax
        .Fields("sn2intlf").Value = rsUPSWorld!sn2intlf
        .Fields("sn2memo").Value = rsUPSWorld!sn2memo
        .Fields("verbconfop").Value = rsUPSWorld!verbconfop
        .Fields("verbcont").Value = rsUPSWorld!verbcont
        .Fields("verbphone").Value = rsUPSWorld!verbphone
        .Fields("length").Value = rsUPSWorld!length
        .Fields("width").Value = rsUPSWorld!Width
        .Fields("height").Value = rsUPSWorld!Height
        .Fields("merchdesc").Value = rsUPSWorld!merchdesc
        .Fields("date").Value = Format$(Date, "mm/dd/yyyy")
        'Update it
        .Update
        'Close it
        .Close
    End With
    Set db = Nothing
End Sub


Private Sub UpdateRecords()
Form1.Visible = True
Label1.Caption = "Updating records...."

Do While Not rsUPSWorld.EOF
    Call Add
    Call Add2 dbUSPS.Name, "USPS"
    Call Add2 "G:\MOMAll.mdb" "TableMain"
    rsUPSWorld.MoveNext
Loop


I tried adding quotes around dbUSPS.Name, to see if that would fix the syntax error but it didn't. Not sure what to try next.

Thank you very much,

malaize2
 
Upvote 0 Downvote
If dbUSPS is a currently open DAO Database then dbUSPS.Name will return the path to the database. You could change dbUSPS.Name to the path specification for the USPS database. For example
Code: 
   Add2 "C:\USPSFolder\USPS.mdb", "USPS"

You also need to change the code to open the recordset

Code: 
Set rsUSPS = dbUSPS.OpenRecordset("Select * FROM " & TableMain)

I think that you said you wanted to add stuff to table ThreeCompanies so the second call should be
Code: 
   Add2 "G:\MOMAll.mdb", "ThreeCompanies"
 
Upvote 0 Downvote
Golom,

I edited the code but for some reason it is still kicking a syntax error with the following code highlighted when I try to compile.

Code: 
Call Add2 "G:\rabbitstop\UPS\UOWS\ImpExp\MOMImport.mdb", "USPS"



I have created the MOMAll.mdb database with the ThreeCompanies table on our server at the path of G:\MOMAll.mdb.

The MOMImport.mdb database with the USPS table is located at G:\rabbitstop\UPS\UOWS\ImpExp\MOMImport.mdb.

Here is the revised code starting from the Add2 sub.

Code: 
Private Sub Add2(MOMAll As String, TableMain As String)
Dim db As DAO.Database
Set db = DAO.DBEngine(0).OpenDatabase(MOMAll)
Set rsUSPS = dbUSPS.OpenRecordset("Select * FROM " & ThreeCompanies)

With rsUSPS
        'Set it to Add mode
        .AddNew
        'Enter the field values
        .Fields("void").Value = rsUPSWorld!void
        .Fields("serv_type").Value = rsUPSWorld!serv_type
        .Fields("billing_op").Value = rsUPSWorld!billing_op
        .Fields("return_op").Value = rsUPSWorld!return_op
        .Fields("return_typ").Value = rsUPSWorld!return_typ
        .Fields("satdelop").Value = rsUPSWorld!satdelop
        .Fields("satpickop").Value = rsUPSWorld!satpickop
        .Fields("shipnot1op").Value = rsUPSWorld!shipnot1op
        .Fields("shn1_type").Value = rsUPSWorld!shn1_type
        .Fields("shn1_comp").Value = rsUPSWorld!shn1_comp
        .Fields("shn1_cont").Value = rsUPSWorld!shn1_cont
        .Fields("shn1_phone").Value = rsUPSWorld!shn1_phone
        .Fields("shn1_fax").Value = rsUPSWorld!shn1_fax
        .Fields("shn1_intlf").Value = rsUPSWorld!shn1_intlf
        .Fields("shn1_memo").Value = rsUPSWorld!shn1_memo
        .Fields("shipnot2op").Value = rsUPSWorld!shipnot2op
        .Fields("shn2_type").Value = rsUPSWorld!shn2_type
        .Fields("shn2_comp").Value = rsUPSWorld!shn2_comp
        .Fields("shn2_cont").Value = rsUPSWorld!shn2_cont
        .Fields("shn2_phone").Value = rsUPSWorld!shn2_phone
        .Fields("shn2_fax").Value = rsUPSWorld!shn2_fax
        .Fields("shn2_intlf").Value = rsUPSWorld!shn2_intlf
        .Fields("shn2_memo").Value = rsUPSWorld!shn2_memo
        .Fields("descofgood").Value = rsUPSWorld!descofgood
        .Fields("doconlyind").Value = rsUPSWorld!doconlyind
        .Fields("spec_inst").Value = rsUPSWorld!spec_inst
        .Fields("to_id").Value = rsUPSWorld!to_id
        .Fields("to_comp").Value = rsUPSWorld!to_comp
        .Fields("to_attn").Value = rsUPSWorld!to_attn
        .Fields("to_addr").Value = rsUPSWorld!to_addr
        .Fields("to_addr2").Value = rsUPSWorld!to_addr2
        .Fields("to_addr3").Value = rsUPSWorld!to_addr3
        .Fields("to_country").Value = rsUPSWorld!to_country
        .Fields("to_zip").Value = rsUPSWorld!to_zip
        .Fields("to_city").Value = rsUPSWorld!to_city
        .Fields("to_state").Value = rsUPSWorld!to_state
        .Fields("to_phone").Value = rsUPSWorld!to_phone
        .Fields("to_fax").Value = rsUPSWorld!to_fax
        .Fields("to_taxid").Value = rsUPSWorld!to_taxid
        .Fields("rec_upsnum").Value = rsUPSWorld!rec_upsnum
        .Fields("loc_id").Value = rsUPSWorld!loc_id
        .Fields("resind").Value = rsUPSWorld!resind
        .Fields("pkg_type").Value = rsUPSWorld!pkg_type
        .Fields("weight").Value = rsUPSWorld!Weight
        .Fields("trackno").Value = rsUPSWorld!trackno
        .Fields("oversize").Value = rsUPSWorld!oversize
        .Fields("pkg_ref1").Value = rsUPSWorld!pkg_ref1
        .Fields("pkg_ref2").Value = rsUPSWorld!pkg_ref2
        .Fields("pkg_ref3").Value = rsUPSWorld!pkg_ref3
        .Fields("pkg_ref4").Value = rsUPSWorld!pkg_ref4
        .Fields("pkg_ref5").Value = rsUPSWorld!pkg_ref5
        .Fields("addlhand").Value = rsUPSWorld!addlhand
        .Fields("codopt").Value = rsUPSWorld!codopt
        .Fields("codamount").Value = rsUPSWorld!codamount
        .Fields("cashonly").Value = rsUPSWorld!cashonly
        .Fields("declvalop").Value = rsUPSWorld!declvalop
        .Fields("declvalam").Value = rsUPSWorld!declvalam
        .Fields("delconfop").Value = rsUPSWorld!delconfop
        .Fields("delconfsig").Value = rsUPSWorld!delconfsig
        .Fields("hazmatop").Value = rsUPSWorld!hazmatop
        .Fields("sn1op").Value = rsUPSWorld!sn1op
        .Fields("sn1type").Value = rsUPSWorld!sn1type
        .Fields("sn1comp").Value = rsUPSWorld!sn1comp
        .Fields("sn1cont").Value = rsUPSWorld!sn1cont
        .Fields("sn1phone").Value = rsUPSWorld!sn1phone
        .Fields("sn1fax").Value = rsUPSWorld!sn1fax
        .Fields("sn1intlf").Value = rsUPSWorld!sn1intlf
        .Fields("sn1memo").Value = rsUPSWorld!sn1memo
        .Fields("sn2op").Value = rsUPSWorld!sn2op
        .Fields("sn2type").Value = rsUPSWorld!sn2type
        .Fields("sn2comp").Value = rsUPSWorld!sn2comp
        .Fields("sn2cont").Value = rsUPSWorld!sn2cont
        .Fields("sn2phone").Value = rsUPSWorld!sn2phone
        .Fields("sn2fax").Value = rsUPSWorld!sn2fax
        .Fields("sn2intlf").Value = rsUPSWorld!sn2intlf
        .Fields("sn2memo").Value = rsUPSWorld!sn2memo
        .Fields("verbconfop").Value = rsUPSWorld!verbconfop
        .Fields("verbcont").Value = rsUPSWorld!verbcont
        .Fields("verbphone").Value = rsUPSWorld!verbphone
        .Fields("length").Value = rsUPSWorld!length
        .Fields("width").Value = rsUPSWorld!Width
        .Fields("height").Value = rsUPSWorld!Height
        .Fields("merchdesc").Value = rsUPSWorld!merchdesc
        .Fields("date").Value = Format$(Date, "mm/dd/yyyy")
        'Update it
        .Update
        'Close it
        .Close
    End With
    Set db = Nothing
End Sub


Private Sub UpdateRecords()
Form1.Visible = True
Label1.Caption = "Updating records...."

Do While Not rsUPSWorld.EOF
    Call Add
    Call Add2 "G:\rabbitstop\UPS\UOWS\ImpExp\MOMImport.mdb", "USPS"
    Call Add2 "G:\MOMAll.mdb", "ThreeCompanies"
    rsUPSWorld.MoveNext
Loop

Thanks a lot,

malaize2
 
Upvote 0 Downvote
You're probably blowing up because of this
Code: 
Private Sub Add2(MOMAll As String, TableMain As String)
Dim db As DAO.Database
Set db = DAO.DBEngine(0).OpenDatabase(MOMAll)
Set rsUSPS = dbUSPS.OpenRecordset("Select * FROM " & [COLOR=red]ThreeCompanies[/color])

What you need here is the calling argument TableMain as in

Code: 
Private Sub Add2(MOMAll As String, TableMain As String)
Dim db As DAO.Database
Set db = DAO.DBEngine(0).OpenDatabase(MOMAll)
Set rsUSPS = dbUSPS.OpenRecordset("Select * FROM " & [COLOR=red]TableMain[/color])

You are dynamically passing the name of the table that you want to append the record to and you need to open that table in your SQL. As you have it, you're probably seeing an "Undefined Variable" error because ThreeCompanies is the table name and not the name of the variable containing the string "ThreeCompanies".
 
Upvote 0 Downvote
Hi Golom,

Unfortunately it keeps highlighting this line of code and kicking a syntax error.
Code: 
Call Add2 "G:\rabbitstop\UPS\UOWS\ImpExp\MOMImport.mdb", "USPS"

I have tried editing this line of code but I continue to get the syntax error. Can you think of something else that I messed up?

Code: 
Dim dbUPSWorld As DAO.Database
Dim dbTableForUPSImport As Database
Dim rsUPSWorld As DAO.Recordset
Dim rsTableForUPSImport As DAO.Recordset
Dim dbUSPS As DAO.Database
Dim rsUSPS As DAO.Recordset



Private Sub Add()

Set rsTableForUPSImport = dbTableForUPSImport.OpenRecordset("SELECT * FROM TableForUPSImport")

    With rsTableForUPSImport
        'Set it to Add mode
        .AddNew
        'Enter the field values
        .Fields("void").Value = rsUPSWorld!void
        .Fields("serv_type").Value = rsUPSWorld!serv_type
        .Fields("billing_op").Value = rsUPSWorld!billing_op
        .Fields("return_op").Value = rsUPSWorld!return_op
        .Fields("return_typ").Value = rsUPSWorld!return_typ
        .Fields("satdelop").Value = rsUPSWorld!satdelop
        .Fields("satpickop").Value = rsUPSWorld!satpickop
        .Fields("shipnot1op").Value = rsUPSWorld!shipnot1op
        .Fields("shn1_type").Value = rsUPSWorld!shn1_type
        .Fields("shn1_comp").Value = rsUPSWorld!shn1_comp
        .Fields("shn1_cont").Value = rsUPSWorld!shn1_cont
        .Fields("shn1_phone").Value = rsUPSWorld!shn1_phone
        .Fields("shn1_fax").Value = rsUPSWorld!shn1_fax
        .Fields("shn1_intlf").Value = rsUPSWorld!shn1_intlf
        .Fields("shn1_memo").Value = rsUPSWorld!shn1_memo
        .Fields("shipnot2op").Value = rsUPSWorld!shipnot2op
        .Fields("shn2_type").Value = rsUPSWorld!shn2_type
        .Fields("shn2_comp").Value = rsUPSWorld!shn2_comp
        .Fields("shn2_cont").Value = rsUPSWorld!shn2_cont
        .Fields("shn2_phone").Value = rsUPSWorld!shn2_phone
        .Fields("shn2_fax").Value = rsUPSWorld!shn2_fax
        .Fields("shn2_intlf").Value = rsUPSWorld!shn2_intlf
        .Fields("shn2_memo").Value = rsUPSWorld!shn2_memo
        .Fields("descofgood").Value = rsUPSWorld!descofgood
        .Fields("doconlyind").Value = rsUPSWorld!doconlyind
        .Fields("spec_inst").Value = rsUPSWorld!spec_inst
        .Fields("to_id").Value = rsUPSWorld!to_id
        .Fields("to_comp").Value = rsUPSWorld!to_comp
        .Fields("to_attn").Value = rsUPSWorld!to_attn
        .Fields("to_addr").Value = rsUPSWorld!to_addr
        .Fields("to_addr2").Value = rsUPSWorld!to_addr2
        .Fields("to_addr3").Value = rsUPSWorld!to_addr3
        .Fields("to_country").Value = rsUPSWorld!to_country
        .Fields("to_zip").Value = rsUPSWorld!to_zip
        .Fields("to_city").Value = rsUPSWorld!to_city
        .Fields("to_state").Value = rsUPSWorld!to_state
        .Fields("to_phone").Value = rsUPSWorld!to_phone
        .Fields("to_fax").Value = rsUPSWorld!to_fax
        .Fields("to_taxid").Value = rsUPSWorld!to_taxid
        .Fields("rec_upsnum").Value = rsUPSWorld!rec_upsnum
        .Fields("loc_id").Value = rsUPSWorld!loc_id
        .Fields("resind").Value = rsUPSWorld!resind
        .Fields("pkg_type").Value = rsUPSWorld!pkg_type
        .Fields("weight").Value = rsUPSWorld!Weight
        .Fields("trackno").Value = rsUPSWorld!trackno
        .Fields("oversize").Value = rsUPSWorld!oversize
        .Fields("pkg_ref1").Value = rsUPSWorld!pkg_ref1
        .Fields("pkg_ref2").Value = rsUPSWorld!pkg_ref2
        .Fields("pkg_ref3").Value = rsUPSWorld!pkg_ref3
        .Fields("pkg_ref4").Value = rsUPSWorld!pkg_ref4
        .Fields("pkg_ref5").Value = rsUPSWorld!pkg_ref5
        .Fields("addlhand").Value = rsUPSWorld!addlhand
        .Fields("codopt").Value = rsUPSWorld!codopt
        .Fields("codamount").Value = rsUPSWorld!codamount
        .Fields("cashonly").Value = rsUPSWorld!cashonly
        .Fields("declvalop").Value = rsUPSWorld!declvalop
        .Fields("declvalam").Value = rsUPSWorld!declvalam
        .Fields("delconfop").Value = rsUPSWorld!delconfop
        .Fields("delconfsig").Value = rsUPSWorld!delconfsig
        .Fields("hazmatop").Value = rsUPSWorld!hazmatop
        .Fields("sn1op").Value = rsUPSWorld!sn1op
        .Fields("sn1type").Value = rsUPSWorld!sn1type
        .Fields("sn1comp").Value = rsUPSWorld!sn1comp
        .Fields("sn1cont").Value = rsUPSWorld!sn1cont
        .Fields("sn1phone").Value = rsUPSWorld!sn1phone
        .Fields("sn1fax").Value = rsUPSWorld!sn1fax
        .Fields("sn1intlf").Value = rsUPSWorld!sn1intlf
        .Fields("sn1memo").Value = rsUPSWorld!sn1memo
        .Fields("sn2op").Value = rsUPSWorld!sn2op
        .Fields("sn2type").Value = rsUPSWorld!sn2type
        .Fields("sn2comp").Value = rsUPSWorld!sn2comp
        .Fields("sn2cont").Value = rsUPSWorld!sn2cont
        .Fields("sn2phone").Value = rsUPSWorld!sn2phone
        .Fields("sn2fax").Value = rsUPSWorld!sn2fax
        .Fields("sn2intlf").Value = rsUPSWorld!sn2intlf
        .Fields("sn2memo").Value = rsUPSWorld!sn2memo
        .Fields("verbconfop").Value = rsUPSWorld!verbconfop
        .Fields("verbcont").Value = rsUPSWorld!verbcont
        .Fields("verbphone").Value = rsUPSWorld!verbphone
        .Fields("length").Value = rsUPSWorld!length
        .Fields("width").Value = rsUPSWorld!Width
        .Fields("height").Value = rsUPSWorld!Height
        .Fields("merchdesc").Value = rsUPSWorld!merchdesc
        .Fields("date").Value = Format$(Date, "mm/dd/yyyy")
        'Update it
        .Update
        'Close it
        .Close
    End With

    

End Sub

Private Sub Add2(MOMAll As String, TableMain As String)
Dim db As DAO.Database
Set db = DAO.DBEngine(0).OpenDatabase(MOMAll)
Set rsUSPS = dbUSPS.OpenRecordset("Select * FROM " & TableMain)

With rsUSPS
        'Set it to Add mode
        .AddNew
        'Enter the field values
        .Fields("void").Value = rsUPSWorld!void
        .Fields("serv_type").Value = rsUPSWorld!serv_type
        .Fields("billing_op").Value = rsUPSWorld!billing_op
        .Fields("return_op").Value = rsUPSWorld!return_op
        .Fields("return_typ").Value = rsUPSWorld!return_typ
        .Fields("satdelop").Value = rsUPSWorld!satdelop
        .Fields("satpickop").Value = rsUPSWorld!satpickop
        .Fields("shipnot1op").Value = rsUPSWorld!shipnot1op
        .Fields("shn1_type").Value = rsUPSWorld!shn1_type
        .Fields("shn1_comp").Value = rsUPSWorld!shn1_comp
        .Fields("shn1_cont").Value = rsUPSWorld!shn1_cont
        .Fields("shn1_phone").Value = rsUPSWorld!shn1_phone
        .Fields("shn1_fax").Value = rsUPSWorld!shn1_fax
        .Fields("shn1_intlf").Value = rsUPSWorld!shn1_intlf
        .Fields("shn1_memo").Value = rsUPSWorld!shn1_memo
        .Fields("shipnot2op").Value = rsUPSWorld!shipnot2op
        .Fields("shn2_type").Value = rsUPSWorld!shn2_type
        .Fields("shn2_comp").Value = rsUPSWorld!shn2_comp
        .Fields("shn2_cont").Value = rsUPSWorld!shn2_cont
        .Fields("shn2_phone").Value = rsUPSWorld!shn2_phone
        .Fields("shn2_fax").Value = rsUPSWorld!shn2_fax
        .Fields("shn2_intlf").Value = rsUPSWorld!shn2_intlf
        .Fields("shn2_memo").Value = rsUPSWorld!shn2_memo
        .Fields("descofgood").Value = rsUPSWorld!descofgood
        .Fields("doconlyind").Value = rsUPSWorld!doconlyind
        .Fields("spec_inst").Value = rsUPSWorld!spec_inst
        .Fields("to_id").Value = rsUPSWorld!to_id
        .Fields("to_comp").Value = rsUPSWorld!to_comp
        .Fields("to_attn").Value = rsUPSWorld!to_attn
        .Fields("to_addr").Value = rsUPSWorld!to_addr
        .Fields("to_addr2").Value = rsUPSWorld!to_addr2
        .Fields("to_addr3").Value = rsUPSWorld!to_addr3
        .Fields("to_country").Value = rsUPSWorld!to_country
        .Fields("to_zip").Value = rsUPSWorld!to_zip
        .Fields("to_city").Value = rsUPSWorld!to_city
        .Fields("to_state").Value = rsUPSWorld!to_state
        .Fields("to_phone").Value = rsUPSWorld!to_phone
        .Fields("to_fax").Value = rsUPSWorld!to_fax
        .Fields("to_taxid").Value = rsUPSWorld!to_taxid
        .Fields("rec_upsnum").Value = rsUPSWorld!rec_upsnum
        .Fields("loc_id").Value = rsUPSWorld!loc_id
        .Fields("resind").Value = rsUPSWorld!resind
        .Fields("pkg_type").Value = rsUPSWorld!pkg_type
        .Fields("weight").Value = rsUPSWorld!Weight
        .Fields("trackno").Value = rsUPSWorld!trackno
        .Fields("oversize").Value = rsUPSWorld!oversize
        .Fields("pkg_ref1").Value = rsUPSWorld!pkg_ref1
        .Fields("pkg_ref2").Value = rsUPSWorld!pkg_ref2
        .Fields("pkg_ref3").Value = rsUPSWorld!pkg_ref3
        .Fields("pkg_ref4").Value = rsUPSWorld!pkg_ref4
        .Fields("pkg_ref5").Value = rsUPSWorld!pkg_ref5
        .Fields("addlhand").Value = rsUPSWorld!addlhand
        .Fields("codopt").Value = rsUPSWorld!codopt
        .Fields("codamount").Value = rsUPSWorld!codamount
        .Fields("cashonly").Value = rsUPSWorld!cashonly
        .Fields("declvalop").Value = rsUPSWorld!declvalop
        .Fields("declvalam").Value = rsUPSWorld!declvalam
        .Fields("delconfop").Value = rsUPSWorld!delconfop
        .Fields("delconfsig").Value = rsUPSWorld!delconfsig
        .Fields("hazmatop").Value = rsUPSWorld!hazmatop
        .Fields("sn1op").Value = rsUPSWorld!sn1op
        .Fields("sn1type").Value = rsUPSWorld!sn1type
        .Fields("sn1comp").Value = rsUPSWorld!sn1comp
        .Fields("sn1cont").Value = rsUPSWorld!sn1cont
        .Fields("sn1phone").Value = rsUPSWorld!sn1phone
        .Fields("sn1fax").Value = rsUPSWorld!sn1fax
        .Fields("sn1intlf").Value = rsUPSWorld!sn1intlf
        .Fields("sn1memo").Value = rsUPSWorld!sn1memo
        .Fields("sn2op").Value = rsUPSWorld!sn2op
        .Fields("sn2type").Value = rsUPSWorld!sn2type
        .Fields("sn2comp").Value = rsUPSWorld!sn2comp
        .Fields("sn2cont").Value = rsUPSWorld!sn2cont
        .Fields("sn2phone").Value = rsUPSWorld!sn2phone
        .Fields("sn2fax").Value = rsUPSWorld!sn2fax
        .Fields("sn2intlf").Value = rsUPSWorld!sn2intlf
        .Fields("sn2memo").Value = rsUPSWorld!sn2memo
        .Fields("verbconfop").Value = rsUPSWorld!verbconfop
        .Fields("verbcont").Value = rsUPSWorld!verbcont
        .Fields("verbphone").Value = rsUPSWorld!verbphone
        .Fields("length").Value = rsUPSWorld!length
        .Fields("width").Value = rsUPSWorld!Width
        .Fields("height").Value = rsUPSWorld!Height
        .Fields("merchdesc").Value = rsUPSWorld!merchdesc
        .Fields("date").Value = Format$(Date, "mm/dd/yyyy")
        'Update it
        .Update
        'Close it
        .Close
    End With
    Set db = Nothing
End Sub


Private Sub UpdateRecords()
Form1.Visible = True
Label1.Caption = "Updating records...."

Do While Not rsUPSWorld.EOF
    Call Add
    Call Add2 "G:\rabbitstop\UPS\UOWS\ImpExp\MOMImport.mdb", "USPS"
    Call Add2 "G:\MOMAll.mdb", "ThreeCompanies"
    rsUPSWorld.MoveNext
Loop

'Close upsworld.dbf
rsUPSWorld.Close
Set rsUPSWorld = Nothing
dbUPSWorld.Close
Set dbUPSWorld = Nothing

'rsTableForUPSImport.Close
Set rsTableForUPSImport = Nothing
'dbTableForUPSImport.Close
Set dbTableForUPSImport = Nothing

'rsUSPS.Close
Set rsUSPS = Nothing
'dbUSPS.Close
Set dbUSPS = Nothing





Label1.Caption = "Updating records....Done!"
Call Finish
End Sub

Private Sub Finish()
Call SetArchive
Timer1.Enabled = True
Timer2.Enabled = True
End Sub



Private Sub Timer1_Timer()
On Error GoTo errorhandler
Dim archive As Integer

archive = GetAttr("upsworld.dbf") And vbArchive

If archive = 32 Then
Timer1.Enabled = False
Set dbUPSWorld = OpenDatabase("MOMImport.mdb")
Set rsUPSWorld = dbUPSWorld.OpenRecordset("SELECT * FROM UPSWorld")
Set dbTableForUPSImport = OpenDatabase("MOMImport.mdb")
Set dbUSPS = OpenDatabase("MOMImport.mdb")
'Delete records that are 21 days old
dbUSPS.Execute ("USPSQuery")
dbTableForUPSImport.Execute ("DeleteQuery")
Set rsUSPS = dbUSPS.OpenRecordset("Select * FROM USPS")
Set rsTableForUPSImport = dbTableForUPSImport.OpenRecordset("SELECT * FROM TableForUPSImport")
Call UpdateRecords
End If

Exit Sub
errorhandler:
If Err.Number = 53 Then
MsgBox "file not found"
Else
MsgBox Err.Description, vbOKOnly, "Error"
End
End If
End Sub

Private Sub SetArchive()
On Error GoTo error_handler
Set fso = CreateObject("Scripting.FileSystemObject")
Set file = fso.GetFile("upsworld.dbf")
file.Attributes = 0
Exit Sub
error_handler:
MsgBox Err.Description, vbOKOnly, "Error"
End Sub

Private Sub Timer2_Timer()
Timer2.Enabled = False
Form1.Visible = False
Shell "temp.exe", vbNormalNoFocus
End
End Sub

Thank you for your patience,

malaize2


 
Upvote 0 Downvote
Upvote 0 Downvote
Sorry. I wasn't looking closely at your syntax. These statements
Code: 
Call Add2 "G:\rabbitstop\UPS\UOWS\ImpExp\MOMImport.mdb", "USPS"
Call Add2 "G:\MOMAll.mdb", "ThreeCompanies"

Should be

Code: 
Call Add2 [COLOR=red]([/color]"G:\rabbitstop\UPS\UOWS\ImpExp\MOMImport.mdb", "USPS"[COLOR=red])[/color]
Call Add2 [COLOR=red]([/color]"G:\MOMAll.mdb", "ThreeCompanies"[COLOR=red])[/color]
 
Upvote 0 Downvote
Beat me to it, Golom, as well you should since you've been the one spending all the time. :)

malaize, here are the basic syntax rules for procedure calls:

Subs can be either

mySub myArg1, myArg2 [, myArgN]

or

Call mySub (myArg1, myArg2 [, myArgN])

You have confused the two together. If you use the Call statement, you have to enclose the argument list in parentheses, if you do not, you have to NOT do so.

HTH

Bob
 
Upvote 0 Downvote
Hi Golom,

That fixed the syntax and I was able to compile the code successfully. Unfortunately when the program ran I received an error.

"The Microsoft Jet database engine cannot find the input table or query 'ThreeCompanies'. make sure it exists and that its name is spelled correctly."

I checked the spelling of the ThreeCompanies table and it is correct. I also checked the path to the MOMAll.mdb database that contains the ThreeCompanies table and that is correct too. I haven't made any more changes to the code since I added the parentheses that you suggested in your last post.

Thanks again,

malaize2
 
Upvote 0 Downvote
In ADD2 you need
Code: 
Set rsUSPS = [COLOR=red]db[/color].OpenRecordset("Select * FROM " & TableMain)
 
Upvote 0 Downvote
Sorry it has taken me so long to respond Golom. The code now works, thanks to YOU!

Thank you very much for all of the help and patience. :)

malaize2
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

2009luca
  • Locked
  • Question Question
possible to use xml2 or Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1"), instead code in message
Replies
5
Views
963
2009luca
2009luca
sal21
  • Locked
  • Question Question
SORT DATE in msflexgrid not correct
Replies
4
Views
559
sal21
sal21
sal21
  • Locked
  • Question Question
how to send SMS via free API or other service
Replies
2
Views
797
strongm
strongm
marty1944
  • Locked
  • Question Question
error code 380 when trying to run the visual basic v6 deployment wizard
Replies
1
Views
920
marty1944
marty1944
sal21
  • Locked
  • Question Question
NOT FOR ME, get value from table web
Replies
1
Views
464
sal21
sal21

Part and Inventory Search

Sponsor

Back
Top