Adding and editing records fropm unbound fields (1 Viewer)

[Paul Clark]

I total novice at VBA
I am trying to code a button to modify (the last) record in a subform list and then add a new record based on values in unrelated or unbound fields on the button form.

The following code is based on the first of two YouTube tutorials (this bit on the edit) and looks like it should work. Except that my Access 2010 with Visual Basic for Aplication v7 does not recognise the type definition Database or Recordset

Private Sub ANOwner_Click()
Dim cn As Integer
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(OwnershipLog)

For cn = 0 To rs.Recordcount = 1
    If rs.Fields("Chassis_No") = Me.CChassisNo Then
    
        rs.Edit
        rs.Fields("Current Owner") = ""
        rs.Update
        
    End If
        rs.MoveNext
    Next i
    Debug.Print db
End Sub

Any help with this will greatly appeciated

 

[mkaeser]

try ado.database and ado.recordset
or
dao.database and dao.recordset
I believe dao is the old school version but it is still supported
you will also have to set your references to allow for ActiveX Data Objects (ADO) or Data Access Objects (DAO).

 

[Paul Clark]

try ado.database and ado.recordset
or
dao.database and dao.recordset
I believe dao is the old school version but it is still supported
you will also have to set your references to allow for ActiveX Data Objects (ADO) or Data Access Objects (DAO).

Thanks that moved me in the right direction, finding a whole world of definitions under Tools/References. Found ADO and DAO but also "Microsoft Office 14.0 Access datasbase engine objects library" which once ticked gave what I originally wanted. Stange it was not already loaded presumably an install option.
Anyway making progress. Now this code prints back some values in the range from the table but nothing changes.

Dim cn As Integer
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("OwnershipLog")

For cn = 0 To rs.Recordcount
    If rs.Fields("Chassis_No") = Me.CChassisNo Then
    Debug.Print rs.Fields("Chassis_No")
        rs.Edit
        rs.Fields("Current Owner") = " "
        rs.Update
        
    End If
        rs.MoveNext
    Next cn
rs.Close
Set rs = Nothing
db.Close    
End Sub

What is required here is to clear the contents of the "Current Owner" field.

Similarly with AddNew all the tables fields print back but nothing is recorded in the table.

 

[PeterF]

You are pulling a complete table and skip the records you don't need, on a large table this takes more time then filtering it in front.
Standard SQL would be even quicker, like:

strSQL = "UPDATE OwnershipLog SET OwnershipLog.[Current Owner] = Null " & _
          "WHERE OwnershipLog.[Chassis_No])= [COLOR="Red"]'[/COLOR]" & me.CChassis_No "[COLOR="Red"]'[/COLOR];"
Currentdb.execute(strSQL, dbfailonerror)

Don't clear the current owner with a space (" ") but use null or a zero length string (I hate them).
Don't use spaces in fieldnames/tables ("Current Owner") as it will give trouble at some point, if you can't change that use square brackets around the field name (may be the issue here).
If Chassis_No is a numeric field the red single quotes are not needed around the me.CChassis_No

 

[Paul Clark]

Getting no where with any of this!
This code with ) removed and ampersand added gets to the last line before going red on compile.

[FONT=&quot]Dim strSQL As String

   strSQL = "UPDATE OwnershipLog SET OwnershipLog.[Current Owner] = Null " & _
   "WHERE OwnershipLog.[Chassis_No]= '" & Me.CChassis_No & "';"
     
   [COLOR=red]Currentdb.execute(strSQL, dbfailonerror)[/COLOR]
   [/FONT]

[FONT=&quot]
Comp[FONT=&quot]il[FONT=&quot]e E[FONT=&quot]rror Expected: =[/FONT][/FONT][/FONT]

While trying the other requirement, to add a record

[/FONT]

[FONT=&quot]Dim strSQL As String[/FONT][FONT=&quot]
  
  [/FONT][FONT=&quot]    strSQL = "INSERT INTO OwnershipLog ([Chassis_No], [Membership_No], [Owner_No], [Current Owner], [Start Date], [Note])"[/FONT][FONT=&quot]
  [/FONT][FONT=&quot]    VALUES (" & Me.CChassisNo & "," & Me.CMemNo & "," & Me.CNextOwner & "," & Me.CCurrent &"," & Me.CStartDate & "," & Me.CNote &")[COLOR=red];[/COLOR]"

[/FONT]

Compile Error: Expected end of statement

 

[mkaeser]

strSQL = "UPDATE OwnershipLog SET OwnershipLog.[Current Owner] = Null " _
& "WHERE OwnershipLog.[Chassis_No]= '" & Me.CChassis_No & "';"

Same thing with the 2nd SQL, doesn't look like you are connecting the stings correctly...

 

[Paul Clark]

Yipee! Finally got this sorted.
The update is worked fine but not all the addnew.
By adding one field/value at a time everthing is ok until the date field.
Put it back as numeric at the end and all is still working.