duplicating control and incrementing another control

G

Guest

hi,

wish i could've responded sooner -- the suspense's been killing me.....but i
just got a chance to work on this. w/o further ado, the code's now looking
like the following which reflects the added snippet

Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click
Dim sql As String
Dim pn As Long
Dim ln As Integer

pn = [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]
ln = Nz(DMax("[Lesion Number]", "[Lesions: Non-Target]", "[Patient
Number] = " _
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]), 0) + 1

sql = "INSERT INTO [Lesions: Non-Target] ( [Patient Number], [Lesion
Number] )" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(ln) & " AS LN;"

Dim db As Database
Set db = CurrentDb
db.Execute sql
Set db = Nothing

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Lesion Number]=" &
CStr(ln)
Me.Bookmark = rs.Bookmark


' DoCmd.GoToRecord , , acNewRec

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub

AND the behavior's no different. clicking still results in an added record
to the ControlSource table but no apparent difference to the observer using
the form that has the Add Record button :-(

-ted
 
G

Guest

.....i have tried this both w/ and w/o "AddRecords" property enabled and it
made no difference.

what i have noticed however is that if i were to return to the same patient
number on the form with the AddRecord button afterwards, the newly added
record having the desired PN and incrementally correct LN was visible and
usable.

wonder why?
 
G

Guest

.....

i want to add the following to this thread:

a) i added docmd.requery immediately after the last batch of code and that
seemed to help bring the newly added record into view on the form when there
are others already there for a given PN

b) when adding a record when the PN does not pre-exist though, a small
message arrives after clicking the AddRecord button to the effect that
there's "No current record" or words to this effect

hope this is useful.

-ted
 
R

Rob Oldfield

D'oh. Yes, think I missed a requery. Haven't got a chance to test this now
but it may well be that you need the requery before you can move to the
record.... so try this...


Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click
Dim sql As String
Dim pn As Long
Dim ln As Integer

pn = [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]
ln = Nz(DMax("[Lesion Number]", "[Lesions: Non-Target]", "[Patient
Number] = " _
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]), 0) + 1

sql = "INSERT INTO [Lesions: Non-Target] ( [Patient Number], [Lesion
Number] )" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(ln) & " AS LN;"

Dim db As Database
Set db = CurrentDb
db.Execute sql
Set db = Nothing

me.requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Lesion Number]=" &
CStr(ln)
Me.Bookmark = rs.Bookmark


' DoCmd.GoToRecord , , acNewRec

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub


Ted said:
....

i want to add the following to this thread:

a) i added docmd.requery immediately after the last batch of code and that
seemed to help bring the newly added record into view on the form when there
are others already there for a given PN

b) when adding a record when the PN does not pre-exist though, a small
message arrives after clicking the AddRecord button to the effect that
there's "No current record" or words to this effect

hope this is useful.

-ted


Rob Oldfield said:
The trouble with the "*" is that it will add a new record immediately i.e.
before your code has a chance to kick in and set default values.

(I think so anyway, I haven't set up a form that actually used it in so long
that I don't really remember.)

Anyway, all you will need at the end of the previous code will be...

dim rs as recordset
set rs=me.recordset.clone
rs.findfirst "[Patient Number]="&cstr(pn)&" and [Lesion Number]="&cstr(ln)
me.bookmark=rs.bookmark

Any good?
Click to expand...
Click to expand...
 
G

Guest

yeah and hooray!

i tried the latest code fix and it worked w/o a hitch. i have modified the
user's file and hopefully it's going to work as well there.

it would be great to if it were possible to turn this thread into a
meaningful learning experience; give a man a fish and you feed him once. give
him a fishing pole and you feed him for life or something like that comes to
mind.

what all does this code do. how's it work?

(i can sense the user wanting to position the pointer when the new record
gets added onto the newest member and not the first one).

all the best,

-ted


Rob Oldfield said:
D'oh. Yes, think I missed a requery. Haven't got a chance to test this now
but it may well be that you need the requery before you can move to the
record.... so try this...


Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click
Dim sql As String
Dim pn As Long
Dim ln As Integer

pn = [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]
ln = Nz(DMax("[Lesion Number]", "[Lesions: Non-Target]", "[Patient
Number] = " _
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]), 0) + 1

