Visual Basic :: Adding Records To A Table And Editing Records

Editing And Adding Records To Db
Hi.
I'm new in db programing and something (editing and adding records) is not clear for me.
I understand this:
I have a db (in MS access). If I want to use the data i use ADO objects for creating recordsets. This recordset I assign to data control (datagrid, flexgrid, datacombo,.....) that shows the data. control.DataSource = recordset.
And question: if I want to edit data or add data to the database do I have to do it manually? Or Is there a way, when I modify the value in the control, it changes automatically value in the recordset and recorset automatically writes the new value into db?

I'm doing it now in this way: I use the data controls just for showing data. If the user changes the data e.g. in the flexgrid by textbox, I use this new value for SQL command used by ADO command object. Or I modify data (rst.fields("xx").value=yyy) in the recordset object and update. Then I requery recordset and assign it to the data control again. Is this way correct?

michal

Edited by - plsanek on 8/30/2006 7:35:15 AM

Record Locking When Adding & Editing Records
Hello!

I have a question regarding record locking. My application often either add new record or edit an existing record(s) of a table. What do you suggest is the best way to prevent conflicts resulting from other users adding records at the same time or editing the same record(s)? I'm not so familiar with the parameters of the db.OpenRecordset statement. I have these pieces of sample code:

Dim ws as Workspace
Dim db as Database
Dim rs as Recordset

Set ws=DBEngine.Workspaces(0)
Set db=OpenDatabase(App.Path & "dbfMyDB.mdb")
ws.BeginTrans

' For editing:
Set rs=db.OpenRecordset("Select * From MyTable1 " & _
"Where Fieldn=valuen")
rs.Edit
rs.Fields("Field1")=value1
....
....
rs.Update
rs.Close

' For adding new record:
Set rs=db.OpenRecordset("MyTable2")
rs.AddNew
rs.Fields("Field1")=value1
....
....
rs.Update
rs.Close

ws.CommitTrans
db.Close
Set ws=Nothing

Furthermore, does ws.BeginTrans lock the entire tables (MyTable1 & MyTable2) or just the record(s) that are being edited or added until the ws.CommitTrans is encountered? Does the use of these 2 statements increase database security? Thanks in advance!!

Adding, Editing, Deleting Records In Sql Database(URGENT)
Good day!

I have a project in adding, deleteing, editing, searching records in sql database. I have two tables on my database, customer table and accounts table. The customer table consists of fields like ID, Firstname, middlename,lastname, address and account type. While the accounts table contain id(linked to the customers table), accounttype and accountnumber. Can u guys give me some pointers on how to start it? Or some basic code perhaps? I'm a newbie in visual basic.

I'm a little confused in vb

Thanks in advance!

What's The Best Form Design For Searching,editing,adding And Saving Records
Hi Guys,

I want to know the best way to design a form to do all of the above.

The programmer before me did it this way,

You select for example "Doctors" on a menu,

Then a form comes up with textboxes for Doctor Name, doctor address etc and there are buttons for saving,editing,modifying and add.

There is also a button for searching which when clicked brings up another form in which u enter a name to search and if u click use that information is then displayed on the first form where you can edit it.

But if you want to edit you must click the modify button, make your changes and then save.

The users complained that it is too many steps to do.

How should I design a new form and what controls should I use for the search(listview,lisbox...???)

ADO Code For Adding, Editing & Deleting Records In Access 2000
I would like to get some basic ADO "Code" for Adding, Editing & Deleting records.

I have an Access 2000 database with multiple fields, "FirstName", "LastName", "City", "Country", etc. and have the ability to search the records with option buttons to determine the field to select for the search, but I don't know how I can add, or delete these records using code. If you need to see the existing code that I have for further information to answer the question, just let me know and I'll put it up.

On the other hand, is it better to use the ADODC control.

I tried to use the form wizard to make the form and then add a search feature to it, but I just can't get it to work together.

Independently, the search works just fine, but not on the same form with the Add, Edit, Delete features.

Any help would be greatly appreciated & thanx in advance.

You can email me directly at:
izaquarius@hotmail.com
or
ICQ: 172175762

Adding Records To DB2 Table
I am new to Visual Basic and I'm trying to figure out how to add records to a DB2 table. What is the difference between addnew and the DB2 insert? Do I use both, or will addnew and update do what I need?

Adding Records To An SQL Table Using ADO
Hi

I know this question has been asked a 1001 times - I have done a search on the forum to see if I could find the answer for myself but I've not had much luck...

I have a form which contains 5 txt boxes - I want to clear the 5 five text boxes so a user can enter new values and add this to the table which resides on the server. Hope I have made myself clear.....

my code so far is as follows.

VB Code:
Private Sub CmdAdd_Click()RsRec.MoveLast With RsRec txtPACKINGSPEC = "" txtTARE = "" txtHEIGHT = "" txtLENGTH = "" txtWIDTH = "" txtVOLUME = "" End With End SubPublic Function updatetable()Dim strseql As String strseql = "insert into packing_specification" _& "(Packingspec, Lenght, Weight, Height) values" _& "('txtPACKINGSPEC','txtTare','txtheight');" End Function Private Sub cmdupdate_Click()updatetableEnd Sub

