PDA

View Full Version : Automatically populating ID in input sub form (not dis-similar to the other post)



Ian Mac
08-10-2004, 11:37 AM
Hello All,

My problem is not to far removed from the other question about this.

I have a Main Employee form:
The user selects a Team, then Person from Comboboxes on the form.

I then have several subforms on a Tab Control.
The pages are historical data about the person, headings are
Hours
Contract End
Contract Type
Team
Grade. etc.

Each 1 is linked to a qry.

My Main Employee table has the following fields:
SysID, GivenName, Surname, EmpID, StartDate, LeavingDate, DOB

All the other 'Main' tables use the SysID for the linked key.
Have a StartDate and EndDate, then the data fields required. And also carry their own uniqueID

I have then set up a simple qry which pulls together a FullName.

The linked qry for each sub form is using all of the fields from the relivent tables + FullName (this is the criteria for the Requery)

When the Person is selected I Requery all the Subforms to display the information.
BTW I am using Continuous Forms. I have removed sysID and FullName from the forms.

----------------------------------------

The problems (or questions)

Like the other question I would like the sysID of the underlying Table to populate automatically from the Name on the sheet. (This stored in a Textbox currently but I will move it to a variable once everything runs correctly)
I can add a record no problem, with all of the details. But, currently there is no way to add the sysID to the table as I have hidden the field option form the Sub Form. Going back to the underlying table has 0000 as the ID, as I would expect as I have not entered this data.

How can acheive this?

-----------------------------------
Second question:

This is a doozy!
How would can I automatically populate the EndDate of the last record for that person (Based on Max StartDate) to be 1 day before the new change?

I would like this to be reflected on the Sub Form straight away for the user to see. I am assuming some Form refreshing will will achieve this but it is beyond my knowledge.


Sorry if that's all a bit long winded.

Thanks for any help.

Ian Mac

SJ McAbney
08-11-2004, 03:24 AM
For the first question it looks like you have created a one-to-one relationship when you actually want a one-to-many. The SysID should only be the primary key in the Employee table - it should be a foreign key in any other table.

i.e.

tblEmployees
EmployeeID
Forename
Surname

tblContracts
ContractID
EmployeeID
StartDate
EndDate
Hours



For the second question, lookup the DateAdd() function.

Pat Hartman
08-11-2004, 06:32 AM
1. If the foreign key in a subform (it should be hidden) is not populating, the problem is that the master/child links are not properly set. Access automatically populates the foreign key in a subform once the links are set.

2. Do not store the EndDate. Calculate in a query using the DateAdd() function as mile suggested. If you calculate it in the form's query, you won't need any code in the form, plus you will see appropriate values for all records if you use a continuous form. Remember unbound controls show the same value for ALL records on continuous forms.

Ian Mac
08-11-2004, 02:36 PM
Hi,

My tables and relationships are set up fine, in fact excellently. I have no issues on that front.
I have the solution for the first question from the other thread.
I actually can't see why the sysID should populate automatically without the BeforeInsert Event. I'm certainally willing to listen to ideas on that front.

I will have to look at DateAdd() and get back with this, looks promising.

Cheers,

Ian Mac

Pat Hartman
08-11-2004, 07:16 PM
I actually can't see why the sysID should populate automatically without the BeforeInsert Event. - Whether you can see it or not, that is the way subforms work. If you have opened a separate form, then that form is not a subform and in that case you need to put code in the BeforeInsert event to populate the foreign key. If you are calling the "popup" form a subform, that is what is causing your confusion.

Ian Mac
08-11-2004, 10:41 PM
- Whether you can see it or not, that is the way subforms work. If you have opened a separate form, then that form is not a subform and in that case you need to put code in the BeforeInsert event to populate the foreign key. If you are calling the "popup" form a subform, that is what is causing your confusion. But I'm not using a pop-up form, the person in the other thread was!

However, I now see where your coming from, I'm going to go back to the drawing board on this.

I can already see a possible problem brewing, not 1 I've asked yet.
In this particular instance of the form I don't wish anyone to be able to update the previous records for that person, but I still need the empty EndDate to update with the day before my new records StartDate.
Better still, only allowing edits on previous records by certain staff. I'm using GetUserName function for the security on the rest of this project, can I use it here.

I'm sorry if it seems like I'm asking questions before I've tested/played with any idea, but I don't have internet access at work due to the information I deal with.
I have to take printouts in from my questions and re-do the work there :eek:.

Cheers,

Ian.