Update code not running

[td]

Looking for ideas on things that might be wrong w code for a button on a
form. I'll simplify the description a bit, but I think this should have the
main points. I'm not an expert programmer, so any suggestions on better ways
to do this are appreciated.

I have a database with a sequenial index number. A lot of what we do with
this database form involves users updating old data (i.e., existing records),
but sometimes there is a new record added to the database with a new index
number. Sometimes the new record is almost the same (except for one or two
fields) as the previous record, so I wanted a button that would copy all of
the rest of the data from the previous index number. Simple, right?

The button I made to solve this problem does the following: gets a recordset
from the previous record where index = index minus one, uses data from that
recordset to assign values to a few variables. Then an update statement is
executed for the current record (where index = index) to write those variable
to specific fields of the current record.

While debugging, I noticed that the button doesn't seem to work if the
starting record is a new record. It works fine for existing records, but
doesn't do anything when clicked while viewing a new record from the form.
You can defeat this error in some simple ways. If you create a record, go
back to a previous record, and then go forward to the new record again, then
the button works when clicked. There's something about the newness of a
freshly new record that won't let my code work though. I'm suspecting that
Access hasn't written a new record to the table yet, so that the update
statement doesn't go anywhere (the WHERE index = index# statement doesn't
return any records to be updated).

The weird thing is that it has worked a couple times with new data, so it's
a little sketchy. The first time I saw this, I thought maybe I should write
in a Save Record line of code prior to the rest of the button code running,
to make sure the record was written to the table. I couldn't find code for
Save Record, so I thought I'd try out just a Refresh command. I wrote that
Refresh command in there and it solved my problems... for about five minutes.
I kept testing my code, and it worked several times with new records, and
then started doing nothing again. Now it never works. V confusing.

Anyway, that's what I got. Any insight is greatly appreciated. In
particular, any idea how to issue a Save Record command within code? Or is
there a simple way to make Access write the data in the current record to the
table before it runs the rest of the button code?

Thx,
T

 

[td]

BTW, I realized I hadn't searched properly for "Save Record." So I did that
and saw that I could use a [form_name].Dirty = False Command to save. But
this did not work... Still does nothing when button is pressed while viewing
a new record.

 

[td]

Here's some snippits of my code, if it helps. 10 points to the person who
can resist the urge to make fun of what a bad programmer I am.

The actual (full) code updates a lot of fields, but I deleted all of them
except APPLICANT below, just for simplicity.

_ _ _ _ _ _

Private Sub btncopy_Click()

Dim myRoot As String
Dim myCurrentSub As String
Dim IntCurrentSub As Integer
Dim myPreviousSub As String
Dim IntPreviousSub As Integer
Dim MyExists As Boolean
Dim NullApplicant As Boolean
Dim strMsgBox As String
Dim myMsgBox As Integer
Dim sqlstr As String

Dim varAPPLICANT As Boolean

Dim myconn As ADODB.Connection
Dim myrst As ADODB.Recordset

myRoot = DOCKET
myCurrentSub = SUBDOCKET
IntCurrentSub = myCurrentSub 'change 000 to 0, or 001 to 1

'save current record
Form_QF.Dirty = False

If IntCurrentSub <= 0 Then

MsgBox ("Subdocket must be greater than 000 to copy previous subdockets")
End

End If

IntPreviousSub = IntCurrentSub - 1

'code to deal w the fact that subdockets always have three digits, even when
000, 001, etc.
If IntPreviousSub < 10 Then
myPreviousSub = "00" & IntPreviousSub
ElseIf IntPreviousSub < 100 Then
myPreviousSub = "0" & IntPreviousSub
ElseIf IntPreviousSub < 1000 Then
myPreviousSub = IntPreviousSub
Else
MsgBox ("Current subdocket out of range 000 to 999")
End
End If

Set myconn = New ADODB.Connection
With myconn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open ("P:\QF\QF Database\QF.mdb")
End With

sqlstr = "SELECT * FROM QF WHERE DOCKET = '" & myRoot & "' and SUBDOCKET =
'" & myPreviousSub & "'"
Set myrst = myconn.Execute(sqlstr)

If myrst.EOF Then 'if no record w previous subdocket exists
MsgBox ("There is no previous subdocket from which to copy data")
End
End If

Call popdata(myRoot, myCurrentSub, myPreviousSub)

Form_QF.Refresh
Set myrst = Nothing

End Sub
_ _ _ _ _ _

Private Sub popdata(myRoot As String, myCurrentSub As String, myPreviousSub
As String)

Dim myconn As ADODB.Connection
Dim myrst As ADODB.Recordset

Dim sqlstr As String

Dim myApplicant As String
Dim myCity As String
Dim myCounty As String
Dim myState As String
Dim myQFType As String
Dim myCapacityKw As Double
Dim myEnergySource As String

Set myconn = New ADODB.Connection
With myconn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open ("P:\QF\QF Database\QF.mdb")
End With

Set myrst = myconn.Execute("Select * from QF where DOCKET = '" & myRoot & "'
AND SUBDOCKET = '" & myPreviousSub & "'")

sqlstr = "UPDATE QF SET "

If Not (IsNull(myrst!APPLICANT)) Then
myApplicant = myrst!APPLICANT
sqlstr = sqlstr & "APPLICANT = '" & myApplicant & "'"
End If

sqlstr = sqlstr & " Where DOCKET = '" & myRoot & "' AND SUBDOCKET = '" &
myCurrentSub & "'"

myconn.Execute sqlstr

Set myrst = Nothing

End Sub

 

[Rick A.B.]

The button I made to solve this problem does the following: gets a recordset
from the previous record where index = index minus one, uses data from that
recordset to assign values to a few variables.  Then an update statement is
executed for the current record (where index = index) to write those variable
to specific fields of the current record.

Just glanced at your question did not go through your code, but if
you're using an update statement to update the record that won't work
with a new record. You would need to use and Append to insert a new
record into a table.

Rick