Thanks for any help...

Holly

Adding Records To A Table
Hi All,

I have a problem of adding records into table, if the cursor is on the first record the record that I add replaces the first record and then I moved it to the last record and the new record replaces that last record in the table. Is there a method that I can use to make sure this doesn't happen. The thing is my program was working fine until recently, as I have about 197 records in that table. Can someone please rescue me.
this is the code that I use to add the record:

On Error GoTo AddErr

With rsemp
If bAddNew Then .AddNew
!empnum = txtEmpNum
!EmpDeptId = cboDept.ItemData(cboDept.ListIndex)
!Title = cboTitle
!lastname = txtLastName
!firstname = txtFirstName
!offloc = cboOffLoc
!officenum = txtOffNum
!OffPhone = Me.txtPhone
!email = txtEmail
.Save
End With
cmdAdd.Enabled = True

cmdSave.Enabled = False
Exit Sub

AddErr:
MsgBox "there was an error " & Err.Number & " " & Err.Description

End Sub

Adding Records To Empty Table
Whenever I try to add a record to an empty table, I get the error "This action was cancelled by an associated object". If I already have at least one record in the table, however, I can add new records without any problems.

I have attached the form. After spending hours in debug mode I'm fed up with it, so hopefully someone out there will see what it is that I have missed....

Edit: I have fix the upload and it should run now. It is a visual basic 6 project.

Adding Records On Existing Table
I have Ms Access table with 4 fields. I build a form so that users can add or edit the table. But I need only last three field to be modified while the first field to remain on showing the data/value exists. Summary: When I click add new record button, I need the first field to populate the existing values.

code:
DoCmd.GoToRecord, , acNewRec

I will appreciate your help.

Frank

Adding Records To MS Access Table
Hi,

I've created a table object in Microsoft Access with ADO. The table is empty and I want to now fill it with the results of a query. I've tried this the following 2 ways and things aren't working.

Method #1 gives me errors suggesting that my table is not updateable or that I've got SQL errors. The second method is using straight "SELECT...INTO" from SQL. This way, I'm getting errors suggesting SQL problems. Any possible remedies to this problem??

Thanks!!

Method #1

Code:
Private Sub populateTable(tabName As String)
Dim rsUpdate As ADODB.Recordset
Set rsUpdate = New ADODB.Recordset
Dim strTable As String

strTable = tabName
Set rsData = New ADODB.Recordset

'creates recordset representing empty table (created as an ADO table definition
'strTable is a String value representing the name of the empty table object to be filled

rsUpdate.Open "SELECT * FROM " & strTable, conn

'creates a recordset with the data to be added to the empty table from above
rsData.Open "SELECT dbo_Routes.DISTCODE, dbo_Routes.GEOGTYPE, Sum(dbo_Routes.HOME) AS SumOfHOME, Sum(dbo_Routes.BUS) AS SumOfBUS, Sum(dbo_Routes.APT) AS SumOfAPT, Sum(dbo_Routes.TOTAL) AS SumOfTOTAL, dbo_Routes.FSA FROM dbo_Routes where dbo_Routes.DISTCODE = 'cpc' and dbo_Routes.GEOGTYPE = '" & strGeogType & "' GROUP BY dbo_Routes.DISTCODE, dbo_Routes.GEOGTYPE, dbo_Routes.FSA", conn, adOpenKeyset, adLockOptimistic, adCmdText

'moves to first record of recordset to be populated
rsData.MoveFirst

'loops through data recordset and adds its records to the empty table (i.e. rsUpdate recordset)
While rsData.EOF = False
rsUpdate.AddNew
rsUpdate.Fields("homesum").Value = rsData!sumOfHOME
rsUpdate.Fields("aptsum").Value = rsData!sumOfAPT
rsUpdate.Fields("bussum").Value = rsData!sumOfBUS
rsUpdate.Fields("totalsum").Value = rsData!sumOfTOTAL
rsUpdate.Fields("fsa").Value = rsData!FSA
rsUpdate.Update
rsData.MoveNext
Wend

Method # 2

Code:
Private Sub add(tabName As String)
Dim sSql As String

sSql = "Insert Into " & tabName & "(homesum, aptsum, bussum, totalsum, fsa) " & _
"Select Sum(dbo_Routes.HOME)" & _
" , Sum(dbo_Routes.BUS)" & _
" , Sum(dbo_Routes.APT)" & _
" , Sum(dbo_Routes.TOTAL)" & _
" , dbRoutes.FSA " & _
"From dbo_Routes " & _
"Where dbo_Routes.DISTCODE = 'cpc' " & _
" And dbo_Routes.GEOTYPE = '" & strGeogType & "'" & _
"Group By dbo_Routes.DISTCODE, dbo_Routes.GEOGTYPE, dbo_Routes.FSA"

conn.Execute (sql)

End Sub

Adding Records To Child Table
I really hope someone can help me with this.
I have a parent table Books that has a primary key as an autonumber. Table Barcodes is the child table. One to many relationship. One book can have multiple copies. If so, then each copy will have its own barcode number.
I am unable to save records to the database from VB using ADODB.Recordset object. I don't how to store the new ID (autonumber)into the child table barcodes.
I think it is having problem finding the new add ID number that access creates each a new record is entered into the database.
Can anyone help me please?
Thank you
Bensta

