Access vba ADO - update a table

[pangeh@hotmail.co.uk]

Hi,

I'm new to this forum and require some help with an access db I cant get to work.

I have two different tables student records and siblings. I have an add new record fom that goes to a new blank record and when you press save it searches the latest record number then assigns a new one by incrementing the last number.

This forms source is the students record table.

I have some unbound controls on the form and I want the values of these controls to be added as a new record to the fields of the tblsiblings table.

To do this I have used ado in vba. However when ever i run the program I get an "object variable or with block variable not set". When going to debug it highlights the source part of my ado code. However the sql I am using to get the new table looks ok, I tried it in a query and it was fine.

The code I am using is as follows:

Dim rst_sibs As ADODB.Recordset
        Dim sql_sibs As String
        Dim sibs As String
        Dim sibnum As Integer
        Dim sib1 As String
        Dim sib2 As String
        Dim sib3 As String
        Dim sib4 As String
        Dim sib5 As String
        
        
        sibs = [Siblings].Value
        sibnum = [Number of siblings].Value
        sib1 = [Sibling 1].Value
        sib2 = [Sibling 2].Value
        sib3 = [Sibling 3].Value
        sib4 = [Sibling 4].Value
        sib5 = [Sibling 5].Value
        
        sql_sibs = "SELECT tblSiblings.[Student ID], tblSiblings.Siblings, tblSiblings.[Sibling 1], tblSiblings.[Sibling 2]," & _
        "tblSiblings.[Sibling 3], tblSiblings.[Sibling 4], tblSiblings.[Sibling 5], tblSiblings.[Number of siblings]FROM tblSiblings"
        
        Set tst_sibs = New ADODB.Recordset
        
        With rst_sibs
            .Source = sql_sibs
            .ActiveConnection = CurrentProject.Connection
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
        End With
                    
        rst_sibs.AddNew
                            
        rst_sibs.Fields("Siblings") = sibs
        rst_sibs.Fields("Number of siblings") = sibnum
        rst_sibs.Fields("Sibling 1") = sib1
        rst_sibs.Fields("Sibling 2") = sib2
        rst_sibs.Fields("Sibling 3") = sib3
        rst_sibs.Fields("Sibling 4") = sib4
        rst_sibs.Fields("Sibling 5") = sib5
        rst_sibs.Fields("Student ID") = new_student_id
        
        rst_sibs.Update
        
        rst_sibs.Close
        
        DoCmd.save
        DoCmd.Close

Any assistance would be greatly appreciated.

Kind Regards
Pangeh

 

[Banana]

I'm not seeing why you have to use ADO, which is just more work for no gain nor do I see why we need unbound fields here.

If you just want to display siblings for each student, use a subform control and you can achieve this with zero lines of code and no bugs & maintenance issues.

If subform doesn't work, please explain why and we can show you a good solution.

 

[pangeh@hotmail.co.uk]

Sorry just saw what I had done wrong, lol a typo was stopping me.

Thanks in advance for your assistance.

 

[pangeh@hotmail.co.uk]

Hi,

thanks for your reply.

I tried the subform option. When the add new record form is opened their is a macro that moves to a new blank record. I had this same one on the subform but for some reason I kept getting an error saying that it could not ge to the last record.

I have no idea why that happened.

 

[Banana]

Are you saying you had two macros total, one in the main form's event and other in subform's event, both trying to move to the last record?

Also, just to be sure, are 'macros' VBA code or actual macros?

 

[pangeh@hotmail.co.uk]

Hi Banana,

Thanks for your help with this.

I created a standard macro (i.ee not vba code) in the access db.

When the add new record form loads I have a reference to this macro to run in the onload event for each of the forms.

Basically I need the student ID on the form to save to a new record on both the tblstudent records and tblsiblings - there is a student id field. (there are other fields that also need to save to the siblings table as well)

I've worked around this using the ado options but it would be great to understand how to update the different tables using the subform option. Would be far more time saving and efficient.

 

[Banana]

I'm unfamiliar with 2003 macros, so I'm not sure exactly why, but I'd just do this in VBA:

In the main form's OnCurrent event:

Static bLoaded As Boolean

If Not bLoaded Then
   Me.Recordset.AddNew
   Me.<NameOfSubformContainer>.Form.Recordset.AddNew
   bLoaded = True
End If

(untested)

 

[pangeh@hotmail.co.uk]

Hi Banana,

THanks for your help with this. I will definitely give this approach a go.

THanks again for your time and efforts in explaining things.