Default (Last) record set for Adding New Record Set

Default (Last) record set for Adding New Record Set

Post by precisionf » Thu, 31 Jul 2003 22:03:03


Hi I have been using Access for many years, but only recently
adventured into the realms of coding in Access VB.
I have a main form [frmclient] that has many sub forms and each sub
form has many records.
I need to create a recordset? that picks up the default values of the
last 'recordset' from the previous subforms (so that the user does not
have to re-enter all criteria).
I have setup a query based on one of the subform tables (as a starting
point), sorted descending by date and set the query property Top Value
to 1.
However, I don't know where to go from here - I have tried Append
Query but unsuccessful.

Thanks in Advance.
Heather
 
 
 

Default (Last) record set for Adding New Record Set

Post by Fletcher A » Thu, 31 Jul 2003 23:32:02

Heather" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

We don't know anything about your table structure and can't guess how many
related tables you have or what they contain. Imagine, though you had
tblClients and a related table tblStaff. You could write a function which
takes a ClientID and duplicates it with all the related staff records and
returns the ID of the duplicated client if successful or returns a 0 if not.

' *************************************
Function CloneClient(lngOldID As Long) As Long

On Error GoTo Err_Handler

Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim rstRead As DAO.Recordset
Dim rstWrite As DAO.Recordset
Dim strSQL As String
Dim lngNewID As Long

Set wks = DBEngine.Workspaces(0)

wks.BeginTrans

Set dbs = CurrentDb

' Add new main record
' ********************
strSQL = "SELECT * FROM tblClient"
Set rstWrite = dbs.OpenRecordset(strSQL, , dbAppendOnly)

rstWrite.AddNew
rstWrite!ClientName = "New Client"
lngNewID = rstWrite!ClientID
rstWrite.Update

rstWrite.Close

Set rstWrite = Nothing


' Add new related records
' ***********************
strSQL = "SELECT * FROM tblStaff WHERE ClientID=" & lngOldID
Set rstRead = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)

strSQL = "SELECT * FROM tblStaff"
Set rstWrite = dbs.OpenRecordset(strSQL, , dbAppendOnly)

While Not rstRead.EOF
rstWrite.AddNew
rstWrite!ClientID = lngNewID
rstWrite!Position = rstRead!Position
rstWrite!FullName = rstRead!FullName
rstWrite.Update
rstRead.MoveNext
Wend

wks.CommitTrans

CloneClient = lngNewID

Exit_Handler:

If Not rstWrite Is Nothing Then
rstWrite.Close
Set rstWrite = Nothing
End If

If Not rstRead Is Nothing Then
rstRead.Close
Set rstRead = Nothing
End If

If Not dbs Is Nothing Then Set dbs = Nothing

If Not wks Is Nothing Then Set wks = Nothing

Exit Function

Err_Handler:
lngNewID = 0
wks.Rollback
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

' *************************************



You could then have a button on your form called cmdClone which calls the
function, e.g:


' *************************************
Private Sub cmdClone_Click()

On Error GoTo Err_Handler

Dim lngID As Long

lngID = Me!ClientID

lngID = CloneClient(lngID)

If lngID > 0 Then
Me.Requery
With Me.RecordsetClone
.FindFirst "ClientID=" & lngID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

' *************************************





 
 
 

Default (Last) record set for Adding New Record Set

Post by Heather Be » Wed, 06 Aug 2003 00:23:17


I have managed to create a new "Last" record based upon the information
above - many thanks. However, my structure is a little more indepth and
I have been unsuccessful in updating two fields.

Structure:
tblclients related to tblCaseHistory (manual CaseNo on first entry. I
have written code to Add new record in the Case History screen which
automatically increments on further new 'referral' records for each
client ie 100/1; 100/2 etc.

Each tblCaseHistory is related to various sub tables ie tblchDisability,
tblchMedical etc. (1-many relationship) to accommodate changes to
records.

For each 'incremental' referral record, a 'last' record for each of the
subset tables ie tblchDisability is required (to prevent data entry
duplication). I have developed a query displaying top 1 Descending(ie
for tblchDisability), which after following the instructions - updates
all (last record) fields to a new record.
My problem is that CaseId does not update from the new record in the
tblCaseHistory and the new chDisabilityId reverts to 0. My code is as
follows:

Button:
If IsNull(Me.ExitDate) = False Then
If Forms!frmcasehistory.ClientNo.Value <> 0 Then
Forms!frmcasehistory.ClientNo.Value = [ClientNo] + 1
Me.Requery

Dim lngId As Long
lngId = Me.ClientId
lngId = CloneClient(lngId)

If lngId > 0 Then
Me.Requery
With Me.RecordsetClone
.findfirst "Clientid=" & lngId
Me.Requery
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

Function CloneClient(lngOldId As Long) As Long
On Error GoTo Err_handler

Dim Updatewks As DAO.workspace
Dim BDVASdb As DAO.Database
Dim rstRead As DAO.Recordset
Dim rstWrite As DAO.Recordset
Dim strSQL As String
Dim lngNewId As Long

Set Updatewks = DBEngine.Workspaces(0)
Updatewks.BeginTrans
Set BDVASdb = CurrentDb

'..................................
' Add and Append Record
'..................................

strSQL = "SELECT *FROM qryDisabilityLast WHERE clientid=" & lngOldId
Set rstRead = BDVASdb.OpenRecordset(strSQL, dbOpenForwardOnly)

strSQL = "SELECT * FROM qryDisabilityUpdate"
Set rstWrite = BDVASdb.OpenRecordset(strSQL, , dbAppendOnly)

While Not rstRead.EOF
rstWrite.AddNew
rstWrite!chDisabilityId = lngNewId
rstWrite!CaseId = rstRead!CaseId
rstWrite!Date = rstRead!Date
rstWrite!DVOId = rstRead!DVOId
etc.
rstWrite.Update
rstRead.MoveNext

Wend
Updatewks.CommitTrans
CloneClient = lngNewId

Exit_Handler:
If Not rstWrite Is Nothing Then
rstWrite.close
Set rstWrite = Nothing
End If

If Not rstRead Is Nothing Then
rstRead.close
Set rstRead = Nothing
End If

If Not BDVASdb Is Nothing Then Set BDVASdb = Nothing
If Not Updatewks Is Nothing Then Set Updatewks = Nothing

Exit Function

Your comments/help would be greatly appreciated.
Many Thanks.

*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!
 
 
 

Default (Last) record set for Adding New Record Set

Post by Fletcher A » Wed, 06 Aug 2003 07:26:47

Heather Bertucci" < XXXX@XXXXX.COM > wrote in message
news:3f2e7a65$0$200$ XXXX@XXXXX.COM ...


Hi Heather
There may be other issues involved, but the first thing that strikes me is
this:
In my example, the function CloneClient takes as a parameter the ID of the
client on which the new record is based ('cloned from'). The function
returns the ID of the cloned client if no error occurs otherwise all changes
are rolled back and it returns zero.
In my version, lngNewID is allocated just after rstWrite.AddNew ( a new
autonumber will be generated)

rstWrite.AddNew
rstWrite!ClientName = "New Client"
lngNewID = rstWrite!ClientID
rstWrite.Update

Now as far as I can see, you are not planning to use an autonumber, but
where in your code is lngNewID assigned? It seems to me it will remain
zero.
If you are planning to have non-autonumbers as primary keys, then you may
need to write some functions such as NewCaseNo(ClientID as long) which will
generate the next number for you.

HTH

Fletcher