Adding Records From A Form Based On A Table
I am trying to use two list boxes to allow me to select records from the left list box, which uses a table as its source, and to add them to the right list box, which will be appended to another table. I am not sure how to do this. I do not want to use a value list in the left list box. I just want to select the items, push the arrow, and then teh add records button. I am using Access XP.

Thanks for the help.

Adding Records To A Access Table (EOF True?)
Whats wrong with this bit of code?

Code:
.Open strSQL
.MoveNext
rs.Fields("txteye1") = txteye1
rs.Fields("txteye2") = txteye2
rs.Fields("txthear1") = txthear1
rs.Fields("txthear2") = txthear2
rs.Fields("txtresp1") = txtresp1
rs.Fields("txtresp2") = txtresp2
rs.Fields("txthead1") = txthead1
rs.Fields("txthead2") = txthead2
rs.Fields("txthand1") = txthand1
rs.Fields("txthand2") = txthand2
rs.Fields("txtfoot1") = txtfoot1
rs.Fields("txtfoot2") = txtfoot2
rs.Fields("txtbody1") = txtbody1
rs.Fields("txtbody2") = txtbody2
rs.Fields("txtfall1") = txtfall1
rs.Fields("txtfall2") = txtfall2
rs.Fields("txtfr1") = txtfr1
rs.Fields("txtfr2") = txtfr2
rs.Fields("lbleyecom") = lbleyecom.Caption
rs.Fields("lblhearcom") = lblhearcom.Caption
rs.Fields("lblrespcom") = lblrescom.Caption
rs.Fields("lblheadcom") = lblhead.Caption
rs.Fields("lblhandcom") = lblhand.Caption
rs.Fields("lblfootcom") = lblfoot.Caption
rs.Fields("lblbodycom") = lblbody.Caption
rs.Fields("lblfallcom") = lblfall.Caption
rs.Fields("lblfrcom") = lblfr.Caption
rs.Fields("lbltotalchk") = lbltotalachk.Caption
rs.Fields("lbltotalcom") = lbltotalcom.Caption
rs.Fields("lbltotalacom") = lbltotalacom.Caption
.AddNew

Adding Records To A Table, Using Data Environment
I have used data environment since now, only for reading data from tables and displaying them on a data report.
Does anyone know how to use dataenvironment to ADD new records to an existing table of a database?
And please I would like a sample code for that without using commands and parametrs if possible, just simple recordsets!
Thanx again!

Michael Vlastos
Automation Engineer
Intracom, Research & Development Department
Athens, Greece

Adding Multiple Records To A Table With Single Event
Hi Friends,

I am developing a payroll package using vb & access.

I have a payroll form through which I Generate the record of each employee manually one by one.

I have the master table for the Employee information( emp table) & Salary information (salary table) which i update once in a year when the revision of salary takes place.

The payroll is generated on a monthly basis.

Though the generation of payroll is same for all the employees, I have to go through the records of each employee every month & generate his payroll.

Is there any logic through which the payroll for all the employees is generated with the execution of one event ( Such as clicking a command button )

After generation of one event the records should get added to the payroll table one by one.

In other words , Is there any mechanism by which multiple records can be added to the access table with the generation of one event ?

Pls help.
with many thanks,
Dennis.

multiple records can be added to the access table with the generation of one event ? once in a year payroll for all the employees is generated with the execution of one event

SQL Statement - Listing The Records Where Those Records Are Not In The Bridging Table
Hi what would be the SQL command of when:

-You want to list all the records and columns of table A
- But, only if the employeeID is not in the table B
- If the matching employee ID is in the table B, that record or value corresponding to that ID won't be included in the result

table A
-------
field1 - employeeID
field 2 - firstname
field 3 - lastname

table B (bridging table or many-to-many)
------
field1 - employeeID (PK)(FK)
field x - xxx
field x - xxxx
and so on

Thank you

Problem In Insert Query When Adding Records From The Existing Table
I have a problem in the query.I have insert all the rows based on the conditions i will give the input when the runtime,it will pick up from the existing table.How to do it?

Please reply me.

The query is,

    Dim CMD As New ADODB.Command

    CMD.ActiveConnection = cn
    CMD.CommandText = "insert into stemp Values(select * from AVAIL2 where empno IN(SELECT EMPNO FROM AVAIL2 WHERE DOP=CSTR(FORMAT(MaskEdBox1,'DD/MM/YYYY')) AND MODEPMT=PAYOPT)"

    CMD.Execute

R.Sundar

Editing Records
I have a subform in datasheet view. I want users to be able to make changes to these records and when they are done be able to choose weather to keep the changes or not.

What I was thinking is to open a clone. If they want to save changes made to the clone, update the real recordset from the clone. But I don't know how to do this, or if it is even possible. Any ideas?

Thanks in advance.

