Form data to new record related table

[Marlene]

The record source is the table trmntrecords...

 

[Marlene]

Following the train of thought - I created a new form based on the table with the wizard. The new record navigation button disappeared when I added the default values for the fields....

 

[Cronk]

When you write "add default values for the fields", do you mean Default Value or the control source for the text boxes?

Just to make sure, can you add records manually to your table?

Do you have any relationships defined?

Finally, what version of Access are you using?

 

[Marlene]

I have enter the following in default values, for the text boxes in the form (not in the table itself: in mastitis id =Forms!Treatment Check off!mastitisid and in thetrtmntdate Date()
I can add records both manually and using the current form/ 2 subforms system (the one I wish to replace) where the user chooses a cow number from the combo box and enters data as required. The relationship between tables is 1- many, enforce referential integrity, cascade delete from mastitisid in the treatments table to mastitis id in the tblTreatmentsrecords. The opening of the form with default tables is just to a new record, not retrieving any matching records from the main form.

 

[Cronk]

Marlene,

It might be time for you to post your database.

If you have confidentiality agreements with your cows, you can delete the records from the tables.

 

[Marlene]

I'll pare it down to the relevant tables and put it up. It seems that it may be the placing of values into the form that is causing the issue. Is there perhaps another way to get those two values in as a new record I wonder?

 

[Marlene]

So heres a cut down version of the db with the appropriate tables. Please not that I have removed buttons and links not related to the issue.
Forms: Treatments Current Edit is the current system and working well. However in order to streamline I want to go to something more like Treatment Check off. The treatment date form is just a quick non-pretty version, as you can see! The process for treatments is as follows:
Cow gets first treatment of specified drug (and maybe more than one drug at the same time therefore new record for each drug), and then gets any number of subsequent applications of drug. At some point she gets last treatment (Treatment finished date) and at another later point she leaves the sick mob and returns to the milking herd. (Back) Cows may also be in the sick mob without receiving a drug, hence the no drug option. This keeps track of all cows in sick mob and reason.

 

[Marlene]

I have all the calculations that determine when a cow can go back in the milking herd based on withholding periods of drugs - that is given in other reports and forms, as are details of current treatments. Its just trying to streamline daily data entry for repeated treatments to increase the likelihood that it will actually get done. The feedback I'm getting is that it takes too long, especially over these last two months which is the busiest time of the year.

 

[Cronk]

The problem is that in using default values you are only positioning the form on a new record. It's exactly the same as opening a table, going to the new record line and closing the table. Look at the RecordsID field - is says (New)

You can test this by over-typing any one character in the date field. Immediately the key is pressed, the new RecordsID value appears.

I don't use macros so can't offer you any solution there. I do use VBA and you can get the result I think you want by opening the TreatmentDate form in design mode, opening the Properties window, select Form in the top left hand combo box, select the Event tab. In the line that says On Load, click the ellipses button, select code builder and immediately after the line reading
Private Sub Form_Load()

type
Me.trmntdate = Date()

(The brackets will probably disappear but don't worry.)

This causes data to be entered in the date field and now we have commenced editing a new record, rather than just displaying it.

Let's know how you go.

 

[Marlene]

That worked perfectly! Thank you very much indeed. Can you explain why I can set the default value in the mastitisid field to equal that of the current record on the main form but cannot do so for the date? To help me understand the workings of it all a bit further...

 

[Cronk]

Instead of inserting the date as the form was opened, it could have been your id field, or both.

The point is something had to be entered, not just show the default value. Either the system in VBA, or the user.

You can set the default for the treatment date in the table design, but that does not enter the date in a new record.

 

[Marlene]

Thank you again. I understand the process now (at least for that challenge) and can apply it to several other forms.