Add new row or append new row

[lbunch]

I need the code below to
-find the crm_nbr and revision number on a form
-then increment revision number by 1 -
-adding a whole row of fields to the table with the new revision number so that new copied record can be updated on the form and the old record kept as is.



Private Sub RevisionNumber()
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim count As Integer
Set rs = Currentdb.OpenRecordset("SELECT CRM_NBR FROM tblCorpRMR19_1 WHERE CRM_NBR ='" & Me.cmbCRM & "'")

Set db = Currentdb
count = 0
If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF
count = count + 1
rs.MoveNext
Loop

Me!revision = count + 1 >> works fine to here and updates the form with the new revision number but need the old record saved to the table first and then the revision number added or keep the old row and add a new row with the new revision number........

rs.AddNew
'Me.Requery
Forms("frms19_1").Form![revision] = Me![revision]
Else

Forms("frms19_1").Form![revision] = Me![revision]

Me!revision = 1
rs.AddNew
On Error Resume Next

rs.Edit
rs("revision") = Me!revision
rs.update
End If

rs.Close
Set rs = Nothing
Set db = Nothing

 

[kjv1611]

Looking...

In the mean time, I have a suggestion:
Instead of this:

    If rs.RecordCount <> 0 Then
        rs.MoveFirst
       Do Until rs.EOF
            count = count + 1
            rs.MoveNext
        Loop

Why not this:

    If rs.RecordCount <> 0 Then
        rs.MoveLast

Though I'm not terribly sure that you'd need to move to the last record before adding a new one, anyway... So, really, I would think you could remove the whole portion of trying to get to the last record. If you want to move to the last record, just use MoveLast, as it'll take less resources to accomplish than running through every record until you reach the end, especially, if the table gets large.

I'm looking at the other...

 

[kjv1611]

And, another point: I may be wrong, so correct me if so, but I'm pretty sure that if you set your recordset equal to a query (SQL statement in this case), then you can't actually add to it. You'd have to add the record to the original table, not the SELECT query.

That may actually be causing you all the headaches.

If you need the SQL statement as a recordset, then you could just use 2 recordsets - one for the SQL statement, and one for the table, for updating, or either use an APPEND SQL statement as apposed to rs.AddNew

 

[kjv1611]

Another point: You need to set the database before setting the recordset in order to avoid any errors there as well, you've got it reversed in your code. Well, at first you use Set rs = Currentdb.blablabla. If you're going to use the database object, you ought to do so from the start.

Basically, it would seem best to work from the top level down: Database, Table/Recordset, Field...

 

[kjv1611]

See notes/editions below..

Private Sub RevisionNumber()

Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim count As Integer

Set db = CurrentDb
strSQL = "SELECT CRM_NBR FROM tblCorpRMR19_1 WHERE CRM_NBR ='" & Me.cmbCRM & "'")
Set rs = Currentdb.OpenRecordset(strSQL)
count = 0

    If rs.RecordCount <> 0 Then
        rs.MoveLast
[green]'Instead of having all of the loops and all to calculate the number each time, why not assign one to each record as the ID, and increment it each time for the new record, as apposed to using the autonumber field.  Then, use the DMAX function to find the highest value, and increment one from there, maybe?[/green]     
        Me!revision = DMAX(RevisionID) + 1
         rs.AddNew [green]'This is where I would use an append query, or use the recordset for the table, as apposed to a recordset based on A SQL statement.[/green]
         'Me.Requery
    Forms("frms19_1").Form![revision] = Me![revision]
    Else
   
    Forms("frms19_1").Form![revision] = Me![revision]

        Me!revision = 1
        rs.AddNew
        [green]'Another point, here.  If you are adding a new record, you need to append a value to it, so you don't have a blank/NULL record at this point..  Depending upon what you are wanting to do, you may not even need to use the Recordset.AddNew at all, you may allow your form to do this for you, or use an append query.[/green]
      

        On Error Resume Next [green]'Why are you using this statement here? [/green]

rs.Edit
rs("revision") = Me!revision 
rs.update
End If

   rs.Close
   [highlight]db.Close[/highlight] [green]'This part is good, but you want to close the database just like the recordset.[/green]
    Set rs = Nothing
    Set db = Nothing

 

[lbunch]

This is what is working - but I am getting a blank row also added to the table. What in the code is making that happen???

Private Sub RevisionNumber()
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim count As Integer
Set rs = Currentdb.OpenRecordset("SELECT CRMNBR FROM tblRMR19_1 WHERE CRM_NBR ='" & Me.cmbCRM & "'")


icount = rs.RecordCount

Set db = Currentdb
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrydeleterevtbl"
DoCmd.OpenQuery "qry19_1tbl"
DoCmd.OpenQuery "qry19_1tbl_rev2"

If rs.RecordCount <> 0 Then
rs.MoveLast
'''Me!revision = count + 1

Me!revision = (revision) + 1
Me.RevisionDt = Now
Me.Requery

Forms("frmsemp").Form![revision] = Me![revision]
Else
'Me.Requery
Forms("frmsemp").Form![revision] = Me![revision]

Me!revision = 1
rs.Edit
rs("revision") = Me!revision
rs.update
End If
' End If
rs.Close
Set rs = Nothing
Set db = Nothing
Me.cmbCRM.Requery
Me.Combo191.Requery
End Sub

 

[kjv1611]

Before I try to answer that... you need to move around the CurrentDb/db notations. You see, where you have it now, the db variable is useless, b/c you've already made reference to CurrentDb without it. So, your db variable is not even being used by the code, other than just to take up memory.

It should be in this order:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("MyTableName")


Your code using the rs...


rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

 

[kjv1611]

Also, I think I already mentioned this as well, you aren't using your strSQL variable anywhere. So, just delete it altogether or use it. If you want to use it, here is how:

Instead of

Dim strSQL As String

Set rs = Currentdb.OpenRecordset("SELECT CRMNBR FROM tblRMR19_1 WHERE CRM_NBR ='" & Me.cmbCRM & "'")

Use

Dim strSQL As String
strSQL = "SELECT CRMNBR FROM tblRMR19_1 WHERE CRM_NBR ='" & Me.cmbCRM & "'"
Set rs = Currentdb.OpenRecordset(strSQL)

 

[kjv1611]

Not sure where the blank record is comming in at. Is the entire record blank, or is the revision filled in, and the rest of the record blank?

If the revision is filled in, and the rest blank, then I'd say just comment out the section:

rs.Edit
rs("revision") = Me!revision
rs.update

And step through the code or flat out run it and see if you are still getting the blank row.