Pulling from one table and pushing into another.

[roadman]

Hi all,

I am unpolished in access and have run upon a challenge.

What I have coming in are new referral sheets for Client services. Most of the time, these referrals are from previously tablulated clients. Their basic information can be pulled off of a readonly link to table "DataMartClients". With an age criteria, the linked table will have 7000 records with an ID number, name, and birth date, but no other information respective to the referrals sheets.

New referrals have to be processed within a specific period by statute. This database is being set up to generate a report of referrrals which are nearing the end of that period.

When the new referrals arrive, they do not have the client's ID number, nor Birth Date. I would like new referrals entered into a form "NewReferrals" that appends a table "ClientDataCollection" from which a report is generated.

I would like the form "NewReferrals" to autopopulate client "ID", client "FirstName", client "DOB" from the readonly linked table "DataMartClients" by pulling up the client's "LastName" in a combo box. These data items would then need to be pushed into table "ClientDataCollection", along with 5 more fields from the form "NewReferrals" which will need to be hand entered.

Where my confusion has the better of me, is populating form "NewReferrals" from one table and pushing the records to the second table, while having the integral ability to create new records from the form into the second table.


TblataMartClients
--Field1:CLIENT_NUMBER
--Field2:CLIENT_NAME
--Field3:Birth_Date

Tbl:ClientDataCollection
--Field1:ClientID
--Field2:LastName
--Field3FirstName
--Field4OB
--Field5:ReceivedDate
--Field6:AssessmentSent
--Fiedl7:AssessmentReturned
--Field8:AssessmentCompleted
--Field9:SchoolDistrict
--Field10:AssignedClinicianLastName
--Field11:AssignedClinicinaFirstName

Frm:NewReferrals
--Combo:LastName
--Txt:FirstName
--Txt:ClientID
--TxtOB
--Txt:ReceivedDate
--Txt:SchoolDistrict
--Txt:Residency
--Txt:AssignedClinicianLastName
--Txt:AssignedClinicianFirstName

 

[randysmid]

Hi,
Using this code, you can programmatically find the last ClientID in your table:

'************************************************
'this code will get ALL the ClientDataCollection
'records, then read the last one for ClientID #
' AND, create a new clientID with strNewClientID

Set db = CurrentDb.OpenRecordset("Select * from ClientDataCollection ORDER BY ClientID)
Dim strNewClientID as Single 'ClientID is numeric???
'move to the last record in recordset
db.MoveLast
strNewClientID = db![ClientID] +1
'************************************************


'************************************************
'Loop through NewReferrals table, then write
' new info to ClientDataCollection
Set db2 = CurrentDb.OpenRecordset("Select * from NewReferrals&quot
Do Until db2.EOF
'add new records to ClientDataCollection
Set db = CurrentDb.OpenRecordset("Select * from ClientDataCollection&quot
db.AddNew
db![ClientID] = strNewClientID
db![LastName] = db2![LastName]
db![FirstName] = db2![FirstName]
db![DOB] = db2![DOB]
db![ReceivedDate] = db2![ReceivedDate]
'add other fields here BEFORE the "update"
db.Update
db2.MoveNext
strNewClientID = strNewClientID + 1
Loop
'************************************************

HTH,
Randy Smith
California Teachers Association