Editing Records
Hi, it's me again. Anyway, I got a program with a login screen that works with a database, now once the user logs in with a user name and password, it brings another form. This form includes a button called 'Change Password'. Once the user presses this button, it takes him to another form that contains three textboxes. One for entering the old password, another one for entering the new password, and the last one for confirming the new password. I want him to be able to change the password that is stored in the database this way. Anyone can help me with this? Thanks.

Editing Ado Records
what are the commands to edit a ado record? Thanks

About Editing Records
Hi. I'm using MS Access as my backend. I'm not really use to ado control and data grids. I use pure codes to access to my database, add a record, delete a record, and search. The problem is this, when I edit a record I usually delete the record then add the new/edited record. Does editing a record has to be that way? It causes me a problem especially when the tables has relationship, because as I delete the record, other records from other tables are also deleted. Is there a way to to update a record without deleting first the record? Please help

Need Help Editing Records
Hello I am not sure how to go about getting what I need.

I have a form with labels (48 of them). Each represents a port on a router. I have them color coded, green for active and red for inactive. Each is connected to a field in my database. The fields contain data about the location that the port is attached. I am using the visible command as sort of a mouse over affect. So when I mouse over the the label it displays a lable containing the connection.

What I am trying to do is find a way to edit the locations at run time. If the location becomes inactive or changes I would like to be able to edit the labels. I don't want to use 48 different forms to edit each field. Is there a way to do something like this

Code:Private Sub Label1_Click()
With Form1
              field name is so and so
             Text1.text = field name
End Sub

Private Sub Label2_Click()
With Form1
              field name is so and so
              Text1.text = field name
End Sub

Or is that a bit too much.
Any ideals would be greatly appreciated

Thanks
Bob

Edited by - Bob Taylor on 3/19/2004 8:46:32 PM

Editing Records
Hi there,
When i update or delete records i call an SQL statement that selects only the record i want to update or delete and then I use rst.edit or rst.delte. The problem is that when the table contains lots of records this method is quite slow. I heard about the index numbers of the recordset. I need some help on how to make them work.
I'm using a MSFlexGrid to show the recordset.

Thanks,

Ale

Editing Records
Has anyone got code for editing records in a form. At the moment when I use .Edit I get: Compile Error Method or data member not found!
Thanks!

Editing Records
Any coding tips for editing fields within a particular record using a form? I have a combobox which references the rows in the table I am wishing to add data to, however, I am having problems with getting the data (which the user will enter in the checkboxes and textboxes on the form) to go into the row I want it to as opposed to going into a new row. Do I have to declare a recordset object and create a recordset? I am working in VB/Access and I am a newbee.

Liza

Editing Records In MSFlexgrid
Hi,

My problem is similar to the one kashif iftikhar had regarding adding, deleting, editing records in a MSFlexGrid. Gallicus had made a couple of suggestions but they don't seem to work for me.

I have a MSFlexGrid that displays records from an Access database. Some fields are filled, some are empty. I need to edit the rows where the fields are empty and also have the edits saved back to the database.

Extract from my code:

'setup MSFlexgrid
.
mf1.Row = 0
mf1.Col = 1
mf1.Text = "Name"
mf1.Col = 2
mf1.ColWidth(2) = 4000
mf1.Text = "Activity"
.
.
'get record from database
.
mf1.Col = 1
mf1.Text = rs(2)
mf1.Col = 2
mf1.Text = rs(3)

I need to edit the contents of col 2 and have it saved back to the database.

Thanks.

Listbox And Editing Records?
heya,

Just a quick question. I have an edit function in my program and I can edit fine but I have all my existing records display in a listbox. This listbox only updates the changes after I close the form and later reopen it. Does anyone know the code which will make the listbox update without having to close the form??

Thanks

EDITING & UPDATING RECORDS
Please do help me in my problem. its urgent.
How will i update my existing records in VB?
im using ms-access as back-end.

my problem is, i was able to edit the 1st record but when i get to the 2nd record i want to update, it wont update. it says "it wont update coz it may have duplicates"

what will i do?

THIS IS THE CODE:

Code:
Private Sub cmd_save_Click()
Dim ans As Integer
ans = MsgBox("Do You Really Want To SAVE The Record?", vbYesNoCancel + vbQuestion, "ALERT!")
If ans = 6 Then
If (cbo_month.Text = "January") And (cbo_period.Text = "1: (01-15)") And (cbo_dept.Text = "HouseKeeping") Then
Adodc1.RecordSource = "Select * From house01jan"
Adodc1.Refresh
Adodc1.Recordset.AddNew
Adodc1.Recordset.Fields("IDNumber") = Trim(txt_idnumber.Text)
Adodc1.Recordset.Fields("Lastname") = Trim(txt_lastname.Text)
Adodc1.Recordset.Fields("Firstname") = Trim(txt_firstname.Text)
'Adodc1.Recordset.Fields("Location") = Trim(cbo_location.Text)
Adodc1.Recordset.Fields("Dept") = Trim(cbo_dept.Text)
Adodc1.Recordset.Fields("Rate") = Trim(txt_rate.Text)
Adodc1.Recordset.Fields("Days") = Trim(txt_regdays.Text)
Adodc1.Recordset.Fields("Hours") = Trim(txt_reghours.Text)
Adodc1.Recordset.Fields("OvertimeHours") = Trim(txt_othours.Text)
Adodc1.Recordset.Fields("OvertimePay") = Trim(txt_otpay.Text)
Adodc1.Recordset.Fields("SSS") = Trim(txt_sss.Text)
Adodc1.Recordset.Fields("Medicare") = Trim(txt_medicare.Text)
Adodc1.Recordset.Fields("Pag-ibig") = Trim(txt_pagibig.Text)
Adodc1.Recordset.Fields("Uniform") = Trim(txt_uniform.Text)
Adodc1.Recordset.Fields("CashAdvanceDed") = Trim(txt_cashadvance.Text)
Adodc1.Recordset.Fields("PreviousCashAdvance") = Trim(txt_previous.Text)
Adodc1.Recordset.Fields("PresentCashAdvance") = Trim(txt_present.Text)
Adodc1.Recordset.Fields("TotalDeduction") = Trim(txt_totalded.Text)
Adodc1.Recordset.Fields("TotalSalary") = Trim(txt_totalsalary.Text)
Adodc1.Recordset.Fields("NetSalary") = Trim(txt_netsalary.Text)
Adodc1.Recordset.Update

