Pulling and Pushing - this is work.

[stardusty]

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 (or its opposite), while having the integral ability to create new records from the form into the report generating table.


TblataMartClients 'linked and readonly
--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

Thanks to you attention.
Brent

 

[RichMorrison]

Brent,

To recap;
you want to insert fields into tblClientDataCollection,
some fields will come from tblDataMartClients,
other fields will come from manual entry.

Some fields in your form, NewReferrals, need to be bound to the corresponding fields in tblClientDataCollection. Other fields on the form are bound to fields in tblDataMartClients.

Then you need an "event sub" in your form. When you select a row from tblDataMartClients then the event sub runs. The code in the event sub will copy the values from the fields bound to tblDataMartClients to the corresponding fields bound to tblClientDataCollection.

Next, the user has to fill in the additional fields on the form. Finally you save a record to tblClientDataCollection.

HTH,
RichM

 

[stardusty]

Rich,

Thanks for some light in this tunnel.

Given that "Some fields in...form, NewReferrals, need to be bound to the corresponding fields in tblClientDataCollection. Other fields on the form are bound to fields in tblDataMartClients," is the tblClientDataCollection the better choice as the form's Record Source.

T4TH
Brent

 

[RichMorrison]

you wrote
<<
is the tblClientDataCollection the better choice as the form's Record Source.
>>

Yes, it is. That will let you bind fields on the form to fields in the table. You can create a control bound to a query that reads from tblDataMartClients; either a listbox control or perhaps a subform.

Then you move values from the DataMart-bound fields to the DataCollection-bound fields before saving a new record.

RichM