Using VB6 and ADODC - How do I specify a record? 1

[THOMASNG]

I'm using VB6 and ADODC to manipulate an ACCESS 2000
database. I can already scroll up (or scroll down) through
the database, and have the selected portions of the current record displayed in textboxes.
I wish to use the correct syntax to specify (and delete)
a particular record. Much of the required information is
stored on MS Visual Studio, v. 6, CD #2, which we don't have.

 

[DrJavaJoe]

Try this

Dim cmdDelete As New ADODB.Command
cmdDelete.ActiveConnection = strConn
cmdDelete.CommandText = "DELETE * " & _
"From TableName " & _
"Where TableName.FieldName = '" & Something & "'"
cmdDelete.Execute

 

[THOMASNG]

I've modified the code to:

Private Sub cmd_DeleteRecord_Click

Dim cmdDelete As New ADODB.Command
Dim str_Conn As String

str_Conn = "Provider=Microsoft.Jet.OLEDB.4.0" &
DataSource=C:\Access VBA Practice _Files\CCRTable1.mde;Persist Security Info=False"

cmdDelete.ActiceConnection = str_Conn
' At this spot, the VB6 compiler halts and tells me that
' that C:\Access VBA Practice _Files\CCRTable1.mde is
' not a valid path (though I've already used it for my
' ScrollUp and ScrollDown buttons.

 

[THOMASNG]

I've corrected my syntax to uniformly refer to CRTable1.mdb, not ".mde" at times.
Also, I don't always want to delete a record, sometimes
I just want to change just one of its fields.

Tom Ng

 

[DrJavaJoe]

Dim cmdUpdate As New ADODB.Command
cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = "update TableName " & _
"Set TableName.FieldName = '" & Something & "'" & _
"Where TableName.FieldName = '" & Something & "'"
cmdUpdate.Execute

 

[THOMASNG]

Look, let's keep this simple!

Using "cmd_ScrollUp_MouseDown" and cmd_Scrolldown_MouseDown", I have already selected the particular record that I wish to delete or change a field in. I even have stored the value of this particular record
in a global variable.
Now I wish to go directly to this pre-selected record
(not search for it again), then delete it or change one
or more fields in it.
Is there a simple way of going directly to this preselected record?

 

[DrJavaJoe]

Sorry I just realized you are using the ADODC Control, duh.

Any way you once you have scrolled to the appropriate record you can call the delete method like this Adodc1.Recordset.Delete and the Update method like this Adodc.Recordset.Update "FieldName", "NewName".

 

[THOMASNG]

Give the man a star!

 

[THOMASNG]

OK. The Adodc1.Recordset.Delete works 100%.
The Cmd_ModifyRecord|Click procedure works only when I
pass it a piece of text directly, ie.

Adodc1.RecordSet.Update "OwnerCode", "OOOO"

"OwnerCode" is just the name of the field that I wish to
alter. "OOOO" is the new value that I wish to install in the
record.
However, the new values are already displayed on the
PC's screen. For instance, the txt_OwnerCode has the new
value of OwnerCode. But when I use "txt_OwnerCode" or
"txt_OwnerCode.txt" the VB6 compiler hits me with an
error message.

 

[DrJavaJoe]

Adodc1.RecordSet.Update "OwnerCode", txt_OwnerCode.text
should work, make sure .text not .txt and that you do not have quotation marks around it. You should also validate the data before calling update you want to pass the right data type and make sure not to pass a 10 byte string to a field that expects 5 bytes.

 

[THOMASNG]

Modified per your request, but now I'm getting an error
message from VB6 compiler "Run-time error '424' : Object required."
Temporarily changed it back to "OOOO", and didn't get
the error message.

 

[DrJavaJoe]

My first guess is that txt_OwnerCode.text is spelled wrong or is out of scope because you are calling this from a module. If you are calling from a module change to FormName.txt_OwnerCode.text

 

[THOMASNG]

Nope, I'm calling directly from a form.
Checked my spelling, but got the same error message as before.

 

[DrJavaJoe]

Could you cut and paste the line that's giving the error. Also after typing txt_OwnerCode. does a list of the properties and methods for txt_OwnerCode drop down?
You might also try typing msgbox txt_OwnerCode.text and see if this gives the same error.

 

[THOMASNG]

Yup! After placing a MsgBox, I discovered that I had misnamed "Txt_Owner.Text" to its fullname,
"Txt_OwnerCode.Text". After correcting this error, it now works great! Have a second star!

 

[DrJavaJoe]

Glad to here it works.