Adodc1.RecordSource = "Select * From CashAdvance"
Adodc1.Refresh
Adodc1.Recordset.Update
Adodc1.Recordset.Fields("IDNumber") = Trim(txt_idnumber.Text)
Adodc1.Recordset.Fields("Lastname") = Trim(txt_lastname.Text)
Adodc1.Recordset.Fields("Firstname") = Trim(txt_firstname.Text)
'Adodc1.Recordset.Fields("Location") = Trim(txt_location.Text)
'Adodc1.Recordset.Fields("Department") = Trim(txt_department.Text)
Adodc1.Recordset.Fields("Temp") = Trim(txt_previous.Text)
Adodc1.Recordset.Fields("PreviousCashAdvance") = Trim(txt_present.Text)
Adodc1.Recordset.Fields("PresentCashAdvance") = "0.00"
Adodc1.Recordset.Fields("Temp") = "0.00"
Adodc1.Recordset.Update [b]<<<IM HAVING PROBLEMS HERE ON THE 2ND RECORD[/b]

MsgBox "Records has been saved!", vbOKOnly + vbInformation, "System Message"

txt_lastname.Text = ""
txt_firstname.Text = ""
txt_rate.Text = ""
txt_ratepday.Text = ""
txt_ratepmin.Text = ""
txt_ratepday.Text = ""
txt_reghours.Text = ""
txt_regdays.Text = ""
txt_othours.Text = "0"
txt_otpay.Text = "0.00"
txt_sss.Text = ""
txt_medicare.Text = ""
txt_pagibig.Text = ""
txt_uniform.Text = ""
txt_others.Text = "0.00"
txt_cashadvance.Text = ""
txt_present.Text = ""
txt_previous.Text = ""
txt_lateded.Text = ""
txt_latemins.Text = ""
txt_totalded.Text = ""
txt_totalsalary.Text = ""
txt_netsalary.Text = ""

With rsEmp
.MoveNext
If (.EOF) Then
MsgBox "Reached Last Record", vbOKOnly + vbInformation, "System Message"
.MoveLast
End If
Display
End With

With rsCAdv
If (.EOF) Then
.MoveLast
Else
.MoveNext
If (.EOF) Then
.MoveLast
End If
End If
DisplayCA
If Not txt_previous.Text = "0" Then
txt_cashadvance.Text = (Val(txt_previous.Text) + ((Val(txt_previous.Text) - 300) * 0.1))
Else
txt_cashadvance.Text = "0"
End If
End With
End If

ElseIf ans = 7 Then
MsgBox "Records was not saved!", vbOKOnly + vbInformation, "System Message"
txt_lastname.Text = ""
txt_firstname.Text = ""
txt_rate.Text = ""
txt_ratepday.Text = ""
txt_ratepmin.Text = ""
txt_ratepday.Text = ""
txt_reghours.Text = ""
txt_regdays.Text = ""
txt_othours.Text = "0"
txt_otpay.Text = "0.00"
txt_sss.Text = ""
txt_medicare.Text = ""
txt_pagibig.Text = ""
txt_uniform.Text = ""
txt_others.Text = "0.00"
txt_cashadvance.Text = ""
txt_present.Text = ""
txt_previous.Text = ""
txt_lateded.Text = ""
txt_latemins.Text = ""
txt_totalded.Text = ""
txt_totalsalary.Text = ""
txt_netsalary.Text = ""

End If
End Sub

Editing Database Records
I'm trying to edit records in the data base using the code below:

Code:
strSQL = "UPDATE Utbl SET "
strSQL = strSQL & " Pr = '" & cbmPr.Text & "',"
strSQL = strSQL & " RNumber = '" & cbmRN.Text & "',"
strSQL = strSQL & " Ph = '" & cbmPh.Text & "',"
strSQL = strSQL & " Ext = '" & cbmExt.Text & "',"
strSQL = strSQL & " Mo = '" & cbmMo.Text & "',"
strSQL = strSQL & " Op = '" & cbmOp.Text & "',"
strSQL = strSQL & " Remarks = '" & txtRemarks.Text & "',"
strSQL = strSQL & " Update = '" & Now & " ' "
strSQL = strSQL & " WHERE UID = " & cbmUID.Text & ""

