Form data to new record related table

[Marlene]

I have a form based on a table which includes the mid field. I want to have a macro that takes the value of the current mid, and makes a new record in a 1-many related table (consisting of record id (auto), mid and trmntdate), paste the mid and insert the current date.For the life of me I cannot get it to work? The process should be something like:
copy mid value, add new record to related table, paste value in mid, insert current date in trmntdate, save. I've tried append queries, experimented with copy etc, dabbled blindly with VBA and not got anywhere. Any help would be greatly appreciated..

Marlene

 

[MarkK]

What is your overall purpose? What is "the mid field?"

 

[Marlene]

Cows have a 3,4 or 5 day treatment cycle for infection. The main table on which the form is based holds information concerning the cow, drug, illness, date of first treatment etc and mid is the mastitisid - the id record for these treatment details. She may have had previous treatments with a different drug or at a different time, or for a different illness. After the subsequent treatment, the cow may or may not have a further treatment, once each day.
Currently staff have to retrieve the treatments record for cows that are currently in the treatment program, one by one, by cow number. My new form retrieves a list of currently treated cows, in continuous forms, to allow them to easily enter what has happenned today - eg. yes, she has had another treatment (button that adds todays date and current treatment identifier to the related table and the bit causing me problems), or yes, she has finished treatments or yes she is now returned to the usual milking herd.
If her treatment is finished, she will not appear on the form.

 

[Pat Hartman]

If you use a main for the cow information and a subform for the treatment information (sort in descending order so the newest stuff is first) and the master/child links are correctly set, Access will automatically populate the foreign key in the treatment records.

 

[MarkK]

You should be able to do this with wizards. Create a form based on the master table. Drag the child table onto that form (in design view), and Access should automatically create a subform based on the child table and link it automatically with the master table in the main form. Do that, and the examine the objects the wizard made, and either modify those, or include those features into your existing objects. Particularly, notice the LinkChildFields and LinkMasterFields properties of the subform control, which synchronize the records in the subform with the current record in the main form.

Also, to have a table automatically fill in the current date in a field when a record is added, set the DefaultValue property of a field in a table in design view to "Date()". I believe that covers your two requirements.

In the subform, simply add a new record by typing data into an empty field in the >* new record at the end of the form

hth

 

[Marlene]

However, I need to see all the cows being treated today - and so I cannot have continuous forms with subforms? The form I currently have is as you describe more or less, but I am looking to make it quicker, and also suitable for a touch screen, so users with dirty hands can simply tap the button for data entry rather than in the current form - screenshots attached.

 

[MarkK]

But this thread is about creating a new child record and automatically populating the parent record foreign key value, and automatically entering today's date. Do we have those problems solved?

For record navigation in the parent form, I commonly create a separate pop-up form that shows a list the user can select from, but there are a few ways to solve that problem, including an unbound list on the parent form.

To make something "quicker" we'd need to know what about it is slow.

 

[Cronk]

You could use a list box showing cows treated today and below a sub form below which shows treatments for any cow highlighted in the list box.

 

[Marlene]

The slow issue is that the user has to select each cow and then enter the date of treatment. Use of the keyboard and mouse puts other constraints on use, as staff are usually wet/dirty. It would be so much quicker if they could look at the cow list in its entirety and activate the appropriate option for the day (add todays date, finish, return). Perhaps it would have been better design to have the treatment details and treatment dates all on one table -ie trmnt1, trmnt 2 and so on. Currently they are in 2. The issue there is that there can be anywhere between 1 and say 6 treatments, depending on drug and response .

 

[Cronk]

You have treatments in separate tables? Not good design.

Your database should have one table, for example
tblTreatments
TreatmentID (PK)
CowID (FK to tblCows)
TreatmentTypeID (FK to lookup treatment table listing each treatment)
TreatmentDate
Comments
[Other relevant fields]

As I wrote before, a list box at the top of your form would list the cows, select one and its treatments are shown in a continuous sub form below.

A combo box on the sub form would be bound to TreatmentTypeID which would display the particular treatment.

 

[Marlene]

I have a number of treatment dates attached to one treatment event. I didn't wish to repeat information attached to an event every time the cow is treated, hence the 2 tables. One has: id, cow, firstdate, drugid (link to drug info), amount, reason, memo, daystreatmentrequired, lastdate, and back. Table 2 has autoid, id and treatment date. There is no need to have a table with cow numbers as this is the only part of the database that records individual cow events, Other proprietary systems handle those events.
The staff currently select a cow and see its treatments - that is what I wish to streamline and change as it takes too long, and doesn't suit the touch screen interface.

 

[Cronk]

So have a second sub form.

List box at top shows all cows currently being treated.

First sub form shows all treatments for cow selected in the list box.

Second subform shows all of the daily information for the treatment selected in the first subform, with the default being the current treatment.

My curiosity but how do you distinguish cows? That is, what is an example of what data goes in the cow field?

 

[Marlene]

Its just the cow number in the cow field.
I see what you mean now - I was trying to get away from having to select cows in the list box one by one, but rather have the whole list as continuous forms - worst case scenario is maybe 30 animals, but mostly less.
For your interest, the treatment form attached is the first data entry screen. Subsequent treatments go into current form. My current form has two sub forms but still depends on the user choosing each cow individually in order to see/enter data

 

[Marlene]

I've changed direction - my main form has a button that brings up a form from a related table. There are three fields in the related table - autonumber (primary id) mastitisid and trtmntdate. I have set the second form to open with default values in masitisid (equal to the field of the same name on the main form) and the current date in trtmntdate. This works very reliably BUT what do I need to do to get a new autonumber to appear so I can save the record? I have tried moving to a new record before save and close, but get an error. I have opened the second form both in add mode, or in edit mode, then add new record with the same issue.

 

[Cronk]

I think it might be best if you posted your table structure so we can see how they relate.

 

[Marlene]

Have uploaded the tables showing relationship and an example of the data. Hoping it helps

 

[Cronk]

An aside to start with. I normally reference the unique field in a table with its name eg I would not use mastitisid, but rather TreatmentID. However...

To show cows under treatment, create a query based on tblTreatments with criteria DrugLastDate >=Date()

This will exclude treatments for cows where the last treatment date is in the past.

Next in this form, have a sub form based on tblTreatmentsRe.. linked to mastitisid where the treatment dates can be added.

If you want a list box showing all currently treated cows, use the query above for the list box and have two sub forms, first based on tblTreatments and second on tblTreatmentsRe....

 

[Cronk]

Aaron

That question was asked in Post #2 and answered in #3

 

[Marlene]

Yes indeed - we started just recording mastitis but the scope widened... I have attached my new forms. The main form is based on the treatments table (drug name is a dlookup) and shows every cow who is in the treatment herd, whether the treatments are finished or not (there is witholding period after the last treatment so that drug residues are eliminated from milk). The mastisitid (primary key for the treatments table) is showing far right. You can see for cow # 327 I have retrieved a form based on the trtmntrecords table in add mode, with default values of mastitis id (814) and current date inserted. However I cannot save this record so that it stays in the table. I though if I moved to a new record as part of the closing procedure it would save this one, but no.

 

[Cronk]

You can see from the bottom of the Treatment form that the add new record navigation button is missing.

What is the Recordsource for this form? (In design view, go to Form properties and select the Data tab.)