Update records through a recordset

[Momma]

Hi there
I need help, please!
With the code below I add a set of records into a table tblDNAProfile.
The added record have a null value in the DogID field.
Next I want to update the DogID field in those records with the current DogID value.
I don't get any errors and it does not update those records.
What am I doing wrong?

    Dim DogID As Long
    Dim rs As Recordset
    Dim Reccount as long
    DogID = Me.cboName.Column(0)
    Reccount = DCount("*", "tblDNAProfile", "dogid=" & DogID & "")

    If Reccount = 0 Then
        CurrentDb.Execute ("INSERT INTO tblDNAProfile SELECT dnatestid, dnacategoryid, dnatypeid FROM tblDNATests"), dbFailOnError
    End If
    Set rs = CurrentDb.OpenRecordset("tbldnaprofile")
    
    rs.MoveFirst
    Do Until rs.EOF
        If rs!DogID = "" Then
            rs.Edit
            rs!DogID = DogID
            rs.Update
        End If
        rs.MoveNext
    Loop
    Set rs = Nothing

 

[XPS35]

Nothing is updated bacause of the If rs!DogID = ""
Change that to If IsNull(rs!DogID)

 

[sonic8]

With the code below I add a set of records into a table tblDNAProfile.
The added record have a null value in the DogID field.
Next I want to update the DogID field in those records with the current DogID value.

Why did you split this into two steps? You could also include the DogId in the Insert statement.

If Reccount = 0 Then
        CurrentDb.Execute ("INSERT INTO tblDNAProfile (dnatestid, dnacategoryid, dnatypeid, DogId ) " & _
                            "SELECT dnatestid, dnacategoryid, dnatypeid, " & DogId & " FROM tblDNATests"), dbFailOnError
End If

The tblDNATests table will always only include records for the currently selected dog? If not, you must apply a filter to the Select part of the above statement to restrict the records to those applying to the current dog.

 

[CJ_London]

I don't get any errors and it does not update those records.

You can step through the code to see if it is actually doing what it should be doing

 

[The_Doc_Man]

While I agree with XPS35 that the error is probably that you are running afoul of NULL behavior, the more complete test would be ...

IF NZ( rs.DogID, "" ) = "" THEN ...

I ALSO agree with Sonic8 that if you are going to be updating records anyway, update them all at once by including the correct dog ID in the UPDATE query in your .Execute sequence.

 

[Gasman]

You can step through the code to see if it is actually doing what it should be doing

Now there is a thought?
Always amazed as to why people do not think of that.

 

[DHookom]

Hi there
I need help, please!
With the code below I add a set of records into a table tblDNAProfile.
The added record have a null value in the DogID field.
Next I want to update the DogID field in those records with the current DogID value.
I don't get any errors and it does not update those records.
What am I doing wrong?

    Dim DogID As Long
    Dim rs As Recordset
    Dim Reccount as long
    DogID = Me.cboName.Column(0)
    Reccount = DCount("*", "tblDNAProfile", "dogid=" & DogID & "")

    If Reccount = 0 Then
        CurrentDb.Execute ("INSERT INTO tblDNAProfile SELECT dnatestid, dnacategoryid, dnatypeid FROM tblDNATests"), dbFailOnError
    End If
    Set rs = CurrentDb.OpenRecordset("tbldnaprofile")
   
    rs.MoveFirst
    Do Until rs.EOF
        If rs!DogID = "" Then
            rs.Edit
            rs!DogID = DogID
            rs.Update
        End If
        rs.MoveNext
    Loop
    Set rs = Nothing

I always specify the field names in the target table in an insert statement.

    If Reccount = 0 Then
        CurrentDb.Execute ("INSERT INTO tblDNAProfile (FldA, FldB, FldC) " & _
          "SELECT dnatestid, dnacategoryid, dnatypeid FROM tblDNATests"), dbFailOnError
    End If

 

[Momma]

Thank you for all the suggestions, guys!
I prefer to use the code below but it will not work. The records from tblDNATests is a standard set of tests used for all dogs and does not have a dogid field. After these records have been inserted into tblDNAProfile the DogID from the current form, where the dog has already been selected through a combo box, has to be assigned to those records in tblDNAProfile.
How do I change the code to add the dogid as declared.

    Dim DogID As Long
    Dim rs As Recordset
    Dim Reccount as long
    DogID = Me.cboName.Column(0)

If Reccount = 0 Then
        CurrentDb.Execute ("INSERT INTO tblDNAProfile (dnatestid, dnacategoryid, dnatypeid, DogId ) " & _
                            "SELECT dnatestid, dnacategoryid, dnatypeid, " & DogId & " FROM tblDNATests"), dbFailOnError
End If

 

[tvanstiphout]

> it will not work
That is too vague. Is there a runtime error? Then cite it verbatim.
Are no records inserted? That would be correct if tblDnaTests has no records. How many does it have?
Get the sql statement using:
dim sql as string
sql = "INSERT INTO tblDNAProfile (dnatestid, dnacategoryid, dnatypeid, DogId ) SELECT dnatestid, dnacategoryid, dnatypeid, " & DogId & " FROM tblDNATests"
Set a breakpoint, and in the immediate window get the value of sql:
?sql
Copy that text, and paste it into a new query in sql view. Run it. Any parser errors? How many records does it say will be inserted?

 

[NauticalGent]

Now there is a thought?
Always amazed as to why people do not think of that.

That knowledge isnt as common as you think...