Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




DataEnvironment AddNew Error


I have a project connected to an Access 2000 DB. All is well with the connection (I can navigate the records fine). When I add a new record & then try to navigate, I get the following error:

"Empty row cannot be inserted. Row must have atleast one column value set."

 (BTW nothing has been entered into the text boxes, assuming the user hit the wrong button & did not want to add a record)

The form also will open in the "empty" state if I close the form (MDI Child) & reopen the form w/o closing the program.

my code is:

Code: Private Sub Command3_Click()
DataEnvironment1.rsmain.AddNew
End Sub




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
DataEnvironment AddNew Error
Here's the code:


Code:
DataEnvironment1.rsCommand1.AddNew
DataEnvironment1.rsCommand1![AssetID] = EndingRecord
DataEnvironment1.rsCommand1.Update

When I run this, I get the following run-time error:
"Multi-step operation generated errors. Check each status value."
(Not sure what status value I need to check - the fields that I'm attempting to update are not 'ReadOnly'. I've also got a SUB to change existing data and even delete records, and those are working OK on the same recordset.)

I'm assuming it's possible to use ADDNEW on the DE. I don't error-out on the ADDNEW method of the Command object. My error is in placing data into the newly-created 'record'. If I simply try to update the recordset without putting any data into the record, I get an error that an 'Empty row cannot be inserted. Row must have at least one column value set'.

I'm also assuming that my syntax is OK. Prior to doing the ADDNEW, I've checked the value of the current DataEnvironment1.rsCommand1![AssetID]. It returns a value without errors.

Any thoughts on this? So far, I've made it through updating and deleting records without any problems. The ADDNEW, however, has stopped me.

Newbie, DataEnvironment AddNew???
I am using a DataEnvironment and I want to AddNew. When I get to DatatEnvironmen1.rsCommand1.AddNew , then hit space I get the prompt for ([field list],[Values]) , I have tried to add several different things but I don't seem to be giving it what it wants,
Can someone point me in the right direction and post a sample of code for this.

Thank you
Dale S

ADO - Addnew - Error
Hello All,

I think this should be an easy answer, but I was not able to find it in the tutorial nor by searching the forums.

I am getting an error "3251 Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."


Code:
Set MyConn = New ADODB.Connection

MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NRSTATdblocation & ";"
MyConn.open

Set MyRecSet = MyConn.Execute("SELECT * FROM TestRecords")
MyRecSet.AddNew

I have tried changing the locktype with, but both of these cause different errors:

Code:
MyRecSet.LockType = adLockOptimistic
Set MyRecSet = MyConn.Execute("SELECT * FROM TestRecords")

Code:
Set MyRecSet = MyConn.Execute("SELECT * FROM TestRecords")
MyRecSet.LockType = adLockOptimistic

Any suggestions?

I couldn't get the INSERT INTO SQL to work. Probably something wrong with the statement... there's 48 fields that I have to make sure to write in correctly.


Thanks,
Jason

.AddNew Error
I am having a problem adding records to an Access 2000 database. Must be something simple that I'm just not seeing. .EOF and .RecordCount work okay in other parts of the program.

Here is the code.

Global cn As Connection
Global rsTrans As Recordset

Set cn = New Connection
Set rsTrans = New Recordset

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:GMSDataGMS.mdb"
.Open
End With

Public Sub AddTrans( ........ )

rsTrans.Open "Trans", cn

For recs = 1 To transarray
rsTrans.AddNew
rsTrans!TranNo = vTranNo
rsTrans!CustNo = tCustNo
rsTrans!TranDate = Now
rsTrans!Item = aItem(recs)
rsTrans!ItemNo = aItemNo(recs)
rsTrans!Cat = aCat(recs)
rsTrans!TranAmt = aAmt(recs)
rsTrans!TranTaxAmt = aTax(recs)
rsTrans.Update
Next

rsTrans.Close

End Sub

The program stops at the "rsTrans.AddNew" line and gives a 3251 error: "Object or provider is not capable of performing requested operation.

Please help. Thank you.

ADO AddNew ERROR - Please Help
I am using the ADO Data Control 6.0 and trying to use the AddNew method and I get an error everytime. Here is what I am doing.

The ADO control is connected to an access database customer.mdb. The I do:

ADODC1.Recordset.AddNew

and get this error:

Runtime error 91 - Object variable or With Block variable not set.

Does anyone know what this means? All I am doing is calling AddNew. It seems I cannot do any methods after the Recordset property. What do I need to do first?

Any help will be appreciated!

AddNew Error
Hi,

I use SQL with VB. when I add a new record it adds it properly, but when I try to retrive immediately it gives me an error. here is my coding