sql = "INSERT INTO [Lesions: Non-Target] ( [Patient Number], [Lesion
Number] )" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(ln) & " AS LN;"

Dim db As Database
Set db = CurrentDb
db.Execute sql
Set db = Nothing

me.requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Lesion Number]=" &
CStr(ln)
Me.Bookmark = rs.Bookmark


' DoCmd.GoToRecord , , acNewRec

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub


Ted said:
....

i want to add the following to this thread:

a) i added docmd.requery immediately after the last batch of code and that
seemed to help bring the newly added record into view on the form when there
are others already there for a given PN

b) when adding a record when the PN does not pre-exist though, a small
message arrives after clicking the AddRecord button to the effect that
there's "No current record" or words to this effect

hope this is useful.

-ted


Rob Oldfield said:
The trouble with the "*" is that it will add a new record immediately i.e.
before your code has a chance to kick in and set default values.

(I think so anyway, I haven't set up a form that actually used it in so long
that I don't really remember.)

Anyway, all you will need at the end of the previous code will be...

dim rs as recordset
set rs=me.recordset.clone
rs.findfirst "[Patient Number]="&cstr(pn)&" and [Lesion Number]="&cstr(ln)
me.bookmark=rs.bookmark

Any good?
Click to expand...
Click to expand...
Click to expand...
 
R

Rob Oldfield

yeah and hooray is right


Ted said:
yeah and hooray!

i tried the latest code fix and it worked w/o a hitch. i have modified the
user's file and hopefully it's going to work as well there.

it would be great to if it were possible to turn this thread into a
meaningful learning experience; give a man a fish and you feed him once. give
him a fishing pole and you feed him for life or something like that comes to
mind.

what all does this code do. how's it work?

(i can sense the user wanting to position the pointer when the new record
gets added onto the newest member and not the first one).

all the best,

-ted


Rob Oldfield said:
D'oh. Yes, think I missed a requery. Haven't got a chance to test this now
but it may well be that you need the requery before you can move to the
record.... so try this...


Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click
Dim sql As String
Dim pn As Long
Dim ln As Integer

pn = [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]
ln = Nz(DMax("[Lesion Number]", "[Lesions: Non-Target]", "[Patient
Number] = " _
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]), 0) + 1

sql = "INSERT INTO [Lesions: Non-Target] ( [Patient Number], [Lesion
Number] )" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(ln) & " AS LN;"

Dim db As Database
Set db = CurrentDb
db.Execute sql
Set db = Nothing

me.requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Lesion Number]=" &
CStr(ln)
Me.Bookmark = rs.Bookmark


' DoCmd.GoToRecord , , acNewRec

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub


Ted said:
....

i want to add the following to this thread:

a) i added docmd.requery immediately after the last batch of code and that
seemed to help bring the newly added record into view on the form when there
are others already there for a given PN

b) when adding a record when the PN does not pre-exist though, a small
message arrives after clicking the AddRecord button to the effect that
there's "No current record" or words to this effect

hope this is useful.

-ted


:

The trouble with the "*" is that it will add a new record
Click to expand...
immediately
i.e.
before your code has a chance to kick in and set default values.

(I think so anyway, I haven't set up a form that actually used it in
Click to expand...
so
long
that I don't really remember.)

Anyway, all you will need at the end of the previous code will be...

dim rs as recordset
set rs=me.recordset.clone
rs.findfirst "[Patient Number]="&cstr(pn)&" and [Lesion Number]="&cstr(ln)
me.bookmark=rs.bookmark

Any good?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

#Name? error from Default Value expression 18
Error 3021 No Current Record 34
3 small vba routines 6
besides error 3021 the vba isn't working 2
vba code to open filtered form -- Run-time error 2448 1
RecordSet (I guess) woes 5
Bound Form Search 4
renumbering sequence when record(s) removed 2

Top