But I receive a Syntax error in the statment.
One other question is there any other way to edit the database records instead of the method above, as I did find that this method does not accept too long text values or special characters such as @ in email address.

Editing Records In Database
How can I edit record in a database (Access)?

Update A Records After Editing.
Please any one help me.
i was to update records after i EDIT the records in text boxes.
actually making with SQL statements for UPDATE statements.
so i don't get any idea how m i going to do with this.
help me solve with this particular problem.
thnx to every body in advance.

Editing Records Through Combo Box Selection
<disclaimer>This is my first time working with access and vb, I usually use oracle or mysql type db</disclaimer>

I am trying to set up a form that will let me edit a record by selecting a record name from a combo box. I can think of two ways to implement this.
One, have a popup with a combo box listing all the members of "record name" which will open the form with the selected record for editing.
Two, a combo box with all the members of "record name" directly in the form. Then have that combo box control which record the form displays so the if i select record 1, it will bring up the entire record to be edited.
Is there an easy way to link up all the fields on a form, or do i need to write some real vb to do a popup combo box? Either way, please give me some pointers.

Thanks in advance

Editing Database Records Problem - HELP !!!!
I have a database that is used for creating records that store information on merchandise.

Once the user has put data in the database I want to allow the user to edit the information, but I want the edit date and time recorded.

What I am trying to do is allow them to alter the current information and then at the end of the record create a carriage return and insert the edit date and time. Then if they add additional information I want to create another carriage return and write the record out with the new information appended to the end of the record following the edit date and time.

preliminary example.

rsRecord= rsRecord & "carriage return (what is the VB code)" & Edit Date & Edit Time & "carriage return" & Additional Information.

Can anyone please point me in the right direction to do this. I have some ideas but the main sticking point is I can't find the VB code to create a carriage return. Also, any suggestions for efficiency would be greatly appreciated.

Jenn

Editing Records In An Access Database
Please can anyone tell me how to tell if someone is editing the record I wnat to edit - I get runtime error 3260 and want to avoid it - Help!

Displayind And Editing Large Records
Hi,

In my table I have 43 Record, 4 columns, 3 of the columns is empty.

How can I display this 43 records and let the user edit or add new record
to the 3 empty columns.

I tried using msflex grid but it does not allow editing or adding new.

help please

Regards.

Editing And Updating Excel Records
Some of you may have seen some of the threads about the listview control that I have in my project. Now that I've gotten that working the way I want, I need to be able to edit, add new and update records but I'm having difficulty getting these methods to work. This is an ADO recordset from an excel file. The biggest problem I have is that whenever I update the recordset, it totally jacks with the file and corrupts it. The data in it is all jumbled up and the program can no longer access it. I do not have much in the way of code for these methods other that just calling the update method. I've included my connection code if that's any help.

Code:

Private Sub Form_Load()
Set oConn = New ADODB.Connection
strMtf = "C:Documents and Settingshcmurnd1My DocumentsVB ProjectsHCM.mtf"
txtMtfFile.Text = strMtf
strMtyp = "SheetStockLibrary"

oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =" & strMtf & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"""
oConn.Open

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select F1, F2, F3, F5, F6, F7, F8, F9, F10, F11, F12, F14, F15, F16, F17, F18, F19, F20, F22, F23, F24, F25, F26, F27, F28, F29, F30, F32, F33, F34, F35, F36, F37, F38, F39, F41, F42, F43, F44, F45, F46, F47, F48 From[SheetStockLibrary$]", oConn, adOpenKeyset, adLockOptimistic, adCmdText
fillfields
txtDescription.Locked = True
txtThickness.Locked = True
txtQtyAvailable.Locked = True
txtPanelWidth.Locked = True
txtPanelLength.Locked = True
txtCost.Locked = True
txtLeadingWidthTrim.Locked = True
txtTrailingWidthTrim.Locked = True
txtLeadingLengthTrim.Locked = True
txtTrailingLengthTrim.Locked = True
cmdAddPanelSize.Enabled = False
cmdRemovePanelSize.Enabled = False
cmdAddMaterialToList.Enabled = False
cmdSaveChanges.Enabled = False

End Sub

Any help would be greatly appreciated.

Manipulate Records Without Editing Database
Here are my codes :

Dim MyDatabase as Database
Dim MyRecordset as Recordset
Dim MyQuery as string

set MyRecordset = MyDatabase.OpenRecordset(MyQuery, dbOpenDynaset)

My question is how can I add more recordsets to MyRecordset without editing MyDatabase.
Thank you in advance.

Finding And Editing Existing Records In A Database...
I have a form created that will create new records in a database with the data entered. However, I would like to be able to enter the primary key in the form and have the app search the database for an existing record when I move to the next field. If there is already an existing record, all fields on the form will be populated with the corresponding fields from the database. Then, any changes made on the form will update the database accordingly. Of course, if there is not an existing record, I can simply continue on.