private sub cmdSave_click
rs.open ("Select * from student where stno='" + mstno +"'"
call firstrecord ' this will find the number of records in the rs and returns countr
if countr>0 then
   msgbox "stno exist, cannot duplicate"
   exit sub
endif
rs.addnew
rs("stno")=mstno
rs.update

call updatedetails(rs,me) 'this is a function that update the other details

end sub


function updatedetails(rs as recordset, frm as form)
rs("name")=frm!txtname.text
rs("add")=frm!txtadd.text
rs.update
end function

but when i execute this coding it stores the stno into the table. but when it comes to the function part it returns the following error
-2147217893 identity cannot be determined for newly inserted rows

but this coding was working with MS Access database. and it did work with SQL for some time (until i re-install my SQL server).

I am expecting a quick reply please

thanks
kishor

RsADODB.AddNew Error!
guys why do i get this error while i'm trying to add/edit my ado recordset? below is my code.

error msg: the operation requested by the application is not supported by the provider.

then it point out to "adRsConfig.AddNew"


code:

Private Sub Command1_Click()

Dim adConnConfig As ADODB.Connection
Dim adRsConfig As ADODB.Recordset
Dim cQuer As String

Set adConnConfig = New ADODB.Connection
Set adRsConfig = New ADODB.Recordset

adConnConfig.ConnectionString = "Provider= Microsoft.Jet.OLEDB.3.51;Data Source=test.mdb"
adConnConfig.Open
adConnConfig.CursorLocation = adUseClient

adRsConfig.Source = "SELECT * FROM info"
adRsConfig.ActiveConnection = adConnConfig
adRsConfig.Open
adRsConfig.AddNew
adRsConfig.Fields("User") = "wappyyyy"
adRsConfig.Update
adRsConfig.Close

End Sub

Error 3426 On AddNew
I am using a bound data control from VB6 SP5 that is linked to an Access 2000 table. I am trying to add a new student record using the following code:

        stdDisp.student.Recordset.AddNew
        stdDisp.student.Recordset.Edit
        stdDisp.student.Recordset!ID = Val(stud_id$)
        stdDisp.student.Recordset!lname = lastname$
        stdDisp.student.Recordset!fname = firstname$
        stdDisp.student.Recordset!m_init = ""
        stdDisp.student.Recordset!cur_grade = curgrade
        stdDisp.student.Recordset!bdate = bdate$
        stdDisp.student.Recordset!mfsex = sex$
        stdDisp.student.Recordset!ethnic = ethnic$
        stdDisp.student.Recordset.Update

I am getting a error 3426. This code had been working up until recently. Nothing has changed that I am aware of. I have searched the forums and tried to place an Edit command before the update but still have the error.

Any suggestions????


Error 3426 Using AddNew
Hello,

I am us VB 6 Enterprise with Access 2000. I use .FindFirst to verify if a record exists. If not then I use .AddNew. When I do this I see the entire recordset getting cleared out and receive an error 3426 - Action Cancelled by associated object.

    If addnewrec then
        stddisp.student.recordset.addnew
        stddisp.student.recordset!ID = Val(stud_id$)
        stddisp.student.recordset!last_name = lname$
        stddisp.student.recordset!first_name = fnale$
        stddisp.student.recordset.Update
    end if

I use the same logic on a different table in the database and it works fine. The database is password protected and all of the tables are linked using a DAO connection. I'm so confused. Any help would be greatly appreciated.

AddNew Error 3265
First time use of ADO control and gridcontrol on form:

When user selects cmd button to add a record I collect his entered field data and call an Sub routine AddProcess.

In the Sub I have:
variant array - containing all the fields in order as found in the recordset
variant array buffer - containing fields that I load with queried data from separate tables
(ie. the user gives me two fields of data that I use to query single recs from two different tables and then
automate the loading of certain fields into this record that will be loaded with the AddNew function)

Occasionally I can get the add of the new record to the recordset and entry will display in the gridcontrol so I know my field order in the arrays below is correct but if I click the Add button a second
or more times then the dreaded 3265 error message pops up.

AddProcess fx

Loop(the counter of the loop is really the many rec retrievals from one of the tables to get data to load here)
  call loadbuffer(myBuff)
  Ado.recordset.AddNew myFields, myBuff
  Ado.recordset.Update
  Ado.refresh
end loop

*** when I debug through this loop myFields allways has the hard coded field sequence which is correct and
the myBuff array I make sure has the same number of fields and gets loaded with the data correctly.
do I need to requiry my recordset before the addnew or move the position to the first record before calling
addnew? I was also thinking about making individual field assignments rather than relying on the array args

 

Addnew Error In Dataset
Hi all,

I need to add a new row in a dataset. I use the next statements:

...
dataset.addnew
dataset.fields("Field1") = Value1
if (Condition) then
dataset.fields("Field2") = Value2
endif
dataset.fields("Field3") = Value3
...

If 'Condition' is FALSE then new row is add correctly, but when condition is TRUE, then produced then next error:
"The operation in several steps generated errors. Verify the values of state." The Field2 is a numeric field.

Somebody know why produced a error when i update the 'Field2" value?

Thank you very much.

Ado RecordSet AddNew Method Error 91
hi all, thanks for giving me ur time
i wish to add data to an access DataBase, every thing is cool
but : this my code (i have set the connectionString for the adodc & the recordSource to the database & to the table through the adodc properity window. i have no problem deleting & retriving records & diplaying them into other controls)
.
.
with adodc1 ' for example
.recordset.addnew Array("field1","field2"), Array( text1.text,text2.text)
.recordset.update
.
.
end with

it give me error 91
"object variable or with method not set"
i have no idea what dose that means..
plz help

regards
sanad

Blank Error Msgbox During Addnew
hi all,

i am doing an .addnew to a table in access db. well when addnew executes it gives me a msgbox with no descripotion of the error and then does not update the table.

ne ideas? have already checked all the properties and code involved for simple stuff.

Addnew => Multistep Operation Error
Hi group,

When I add a record to my SQL server 2000 database everything works fine...except when I leave a couple of fields blank.

I get the following error:

Multistep operation generated errors. Check each status value.

The fields in the database (SHOULD) accept NULL values.

Oh I use this code:

(Sorry for the DUTCH words, It's an Employees table)

With RS
.AddNew
RS.Fields("MedewerkerID") = Me.txtMedewerkerID
RS.Fields("MedewerkerNaam") = Me.txtNaam
RS.Fields("medewerkeradres") = Me.txtAdres
RS.Fields("medewerkerdatumindienst") = Me.txtDatum
RS.Fields("medewerkerpostcode") = Me.txtPostcode
RS.Fields("medewerkerwoonplaats") = Me.txtWoonplaats
RS.Fields("medewerkerland") = Me.txtLand
RS.Fields("medewerkertoestel") = Me.txtToestel
RS.Fields("medewerkertelefoonprive") = Me.txtTel
RS.Fields("MedewerkerE_Mail") = Me.txtEmail
.Update
End With
Regards,

Sander

Addnew & Find & Error Trapping
I have seen some examples where people use the addnew method- and let it issue an error message( ie, like "can't have duplicate keys",etc)....
Then I have seen some where a query is issued first to find the record before using addnew method. If the recordset is empty- addnew is performed, or a message is issued. They both prevent duplicates from being added, but I am wondering which way is the right and most efficient way to handle considering I am using an Access database- (jet) and am not using stored procedures. Thanks in advance for your opinions.

AddNew->Update->Delete->get An Error
Hi, I get an error of "could not locate the row ..." when I delete a row which just added, why ? But it can be deleted when i quit the program and then execute again.
Thank you !


private Sub Command1_Click()
DataEnvironment1.rscncustomer.AddNew
me.txtCUSTID = "123456"
me.txtCUSTNAME = "ABC Company"
DataEnvironment1.rscncustomer.Update
DataEnvironment1.rscncustomer.Delete
End Sub

Run-time Error '91' (data1.recordset.addnew)
VB Code:
Run-time error '91' Object variable or With block variable not set.data1.recordset.addnew


Someone want to explain why I get this error? I have used this code before without any problems. And now out of no where

Visual Basic + Interbase : Addnew Error
I am using Visual Basic to connect to Interbase. The connection worked but when I try adding a new record
the following error occurs :

    Runtime Error '3251':
    Object or provider is not capable of performing requested operation.

This is the Visual Basic code I'm using

'Setting & opening the connection

Dim adoConn As New ADODB.Connection

 Set adoConn = New ADODB.Connection
 adoConn.ConnectionString = "provider=IbOLEdb.1;data     source=localhost:C:Program FilesBorlandInterBase
IB_TutorialTutorial.gdb;ctype=win1251;user id=SYSDBA;password=masterkey"
 adoConn.Open

Private Sub TEST()
Dim RS As New ADODB.Recordset, SqlStr as string
 
  SqlStr = "SELECT * FROM Country"
  RS.CursorLocation = adUseClient
  Set RS = New ADODB.Recordset
  RS.Open SqlStr, adoConn, adOpenStatic,
adLockBatchOptimistic
 
  With RS
    .AddNew  ****** ERROR **********
    ![country] = "Malta"
    ![Currency] = "Lm"
    .Update
    .Close
  End With
    Set RS = nothing
 End Sub

I would appreciate any help !!

Thanks



Strange Error: Disconnected RS/Datagrid On Addnew
Has anybody experienced this or am I loosing my mind?

Using ADO & Access
I want to show data from MyTable on a datagrid, then let the user add/remove records without saving any changes on MyTable until user´s confirmation/validation... so, I disconnect the RS to play around with the information.

I have some textboxes and a command button in order to add info to the datagrid using RS.Addnew. To delete rows, the user should delete them using the DEL key on the datagrid.

So adding and deleting rows works fine EXEPT when there are some records in the datagrid, and the all of them are deleted. The RS.recordcount = 0, but after a RS.Addnew it remains in CERO!!! No matter how many .Addnews there are, the .recordcount remains in CERO!

I already tried unbinding the datagrid from the RS and then binding it again after the .update but doesn´t make difference. I put a datagrid.refresh on the afterdelete event, and nothing either!


Is this a Bug? Or am I missing something?

I appreciate any help.

Error When Using DataEnvironment
Hi. I have the following code:
DataEnvironment1.Connection1 = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=agenda_templ"
DataEnvironment1.Commands.Item(1).CommandType = adCmdTable
DataEnvironment1.Commands.Item(1).CommandText = "select * from Agenda_angajati where cod_ang= '" & Text2.Text & "' "
DataEnvironment1.Command1

When the program tries to execute
DataEnvironment1.Command1
I get this error:
"Syntax error in FROM clause!"
But I do not understand what is wrong. Could someone help me please? Thank you!

DataEnvironment Error
I wrote an app using the DataEnvironment within VB to read/write to an
Access database on our network. Instead of installing the app on each users
computer, I put the .exe on the network and sent each user a shortcut to the
application. When a user runs the app, an error message is displayed
(caught by my error handling runtine) which states "Application-defined or
object-defined error 713". It appears to be generated on form load when
trying to execute a dataenvironment command. Any suggestions??

Thanks
Ryan Miller

Add New Not Occuring, Even Though AddNew Function Executes Error Free.
Hey...
I have a simple command button that adds new transactions to a customer table called datTransactionPackage.  I've called the AddNew event for the the recordsource and append the information at runtime.  

Following is the add event called when "Add to Shopping Cart" is clicked.

Private Sub cmdATShoppingCart_Click()
On Error Resume Next

Dim intCustomerID As Integer
Dim intPackageID As Integer
Dim intLotionID As Integer
Dim intPaymentID As Integer
Dim curDiscount As Currency
Dim strSQLPackage As String
Dim strSQLLotion As String
Dim srtSQLCustomerPackage As String


strSQLCustomerPackage = "SELECT TransactionPackage.* " & _
"FROM TransactionPackage;"

datCustomerPackage.RecordSource = srtSQLCustomerPackage
datCustomerPackage.Refresh

If cboATLastName.ListIndex = -1 Then
    MsgBox "A Customer must be selected from the drop down list.", vbOKOnly
    Exit Sub
Else
    intCustomerID = cboATLastName.ItemData(cboATLastName.ListIndex)
End If

intPaymentID = cboATPaymentType.ItemData(cboATPaymentType.ListIndex)

intPackageID = lstATPackage.ItemData(lstATPackage.ListIndex)
    
    strSQLPackage = "SELECT Package.* " & _
    "FROM Package " & _
    "Where Package.PackageID = " & intPackageID & " ;"
    
    datPackage.RecordSource = strSQLPackage
    datPackage.Refresh
    
       
    With datCustomerPackage
        .Recordset.AddNew
        .Recordset("CustomerID") = intCustomerID
        .Recordset("PackageID") = intPackageID
                
            If Err.Number = 3265 Then
            'this is sql server 7.0
                .Recordset("PackageID") = CLng(datPackage.Recordset("PackageID").Value)
            ElseIf Err.Number <> 0 Then
                Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
            End If

       
        .Recordset("NoOfSessions") = datPackage.Recordset("Sessions")
        
        If chkATExpiry.Value = False Then
            .Recordset("Expire") = 0
        End If
        
        If chkATExpiry.Value = True Then
            .Recordset("Expire") = cldATCalendar.OutputText
        End If
          
        If chkATRegPrice.Value = True Then
            .Recordset("Price") = datPackage.Recordset("RegPrice")
            .Recordset("Tax") = datPackage.Recordset("RegTax")
            .Recordset("Total") = datPackage.Recordset("RegTotal") - Val(txtATDiscount.Text)
        End If
        
        If chkATTCPrice.Value = True Then
            .Recordset("Price") = datPackage.Recordset("TCPrice")
            .Recordset("Tax") = datPackage.Recordset("TCTax")
            .Recordset("Total") = datPackage.Recordset("TCTotal") - Val(txtATDiscount.Text)
        End If
        
        If chkATNoCharge.Value = True Then
            .Recordset("Price") = 0
            .Recordset("Tax") = 0
            .Recordset("Total") = 0
            .Recordset("PaymentIDFK") = 1 'default no charge sale as cash payment
        End If
        
        If chkATNoCharge.Value = False Then
            .Recordset("PaymentIDKF") = intPaymentID
        End If
                
        .Recordset("StaffIDFK") = 4 'change this to the staff member variable
        .Recordset("CustomerIDFK") = intCustomerID
        .Recordset("PackageIDFK") = intPackageID
        .Recordset.Update
        
    End With
lstTSShoppingCart.AddItem datPackage.Recordset("PackageName")

MsgBox "Item Added to Shopping Cart.", vbOKOnly
End Sub

My Question: After the add new, and the final message box indicates a new item has been added, my table does not look like it added a record at all.  Why?

ps-I was getting error 3265 because of SQL 7.0 but thought I fixed it... might be part of the problem.

Thanks for your help...



Error Dataenvironment / Report
hello folks
i have made a report in visual basic with the designer.
the source for this report is a sqlcommand from the dataenvironment(acces database). this command has a parrameter.

when i run the application theres some error
when i execute the command the first time all goes well and the report is just fine. but when i execute the same command again i get this error ( run time error -2147217900 (80040e14): no valid sql string found, select insert, update, procedure, delete expected

here is the code that i try to run

Code:
Private Sub cmdafdrukgeg_Click()
Dim intselectedsesi As Double
grdpatsession.col = 0
intselectedsesi = grdpatsession.Text

If DataEnvironment1.Connection1.State = adStateOpen Then
DataEnvironment1.Connection1.Close
End If
DataEnvironment1.Connection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strdatabaselocatie
DataEnvironment1.Connection1.Open
If Not Trim(intselectedsesi) = "" Then
DataEnvironment1.comopmerkingen_Grouping (CDbl(intselectedsesi))
rptopmerkingen.Show
End If
End Sub

and here the sql command ( this works like a breeze, no errors in here i think)

SELECT ptnaam, ptstraat, ptnr, gempostnr, gemnaam, startuur, kalopm, prog_naam, trainername, traineraddres, traineraddres2, sessionid, datum FROM qr_opmerkingen WHERE (sessionid = ?)

what can be the problem
greetings

DataEnvironment Error Message
Am trying to connect to an Access 2000 database using Jet 4.0
the access database is protected by a mdw file
So I enter the user name and password
then when I push the Test connection it comes up with
"Test connection failed because of error in initializing provider. %1"
Have checked the Microsoft site and they have no info on it.
Have also checked the connection with a non protected database (changed the mdw file in wrkgadm)
It worked fine
Have checked the user name and password no problem there.
I was thinking it might have something to do with telling the DataEnvironment where to look for the mdw file
Any one have a similar problem and have a fix for it?



Method Or Data Member Not Found Error In Regards To .AddNew Code
Hello,
I have a VB6 project that I am trying to edit. There is a subroutine in this project that I have copied and pasted into the same project as a new subroutine with the name of Add2.
Here is the original subroutine.


Code:
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
Here is the new subroutine that is also in my project along with the Add sub.

Code:
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 = rsUSPSd!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 = 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
When I try to compile this project I receive an error message with the .AddNew code in the Add2 subroutine highlighted.
The error is:

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


As you have probably guessed by now, I am not very good with VB. Is there something obvious that I am overlooking?

Thanks for any help that you can provide,
malaize2

AddNew Function Is Generating An Error... HEELP!! [SOLVED Thanks To DEKE]
Hey there experts,

I'm trying to add a new record to a 'Microsoft Access v7 MDB database' using 'Microsoft Jet 4.0 OLE DB Provider' in a DataEnvironment with visual basic 6, but when i give the following command:

Code:DataControl.rsMainRecords.AddNew

this error is generated:

Quote:Run Time Error '3251': The operation requested by the application is not supported by the provider.


HELP!!!



Edited by - X3M on 1/10/2004 7:25:38 AM

Database Error Description With DataEnvironment Help
I am trying to get the Database Error Description when using the DataEnvironment. The following code is when you get the database error description when you setup the adodb directly.

On Error GoTo cmdSelect_Error:
Dim errSelect As ADODB.Error, pstrMessage As String
Dim cnn1 As ADODB.Connection
Dim rstRAT As ADODB.Recordset
Dim strCnn As String
Dim strDBLoc As String
strDBLoc = GetSetting("FTPCP", "Path", "DB", "0")
strDBLoc = strDBLoc & "
at.mdb"


'Open an ADO connection
Set cnn1 = New ADODB.Connection
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDBLoc
cnn1.Open strCnn

'Open RAT table
Set rstRAT = New ADODB.Recordset
rstRAT.CursorType = adOpenKeyset
rstRAT.LockType = adLockOptimistic
rstRAT.Open "RAT", cnn1, , , adCmdTable

'Add the new record
rstRAT.AddNew
(etc.)

cmdSelect_Error:
pstrMessage = "There was a Database Error" & Chr(13)
For Each errSelect In cnn1.Errors
pstrMessage = pstrMessage & errSelect.Description & Chr(13)
Next



Does anyone now how to read the description errors from the database when you have a DataEnvironment set up. The name of my DataEnvironment is deFTPVerify.


Thanks for your help.

Syntax Error Passing SQL To DataEnvironment
I am trying to pass a SQL to the DataEnvironment at run time
ysing the Command1_click event:

DataEnvironment1.Commands("orders").CommandText=_
SELECT account FROM orders WHERE account="12345";

And I get syntax error in FROM clause so I change tosee where the problem is to:
SELECT * FROM orders
And still get the same error what am I doing wrong?

Error 3146. AddNew A Record In A DB2 Table (ODBCDirect DAO), From VBA-Excel 2002
I'm trying to INSERT a record in a DB2 table using ODBC Direct DAO, from VBA-Excel 2002.

It stops at the update sentece.
The DBEngine records show this message:
"3146: ODBC: call error".
"30058: S1C00: [IBM][:S1C00 Driver not capable"

My code:

Code:
Sub PRUEBA()
    ConnectAndAddRecords "P108375", "DESARROLLO", "xxxxxx"
End Sub

Sub ConnectAndAddRecords(UID As String, DSN As String, _
                             Optional PWD As String)
       Dim wrkODBC As Workspace
       Dim conPubs As Connection
       Dim rstTmp As Recordset

      On Error GoTo trap:
    
    ' Create an ODBCDirect workspace.
       Set wrkODBC = CreateWorkspace _
           ("NewODBCWorkspace", "admin", "", dbUseODBC)

    ' Open a connection to an ODBC data source.
       Set conPubs = wrkODBC.OpenConnection("Connection1", _
           dbDriverNoPrompt, , "ODBC;DATABASE=DESARROLLO;UID=" & UID _
           & ";PWD=" & PWD & ";DSN=" & DSN)

    ' Open a recordset which is read-only by default.
       Set rstTmp = conPubs.OpenRecordset("APPLDTA.F4106", dbOpenDynamic, 0, dbOptimistic)

       
        rstTmp.AddNew
        rstTmp!BPITM = 22004
        rstTmp!BPMCU = " 18000"
        rstTmp!BPLOCN = " "
        rstTmp!BPLOTN = " "
        rstTmp!BPAN8 = 0
        rstTmp!BPICID = 2308
        rstTmp!BPLOTG = " "
        rstTmp!BPFRMP = 0
        rstTmp!BPCRCD = "USV"
        rstTmp!BPUOM = "GA"
        rstTmp!BPEXDJ = 105001
        rstTmp!BPuprc = 123456
       
       
       rstTmp.Update
       rstTmp.Close
       conPubs.Close
       wrkODBC.Close
trap:
       
    Debug.Print Err & ": " & Error$
    For Each e In DBEngine.Errors
        Debug.Print e.Number & ": " & e.Description
    Next e
 End Sub



 

 

DataEnvironment Raising Unspecified Error Problem
Hi,

I am encountering "Unspecified Error" Problem when using DataEnvironment of VB6.
At Some instances I am getting this error when the Requery Option is Set or when using the Filter Property of the Recordset.

Please let me know what is the problem...

Regards

Robert.

Subform Error: "Update Or CancelUpdate Without AddNew Or Edit"
Hi,

I somehow managed to make a checkbook balancing database about a year ago. At the time I was pretty new to office in general, and esp VB, so while I got it working enough that I could use it, it had a few cosmetic problems. I've finally gotten around to trying to finish it off (got sick of the problems, and have managed to tackle all the problems but one. . . I can't alter records in my table subform, without getting a popup error... "Update or CancelUpdate, without AddNew or Edit"

Here's the setup:

I've a main form that opens when the database is opened. It contains a dropdown list of accounts. below this (still on the main form, is the main data-entry controls. (set up to look like a check). And finally at the bottom, a subform, which lists all account transactions (similar to a checkbook ledger.)

Main forms - cbxAccountID_AfterUpdate()
...
Set .AccountTransactionsSubform.Form.Recordset = .Recordset


(forgive me, i really havent got much expirience with VBA, but it appears here I am setting the subform's recordset to the same as the parent forms. Right?) So both forms should be sharing the same recordset, the main merely showing a single record at a time, while the sub, showing them all. I 'can' control the parent forms cursor via selecting records in the subform, but for some reason... when I attempt to alter a record in the subform (for example to tick the 'posted' checkbox) this is what raises the error.

It is a PAIN. It would be so nice to be able to just quickly run down the subform grid and check each box for records that are posted.

The subform is set as far as I can tell to enable all editing (eg: Allow add new, allow edit, etc). Im quite lost as to what else I can do?


Thanks
Brad

Dataenvironment Recordset Gives Error "Library Not Registered"
Hi All

I have developed a sales/quote system using VB6 sp5 with a SQL 2k database. I connect to the database using ADO for the forms and the dataenvronment for the reports. This system is running on a mixture of Windows NT/2k/XP workstations, no problems so far. However, the copy installed on the server does not print, we are getting error number -2147220992, Library Not Registered.

It has been working with little problem, but a recent update to the front end seems to have messed things up. We have reinstalled the app but with no joy. I know some of you will ask why we are running this on the server, and the answer is that the big boss uses this occassionally as a PC. Scary but true. We are working on him to prevent this but that is an issue well outside the scope of this forum.

I have found information in the MS knowledge base that refers to Visual Interdev, but cannot find anything related to VB.

Any advice anyone.

Regards

Steve

Addnew
I am having severe problems with this.

I'm trying to add a new record to my table using dao.

I have:


Code:
rsprod.addnew
rsprod(0) = rsprod_temp(0)
rsprod.update

I am getting "not on file" error.

Can someone help me out?

Can't Do Anything After An Addnew?
In ADO, after adding a record to a recordset via Addnew, it seems I cannot do any of the following:
update
movefirst
movelast
resync

My cursor type is Keyset and my lock type is Optimistic and my cursor location is Client. The database type is Jet MDB.

Any ideas?

Addnew
Hi,
After addnew is called i want to cancel the current addnew .. how can i do that

AddNew Help
I have an issue that I'm trying to figure out:

I have a MySQL database running on a remote server. I am accessing it via LAN and I have multiple users.

The program I have needs to store data in one table, get the index for that last entry and use it to store data into other tables. My main table however is getting very large and it is taking this procedure quite some time to grab the data from the server so it can be updated locally.

Can anyone suggest a more effecient manner? I'm thinking about changing the procedure to a simple "INSERT INTO", but what is the easy way of getting the index back out of the last record entered even though there are others entering data at the same time!


Code:

rs.Open "SELECT * FROM logDispatch", conn, adOpenForwardOnly, adLockOptimistic
rs.AddNew
'fields to add
rs.Update

Dim iDisID As Long
iDisID = rs.fields("DisID")
rs.Close

addLogRecord = iDisID
addLogRow iDisID
Note: Using adUseClient for Connection and Visual Basic 6

ADO And AddNew
I ma using ADO data object. The following procedure saves a record to my database. I have 3 fields: ECONumber, Originator, and date. ECONumber is of type Autonumber. However, when I click on my button, the autonumber does not seem to work. I need to manually enter a number for the ECONumber field!
I can't figure why I get this behavior.
A second question concerns clearing fields. Is there any way I can clear all my textboxes on my form without having to write for each blah.Text = " "?

Here is my code:

Code:
Private Sub Command3_Click()

Dim rs As New ADODB.Recordset, sql As String

sql = "SELECT ECONumber, Originator, Date " _
& " FROM table1;"

rs.Open sql, sConn, adOpenKeyset, adLockOptimistic, adCmdText

rs.AddNew


rs!ECONumber = Text1.Text
rs!Originator = Text2.Text
rs!Date = Text3.Text

rs.Update
Text1.Text = " "
Text2.Text = " "
Text3.Text = " "
rs.Close
Set rs = Nothing

End Sub


Thanks

AddNew
Hi all i've a prob over here .....................

I use addnew to add data to a access database the data is being add but if i open the file with access evrything is there but not in the wright order.............. so the first added data isn't in the first line etc etc

I've tried movelast but nothing happend ..........................



Thanx in advance guy's and girls

How Bad Is RS.AddNew
ok, in your guys opinion.. how bad is the AddNew method of an ADO Recordset?

I was told it is not as stable as SQL Insert statements.. so I never use it... but using that makes inserting a record a lot easier you don't need all the ' and " and & and all that fun stuff that makes sql statements so much fun.

ADO Addnew Causes Out Of Seq
I currently have a ADO RS(recordset) bringing back an ID number which is checked against a CSV file which contains the similiar data.

When a record exists in the CSV file and not in the RS I use Addnew to add the new ID to the database. This forces the curent position of the RS to the last record, so effectively the RS and CSV file are out of sequence.

Does anyone know how to add records to a RS?

I currently have a solution - one RS which I compare against the CSV and add any new data to another RS, but I feel this is a waste of resources.

Ado Addnew?
I want to add some new information durring runtime
with a ADO control. It is opening a Access Database.
How to I add the new field?

RS.AddNew
I left my stupid MSDN CD's home..

RS.AddNew..How do you use it for more than 1 field at a time...? can you?

this statement: DB.Execute "INSERT INTO ReqDesc (ReqID, ReqDescription, NumberOfTests) VALUES ('" & Text1.Text & "','" & Text2.Text & "','" & Text3.Text & "');"

is for 3 fields...can you do this with Addnew?

Thanks

Addnew Help
I am trying to develp a small program that has access db in the background.

I type items in my form, hit "Save" and I get an error msg. I then hit the debug button, it takes me to rs.update

here is my code
Option Explicit
Dim db As Database
Dim rs As Recordset
Private Sub Form_Load()
Dim x
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "homefirst.mdb")
Set rs = db.OpenRecordset("companyinfo")
End Sub
Private Sub cmdClear_Click()
'Clears the text in all the fields.

txtSalesTaxRate.Text = ""
cboSalesTaxState.Text = ""
cboPaymentTerms.Text = ""
txtInvoiceDes.Text = ""
txtPhone.Text = ""
textFAX.Text = ""
txtCompanyName.Text = ""
txtStreetZip.Text = ""
cboStreetState.Text = ""
txtStreetCity.Text = ""
txtZip.Text = ""
cboMailingState.Text = ""
txtMailingCity.Text = ""
txtMailingAddress.Text = ""
txtstreetAddress.Text = ""
txtEstimateDes.Text = ""

'Moves curser to top of form.
txtCompanyName.SetFocus


End Sub

Private Sub cmdExit_Click()
'Ends the program without saving.

End

End Sub


Private Sub cmdSave_Click()
'Set database
Set rs = db.OpenRecordset("companyinfo")

If txtSalesTaxRate.Text = "" Or _
cboSalesTaxState.Text = "" Or _
cboPaymentTerms.Text = "" Or _
txtInvoiceDes.Text = "" Or _
txtPhone.Text = "" Or _
txtFax.Text = "" Or _
txtCompanyName.Text = "" Or _
txtStreetZip.Text = "" Or _
cboStreetState.Text = "" Or _
txtStreetCity.Text = "" Or _
txtZip.Text = "" Or _
cboMailingState.Text = "" Or _
txtMailingCity.Text = "" Or _
txtMailingAddress.Text = "" Or _
txtstreetAddress.Text = "" Or _
txtEstimateDes.Text = "" Then
MsgBox "Please fill all the boxes."

Else
rs.AddNew
rs!SalesTaxRate = txtSalesTaxRate.Text
rs!SalesTaxState = cboSalesTaxState.Text
rs!DefaultPaymentTerms = cboPaymentTerms.Text
rs!DefaultInvoiceDescription = txtInvoiceDes.Text
rs!PhoneNumber = txtPhone.Text
rs!FaxNumber = txtFax.Text
rs!CompanyName = txtCompanyName.Text
rs!StreetZip = txtStreetZip.Text
rs!StreetState = cboStreetState.Text
rs!StreetCity = txtStreetCity.Text
rs!MailingZip = txtZip.Text
rs!MailingState = cboMailingState.Text
rs!MailingCity = txtMailingCity.Text
rs!MailingAddress = txtMailingAddress.Text
rs!StreetAddress = txtstreetAddress.Text
rs!DefaultEstimateDescription = txtEstimateDes.Text
rs.Update
End If
Unload Me
rs.Close
End Sub


I can be reached by AIM - nustartec
msn - URL
Y! - jmyers_nc

please help


jeff

Ado Addnew
Still being new to ADO I can't get the syntax for adding a new record. What I have in a save button now is the following:

rs.AddNew
rs.Fields("Last Name").Value = txtLastName.Text
rs.Fields("First Name").Value = txtFirstName.Text
rs.Update

At this time for testing I am only using 2 of the 12 fields in the db, could this be a problem?

My connection syntax on form open is as follows:

Set cn = New ADODB.Connection
    cn.Open "DSN=abc"
    
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = cn
    rs.CursorType = adUseClient
    rs.Source = "Select * From Mailist Order By [Last Name]"
    rs.Open

What am I doing wrong in getting the addnew to work?

Using ADO's AddNew
I'm using Vb and ADO to insert some data into MSSQL 2000 table.
I found some examples on how to do this, and everthing works fine, except....
Sometimes I get errors on de the update command which looks like this:

2004/07/23 10:16:08 Error in sub : WriteToSql lijnnr : 572 Error Nummer : -2147217864 Omschrijving : Row cannot be located for updating.
Some values may have been changed since it was last read.

After some research, I found an example that just opens the table, does the addnew and the update. I've tried that also, but since the table is getting big (554859 records)
it takes a long time to execute the code, it is very slow...

So is there another way to do this, or do I just catch the error and continue the code....

/********************* Start of Code example **************************/
Code:
'Ado Variables
Public gstrDataConnection As String
Public ActualConnect As ADODB.Connection
Public ActualRecordset As ADODB.Recordset


'----------------------------------------------------------------------
' Name: WriteToSql
' Copyright: (c) Masterfoods
' Author: Dyck Walter
' Created: dinsdag, december 10,2002 @ 2:57:53 pm (Vers: 3.0.0001)
'
' Description: All the info gatered about the pallet, is saved in
' the SQL database.
'----------------------------------------------------------------------
' ALT: 00002 Vers: 3.0.0017 By: WD @ 2003/10/24 08:55:44
' Added the Seqnr, a WTP request
'----------------------------------------------------------------------
Private Sub WriteToSql()

' Create db connection
Set ActualConnect = New ADODB.Connection
ActualConnect.ConnectionString = gstrDataConnection
ActualConnect.CursorLocation = adUseClient
ActualConnect.Open

If ActualConnect.State = adStateOpen Then
      Set ActualRecordset = New ADODB.Recordset
      ActualRecordset.Open "SELECT TOP 1 * FROM Info_Sscc", ActualConnect, adOpenKeyset, adLockOptimistic, adCmdText
      If ActualRecordset.Supports(adAddNew) Then
         With ActualRecordset
            .AddNew
            !SSCC = Trim(gstrSsccTotal)
            !Item_Code = Trim(lblItemCode)
            If gstrVerified = "0" Then
               !shipmentnr = 99
            Else
               !shipmentnr = 0
            End If
            !CaseCount = Val(lblQty)
            !Tot_Weight_Pallet = CDbl(lblPalletWeight)
            !Best_Before_Date = lblBestBeforeDate
            !Production_Date = lblProductionDateFirst
            !Line_ID = Trim(lblLineNbr)
            !Log_Date = Format(Now, "YYYY-MM-DD HH:nn")
            !Read_Status = Trim(gstrStatusLabel)
            !Verified = Trim(gstrVerified)
            !LandCode = Trim(lblLandCode)
            !BestemmingsCode = Trim(lblBestemming)
            If gstrVerified = "0" Then
               strSsccStatus = "9"
            End If
            !Sscc_Status = Val(strSsccStatus)
            !Prod_Date_Last = lblProductionDateLast
            !Locked_Nr = Val(strLockNr)
            !Shipped = 0
            !Seqnr = lblSeqNr
            .Update
         End With
         ActualRecordset.Close
      Else
         LogToFile "ErrSql.log", "Geen add new mogelijk in Info_SSCC"
         ActualRecordset.Close
      End If

      ActualRecordset.Close
      Set ActualRecordset = Nothing
      ActualConnect.Close
      Set ActualConnect = Nothing
      
End If

   Exit Sub
 sError:
   If Err <> 0 Then
      LogErrToFile "WriteToSql", Err.Number, Err.Description, Erl
   End If

   If Not ActualRecordset Is Nothing Then
      If ActualRecordset.State = adStateOpen Then ActualRecordset.Close
   End If
   Set ActualRecordset = Nothing

   If Not ActualConnect Is Nothing Then
      If ActualConnect.State = adStateOpen Then ActualConnect.Close
   End If
   Set ActualConnect = Nothing

End Sub

/********************* End of Code example **************************/




Edited by - breakfast3001 on 7/29/2004 2:47:06 AM

.AddNew Using ADO Need Help Please!
Hello,

I am trying to log test data in an Access table using ADO, but I keep getting the follwing Error:



here is my code:
Code:
Private Sub Command1_Click()
    On Error GoTo DbError:
    Dim myLocalDbConnectionOK As Boolean
    Dim myLocalDbConn As ADODB.Connection
    Dim myLocalConnStr As String
    Dim myLocalDbPath As String
    Dim myLocalTestDetail As ADODB.Recordset
    Dim myLocalDbConnectOK As Boolean
    
    myLocalDbPath = "C:LocalPIV.mdb"

    Set myLocalDbConn = New ADODB.Connection
    myLocalConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myLocalDbPath & ";Persist Security Info=False"
    myLocalDbConn.Open myLocalConnStr
    Set myLocalTestDetail = New ADODB.Recordset
    
    ''test by adding record
    myLocalTestDetail.Open "Test_Detail", myLocalDbConn, , , adCmdTable
    With myLocalTestDetail
        .AddNew '<<HAS ERROR HERE!!!
        !userid = "Randy"
        !TimeStamp = Now()
        .Update
        .Close
    End With
    
    myLocalDbConnectionOK = True
    Exit Sub
DbError:
    myLocalDbConnectionOK = False
End Sub
 

I think it may be my connection string, but I don't know how to fix it.

Any help is greatly appreciated!

thanks,

-Randy

Addnew
In my application I use recordsets to update my tables. When I add a new record to a table, the record has an unique keynumber. So far so good, but a slight detail is that the record isn't added at the end of the tabble but somewhere in the middle.
Can somebody tell me why?

Access2000 Addnew
This is done by a form in access, I thought a new record would only be added when the save button is clicked.
My problem is that if the form is opened and closed, then a new record is added to my tableMain with only the date which is entered into the form as a default value.
How would I stop a save being made unless the cmdSave_Click has been pressed.


Code:
Private Sub cmdSave_Click()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblMain")
With rst
.AddNew
!pcn = txtPcn
!addedby = txtAddedBy
.Update
End With

rst.Close
Set rst = Nothing
End Sub

Another problem I noticed is that while tabbing through the form and filling out the fields, when the end of the form is reached and the tab returns to the first field then the form is entered, all fields are cleared and the next recordset is started for a save...meaning when the end of the form is reached the save is automatic....hmmmm....what happened to my onclick event?

.AddNew... Too Much Records?
hi everyone... i need to add lots of new records into my database.... but there is too much record.... 'cause an error keep popping out saying "Record is too large"....


Code:
DatCombine.Recordset.AddNew
DatCombine.Recordset.Fields("COC_No") = "" & ArrayCOC_No(c)
DatCombine.Recordset.Fields("Date_Of_COC") = "" & ArrayCOC_Date(c)
DatCombine.Recordset.Fields("Date_Of_Expiry") = "" & ArrayCOC_Expiry(c)
DatCombine.Recordset.Fields("SCO_No") = "" & ArraySCO_No(c)
DatCombine.Recordset.Fields("Process") = "" & ArrayProcess(c)
DatCombine.Recordset.Fields("Officer_In_Charge") = "" & ArrayProcess(c)
....
....
....
DatCombine.Recordset.Update
Is there any way to do it?
Thanks a lot....

Copyright © 2005-08 www.BigResource.com, All rights reserved