get value from main form to put in first record in subfomr

[RangerJeff]

I am still new to access, so I might be just missing something here.

I have two tables, BoaterPermits and BoaterLocations with a one to many relationship. Ihave created a data entry form with BoaterPermits the main form and a subform for entering multiple locations.

When I enter the subform, I want to get the Entry_date from the main form and put it in the ArrivalDate field in the first record of the subform, or location records before entering the rest of the data for the location



I thought this would be simple, but can't get it right. I am hoping it is just a naming issue.

I tried this:

Private Sub Boater_Permit_Locations_V2_subform_Enter()

Me.[Boater_Permit_Locations V2 subform]![ArrivalDate] = Me.[boaterPermit subform V3]![Entry_date]


End Sub

but keep getting error message. I am not sure this is the best way, and open to suggestions.

Here is a picture of the two forms

[CJ_London]

don't know which form is which or whether your code and event is correct but you need to use .form

Me.[Boater_Permit_Locations V2 subform].form.[ArrivalDate] = Me.[boaterPermit subform V3].form.[Entry_date]

[RangerJeff]

don't know which form is which or whether your code and event is correct but you need to use .form

Me.[Boater_Permit_Locations V2 subform].form.[ArrivalDate] = Me.[boaterPermit subform V3].form.[Entry_date]

Thanks Ajax

Here is some explanation. I am not expecting anyone to write the code, but I could really use some direction. I have tried a couple of different methods, so hoping someone and give me some hints.

Main form: boaterPermit subform V3 - This is really a subform within another main form but for testing purposes I made it standalone.
Subform: Boater_Permit_Locations V2 subform to "boatePermit subform V3"

Field in main form: Entry_date
Field in Subform: ArrivalDate

I still learning the naming conventions for statements. I used .form but still getting same error message. I may be using the statement incorrectly or it may be the wrong method. A found this example online, but you never know. What I am trying to do is get the value in a main form (BoaterPermits table), Entry_date field and use it for the ArrivalDate field first record in the subform (Boater Locations table). The main form is the master and the subform is the child. The subform may be used to create several record linked to the main form.

The more I read the different books and look at examples on line the more I get confused. I use to do a lot of programming, and I am still going thru the books, but working on the database at the same time. I am just trying to add some validation rules so the people doing the data entry can make mistakes by pre-populating fields where I can. It works okay without any of the checks right now.

All help will be appreciated.

I know the names are confusing but this might help

[CJ_London]

to help we need to understand the the why of what you are doing. You have explained what you are doing, but we don't know why or the purpose. It may be that all you need to do is set the default value of arrival date to be entry date.

For context, suggest explain what your app and this bit of it is supposed to do.

[RangerJeff]

Thanks for taking the time, hopefully this will help

I work at an island which is in a park. If a boater wants to stay overnight on the island they need to get a boater permit, which currently we do manually and then enter the info into a spreadsheet to get the stats. As part of the permit they need to tell us where they are staying each night. THere are 15 docks they can stay at and each as a limit of how many consecutive nights. We do the same for camping, which is a separate permit, but works the same way.

So when the Ranger issues the permit, they gets the date they entry the park and the day they leave plus some other information. They then give us an itinerary where they are saying each night which goes on the permit. They then print it out and the boater keeps it on their boat or phone if we email so the rangers on patrol can check it.

The entry date is only for the first record. Each record after that should have the next date. Ideally I would want to create the number of records , one for each day with the date, but the ranger would still have to enter which location they are staying at.

The forms work fine, but trying to add some logic to reduce the chances of errors. So forcing the first record to have the entry date reduces errors and makes it more user friendly. The users will be Park Ranger and not IT people, so making it as user friendly as possible is important. Setting the date is the one big thing they asked for. I have some checks already, like Entry date > issue date and exit date > entry date and all these fields are required.

Not sure how to getthe value in one table check the value in another table. I know what I want to do, it just figuring on the proper way, naming method, of putting the field locations into a VBA script. Unfortunately where I am located, not other programmers around ( actually I am a Park Ranger but did work in Silicon Valley for 30 years but new career LOL)

Does this clear things up?
thanks
Jeff

[CJ_London]

thanks that helps to put it in context.

So now I need to know about your table structure - I'm assuming permitData is your parent record and has a hidden PK field and the arrival data is the child record with a FK field to link back to the parent record (PK=primary key, FK=Family or Foreign Key)

There are a number of 'what if' questions - for example what if the boater stays more than one night - does the ranger enter each location night as it occurs? or is there a gap in the dates? I'll assume the former. What is to prevent the user from changing dates in an uncontrolled manner?, etc

I think the formula you need is something like

nz(DMax("ArrivalDate","tblArrivals","PermitFK=" & Parent.PermitPK)+1,Parent.EntryDate)

which is saying - 'get the last date in tblArrivals for this permit and add 1 and if there isn't one then use the entry date for the permit on the parent form'

And I would put this code in the arrivals form current event with the proviso it is only triggered if it is a new record

Private Sub Form_Current()

    if me.newrecord then arrivalDate=nz(DMax("ArrivalDate","tblArrivals","PermitFK=" & Parent.PermitPK)+1,Parent.EntryDate)

End Sub

I would also change the arrivaldate control tab order to make sure the user does not tab into this control by accident and change the tab stop to no (both found on the other tab for the control properties.

This code is triggered when the use clicks on the new record row of the subform - which from your description seems a more natural way of executing it. You may want to add additional code to prevent new records being added once the exit date-1 has been created. That code would be something like this - again in the arrivals form current event after the newrecord line

me.allowadditions=arrivalDate<Parent.ExitDate

This line on it's own is insufficient on it's own - you will need additional code to stop allowing additions when the form is first opened for example - but it should point you in the right direction

[RangerJeff]

thansk,

Give me a good direction. The rangers are suppose to enter each night with no gaps. and suppose to stop when the number of days is reach, but , doesn't always happen, especially if busy. We also understand that the intintary can change due to weather or full docks., but it does help us also if we need to located a boat or even a camper.

I will paly arond with thi. When I get it working, I will post here.

thanks a lot.

Jeff

It seems like a lot of the examples I find online don't give clear examples.COnsider the thousands lof line of Basic I wrote 30 years ago, I thought this would be easy. Oh Well, fun learning something new.

[CJ_London]

online examples are generally intended for a wide audience and by their nature cannot be too focused on any one situation. They demonstrate principles, not provide solutions.

The more relevant information you provide, the more focused a response you will get. This includes an overview of the business and the requirement (e.g. per your post #7) example data, outcome required from that example data (not a description of the outcome) table/field names, form/control names, relationships etc. If you provide vague information, you'll get vague answers.