I already have the update part coded. I just don't know how to do the find-and-populate if there is an existing record. Any thoughts? Thanks in advance!

Editing Existing Records In Multiple Tables At The Same Time
this is the code for my mini project its nothing special(a few textboxes, an ADODC and a button) but i need to add the facility to edit existing records in multiple tables at the same time at the click of a button. the application already creates the records but i cant figure out how i would be able to view and edit existing records in mulitple tables in the database( i have attached the database so you can have a better understanding of what i mean)
The database has many other tables that are not relevant to the project as you will see by the code, i only need to edit the same tables and fields that i have created records in with the following code:
--------------------------------------------------------------------------
Option Explicit
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Command1_Click()
On Error GoTo err
Dim dataPath, adname, comments, startTime, runtime, date1, TransitionSpeed, clientid, endTime, reference, TransitionStyle, Name As String
Dim slotID, channelID1 As Integer

Name = (Text1.Text)
adname = Text2.Text
reference = Text3.Text
TransitionStyle = Text4.Text
comments = Text5.Text
dataPath = Text6.Text
startTime = (Text7.Text)
endTime = (Text8.Text)
clientid = (Text9.Text)
TransitionSpeed = (Text10.Text)
date1 = (Text11.Text)
runtime = (Text12.Text)
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source=C:projstSchedule.mdb"
.Open
End With

'insert the record into the channel table
rs.Open "Channel", cn, adOpenKeyset, adLockPessimistic, adCmdTable
rs.AddNew
rs("MediaID") = 4
rs("Name") = Name
rs("ClientID") = clientid
rs.Update

channelID1 = rs("ChannelID")

rs.Close

'insert the record into the slots table
rs.Open "Slots", cn, adOpenKeyset, adLockPessimistic, adCmdTable
rs.AddNew
rs("channelId") = channelID1
rs("StartTime") = startTime
rs("EndTime") = endTime
rs("ClientID") = clientid
rs("Date") = date1
rs.Update

'get the new slotID
slotID = rs("slotID")

'close slots table
rs.Close

'open the 2nd table & insert stuff into it
rs.Open "ScheduleFooter", cn, adOpenKeyset, adLockPessimistic, adCmdTable
rs.AddNew
rs("slotID") = slotID
rs("adname") = adname
rs("reference") = reference
rs("TransitionStyle") = TransitionStyle
rs("TransitionSpeed") = TransitionSpeed
rs("comments") = comments
rs("dataPath") = dataPath
rs("MediaID") = 4
rs("ClientID") = clientid
rs("Runtime") = runtime
rs.Update
rs.Close

Set rs = Nothing
cn.Close
Set cn = Nothing

MsgBox ("all done!")
Exit Sub

err:
MsgBox ("there was a problem with this - please stop breaking the app!")
Exit Sub
End Sub

--------------------------------------------------------------------------
If anyone out there is up to the challenge id really appreciate any help/advice that could be given

Cheers

Neil

Viewing/Editing Variable Database Records With A Datacombo From Atable
Hi

I've used the Data Environment Designer to make two commands.

- One to fill a datacombo. (from a table in the database)
        This works
