Solved 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...

 

[MadPiet]

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

Why even use a recordset? if it's always true that the only records where the DogID value is null are for the current DogID (in the form(?)), then just use an update query

UPDATE tblDNAProfile
SET DogID = @DogID
WHERE DogID IS NULL;

then maybe you'd have to do something silly like use REPLACE to replace @DogID with a number... and then just execute it. (Yeah, I just really dislike recordsets unless you absolutely have to use them)

 

[Pat Hartman]

then maybe you'd have to do something silly like use REPLACE to replace @DogID with a number... and then just execute it. (Yeah, I just really dislike recordsets unless you absolutely have to use them)

You're a little behind. Fixing the append query solved the problem of the missing dogID which eliminated the need for the recordset loop entirely. I do agree with your opinion on the use of recordsets though. They are much slower than action queries to perform updates. However, if you are inserting a bunch of records in a loop, the recordset method if far superior to running an append query for each new record.