- Two, is a command based on a sql statement.
        "SELECT * FROM tablename WHERE fieldname = ?"
        ? = aGroupID paramater(I've set its name,properties)

All the data properties have been set for the datacombo
in its properties window (RowSource,RowMember,ListField, DataSource,DataMember,DataFormat,DataField,BoundColumn)

What I want is, "once I know what aGroupID is, I want the datacombo to display the value from a field (specified in the datacombo properties window) from the database. And upon changing the datacombo value(from the dropdown list), I want it updated in the database."

  * the values in the datacombo will always be comparable to the values in the database that I want to view/update.
  * I shouldn't need to make new connections to the database.

So, how do I
   - specify what aGroupID is?
   - set the datacombo to show the value that corresponds?
   - change the value in the database to the selected
      datacombo value?

I've been working on this for a while now, I could really use some help,

any thoughts,

Brawn

"My mind is my Shrine,
and my body the Temple around it."

Access97...append Records From Table Of Varying Fields Into Single-field Table
Hi all,

New to this forum, and pretty much a complete noob with VB, but I've done a few things with it in Access so I'm not tatally lost. Here's my situation: my users take in a rather large and ugly Word document from a client (essentially a print-out from a mainframe application) which contains a few key bits of information that need ultimately need to be used to place an online order at a vendor website. Since this online order can involve copying/pasting a single column of data instead of manually keying each bit in, these bits of information need to be extracted from the initial document and normalized into a single column of data for the user. On the source document, these key bits of information are on individual lines separated by commas. I've gotten far enough using import/export specs and queries to produce a table that has each bit of data in it's own field; hoever, the number of fields in this table will vary with each new client document, and not all fields in each row will have values. My need is to code a routine that will cycle through each field in this table, regardless of how many fields there are, and append the non-null values from each into a different single-field table (or single field data source) which can then be spit out to the user. What's the best way to go about this?

Regards and thanks,

Stuart

Update Records In Table 1 With Existing Record In Other Table
hi,

can somebody help me on how I can resolve my problem..here's my code first thing of all...

VB Code:
Dim rsdelSet rsdel = New ADODB.RecordsetDim rsupdSet rsupd = New ADODB.Recordset rsdel.Open "Select * from tblTrackOrder Where RawID Like '" & lblRawID & "'", goConn, adOpenDynamic, adLockOptimistic, adCmdText If rsdel.RecordCount <> 0 Then While Not rsdel.EOF rsdel.Delete rsdel.MoveNext Wend End If rsdel.Close rsupd.Open "Select * from tblContacts Where ContactID Like '" & SupplierID.Caption & "'", goConn, adOpenDynamic, adLockOptimistic, adCmdText If rsupd.RecordCount <> 0 Then rsupd.Fields("CRequest") = "N" rsupd.Update rsupd.Close Else rsupd.Close End If

in my table tblTrackOrder, I deleted a record that has been processed...

let say the remaining records in my tblTrackOrder are:

ID NAME
1 Robee
3 Wendy

now, I have to equate these records to my other table called tblContacts...and in my table tblContacts my records are:

ID NAME CRequest
1 Robee Y
2 Dundee Y
3 Wendy Y
4 Plagie Y

in tblTrackOrder I only have 2 records..now I want these 2 records to be the look up so I can edit the records at tblContacts...

i want the output to be like this:
ID NAME CRequest
1 Robee Y
2 Dundee N
3 Wendy Y
4 Plagie N

when a record exist in the tblTrackOrder, it's corresponding record in the tblContact should have a Y value in CRequest field...but when a record is not existing in the tblTrackOrder it's CRequest field value should be N...I hope I've shown enough on how I want my system to work..im having difficult time thinking..i hope somebody could help me out..many thanks

How To Delete Records In One Table Based On List In Second Table
Hi,

I have 2 tables

tbl_Docs with Fields:
- ID1 (Autonumber)
- DocRef (Integer)
- Others (Text)

tbl_DeleteList with fields
- ID2 (Autonumber)
- DocToDelete (Integer)

I want to write a query to delete those records in tbl_Docs where the DocRef matches the list in tbl_DeleteList (field DocToDelete)

I know how to do the simple delete for a single record
CODE
DELETE tbl_docs.* FROM tbl_docs WHERE (((tbl_docs.DocRef)=2))

Use VB To Append Records To An Linked Table From An Access Table
HELP! Sometimes this statement inserts records and sometimes it doesn't. Any ideas?

Select_Statement = "INSERT INTO OECSHFIL_SQL ( ... ) SELECT ... FROM PrepayRecs"
cnnAccess.Execute Select_Statement, TotalRecsAfter, adCmdText

Using ADO data control with Access2000 database. OECSHFIL is a linked Btrieve table and PrePayRecs is an Access 2000 table.

When I Select In Combo Related Records Display In Grid, Few Records Are Not Displaying
I think this is the right place to post this, without knowing i place this in other discussion.
I am new.

Actually my project's Purpose [in the Form1, two combo boxes listing ItemCode and Description from two tables, where ItemCode is equal in both tables, after selecting in combo, the related record displayed in the Form2-MSHflexgrid]

   Everything is working perfectly except one thing. Only three records is not displaying the details among the 1600 records
OFFICE 2000 W32 EN INT'L
MONEY 2000 W32 ENG INT'L
COMPAQ PRESARIO MV500 15" MONITO . this 3 records are in the present in the table and the ItemCode is also equal. The 3 belongs to Description field. I check through in another way, by giving the Itemcode in the first combobox, it displays correctly the details of all records. where as in the second combobox only these three is strucking up.

where as PRESARIO MV500 15" MONITOR is also a record similar to the three records but it is displaying.
I don't know this is because of any string error or something else. What's the problem, Kindly reply me.

Form_Activate()

    If Form1.optItemcode.Value = True Then
sqls= "selecttab1.Itemcode,tab1.Description,tab1.Product,tab2.warehouse,tab2.Qty,tab2.Amt,tab2.Amt * 5/100 from tab1,tab2 where tab1.Itemcode=tab2.Itemcode AND tab1.Itemcode='" & Form1.cmbItemCode.Text & "'"
    End If

    If Form1.optDesc.Value = True Then
sqls="select tab1.Itemcode,tab1.Description,tab1.Product,tab2.warehouse,tab2.Qty,
tab2.Amt,tab2.Amt * 5/100 from tab1,tab2 where tab1.Itemcode=tab2.Itemcode AND tab2.Description='" & Form1.cmbDescription.Text & "'"
    End If

    rst.Open sqls, cnn, adOpenStatic, adLockReadOnly, adCmdText
If rst.RecordCount = 0 Then
       Me.Hide
       Form1.Show
       MsgBox "No Matching RECORDS !", vbExclamation, " * Invalid Entry"
End If

because of this I am not able to finish my project. Kindly help me. My mail id is

itchocks@rediffmail.com

Thank you very much,
CHOCK.

Hai !!!
Question:Also send it to my mail ID. I did'nt get practice with forums, I don't know where to search this again.

<HTML>
<B><font color="#008000">HAVE A NICE DAY !</font></B>
<HTML>